Einsatz von View's für Auswertungen

Zweck und Nutzen eines View

Ein View ist nichts anderes als eine "Sicht" auf bestimmte Datenbereiche, analog wie man es normalerweise durch ein select-Statement auch macht. Im Gegensatz zu einem select-Statement, das man an die Datenbank absetzt und dafür die entsprechenden Daten erhält, ist der View ein in der Datenbank gespeichertes select-Statement, das auf die Abfrage seiner Daten sich selbst aufbereitet. Ein View erlaubt somit spezifisch für bestimmte Anwender die Daten zu gruppieren und für diese das ganze fix zu installieren.

Der View zeigt sich gegen aussen wie eine normale Tabelle, auf die jede Art von select-Statement und je nach Zugriffsrechten und Aufbau des View auch update-Statement durchgeführt werden können. Für den Anwender ergibt sich somit beim Einsatz eines View kein neues technisches Element, sämtliche Programme und Funktionen können in der gewohnten Weise eingesetzt werden.

Ein View ist dann ein nützliches Hilfsmittel, wenn u.a. folgende Kriterien vorliegen:

• Die Auswahl der Daten über längere Zeit in gleicher Form erfolgen kann. Da ein View nur seine in ihm selbst enthaltenen Selektionskriterien kennt kann er nur schlecht oder mit entspr. Aufwand dynamisch an neue Auswahlsituationen angepasst werden.

• Komplexe Daten in einer einfachen (flachen) Form gezeigt werden sollen. Eine sog. De-normalisierung der Datenbankstruktur erforderlich ist. Dies ist ein sehr häufiger Anwendungsfall.

• Nicht normalisierte Daten in eine Normalform ungesetzt werden sollen (eher selten)

• Die eigentlichen Tabellen vor dem Anwenderzugriff geschützt werden sollen. Der View erlaubt die Anzeige präziser Ausschnitte der Daten und dies nur an berechtigte Personen. Zudem kann der View nur eine Anzeige erlauben ohne dass die darunter liegenden Tabellen davon betroffen sind.

Aufbau eines View

Bevor ein View erstellt wird, muss das entsprechende select-Statement aufgebaut und getestet werden. Dieser Arbeitsschritt ist absolut identisch mit dem Aufbau eines üblichen select-Statement, das zur Auswertung von Daten genommen wird. Es ist auch möglich geeignete select-Statement anderen View, Prozeduren, Triggern, Systemprozeduren usw. zu entnehmen. Zu beachten ist einzig, dass ein View keine temporären Variablen und keine temporären Tabellen referenzieren kann und in seinem Umfang auf ein select-Statement begrenzt ist.

Löschen eines View

Es gehört zum guten Stil eines SQL-Scripts, dass zuerst geprüft wird ob das Objekt schon existiert und dies gelöscht wird. Dies gilt für Tabellen, Indexe, Prozeduren, usw. und natürlich auch für einen View. Dieser Teil gehört eigentlich nicht zum View selbst sondern dient einzig der sauberen Verwaltung der Objekte und der späteren Möglichkeit eine Aenderung problemlos nachladen zu können. Ein solches SQL-Script für einen möglichen Script "myView" sieht folgendermassen aus:

if exists (select 1 from sysobjects where name='myView' and type='V') drop view myView
go

Aufbau des View

Ein View besteht aus dem Statement das den Namen des View vergibt und dem select-Statement. Jeder View beginnt folglich mit:

create view myView as
select ................

Die Bedingung, dass ein View nur aus einem select-Statement bestehen muss gilt nur insofern als dies ein einheitliches Resultset (Resultatwerte) ergeben muss. Es können somit folgende Konstrukte durchaus die Auswahl in mehreren (verknüpften) select-Statement erlauben:

• Subselect die mittels eines select-Statement einen Werte direkt in das Resultset einliefern. Deren interne Bedingungen können wiederum vom umschliessen (Haupt-)select abhängig gemacht werden.

