Von der transaktionalen zur dimensionalen Modellierung · Von der transaktionalen zur dimensionalen Modellierung Vortrag auf der DOAG, Nürnberg, 17.11.2015 Felix Krul I Senior BI

Von der transaktionalen zur dimensionalen Modellierung

Vortrag auf der DOAG,

Nürnberg, 17.11.2015

Felix Krul I Senior BI Consultant

Echter Business Intelligence Spezialist

Standorte Köln, Hamburg

Gegründet 2007

> 40 Mitarbeiter

areto consulting gmbhÜber uns

Agenda

Beispielsystem: Daten einer Kaufhauskette

Neun typische Schritte zum dimensionalen Modell

Denormalisierung

Faktenanreicherung

Datumsdimension

Bereinigung und Konsolidierung

künstliche Schlüssel und Dummywerte

Historisierung

Bitmap-Indizierung der Fremdschlüssel

Partitionierung

Aggregattabellen

Ausblick: Dimensionale Modelle in Zeiten von In-Memory und Big Data?

Das Beispielsystem (Quellen)Verkaufsbelege und Lagerlieferungen an die

Filialen einer Kaufhauskette

Das BeispielsystemTypische Eigenschaften

Trennung Köpfeund Positionen

3. Normalform

Kannbeziehungen

Neun typische Schritte zum dimensionalen Modell

1.: Denormalisierung zur 2. NormalformZusammenfassung der Stammdaten zu „Dimensionen“

Abfrageoptimierung: so wenige Joins, wie möglich

1. Normalform wäre zu viel Redundanz

2. Normalform als Kompromiss

2.: Aufbau und Anreicherung der Fakten (i)Optimierung der Kennzahlabfragen

Bisher:

10 Mio200 Mio

Welches Produkt wurde wann verkauft?

Welcher Umsatz wurde in welcher Filiale wann erwirtschaftet?

In welchem Land wurden welche Produkte verkauft?

=> alle diese Abfragen benötigen den rot markierten Join!

Wir reichern die Belege um die Wertinformationen und die Belegpositionen um alle Schlüssel der Belege an:

Hier würden auch weitere benötigte Berechnungen erfolgen.

Die Resultierenden Kennzahltabellen werden als FaktenFaktenFaktenFakten bezeichnet.

2.: Aufbau und Anreicherung der Fakten (ii)Optimierung der Kennzahlabfragen

2.: Aufbau und Anreicherung der Fakten (iii)Optimierung der Kennzahlabfragen

Das Resultat:

3.: DatumsdimensionEine eigene Dimensionstabelle für die Datumshierarchien

Standardisierte Datumshierarchien, vordefinierte Auswertungsformate

Übergreifend für alle Datumswerte in allen Fakten verwendbar

Abbildung spezieller Informationen, wie Werkskalender, Urlaubsplan, Länderspezifika usw.

4.: Bereinigung und Konsolidierung (i)Vereinheitlichung von Dimensionen über die Quellen hinweg

Ein wichtiges Ziel des BI-Systems: Übergreifende Auswertungen über die Systeme hinweg.

Nur selten: unternehmensweite Stammdatenhaltung

Legitime Frage: Ist das BI-System der richtige Ort dafür?

Die Datenqualität ist häufig mangelhaft

Bewegungen und Stammdaten sind oft nicht konsistent, fehlende Zuordnungen usw.

4.: Bereinigung und Konsolidierung (ii)Typische Konsolidierungsthemen

Unterschiedliche Formate (z.B. Schlüssel mit/ohne führende Nullen)

Widersprüchliche Informationen aus den verschiedenen Quellen

DIM_PRODUKT_BELDIM_PRODUKT_BELDIM_PRODUKT_BELDIM_PRODUKT_BELPRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNG KURZBEZKURZBEZKURZBEZKURZBEZ ............

5611107 Bananen BAN...

DIM_PRODUKT_LAGDIM_PRODUKT_LAGDIM_PRODUKT_LAGDIM_PRODUKT_LAGPRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNG KURZBEZKURZBEZKURZBEZKURZBEZ ............

0005611107 Bananen BAN...


577107 Birnen BIR...


0000577107 Birnen 1Kg1Kg1Kg1Kg BIRNNNN...

4.: Bereinigung und Konsolidierung (iii)Typische Konsolidierungsthemen

Nicht alle Sätze kommen in allen Quellen vor


