Data Warehouse mit Visual FoxPro

1. Data Warehousemit Visual FoxPro Marc Voillat Interbrain AG 27. Mai 2004

2. Übersicht • Theorie • Interbrain AG und das Projekt „Sportamt ZH“ • Grundlegendes zum Data Warehouse • SQL Server Analysis Services oder Contour Cube? • Praxis Contour Cube • Demo anhand der mitgelieferten Beispielapplikation • Definition des Cubes • Laden und Speichern des Cubes • Hinweise auf weitere Probleme beim praktischen Einsatz

3. Interbrain AGund das Projekt „Sportamt ZH“ • Interbrain AG • Projektübersicht • Systemarchitektur • Mengengerüst • Zentrale Auswertungen: Data Warehouse?

4. Interbrain AG • Online-Zutrittskontroll-, Abrechnungs- und Verwaltungslösungen für einzelne und verbundene Fitness- und Wellnessanlagen • Umfassende eigene Softwarelösung auf Basis von Visual FoxPro • Integration verschiedenster Hardware-Komponten von Drittherstellern (POS, Datenträger, Automaten …)

5. Projekt „Sportamt ZH“ (1) • Kassensystem: • Verkauf von lokal gültigen Einzeleintritten (Tickets mit Barcode) • Verkauf von verbundweit gültigen, unpersönlichen Punktekarten sowie persönlichen Saison- und Jahreskarten (Chipkarten mit Pfand) • Verkauf und Verwaltung von verbundweit gültigen Gutscheinen (mit Barcode) • Verkauf und Vermietung von Artikeln

6. Projekt „Sportamt ZH“ (2) • Zutrittskontrolle mit Drehkreuzen und kombinierten Chip- und Barcodelesern • Verwaltung der persönlichen Abonnemente • Zentrale Verwaltung: • Konfiguration der Applikation für alle oder einzelne Anlagen • Abschlussarbeiten für Rechnungswesen • Statistische Auswertungen für einzelne, mehrere oder alle Anlagen • Fernwartung für alle Stationen des Verbundes

7. Systemarchitektur • Lokale VFP-Datenbank auf dem Server jeder Anlage • Replikation der relevanten Daten über WAN (VPN über ADSL; Austausch der Basisdaten alle 24 h und Leistungsverbuchung sofort über FTP) • Zugriff der zentralen Verwaltung auf die einzelne Anlage über Terminal Server • Fernwartung durch Verwaltung mit DameWare • Verdichtung der Eintritts- und Verkaufszahlen in einem zentralen Data Warehouse (VFP mit CC)

8. Mengengerüst • Verbund mit 21 Standorten in Zürich (Anlagen des Sportamtes und fremd-betriebene Anlagen) • Total ca. 50 Server und Arbeitsstationen • Mehr als 2 Mio. Eintritte im Jahr 2003, bis zu 30‘000 an Spitzentagen • Etwa 50‘000 Jahres-, Saison und Punktekarten im Umlauf

9. Zentrale Auswertungen:Data Warehouse? • Verschiedenste zentrale Auswertungen über Eintritte und Verkäufe einzelner, mehrerer oder aller Anlagen • Bezüglich Sichtweise, Gruppierung und Filterung der Daten möglichst flexibel • Endform, in der die Daten präsentiert werden, möglichst frei wählbar • Aktualisierung alle 24 h genügt

10. Grundlegendeszum Data Warehouse • Charakteristika • Cubes, Fact Tables, Measures und Dimensions • Star- und Snowflake-Schema • Star-, Snowflake-, Parent-Child, und Zeitdimensionen • Teile des Data Warehouse: Staging Area und OLAP resp. Presentation Area • Extraction – Transformation – Load • Cube Processing: MOLAP, HOLAP oder ROLAP • Offline Cubes (Data Marts)

11. Charakteristika • Spezielle Art der Datenmodellierung • Datenbank mit Fakten (offline) • Meta-Datenbank mit Cube-Definitionen • Vorberechnete aggregierte Werte • Präsentationsschicht • Tools zur Automatisierung des ETL-Prozesses (Überführung der Daten vom OLTP-System in die Cubes des DWH)

12. Cubes, Fact Tables,Measures und Dimensions (1) • Cube: • Data Warehouse besteht aus einem oder Cubes („Datenwürfel“) • Strukturdefinition (eine Fact Table pro Cube, mehrere Dimensionen und Messwerte) • Vorberechnete aggregierte Werte • Präsentationsschicht zum Auswerten der Daten (Slice, Dice und Drill)

13. Cubes, Fact Tables,Measures und Dimensions (2) • Fact Table: • Datenquelle des Cubes (in der Staging Area) • Flache Struktur mit Referenzwerten aus Dimension Tables und Messwerten • Enthält einen Datensatz pro Vorgang (Fact) • Measure (Messwert): • Beispiele: Menge, Preis, Anzahl, Dauer… • Basis für die im Cube aggregierten Werte, wie Anzahl Datensätze, Summe von Werten, Minimal- und Maximalwerte, Durchschnitte…

14. Cubes, Fact Tables,Measures und Dimensions (3) • Dimension: • Beispiele: Zeit, Ort, Art der Vorgangs, Merkmale von Leistungserbringern und Leistungsempfängern… • Referenztabelle mit Schlüsselwert und Bezeichnung sowie allfälligen Referenzwerten aus sekundären Dimensionen (Snowflake) • Kriterium für Gruppierung, Gliederung und Filterung der Vorgänge (Facts)

15. Star- und Snowflake-Schema

16. Star-, Snowflake-, Parent-Child- und Zeitdimensionen (1) • Star-Dimension: • Code in der Fact Table • Code und zugehörige Bezeichnung in der Dimension Table • Snowflake-Dimension: • Dimensionen, welche ihrerseits Referenzwerte (Codes) einer weiteren Dimension enthalten