• Verknüpfte select mit gleichem (ergänzendem) Resultset die durch "union" gegenseitig gekoppelt werden.

Zugriffsrechte

Nebst dem, dass innerhalb des View präzise diejenigen Felder angegeben werden können die der Benutzer sehen darf und auch zusätzliche Prüfungen auf separate Benutzerrechts-Tabellen gemacht werden können, muss ein View auch selbst für den Benutzer freigegeben werden. Dies geschieht genau gleich wie bei anderen Datenbankobjekten mittels des grant-Statement. Ein solches Beispiel wäre:

GRANT select ON myView TO test1
go

Technische Grenzen

Da ein View aus einem select-Statement besteht gelten für ihn die Grenzen dieses select-Statement. Dieses ist normalerweise auf max. 16 Tabellen im Zugriff begrenzt. Zu beachten ist, dass ein in das select eingeschlossener weiterer View mit all seinen Tabellen auch dazu zählt und somit rein optisch auf dem select des View selbst die Grenze nicht überschritten wird, aber der eingeschlossene View mit seinen Tabellen diese überschreitet.

Beispiele

Normalisierte Daten in flache (denormalisierte) Form umwandeln

Als Basis dient folgende Gruppenstruktur. Die Gruppen sind über die Tabelle ww_grpartvar den Artikeln zugewiesen. Es sind mehrere Gruppenbereiche möglich, für eine Auswertung ist aber exakt ein Bereich (derjenige der "Allg. Gruppe 1") erforderlich. Dies als eine Zeile pro Artikel mit seiner Gruppe.

Diese Anforderung entspricht dem Profil eines Views, der die auf mind. 3 Tabellen verteilte Information zusammenziehen kann. Der entsprechende Script des View "ww_artgrp_info" sieht somit folgendermassen aus:

if exists (select 1 from sysobjects where name='ww_artgrp_info' and type='V') 
   drop view ww_artgrp_info
go
 
create view ww_artgrp_info as
select g.mandid, g.artvarnum, artnum, artgrp 
from ww_grpartvar g, ww_artvar a
where g.artvarnum=a.artvarnum
and g.bereich=1
go
 
GRANT select ON ww_artgrp_info TO test1
go

Der View lässt sich nun normal über SQL-Abfragen, sei dies von einem anderen Programm wie z.b. Excel oder über ISQL/w abfragen:

Beispiel:

Komplexe Strukten auf Anzeigeform bringen

Das folgende Beispiel soll zu jeder Belegzeile der Auftragsbestätigungen ergänzend die entsprechenden Informationen aus dem Belegkopf, Umrechnungskurse und Faktor, Projektinformation, Sachbearbeiter, Vertreter usw. anzeigen. Diese Rohdaten dienen dann als Basis entsprechender Kreuztabellen in Excel, Access oder auch Crystal Reports.

Der View muss für seine Informationen über mehrere Tabellenbereiche hinweg die Daten zusammenführen. Teilweise ist dies mit Subselects direkt gemacht. Da in solchen Fällen, im Gegensatz zum vorherigen Beispiel, nicht mehr die Originaltitel der Tabellen als Spaltentitel des View genommen werden können sind hier eigene Spaltentitel zusätzlich angegeben.

if exists (select 1 from sysobjects where name='ve_zeile_info' and type = 'V')
   drop view ve_zeile_info