5611107 Bananen BAN...577107 Birnen BIR...314159314159314159314159 PilsPilsPilsPils PILPILPILPIL ............


0005611107 Bananen BAN...0000577107 Birnen 1Kg BIRN...0000271828000027182800002718280000271828 ExportExportExportExport EXPEXPEXPEXP...

4.: Bereinigung und Konsolidierung (iv)Typische Konsolidierungsthemen

Eine gemeinsame Produktdimension muss nach definierten (fachlichen!) Regeln aufgebaut werden, z.B.

das Belegsystem ist führend,

die Produktnummer ist 10-stellig mit führenden Nullen

es wird eine gemeinsame Hierarchie definiert

Beispiel für ein Endergebnis:DIM_PRODUKTDIM_PRODUKTDIM_PRODUKTDIM_PRODUKT

PRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNGKURZBEZKURZBEZKURZBEZKURZBEZ GRUPPE_NRGRUPPE_NRGRUPPE_NRGRUPPE_NRGRUPPE_BEZGRUPPE_BEZGRUPPE_BEZGRUPPE_BEZKLASSE_KURZKLASSE_KURZKLASSE_KURZKLASSE_KURZ KLASSE_BEZKLASSE_BEZKLASSE_BEZKLASSE_BEZ

0005611107 Bananen BAN 10 Obst LM Lebensmittel0000577107 Birnen BIR 10 Obst LM Lebensmittel0000314159 Pils PIL 190 Biere GE Getränke0000271828 Export EXP 190 Biere GE Getränke

0074331668 Wasser WAS 200Nicht-

alkoholisch GE Getränke

4.: Bereinigung und Konsolidierung (v)Datenqualität

Weitere Datenqualitätsprobleme:

Fehlerhafte Adressen

Dubletten

Rechtschreibfehler

Ist das BI-System der richtige Ort für die Korrektur von Daten?

Inkonsistenz zwischen Auswertungen und Quellen!

Alternativer Ansatz:

Das BI-System analysiertanalysiertanalysiertanalysiert, wo die Fehler auftreten

Feedback an die Quellen, Korrektur dortKorrektur dortKorrektur dortKorrektur dort

Beim nächsten Ladelauf auch im BI-System

4.: Bereinigung und Konsolidierung (vi)Das Datenmodell im jetzigen Zustand

EinschubEin paar wichtige Begriffe (nach Kimball)

Sternschema:Sternschema:Sternschema:Sternschema:Eine Faktentabelle mit ihren zugehörigen Dimensionen

Conformed Dimension:Conformed Dimension:Conformed Dimension:Conformed Dimension:Eine Dimension, die sich auf mehrere Fakten bezieht

Busschema:Busschema:Busschema:Busschema:Alle Fakten und Dimensionen zusammen

5.: Künstliche Schlüssel und Dummywerte (i)Einfachere Joinbedingungen und Vermeidung von outer Joins

Primärschlüssel in Dimensionen können für Abfragen ungeeignet sein

Lange Varchar-Spalten

Zusammengesetzt aus mehreren Spalten

Kannbeziehungen => outer Joins

Besser wäre

Integer- oder Datumswerte

Möglichst dichter Nummernkreis ohne große Lücken

Einzelne Spalten

Inner Joins

Nur: woher nehmen, und nicht stehlen? Die Quellsysteme liefern das oft nicht!

5.: Künstliche Schlüssel und Dummywerte (ii)Einfachere Joinbedingungen und Vermeidung von outer Joins

Im Data Warehouse werden eigene, künstliche Integer-Schlüssel für die Dimensionen erzeugt (im folgenden „SIK“ für Surrogate Integer Key genannt)

Dieser wird im Lookup-Prozess in die Fakten mit übernommen

Abfragejoins können nun diesen Schlüssel verwenden

Zur Vermeidung von Outer Joins: Neue Dimensionseinträge für „leer“ und „falsch gefüllt“

Diese erhalten die SIKs -1 und -2

In den Fakten werden die entsprechenden Werte im ETL-Prozess wenn nötig zugewiesen

5.: Künstliche Schlüssel und Dummywerte (iii)Das Datenmodell

6.: Historisierung (i)Wird die Historie der Dimensionen bewahrt?

Es geht um die Frage, wie mit Änderungen bestehender Stammdaten verfahren werden soll.

Nach Kimball wurden die zwei wichtigsten Methoden zur Dimensions-Historisierung benannt:

Slowly Changing Dimension Typ 1, kurz SCD1:Slowly Changing Dimension Typ 1, kurz SCD1:Slowly Changing Dimension Typ 1, kurz SCD1:Slowly Changing Dimension Typ 1, kurz SCD1: Nur die jeweils aktuellsten Versionen werden aufbewahrt

Slowly Changing Dimension Typ Slowly Changing Dimension Typ Slowly Changing Dimension Typ Slowly Changing Dimension Typ 2, 2, 2, 2, kurz kurz kurz kurz SCD2:SCD2:SCD2:SCD2: Die volle Historie aller Stammdaten wird aufbewahrt. In der Dimension werden Gültig von und Gültig bis ergänzt.

Oft wird ein historischer künstlicher Schlüssel („HSIK) erzeugt, um den folgenden Join zu vereinfachen:FAKT.PRODUKT_NR = DIM_PRODUKT.PRODUKT_NR

and

FAKT.DATUM between

DIM_PRODUKT. GUELTIG_VON and DIM_PRODUKT.GUELTIG_BIS

6.: Historisierung (ii)SCD1 und 2 im Vergleich

Produktinformation am 17.11.2015Produktinformation am 17.11.2015Produktinformation am 17.11.2015Produktinformation am 17.11.2015

PRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNG KURZBEZKURZBEZKURZBEZKURZBEZ GRUPPE_NRGRUPPE_NRGRUPPE_NRGRUPPE_NR GRUPPE_BEZGRUPPE_BEZGRUPPE_BEZGRUPPE_BEZ KLASSE_KURZKLASSE_KURZKLASSE_KURZKLASSE_KURZ KLASSE_BEZKLASSE_BEZKLASSE_BEZKLASSE_BEZ

0005611107 Bananen BAN 10 Obst LM Lebensmittel

0000577107 Birnen BIR 10 Obst LM Lebensmittel

DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1



0000577107 Birnen BIR 10 Obst LM Lebensmittel

DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2PRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR GUELTIG_VONGUELTIG_VONGUELTIG_VONGUELTIG_VON GUELTIG_BISGUELTIG_BISGUELTIG_BISGUELTIG_BIS BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNG KURZBEZKURZBEZKURZBEZKURZBEZ GRUPPE_NRGRUPPE_NRGRUPPE_NRGRUPPE_NR GRUPPE_BEZGRUPPE_BEZGRUPPE_BEZGRUPPE_BEZ KLASSE_KURZKLASSE_KURZKLASSE_KURZKLASSE_KURZ KLASSE_BEZKLASSE_BEZKLASSE_BEZKLASSE_BEZ

0005611107 17.11.2015 31.12.9999 Bananen BAN 10 Obst LM Lebensmittel

0000577107 17.11.2015 31.12.9999 Birnen BIR 10 Obst LM Lebensmittel

6.: Historisierung (iii)SCD1 und 2 im Vergleich

Produktinformation am Produktinformation am Produktinformation am Produktinformation am 20.11.201520.11.201520.11.201520.11.2015



0000577107 Birnen BIR 10 ObstsortenObstsortenObstsortenObstsorten LM Lebensmittel

0000314159000031415900003141590000314159 PilsPilsPilsPils PILPILPILPIL 190190190190 BiereBiereBiereBiere GEGEGEGE GetränkeGetränkeGetränkeGetränke

DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1DIM_PRODUKT_SCD1



0000577107 Birnen BIR 10 ObstsortenObstsortenObstsortenObstsorten LM Lebensmittel

0000314159 Pils PIL 190 Biere GE Getränke

DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2DIM_PRODUKT_SCD2PRODUKT_NRPRODUKT_NRPRODUKT_NRPRODUKT_NR GUELTIG_VONGUELTIG_VONGUELTIG_VONGUELTIG_VON GUELTIG_BISGUELTIG_BISGUELTIG_BISGUELTIG_BIS BEZEICHNUNGBEZEICHNUNGBEZEICHNUNGBEZEICHNUNG KURZBEZKURZBEZKURZBEZKURZBEZ GRUPPE_NRGRUPPE_NRGRUPPE_NRGRUPPE_NR GRUPPE_BEZGRUPPE_BEZGRUPPE_BEZGRUPPE_BEZ KLASSE_KURZKLASSE_KURZKLASSE_KURZKLASSE_KURZ KLASSE_BEZKLASSE_BEZKLASSE_BEZKLASSE_BEZ