17. Star-, Snowflake-, Parent-Child- und Zeitdimensionen (2) • Parent-Child-Dimension: • Dimensionen, welche einen rekursiven Verweis auf sich selbst enthalten (Stücklistenstruktur) • Zeitdimension: • Basierend auf Datum-Zeit-Wert • Granularität wählbar (Datum, Jahr, Monat, Tag, Stunde, Kalenderwoche, Wochentag…) • Vom System zur Verfügung gestellt, keine Dimension Table notwendig

18. Teile des Data Warehouse:Staging Area und OLAP resp. Presentation Area • Staging Area: Offline-Datenbank mit den aus der OLTP Area extrahierten und geladenen Fact und Dimension Tables • OLAP oder Presentation Area: Cubes mit den aus den Daten der Staging Area berechneten aggregierten Werten

19. Extraction – Transformation – Load • Data Extraction: • Extrahieren aus dem produktiven OLTP-System • Übermitteln in die Staging Area • Data Transformation: • Transformation auf einheitliche Standards • Prüfung und Bereinigung der Daten (Cleansing) • Anreicherung durch Denormalisierung und Informationen aus externen Quellen (Enrichment) • Data Load: • Laden der Dimension Tables • Laden der Fact Tables

20. Cube Processing (1): MOLAP • Multidimensional Online Analytical Processing: • Struktur, aggregierte Werte und Detaildaten im Data Warehouse • Platzintensiv, lange Ladezeiten • Schneller Zugriff, unabhängig vom OLTP-System • Normalfall

21. Cube Processing (2): HOLAP • Hybrid Online Analytical Processing: • Struktur und aggregierte Werte im Data Warehouse • Detaildaten im OLTP-System • Platzsparend, kurze Ladezeiten • Abhängig vom OLTP-System • Nur für sehr grosse, aber beständige DWH

22. Cube Processing (3): ROLAP • Relational Online Analytical Processing: • Nur Struktur im Data Warehouse • Keine aggregierten Werte • Detaildaten im OLTP-System • Platzsparend • Abhängig vom OLTP-System, ineffizient und extrem lastintensiv in der Ausführung • Nur für Echtzeit-Cubes

23. Offline Cubes (Data Marts) • Können, unabhängig vom Data Warehouse Server, als separate Datei verteilt und mit Excel oder einem speziellen Viewer angezeigt werden • Können, einmal erzeugt, nicht aktualisiert, sondern nur neu erstellt werden • Erfordern bei grossen Datenmengen entsprechend viel Arbeitsspeicher auf den Arbeitsstationen, die darauf zugreifen

24. SQL Server Analysis Servicesoder Contour Cube? • SQL Server Analysis Services: • Charakteristika • Vorteile • Nachteile • Contour Cube: • Charakteristika • Vorteile • Nachteile

25. SQL Server Analysis Services: Charakteristika • Microsoft-Produkt • Eigenständige Applikation • Basiert umfassend auf SQL Server und dessen Tools • Integration in eine VFP-Applikation anspruchsvoll, mit Pivot-Table-Assistent aber prinzipiell möglich • Nahezu beliebige OLTP-Datenquellen (OLEDB und ODBC) verwendbar, Staging Area wird sinnvollerweise als SQL-Datenbank angelegt • MOLAP, HOLAP und ROLAP möglich • Online- und Offline-Cubes

26. SQL Server Analysis Services: Vorteile • Auch für sehr grosse Datenmengen geeignet • Defacto-Standard, viele Zusatzprodukte • Grosser Funktionsumfang • Starke Werkzeuge zur Automatisierung des ETL-Prozesses (DTS, SQL Server Agent) • Ausführliche Dokumentation und Beispiele sowie diverse Literatur • Sicherheit

27. SQL Server Analysis Services: Nachteile • Gesamthaft betrachtet relativ teuer • Hoher Einarbeitungsaufwand • Hohe Komplexität • Installation und Konfiguration beim Kunden und Verteilung von Updates relativ kompliziert • Komplette Aktualisierung der Cubes braucht viel Zeit (mehrere Stunden) • Nicht endanwendertauglich, Drittprodukte für professionelle Präsentation und Data Mining in der Regel sehr teuer

28. Contour Cube: Charakteristika • Produkt einer Moskauer Software-Firma • Visuelles Active-X-Control • Integrierbar in jedes VFP-Formular und damit direkt in eine VFP-Applikation • Laden des Cubes über ADO (OLEDB und ODBC) und BDE direkt aus verschiedensten Datenquellen, auch aus Visual FoxPro • Aussschliesslich MOLAP • Offline-Cubes

29. Contour Cube: Vorteile • Billige Entwicklerlizenz, Royalty free Runtime Licenses • Kurze Einarbeitungszeit • Gute Programmbeispiele auch in Visual FoxPro • Einfache Installation und Aktualisierung beim Kunden • Integration in Webseiten problemlos • Interbrain kann Vorlagen ausliefern, die der Kunde individuell anpassen kann • Schnelles Reprocessing des Cubes

30. Contour Cube: Nachteile • Für sehr grosse Datenmengen nicht oder nur bedingt geeignet (ohne Server) • Beschränkter Funktionsumfang • Eingeschränkte Präsentations-, Druck- und Exportmöglichkeiten • Knappe, nicht ganz fehlerfreie Dokumentation, stark Visual-Basic-lastig • Relativ hohe Ladezeiten für bestehenden Cube • Grosser Bedarf an Arbeitsspeicher auf dem Client (mindestens ½ GB, besser deutlich mehr)