go
create view ve_zeile_info as
select
 Belegnr         =    ve_belegkopf.belegnum,
 Belegdatum      =    convert (char(10),ve_belegkopf.belegdt,104),
 Debi_kredi_Nr   =    bst.adrnbr,
 Debi_kredi_bez  =    ve_belegadr.dkname,
 Beleg_referenz  =    ISNULL(ve_belegKOPF.belegstampnummer,''), 
 Bestelldatum    =    CONVERT(CHAR(10),ve_belegkopf.bstdt,104),
 Artnum          =    ve_zeilezusatz.artnum,
 Bezeichnung     =    ve_zeilen.bhgtext,
 Menge           =    ve_zeilen.liefermng,
 Einheit         =    ISNULL(verpbez.kurztext,''),
 Preis           =    ve_zeilen.einzelprs,
 Rabatt          =    ve_zeilen.rabsatz,
 Total           =    ve_zeilen.totalbetrag,
 Total_Basis_whg = 
   convert(decimal(21,6),ve_zeilen.totalbetrag*ve_belegkopf.kurs/waehrung.umrfakt),
 ZeilenID        =    ve_zeilen.zeilennbr,
 Lagerort        =    isnull(bst.lagnum,0),
 Bestellmenge    =    bst.bstmng,
 Reuckstand      =    isnull(rcst.rueckstandmng,0),
 nachlief = 
   ISNULL(CONVERT(CHAR(10),rcst.nachlieferdt,104),CONVERT(CHAR(10),bst.nachlieferdt,104)),
 Projekt         =    ISNULL(projekte.projektnum,''),
 Sachbearbeiter  =    isnull(ve_belegzusatz.SACHBEARBEITER,''),
 Vertreter       =    isnull(adresse.name,''),
 Whg             =    isnull(ve_belegkopf.whgid,''),
 Kurs            =    isnull(ve_belegkopf.kurs,1),
 UmrFaktor       =    isnull(waehrung.umrfakt,1),
 Mandid          =    ve_belegkopf.mandid,
 Artvarnum       =    bst.artvarnum
from 
 periode, 
 ve_belegkopf
  left outer join ve_entitylink pl
     on ve_belegkopf.internbelnum=pl.entid
     and pl.enttyp=20
     and pl.oberenttyp=10
     left outer join projekte
     on projaufnum=pl.oberentid
     and pl.mandid=pl.mandid
     left outer join ve_ww_zeilen bst
      on bst.belegnum=ve_belegkopf.internbelnum
     and bst.bstmng <>0
     left outer join ve_ww_zeilen rcst
      on bst.zeilennbr=rcst.zeilennbr
     and rcst.rueckstandmng <>0,
     ve_entitylink el1,
     ve_belegadr, 
     ww_verpackung
     left outer join ww_bez verpbez
     on 
          ww_verpackung.mandid = verpbez.mandid
     and  verpbez.beztyp=50 
     and  verpbez.beznum=ww_verpackung.verpenhnum 
     and  verpbez.mandid = ww_verpackung.mandid 
     and  verpbez.lcid=2055,
     ve_zeilen,
     ve_zeilezusatz,
     ve_belegzusatz
     left outer join Vertreter
       on ve_belegzusatz.vertreter=vertreter.kurzid
      and ve_belegzusatz.mandid=vertreter.mandid
     left outer join adresse
       on vertreter.adrnbr=adresse.adrnbr
      and vertreter.mandid=adresse.mandid,
     waehrung
where periode.status='o'
  and periode.mandid=ve_belegkopf.mandid
  and periode.bhgdtevon<=ve_belegkopf.valutadt
  and periode.bhgdtebis>=ve_belegkopf.valutadt
  and ve_belegkopf.internbelnum=el1.oberentid
  and ve_belegkopf.belegtyp=2
  and el1.oberenttyp=20
  and el1.enttyp=ve_belegadr.enttyp
  and el1.entid=ve_belegadr.beladressnum
  and ve_belegadr.beladresstyp=1
  and ve_belegkopf.belegzusatznbr=ve_belegzusatz.belegzusatznbr
  and bst.zeilennbr=ve_zeilen.zeilennbr
  and bst.verpnum=ww_verpackung.verpnum
  and ww_verpackung.mandid=bst.mandid
  and bst.zeilennbr= ve_zeilezusatz.zeilennbr
  and ve_belegkopf.mandid=waehrung.mandid
  and ve_belegkopf.whgid=waehrung.whgid
  and isnull(ve_zeilen.storno,0)=0
go
GRANT select ON ve_zeile_info TO test1
go

Normalisierung von Daten (Aufteilung auf mehrere Datensätze)