0005611107 17.11.2015 19.11.201519.11.201519.11.201519.11.2015 Bananen BAN 10 Obst LM Lebensmittel

0000577107 17.11.2015 19.11.201519.11.201519.11.201519.11.2015 Birnen BIR 10 Obst LM Lebensmittel

0000577107000057710700005771070000577107 20.11.201520.11.201520.11.201520.11.2015 31.12.999931.12.999931.12.999931.12.9999 BirnenBirnenBirnenBirnen BIRBIRBIRBIR 10101010 ObstsortenObstsortenObstsortenObstsorten LMLMLMLMLebensmittelLebensmittelLebensmittelLebensmittel

0000314159000031415900003141590000314159 20.11.201520.11.201520.11.201520.11.2015 31.12.999931.12.999931.12.999931.12.9999 PilsPilsPilsPils PILPILPILPIL 190190190190 BiereBiereBiereBiere GEGEGEGE GetränkeGetränkeGetränkeGetränke

7.: Bitmap-IndizierungFremdschlüssel in den Fakten

Zur Performancesteigerung sollten in Abfragen Indizes verwendet werden

In einer DWH-Abfrage werden aber oft mehrere Dimensionen mit Bedingungen und Fakten und oft nicht vorhersagbar kombiniert.

Gewöhnliche B*Tree-Indizes können aber nicht kombiniert werden; wegen der unvorhersagbaren Kombinationen sind auch Kombinationsindizes oft nicht ausreichend.

Bitmap-Indizes können aber kombiniert werden!

Bewährte Best Practice seit vielen Jahren:

getrennte Bitmap-Indizes auf jeden der Fremdschlüssel in den Fakten legen

diese werden dann vom Optimizer bestmöglich kombiniert.

8.: Partitionierungin den Fakten

Partitionierung teilt eine Tabelle in definierte Unterbereiche.

Wird in einer Abfrage eine Bedingung gesetzt, die nur den Zugriff auf bestimmte Partitionen erfordert, so werden auch nur diese gelesen. Es kann daher maximal zu einem Full Partition Scan, nicht mehr zum Full Table Scan kommen.

Partitionierung unterstützt auch die Parallelisierung

Auch für die Optimierung der Beladung relevant.

Indizes auf eine partitionierte Faktentabelle sind synchron zur Faktentabelle partitioniert.

9.: AggregattabellenVorberechnung bestimmter Abfrageergebnisse

Fakten können sehr groß werden und sind meist auf granularster Ebene.

Oft werden aber stark aggregierte Abfrageergebnisse benötigt, z.B, Monatssummen, Hierarchische Verdichtungen auf Produktklasse, keine Einzelbelege usw.

In Aggregattabellen werden bereits im Beladungsprozess vorverdichtete Ergebnisse abgespeichert.

Sie sind dadurch viel kleiner, als die Fakten=> Die Abfragen können um ein vielfaches schneller werden.

In Oracle: Am einfachsten über Materialized Views mit Query Rewrite zu realisieren.

AusblickDimensionale Modelle in Zeiten von In-

Memory und Big Data?

AusblickDimensionale Modelle in Zeiten von In-Memory und Big Data?

In-Memory-Datenbanken steigern die Lade- und Abfrageperformance enorm, ist da die dimensionale Modellierung überhaupt noch relevant?

Performanceoptimierung spielt eine sehr viel kleinere Rolle

Dimensionale Modellierung wird aber auch für die Konsolidierung der Daten verwendet, und die ist weiterhin nötig.

Dimensionale Strukturen sind für Abfragen deutlich einfacher zu verstehen als transaktionale.

Und wie steht es mit Big Data?

In einem dimensionalen Modell können unstrukturierte Daten natürlich nicht gespeichert werden.

Die fertigen statistischen Analysen aber sehr wohl!

Transaktionale Daten werden auch künftig relational sein.

areto consulting gmbhData WarehouseBusiness Intelligence

Schanzenstraße 6-2051063 Kölnwww.areto-consulting.de

Welche Fragen darf ich Ihnen noch beantworten?

Felix KrulSenior BI [email protected]

Documents

Von der transaktionalen zur dimensionalen Modellierung · Von der transaktionalen zur dimensionalen Modellierung Vortrag auf der DOAG, Nürnberg, 17.11.2015 Felix Krul I Senior BI