Es kann technisch (z.B. wegen Performance oder der Zugriffsstrategie) erforderlich sein eine Tabelle nicht in der dritten Normalform zu halten. Zu beachten ist auch sehr oft die maximale Grenze von 16 Tabellen in einem select-Statement. Diese Grenze gilt auch für einen View. Es kann aber wegen solchen Grenzen erforderlich sein die Tabelle zu denormalisieren und bestimmte Zugriffe darauf abzustützen, andere Zugriffe mit einem normalisierenden View zu realisieren.

Ein entsprechendes Beispiel für folgende Aufgabenstellung befindet sich bereits in jeder Infoniqa ONE 200-Auftragsinstallation als View "av_debiadr":

• Bei jeder Kundenadresse sollen im Tab "Individuelle Informationen" bis zu 8 Ansprechpartner erfasst werden können.

• Der numerische Wert dient als Flag, ob die Adresse angeschrieben werden soll und auch als Code der steuernd auf dem Brief eingesetzt werden kann (Anrede).

• Im alphanumerischen Feld steht der Name (Kontakt). Das erste Wort soll als Vorname, das zweite und folgende als Nachname separat als Feld später ausgewertet werden.

Die Erfassung sieht somit folgendermassen aus:

Aus diesen Werten, die nicht normalisiert in der Debitoren-Tabelle gespeichert sind, sollen Serienbriefe mit Word erstellt werden. Word benötigt pro Kontaktadresse sowohl die Hauptdaten des Kunden resp. seiner Adresse und jeweils je einen der Kontaktangaben. Günstig ist es zudem, wenn bestimmte Angaben, die zwar aus Gründen des Erfassungskomforts in einer Zeile (einem Feld) erfasst wurden, separat ausgewiesen werden. Dies trifft hier für den Vornamen und Namen zu.

Der View "av_debiadr" liefert diese Informationen in der gewünschten Form. Dies kann mittels ISQL/w mit einem einfachen "select * from av_debiadr" überprüft werden, was für obige Eintragungen folgendes Resultat ergibt:

 

Der View "av_debiadr" kann mittels Enterprise Manager durchgesehen werden.

Er beinhaltet mehrere mit union verknüpfte select-Statement und ist rund 280 Zeilen gross.

Dies zeigt auch dass der Umfang eines View vom simplen Einzeiler (als Beispiel "create view myView as select * from adresse") bis zum komplexen "Programm" reichen kann.

Einbinden des View in Serienbrief-Dokument (Word)

Der View kann wie jede andere Tabelle in das Serienbrief-Dokument von Word eingebunden werden. Dazu ist eine Abfrage über Microsoft Query aufzurufen. Bei der neu zu erstellenden Datenquelle kann in der vierten Sektion der entsprechende View direkt ausgewählt werden.

Es kann entweder eine bestehende Datenquelle die auf diesen View verweist gewählt oder eine neue Datenquelle mittels Microsoft Query eröffnet werden.

Das Set der für die Adressen des Serienbriefes benötigten Daten kann mittels Query dann zusammengestellt und an Word zurückgegeben werden.

Analog kann auch für die anderen Typen der Seriendruckdokumente verfahren werden. Mit dem Einsatz des View können die Daten bereits in einer für den Anwender und sein Umfeld (also die Briefadresse oder Etikette) richtigen Form präsentiert werden ohne dass er sich über Datenbankdetails Informationen verschaffen müsste.

Einbinden des View in Excel

Die Datenquellen für Excel können analog genutzt werden wie im vorherigen Beispiel mit Word. Wenn also für Serienbriefe eine Datenquelle eingerichtet wurde kann diese als Vorschau oder Zusammenzug der Adressen auch in Excel aufgerufen werden. Die Schritte sehen folgendermassen aus:

• Menupunkt "Daten", "Externe Daten", "Neue Abfrage erstellen"

• <Neue Datenquelle> sofern noch keine geeignete Datenquelle existiert oder bestehende Datenquelle anwählen.

• Datenset zusammenstellen und an Excel zurückgeben.