Data-Warehouse-Technologien · Dimensionales Attribut I Element der Menge der Attribute, die vom Primärattribut oder einem Klassiﬁkationsattribut bestimmt werden und nur Top D

Data-Warehouse-Technologien

Prof. Dr.-Ing. Kai-Uwe Sattler1 Prof. Dr. Gunter Saake2

Dr. Veit Köppen2

1TU IlmenauFG Datenbanken & Informationssysteme

2Universität MagdeburgInstitut für Technische und Betriebliche Informationssysteme

Letzte Änderung: 15.10.2018

c© Sattler / Saake / Köppen Data-Warehouse-Technologien Letzte Änderung: 15.10.2018 0–1

Teil III

Multidimensionales Datenmodell



1 Grundbegriffe

2 Der Würfel

3 Konzeptuelle Modellierung

4 Operationen zur Datenanalyse

5 Relationale Umsetzung des multidimensionalen Datenmodells

6 Slowly Changing Dimensions


Multidimensionales Datenmodell Grundbegriffe

Grundbegriffe

DimensionenFakten / Kennzahlen

Produkt

Verkaufsort

Zeit

KennzahlUmsatz

Filiale

Stadt

Bundesland

Gruppe Artikel

JahrQuartal

Monat

Kategorie



Motivation

Datenmodell ausgerichtet auf Unterstützung der AnalyseDatenanalyse im Entscheidungsprozess

I Betriebswirtschaftliche Kennzahlen stehen im Mittelpunkt→ Fakten• Gewinn, • Umsatz, • Kosten, • etc.

I Betrachtung der Kennzahlen aus unterschiedlichen Perspektiven→ Dimensionen• Zeit, • Raum, • Sache

I Unterteilung der Auswertedimensionen möglich→ Hierarchien oder Konsolidierungsebenen• Jahr, • Quartal, • Monat



Verfügbare Informationen

QualifizierendI Repräsentiert durch „Kategorienattribute“I Daten zur Nutzung als Navigationsraster („Drill-Pfade“)I Modelliert als Begriffshierarchien im Rahmen von Dimensionen

QuantifizierendI Bilden Gegenstand der Auswertung

(„Summenattribute“ oder andere arithmetische Operationen)I Zellen eines Würfels, mit Dimensionen als Kanten



Dimensionen

Dimension:I Beschreibt mögliche Sicht auf assoziierte KennzahlenI Endliche Menge von n (n ≥ 2) Dimensionselementen

(Hierarchieobjekten), die eine semantische Beziehung aufweisenI Dienen der orthogonalen Strukturierung des Datenraums

Beispiele:I Produkt,I Filialstruktur,I Geschäftsjahr



Hierarchien in Dimensionen

Dimensionselemente:I Knoten einer KlassifikationshierarchieI Klassifikationsstufe beschreibt VerdichtungsgradI Darstellung von Dimensionen über Klassifikationsschema (Schema

von Klassifikationshierarchien)Formen:

I Einfache HierarchienI Parallele Hierarchien



Einfache Hierarchie

Höhere Hierarchieebene enthält die aggregierten Werte genaueiner niedrigeren HierarchiestufeOberster Knoten: Top

I Enthält Verdichtung auf einen einzelnen Wert für die Dimension

Top

Bundesland

Stadt

Filiale



Parallele Hierarchie

Innerhalb einer Dimension sind mehrere unabhängige Arten derGruppierung möglichKeine hierarchische Beziehung zwischen parallelen ZweigenParallelhierarchie

I Pfad im KlassifikationsschemaI Konsolidierungspfad

Top

Jahr

Quartal

Monat

Tag

Woche



Schema einer Dimension D

Partiell geordnete Menge von Kategorienattributen({D1, . . . ,Dn,TopD};→)

I Generisches maximales Element TopDI Funktionale Abhängigkeit→

TopD wird von allen Attributen funktional bestimmt:

∀i, 1 ≤ i ≤ n : Di → TopD

Es gibt genau ein Di, das alle anderen Kategorieattributebestimmt

I Gibt feinste Granularität einer Dimension vor

∃i, 1 ≤ i ≤ n,∀j, 1 < j ≤ n, i 6= j : Di → Dj



Kategorienattribute

Inhaltliche Verfeinerung durch unterschiedliche Rollen:Primärattribut

I Kategorienattribut, das alle anderen Attribute einer Dimensionbestimmt

I Definiert maximale FeinheitI Beispiel: „Auftragsposition“

KlassifikationsattributI Element der Menge, die mehrstufige Kategorisierung

(Klassifikationshierarchie) bildenI Beispiel: „Produkt“, „Produktgruppe“, „Produktkategorie“

Dimensionales AttributI Element der Menge der Attribute, die vom Primärattribut oder einem

Klassifikationsattribut bestimmt werden und nur TopD bestimmenI Beispiel: „Regalposition“



Struktur einer Dimension: Beispiel

Top

Produkt-kategorie

Produkt-gruppe

Artikel

LagerortSteuernVerkaufs-preis

Klassifikations-attribute

dimensionalesAttribut

Primär Attribut



Kennzahlen

Kennzahlen / Fakten (engl. facts):I (Verdichtete) numerische MessgrößenI Beschreiben betriebswirtschaftliche Sachverhalte

Fakt: Maßzahl (engl. measure)Kennzahl:

I Aus Fakten konstruiert (abgeleitete Kennzahl)I Durch Anwendung arithmetischer Operationen

Beispiele:I Umsatz, Gewinn, KostenI Deckungsbeitrag, ROI (Return on Investment)I Fluktuationsquote, Umsatzsteigerung



Fakt: Schema

Schema wird durch mehrere Bestandteile spezifiziertGranularität G = {G1, . . . ,Gk}

I G ist Teilmenge aller Kategorienattribute aller im Schemaexistierenden Dimensionsschemata DS1, . . . ,DSn

F ∀i, 1 ≤ i ≤ k, ∃j, 1 ≤ j ≤ n : Gi ∈ DSjF ∀i, 1 ≤ i ≤ k, ∀j, 1 ≤ j ≤ k, i 6= j : Gi 6→ Gj

(keine funktionalen Abhängigkeit zwischen Kategorienattributen einerGranularität)

I „Detailliertheitsgrad“ der FaktenSummationstyp SumTyp

I BestandsgrößeI StromgrößeI Einheit



Kennzahl

Kennzahl M ist definiert durchI Granularität GI Berechnungsvorschrift f () über FaktenI Summationstyp SumTyp

Berechnung über nichtleerer Teilmenge der im Schemaexistierenden FaktenM = (G, f (F1, . . . ,Fk), SumTyp)



Kennzahl: Bildung von f()

SkalarfunktionenI +,−, ∗, /,modI Beispiel: Umsatzsteueranteil = Menge ∗ Preis ∗ Steuersatz

AggregatfunktionenI Funktion H() zur Verdichtung eines Datenbestandes, indem aus n

Einzelwerten ein Aggregatwert ermittelt wird

H : 2dom(X1)×···×dom(Xn) → dom(Y)

I Bsp.: SUM(),AVG(),MIN(),MAX(),COUNT()

Ordnungsbasierte FunktionenI Definition von Kennzahlen auf Basis zuvor definierter OrdnungenI Bsp.: Kumulation, TOP(n), MEDIAN()



Summationstypen

Zuweisung eines Summationstyps charakterisiert erlaubteAggregationsoperationenFLOW

I zeitraumbezogen (pro Zeiteinheit)I Beliebig aggregierbarI Beispiel: Bestellmenge eines Artikels pro Tag

STOCKI Maß über ZeitraumI Beliebig aggregierbar mit Ausnahme temporaler DimensionI Beispiel: Lagerbestand, Einwohnerzahl

VALUE − PER− UNIT(VPU)I zeitpunktbezogen (zum Zeitpunkt)I Aktuelle Zustände, die nicht summierbar sindI Zulässig nur: MIN(),MAX(),AVG()I Beispiele: Preis, Wechselkurs, Steuersatz



Summierbarkeit

FLOW STOCK VPUAggregation über

temporale Dimension?nein ja

MIN/MAX + + +

SUM + + − −AVG + + +

COUNT + + +



Disjunktheit

Konkreter Wert einer Kennzahl geht exakt einmal in Ergebnis einBsp.: Verkäufe einer Filiale

Umsatz 2010 2011Bier 38 42Biermix 27 31Softdrinks 54 57Gesamt 92 99

Wie ist der Gesamtumsatz pro Jahr?Wie ist der Gesamtumsatz über die Produktgruppen im Jahr?



Vollständigkeit

Kennzahlen auf höherer Aggregationsebene lassen sich komplettaus Werten tieferer Stufen berechnen

Weinregion 2010 2011Rheinhessen 152 153Saale-Unstrut 98 104Mosel 161 172Sonstige 20 22Gesamt 431 451

Sonstige = Einige Produzenten aus anderen Gebieten



Simpson Paradox

Gruppenbewertungen ergeben unterschiedliche Ergebnissehinsichtlich der betrachteten AggregationsstufeQuotientenbildung auf stark unterschiedlichen Gruppengrößen

Rotwein Weißwein

mit Gesamt Prädikats- mit Gesamt Prädikats-Prädikat Gesamt quote Prädikat Gesamt quote

2010 5 5 100% 7 8 88%2011 10 15 67% 1 2 50%

Summe 15 20 75% 8 10 80%



AggregatfunktionenX ist Klassifikationsknoten und (X1,X2, . . . ,Xn) ist Partitionierung

Distributive Aggregatfunktion: ∃g : f (X) = f (g(X1), g(X2), . . . , g(Xn))

Algebraische Aggregatsfunktion: f ist berechnbar aus festerMenge G

Holistische Aggregatsfunktion: f kann nur aus denGrundelementen von X berechnet werden

Aggregatstyp BeispielDistributiv SUM(), COUNT(), MAX(), MIN()Algebraisch AVG() mit g1 := SUM() und g2 := COUNT(), STDDEV()Holistisch MEDIAN(), RANK(), PERCENTILE()


Multidimensionales Datenmodell Der Würfel

Der Würfel

Würfel (engl. cube, eigentlich Quader): Grundlage dermultidimensionalen AnalyseKanten→ DimensionenZellen→ ein oder mehrere Kennzahlen (als Funktion derDimensionen)Anzahl der Dimensionen→ DimensionalitätVisualisierung

I 2 Dimensionen: TabelleI 3 Dimensionen: WürfelI > 3 Dimensionen: Multidimensionale Domänenstruktur

Schema C eines WürfelsI Menge der Dimensionen(-schemata) DSI Menge der Kennzahlen M

C = (DS,M) = ({D1, . . . ,Dn}, {M1, . . . ,Mm})



Orthogonalität

In multidimensionalen Schemata gilt Orthogonalität, d.h.I Keine funktionalen Abhängigkeiten zwischen Attributen

unterschiedlicher Dimensionen

∀i, 1 ≤ i ≤ n,∀j, 1 ≤ j ≤ n, i 6= j¬∃k, l : Di.Dk → Dj.Dl



Multidimensionaler Datenwürfel

Produkt

Verkaufsort

Zeit

KennzahlUmsatz

Filiale

Stadt

Bundesland

Gruppe Artikel

JahrQuartal

Monat

Kategorie


Multidimensionales Datenmodell Konzeptuelle Modellierung

Konzeptuelle Modellierung

Formale Beschreibung des Fachproblems und der imAnwendungsbereich benötigten Informationsstrukturen

Probleme konventioneller Entwurfstechniken (ER, UML):I Unzureichende Semantik für multidimensionales DatenmodellI Hier: Verzicht auf universelle Anwendbarkeit, stattdessen

Konzentration auf AnalyseI Beispiel: Klassifikationsstufe, Fakt→ Entity ?



ME/R-Modell

Multidimensional Entity/Relationship Model[Sapia et. al. (1998)]Erweiterung des klassischen ER-Modells

I Entity-Menge „Dimension Level“ (Klassifikationsstufe)F Keine explizite Modellierung von Dimensionen

I n-äre Beziehungsmenge „Fact“F Kennzahlen als Attribute der Beziehung

I Binäre Beziehungsmenge „Classification“ bzw. „Roll-Up“(Verbindung von Klassifikationsstufen)

F Definiert gerichteten, nicht-zyklischen Graphen



ME/R: Notationen

Fakten-name Ebene

Faktenbeziehung KlassifikationsstufeKlassifikations-

beziehung

Attribut-bezeichnung

Attribut



ME/R: Beispiel

Verkauf Filiale

Tag

ArtikelProdukt-gruppe

Produkt-kategorie

Monat Quartal JahrWoche

Stadt Bundes-land

Anzahl Umsatz

KundeKunden-gruppe



ADAPT

Application Design for Analytical Processing TechnologiesBulos 1998 & Bulos 2006Berücksichtigt DDL und DML AspekteFokus auf DimensionenRegeln für Kennzahlen möglichVermischung von Metadaten und Ausprägungen



ADAPT

Hypercube

Dimension 1Dimension 2 Dimension

Berechnungs-formel!(#) Hierarchie

{ } Hierarchie-stufe

dimensionalesAttribut { } Dimensions-

ausschnitt{ } Dimensions-ausprägung



ADAPT: Beispiel

Verkäufe

Zeit

OrtProdukt

Ort

Standard-hierarchie

{ }Bundesland

{ } Filiale

{ } Stadt

Produkt

Standard-hierarchie

{ } Produkt-kategorie

{ } Artikel

{ } Produkt-gruppe

Zeit

{ } Jahr

Wochen-hierarchie

Standard-hierarchie

{ } Quartal

{ } Monat

{ } Tag

{ } Woche

Kunde

Standard-hierarchie

{ }Kunde

{ } Kunden-gruppe

Kunde

VerkaufskanalVertriebskanal{ } Internet

{ } Laden-geschäft

{ } Kern-produkte

Rabatt

Steuern

Verkaufs-preis

Einkaufs-preis


Multidimensionales Datenmodell Operationen zur Datenanalyse

Operationen zur Datenanalyse

OLAP-Operationen auf multidimensionalen DatenstrukturenStandardoperationen

I PivotierungI Roll-Up, Drill-DownI Drill-AcrossI Slice und Dice



Pivotierung / Rotation

Drehen des Würfels durch Vertauschen der DimensionenAnalyse der Daten aus verschiedenen Perspektiven

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt Ba

yern

2009

2010

2011

ProduktOrt

Zeit

Bier

Wein

2009

Thüringen

Sachsen

-

Anhalt Ba

yern

20112010

Softdrink



Roll-Up, Drill-Down, Drill-Across

Roll-Up:I Erzeugen neuer Informationen durch Aggregierung der Daten

entlang des KonsolidierungspfadesI Dimensionalität bleibt erhaltenI Beispiel: Tag→ Monat→ Quartal→ Jahr

Drill-Down:I Komplementär zu Roll-UpI Navigation von aggregierten Daten zu Detail-Daten entlang der

KlassifikationshierarchieDrill-Across:

I Wechsel von einem Würfel zu einem anderen



Roll-Up und Drill-Down

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

2009

2010

2011

Produkt

Ort

Zeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

Q1

Q2

Q3

Q4

2009

2010

2011

...

Drill Down

Roll Up



Slice und Dice

Erzeugen individueller SichtenSlice:

I Herausschneiden von „Scheiben“ aus dem WürfelI Verringerung der Dimensionalität durch Konditionierung der

DimensionenI Beispiel: alle Werte des aktuellen JahresI Entspricht der relationalen Selektion in den Dimensionen

Dice:I Herausschneiden einen „Teilwürfels“I Erhaltung der Dimensionalität, Veränderung der HierarchieobjekteI Beispiel: die Werte bestimmter Produkte oder RegionenI Entspricht der relationalen Selektion mehrerer Dimensionen



Slice

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

2009

2010

2011

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

2009

2010

2011



Dice

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

2009

2010

2011

Produkt

OrtZeit

BierWeinSoftdrink

Thüringen

Sachsen

-

Anhalt

Bayern

2009

2010

2011



Problem des DimensionswechselAustausch oder Weglassen einer Dimension kann Bestandteil der Analyse sein

Beispiel: statt Cube by Produkt, Ort und Zeit kann Kunde, Ort und Zeit interessant sein.

Produkt Filiale Tag Verk.Rotwein Magdeburg 18.02.10 145Weißbier Magdeburg 18.02.10 267Rotwein Ilmenau 18.02.10 70

. . .Kundengruppe Filiale Tag Verk.Weintrinker Magdeburg 18.02.10 210Bayer Magdeburg 18.02.10 407Genießer Ilmenau 18.02.10 35

. . .

Was bedeutet ein Wechsel der Dimension?



Marginalisierung

Randsummenbildung bei Entfernen einer DimensionEntspricht der relationalen ProjektionAbhängig vom Typen der Aggregatfunktion→Interpretationsproblem

Anteil BierflaschenMagdeburg Ilmenau

1. Halbwoche 95% 88%2. Halbwoche 68% 54%

GesamtWoche 74% 80%


Multidimensionales Datenmodell Relationale Umsetzung des multidimensionalen Datenmodells

Umsetzung des multidimensionalen Datenmodells I

Multidimensionale SichtI Modellierung der DatenI Anfrageformulierung

Interne Verwaltung der Daten erfordert Umsetzung aufI Relationale Strukturen (Tabellen)→ ROLAP (relationales OLAP)

F Verfügbarkeit, Reife der SystemeI Multidimensionale Strukturen (direkte Speicherung)→ MOLAP (multidimensionales OLAP)

F Wegfall der Transformation bzw. VorwegberechnungF Mehrdimensionale Arrays (Fakten) und assoziierte Dimensionslisten

I Hybride Struktur (Mischform)→ HOLAP (hybrides OLAP)

F Detaildaten relational abgespeichert (ROLAP)F Aggregate werden multidimensional abgespeichert (MOLAP)



Umsetzung des multidimensionalen Datenmodells IIAspekte

I SpeicherungI Anfrageformulierung bzw. -ausführung



Relationale Speicherung

Vermeidung des Verlustes anwendungs-bezogener Semantik (ausdem multidimensionalen Modell, z.B. Klassifikationshierarchien)Effiziente Übersetzung multidimensionaler AnfragenEffiziente Verarbeitung der übersetzten AnfragenEinfache Pflege der entstandenen Relationen (z.B. Laden neuerDaten)Berücksichtigung der Anfragecharakteristik und desDatenvolumens von Analyseanwendungen



Relationale Umsetzung: Faktentabelle

Ausgangspunkt: Umsetzung des Datenwürfels ohneKlassifikationshierarchien

I Dimensionen, Kennzahlen→ Spalten der RelationI Zelle→ Tupel

Produkt

Region

Zeit-raum

Magdeburg

Ilmenau

18.02.2010

19.02.2010

RotweinWeißbier

Produkt Filiale Tag Verk.Rotwein Magdeburg 18.02.10 145Weißbier Magdeburg 18.02.10 267Rotwein Ilmenau 18.02.10 70

. . .



Snowflake-Schema

Abbildung von Klassifikationen: eigene Tabelle für jedeKlassifikationsstufe (z.B. Artikel, Produktgruppe, etc.)Dimensionstabelle enthält

I ID für KlassifikationsknotenI Beschreibendes Attribut (z.B. Marke, Hersteller, Bezeichnung)I Fremdschlüssel der direkt übergeordneten Klassifikationsstufe

Faktentabelle enthält (neben Kenngrößen):I Fremdschlüssel der jeweils niedrigsten KlassifikationsstufeI Fremdschlüssel bilden zusammengesetzte Primärschlüssel für

Faktentabelle



Snowflake-Schema: Muster

Fakt1Fakt2Fakt3...

Dim1_Schlüssel1Dim2_Schlüssel1Dim3_Schlüssel1Dim4_Schlüssel1

Faktentabelle

Dim2_Attribut1Dim2_Schlüssel12. Dimensionstabelle1



Dim2_Schlüssel2

Dim4_Attribut2Dim4_Schlüssel2

Dim3_Schlüssel2


Dim1_Schlüssel3


Dim1_Attribut4


Dim1_Schlüssel2




Dim2_Schlüssel2




Snowflake-Schema: Beispiel

ZeitZ_IDZ_Datum

1

*

1

11

*

*

*

ProduktP_IDP_BezeichnungP_VerkaufspreisP_EinkaufspreisP_RabattP_SteuernP_PGruppe_IDVerkauf

V_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Filial_ID

KundeK_IDK_NameK_WohnortK_StrasseK_GeschlechtK_KGruppe_ID

FilialeF_IDF_FilialeF_Stadt_ID

StadtS_IDS_NameS_BLand_ID

BundeslandB_IDB_NameB_Land_ID

LandL_IDL_Name

KundengruppeKG_IDKG_Bezeichnung

ProduktgruppePG_IDPG_BezeichnungPG_PKategorie_ID

ProduktkategoriePK_IDPK_Bezeichnung

11

*

*

**

*

*

1

11

1

Dimension "Produkt"

Dimension "Zeit"

Dimension "Kunde"

Dimension "Ort"



Star-Schema

Snowflake-Schema ist normalisiert: Vermeidung vonUpdate-Anomalien→ 3. NF

I Aber: erfordert Join über mehrere Tabellen!Star-Schema:

I Denormalisierung der zu einer Dimension gehörenden Tabellen→1. NF

I Für jede Dimension genau eine DimensionstabelleI Redundanzen in der Dimensionstabelle für schnellere

AnfragebearbeitungI Beispiel: Artikel, Produkt, Produktgruppe etc. als Spalten in einer

Tabelle Produkt



Star-Schema: Muster

Fakt1Fakt2Fakt3...

Dim1_SchlüsselDim2_SchlüsselDim3_SchlüsselDim4_Schlüssel...

Faktentabelle

Dim2_Attribut1Dim2_Schlüssel2. Dimensionstabelle




Dim1_Attribut2 Dim2_Attribut2

Dim4_Attribut2

Dim3_Attribut2



Star-Schema: Beispiel

ZeitZ_IDZ_Datum

1

*

1

11

*

*

*

ProduktP_IDP_BezeichnungP_VerkaufspreisP_EinkaufspreisP_RabattP_SteuernP_ProduktgruppeP_Produktkategorie

VerkaufV_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Ort_ID

KundeK_IDK_NameK_WohnortK_StrasseK_GeschlechtK_Kundengruppe

OrtO_IDO_FilialeO_StadtO_BundeslandO_Land



Star-Schema formal

Multidimensionales Schema mit n DimensionenI Dimensionstabellen D1, . . . ,Dn der Form Di(Dimi_Key,Ai,1, . . . ,Ai,ki)I Faktentabelle F(Dim1_Key, . . . ,Dimn_Key, f1, . . . , fm) mit m Fakten

Jeder Teil des kompositen Primärschlüssels der Faktentabelle istFremdschlüssel zum Primärschlüsselattribut derkorrespondierenden Dimension



CREATE DIMENSION in Oracle

Fremdschlüsselbedingungen in SQL ausdrückbarAber: funktionale Beziehungen zwischen Attributen innerhalbeiner Dimension nicht spezifizierbarOracle-Erweiterung: CREATE DIMENSION

I „informative“ ZusicherungI Korrektheit wird vom DBS nicht überprüftI Nutzung beim Query Rewriting über materialisierten Sichten



Beispiel CREATE DIMENSION

CREATE DIMENSION ProduktDimensionLEVEL Artikel IS (Produkt.P_ID)LEVEL Produktgruppe IS (Produkt.P_PGruppe_ID)LEVEL Produktkategorie IS

(Produkt.P_Produktkategorie)HIERARCHY ProduktHierarchie (

Artikel CHILD OFProduktgruppe CHILD OFProduktkategorie)

ATTRIBUTE Artikel DETERMINES (P_Bezeichnung,P_Verkaufspreis, P_Einkaufspreis,P_Rabatt, P_Steuern)

ATTRIBUTE Produktgruppe DETERMINES(P_Produktgruppe)



Schlüsselworte

LEVELI Definiert Klassifikationsstufen

HIERARCHYI Festlegung der Abhängigkeiten der Klassifikationsstufen

ATTRIBUTE ... DETERMINESI Definiert Abhängigkeit zwischen Klassifikationsattribut und

dimensionalen Attributen



Snowflake mit CREATE DIMENSION

PG_IDPG_BezeichnungPG_PKategorie_ID

ProduktgruppeP_IDP_BezeichnungP_VerkaufspreisP_EinkaufspreisP_RabattP_SteuernP_PGruppe_ID

ProduktV_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Filial_ID

Verkauf

CREATE DIMENSION ProduktDimensionLEVEL Artikel IS (Produkt.P_ID)LEVEL Produktgruppe IS (Produktgruppe.PG_ID)

HIERARCHY ProduktHierarchie (Artikel CHILD OF Produktgruppe)

JOIN KEY (Produkt.P_PGruppe_ID)REFERENCES Produktgruppe



Star mit CREATE DIMENSION

V_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Filial_ID

VerkaufZ_IDZ_Tag_IDZ_TagZ_Monat_IDZ_MonatZ_Jahr_IDZ_Jahr

Zeit

CREATE DIMENSION ZeitDimensionLEVEL Tag IS (Zeit.Z_Tag_ID)LEVEL Monat IS (Zeit.Z_Monat_ID)LEVEL Jahr IS (Zeit.Z_Jahr_ID)

HIERARCHY ZeitHierarchie (Tag CHILD OF Monat CHILD OF Jahr)

ATTRIBUTE Tag DETERMINES (Z_Tag)ATTRIBUTE Monat DETERMINES (Z_Monat)ATTRIBUTE Jahr DETERMINES (Z_Jahr)



Vergleich Star und Snowflake

Charakteristika von DW-AnwendungenI Typischerweise Einschränkungen in Anfragen auf höherer

Granularitätsstufe (Join-Operationen)I Geringes Datenvolumen der Dimensionstabellen im Vergleich zu

FaktentabellenI Seltene Änderungen an Klassifikationen (Gefahr von

Update-Anomalien)Vorteile des Star-Schemas

I Einfache Struktur (vereinfachte Anfrageformulierung)I Einfache und flexible Darstellung von Klassifikationshierarchien

(Spalten in Dimensionstabellen)I Effiziente Anfrageverarbeitung innerhalb einer Dimension (keine

Join-Operation notwendig)



Annahmen für Kostenbetrachtungen

n Dimensionen (Dn), je K Klassifikationsstufen plus TopJeder Klassifikationsknoten hat 3 Kinder

I Level i = K: 1 = 30 Knoten (Top)I Level i = K − 1: 3 = 31 KnotenI Level i = K − 2: 9 = 32 KnotenI ...I Level i = 0: Höchste Granularität, 3K KnotenI ND =

∑i=0...K Knoten pro Dimension

ND =∑

i=0...K

3i

M Fakten, gleichverteilt in DimensionenAttribut: b Bytes; Knoten haben nur ID; m Faktattribute



Volle Klassifikationshierarchie

Zu jedem Knoten gibt es (gleich viele) Fakten

Level 2

Level 1

Level 0

Fakten

Top

K1 K2 K3

K1.1 K1.2 K1.3 K2.1 K2.2 K2.3 K3.1 K3.2 K3.3

... ... ...



Anfragekosten Star vs. Snowflake

Speicherplatz Snowflake:

(((n + m) ·M) + n · ND) · b

I n Anzahl Fremdschlüssel in FaktentabelleI m Anzahl FaktenattributeI n · ND Ein Tupel pro Klassifikationsknoten

Speicherplatz Star:

(((n + m) ·M) + n · 3K · K) · b

I n · 3K Ein Tupel pro Klassifikationsknoten Level 0I K Ein Attribut pro Klassifikationsstufe



Kosten Star vs. Snowflake

2 4 68

10

24

68

10

0

5

10

15

20

Speicher-bedarfin MB

Anzahl Klassifikationsstufen

Anzahl Dimensionen

Snowflake-Schema

Star-Schema



Anfrage Star und Snowflake

Anfrage: Verkäufe der Produktgruppe „Wein“ pro Stadt und JahrSnowflake-Schema:

SELECT S_Name, YEAR(Z_Datum), SUM(V_Anzahl)FROM Verkauf, Filiale, Stadt, Produkt, Produktgruppe, ZeitWHERE V_Produkt_ID = P_ID AND

P_PGruppe_ID = PG_ID ANDV_Filial_ID = F_ID ANDF_Stadt_ID = S_ID ANDV_Zeit_ID = Z_ID ANDPG_Bezeichnung = ’Wein’

GROUP BY S_Name, YEAR(Z_Datum)

Anzahl der Joins: 5(steigt linear mit Anzahl der Aggregationspfade)



Anfrage Star und Snowflake (2)

Anfrage für Star-Schema:

SELECT O_Stadt, YEAR(Z_Datum), SUM(V_Anzahl)FROM Verkauf, Ort, Produkt, ZeitWHERE V_Produkt_ID = P_ID AND

V_Zeit_ID = Z_ID ANDV_Ort_ID = O_ID ANDP_Produktgruppe = ’Wein’

GROUP BY O_Stadt, YEAR(Z_Datum)

Anzahl der Joins: 3(unabhängig von der Länge der Aggregationspfade)



Mischformen

Abbildung einzelner Dimensionen analog Snowflake-Schema oderStar-SchemaEntscheidungskriterien:

I Änderungshäufigkeit der Dimensionen:F Reduzierung des Pflegeaufwandes durch Normalisierung (Snowflake)

I Anzahl der Klassifikationsstufen einer Dimension:F Mehr Klassifikationsstufen→ größere Redundanz im Star-Schema

I Anzahl der Dimensionselemente:F Einsparung durch Normalisierung bei vielen Elementen einer

Dimension auf niedrigster KlassifikationsstufeI Materialisierung von Aggregaten:

F Performance-Verbesserung durch Normalisierung bei materialisiertenAggregaten für eine Klassifikationsstufe



Galaxie-Schema

Star- und SnowflakeschemaI Eine FaktentabelleI Mehrere Kennzahlen nur möglich bei gleichen Dimensionen

Galaxie-SchemaI Mehrere FaktentabellenI Teilweise mit gleichen Dimensionstabellen verknüpftI Auch: Multi-Faktentabellen-Schema, Multi-Cube, Hyper-Cube



Galaxie-Schema: Muster

Dim1_SchlüsselDim2_SchlüsselDim3_Schlüssel

Faktentabelle1

Dim2_AttributeDim2_Schlüssel2. Dimensionstabelle




Fakt3Fakt4

Dim3_SchlüsselDim4_Schlüssel

Faktentabelle2

Fakt1Fakt2



Galaxie-Schema: Beispiel

1

*

1

11

*

*

*

ProduktP_IDP_BezeichnungP_VerkaufspreisP_EinkaufspreisP_RabattP_SteuernP_PGruppe_ID

VerkaufV_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Filial_ID

KundeK_IDK_NameK_WohnortK_StrasseK_GeschlechtK_KGruppe_ID

FilialeF_IDF_FilialeF_Stadt_ID

StadtS_IDS_NameS_BLand_ID

BundeslandB_IDB_NameB_Land_ID

LandL_IDL_Name

KundengruppeKG_IDKG_Bezeichnung

ProduktgruppePG_IDPG_BezeichnungPG_PKategorie_ID

ProduktkategoriePK_IDPK_Bezeichnung 1

1

*

*

**

**

1

1

1

1

*

Summe_VerkaufSV_AnzahlSV_KanalSV_Monat_ID SV_PGruppe_IDSV_BLand_ID

*

*

1

ZeitZ_IDZ_DatumZ_Monat_IDZ_Monat

1

1



Fact Constellation

Speicherung vorberechneter Aggregate in FaktentabelleI Beispiel: Umsatz für RegionI Unterscheidung in Dimensionstabelle über spezielle Attribute (Bsp.:

„Stufe“)Alternative: Auslagerung in eigene Faktentabelle

I Fact-Constellation-Schema (Spezialfall eines Galaxie-Schemas)



Fact Constellation: Beispiel

ZeitZ_IDZ_DatumZ_Monat_IDZ_Monat

1

*

1

11

*

*

*

ProduktP_IDP_BezeichnungP_VerkaufspreisP_EinkaufspreisP_RabattP_SteuernP_PGruppe_IDP_ProduktgruppeP_Produktkategorie

VerkaufV_AnzahlV_KanalV_Produkt_IDV_Zeit_IDV_Kunden_IDV_Ort_ID

KundeK_IDK_NameK_WohnortK_StrasseK_GeschlechtK_Kundengruppe

OrtO_IDO_FilialeO_StadtO_BLand_IDO_BundeslandO_Land

*

Summe_VerkaufSV_AnzahlSV_KanalSV_PGruppe_IDSV_Monat_IDSV_BLand_ID

1

11

*

*



Darstellung von Klassifikationshierarchien

Horizontal: Modellierung der Stufen der Klassifikationshierarchieals Spalten der denormalisierten Dimensionstabelle

I Vorteil:F Einschränkungen auf höherer Granularität ohne Join

I Nachteile:F Duplikateliminierung beim Anfragen bestimmter Stufen (Bsp.:

Produktgruppe innerhalb einer Kategorie)F Schemaänderung beim Hinzufügen neuer Stufen

Produkt_ID Artikel Produktgruppe Produktkategorie1234 Immer Ultra Hygiene Kosmetik1235 Putzich Hygiene Kosmetik2345 Rohrfrei Reiniger Haushalt




Vertikal (rekursiv): normalisierte Dimensionstabelle mit AttributenI Dimensions_ID: Schlüssel für FaktentabelleI Eltern_ID: Attributwert der Dimensions-ID der nächsthöheren Stufe

Vorteile:I Einfache Änderung am KlassifikationsschemaI Einfache Behandlung vorberechneter Aggregate

Nachteil:I Self-Join für Anfragen einzelner Stufen (Bsp.: Produktgruppe

innerhalb einer Kategorie)

Dimensions_ID Eltern_IDImmer Ultra Hygiene

Hygiene KosmetikPutzich Hygiene




Kombiniert: Verbindung beider StrategienI Repräsentation der Klassifikationsstufen als Spalten (jedoch

generische Bezeichnung)I Speicherung der Knoten aller höheren Stufen als TupelI Zusätzliches Attribut „Stufe“→ Angabe der bezeichneten

Klassifikationsstufe

Dimensions_ID Stufe1_ID Stufe2_ID StufeImmer Ultra Hygiene Kosmetik 0

Putzich Hygiene Kosmetik 0Hygiene Kosmetik NULL 1Kosmetik NULL NULL 2



Vermeidung von Semantikverlusten

Semantikverlust bei relationaler Abbildung:I Unterscheidung zwischen Kennzahl und Dimension (Attribute der

Faktentabelle)I Attribute von Dimensionstabellen (beschreibend, Aufbau der

Hierarchie)I Aufbau der Dimensionen (Drill-Pfade)

Ausweg:I Erweiterung des Systemkatalogs um Metadaten für

multidimensionale AnwendungenI Beispiel: CREATE DIMENSION, HIERARCHY in Oracle



Probleme der relationalen Umsetzung

Transformation multidimensionaler Anfragen in relationaleRepräsentation notwendig→ komplexe AnfragenEinsatz komplexer Anfragewerkzeuge notwendig(OLAP-Werkzeuge)SemantikverlustDaher: direkte multidimensionale Speicherung→ siehe Teil VI


Multidimensionales Datenmodell Slowly Changing Dimensions

Slowly Changing Dimensions

Historisierung: Änderungen von Attributsausprägungen,Beziehungen und Entitäten im ZeitablaufStrukturveränderungen in den DimensionenSchemaveränderungen in den DimensionenEinfluss auf Analysen



Slowly Changing Dimensions: Beispiel

Bier Wein

Erfurter Bock

Ilmenauer Pils

AnhaltinischFlüssig WeißweinRotwein

Getränke

Dornfelder PortugieserWeißherbst

Müller- Thurgau Riesling

Obergärig

Wein

Erfurter Bock

Ilmenauer Pils

AnhaltinischFlüssig

WeißweinRotwein

Getränke

Dornfelder Blaubur-gunder

PortugieserWeißherbstt Riesling

Untergärig

BerlinerWeiße

Bier

Müller-Thurgau



Analyseanforderungen

nach aktueller Struktur: as isnach definierter historischer Struktur: as ofgemäß historischer Wahrheit: as postedvergleichbare Resultate



Beispiel für Slowly Changing Dimensions

Produktdimension 2010Produkt ProduktgruppeMüller-Thurgau WeißweinRiesling WeißweinDornfelder RotweinPortugieser Weißherbst Rotwein

Produktdimension 2011Produkt ProduktgruppeMüller-Thurgau WeißweinRiesling WeißweinPortugieser Weißherbst WeißweinBlauburgunder RotweinDornfelder Rotwein

FaktenProdukt Jahr PaketeDornfelder 2010 1000Müller-Thurgau 2010 1000Portugieser Weißherbst 2010 1000Riesling 2010 1000Blauburgunder 2011 1000Dornfelder 2011 1000Müller-Thurgau 2011 1000Portugieser Weißherbst 2011 1000Riesling 2011 1000



Berichtsanforderungen

aktuelle StrukturProduktgruppe Pakete 2010 Pakete 2011Weißwein 3000 3000Rotwein 1000 2000

alte StrukturProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 2000Rotwein 2000 2000

historische WahrheitProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 3000Rotwein 2000 2000

vergleichbare ResultateProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 2000Rotwein 1000 1000



. . . mit aktueller Struktur



aktuelle StrukturProduktgruppe Pakete 2010 Pakete 2011Weißwein 3000 3000Rotwein 1000 2000



. . . nach alter Struktur

Produktdimension 2010Produkt ProduktgruppeMüller-Thurgau WeißweinRiesling WeißweinDornfelder RotweinPortugieser Weißherbst Rotwein


alte StrukturProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 2000Rotwein 2000 2000



. . . nach historischer WahrheitProduktdimension 2010

Produkt ProduktgruppeMüller-Thurgau WeißweinRiesling WeißweinDornfelder RotweinPortugieser Weißherbst Rotwein



historische WahrheitProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 3000Rotwein 2000 2000



. . . mit vergleichbaren ResultatenProduktdimension 2010

Produkt ProduktgruppeMüller-Thurgau WeißweinRiesling WeißweinDornfelder RotweinPortugieser Weißherbst Rotwein



vergleichbare ResultateProduktgruppe Pakete 2010 Pakete 2011Weißwein 2000 2000Rotwein 1000 1000



Realisierungen

Anpassung des historischen Datenmaterials an neue Strukturenkleiner Datenbestand, aber alte Strukturen sind nicht vorhandenSeparate Speicherung des historischen Datenbestandeszusätzlich zu neuen Strukturengroßer Datenbestand und Komplexität für Anwender, aber alteStrukturen abrufbarAufbau paralleler Hierarchien mit allen StrukturenDimensionsstruktur komplex, beliebiges Reporting möglichGültigkeitsstempelbeliebige Strukturen abrufbar, aber Performancefrage



„As is“-Szenario

Einfach zu implemetierenKeine HistorieNur für „as is“

Getraenk_ID Produkt Produktgruppe1 Dornfelder Rotwein2 Müller-Thurgau Weißwein

Getraenk_ID Produkt Produktgruppe1 Dornfelder Rotwein2 Müller-Thurgau Weißwein3 Portugieser Weißherbst Rotwein3 Portugieser Weißherbst Weißwein



„As is“ & quasi „as of“-Szenario

Etwas höherer SpeicherbedarfHistorie ohne ZeitzuordnungEntspricht paralleler Hierarchie

Getraenk_ID Produkt Produktgruppe Alte_Gruppe1 Dornfelder Rotwein Rotwein2 Müller-Thurgau Weißwein Weißwein3 Portugieser Weißherbst Rotwein Rotwein

Getraenk_ID Produkt Produktgruppe Alte_Gruppe1 Dornfelder Rotwein Rotwein2 Müller-Thurgau Weißwein Weißwein3 Portugieser Weißherbst Weißwein Rotwein



„As is“ & „as of“-Szenario: Versionierung

Etwas höherer SpeicherbedarfHistorie ohne ZeitzuordnungKünstliche Dimensionsschlüssel notwendig

Getraenk_ID Produkt Produktgruppe Version G_ID_alt aktiv1 Dornfelder Rotwein 1 1 X2 Müller-Thurgau Weißwein 1 2 X3 Portugieser Weißherbst Rotwein 1 3 X

Getraenk_ID Produkt Produktgruppe Version G_ID_alt aktiv1 Dornfelder Rotwein 1 1 X2 Müller-Thurgau Weißwein 1 2 X3 Portugieser Weißherbst Rotwein 1 33 Portugieser Weißherbst Weißwein 2 6 X



„As is“ & „as of“-Szenario: Zeitstempel

Höherer Speicherbedarf

Historie mit Zeitzuordnung

Künstliche Dimensionsschlüssel (oder zusammengesetzte Schlüssel) notwendig

in den Fakten Verknüpfung mit Getraenk_ID

G_ID G_ID_alt Produkt Produktgruppe Gültig_Von Gültig_Bis1 1 Dornfelder Rotwein 01.01.2010 31.12.99992 2 Müller-Thurgau Weißwein 01.01.2010 31.12.99993 3 Portugieser Weißherbst Rotwein 01.01.2010 31.12.9999

G_ID G_ID_alt Produkt Produktgruppe Gültig_Von Gültig_Bis1 1 Dornfelder Rotwein 01.01.2010 31.12.99992 2 Müller-Thurgau Weißwein 01.01.2010 31.12.99993 3 Portugieser Weißherbst Rotwein 01.01.2010 31.12.20103 6 Portugieser Weißherbst Weißwein 01.01.2011 31.12.9999



„As posted“-Szenario

Höherer SpeicherbedarfHistorie zum TransaktionszeitpunktKünstliche Dimensionsschlüssel (oder zusammengesetzteSchlüssel) notwendigin den Fakten Verknüpfung mit G_ID_alt

Getraenk_ID Produkt Produktgruppe Version G_ID_alt1 Dornfelder Rotwein 1 12 Müller-Thurgau Weißwein 1 23 Portugieser Weißherbst Rotwein 1 3

Getraenk_ID Produkt Produktgruppe Version G_ID_alt1 Dornfelder Rotwein 1 12 Müller-Thurgau Weißwein 1 23 Portugieser Weißherbst Rotwein 1 33 Portugieser Weißherbst Weißwein 2 6



„As is“ & „as of“ & „as posted“-Szenario: Snapshot

Höherer Speicherbedarf in Dimensions- und FaktentabellenFlag für aktuelle ZuordnungKünstliche Ladezeitpunktdatennur für “kleine“ Dimensionen empfehlenswert

Getraenk_ID Produkt Produktgruppe Ladedatum aktuell1 Dornfelder Rotwein 01.01.2010 02 Müller-Thurgau Weißwein 01.01.2010 03 Portugieser Weißherbst Rotwein 01.01.2010 01 Dornfelder Rotwein 01.01.2011 12 Müller-Thurgau Weißwein 01.01.2011 13 Portugieser Weißherbst Weißwein 01.01.2011 1



Zusammenfassung

Multidimensionales Datenmodell:I Würfel, Kennzahlen, DimensionenI OLAP-Operationen

konzeptionelle ModellierungstechnikenI kein „Quasi“-Standard wie ER-ModellI spezialisierte Kontrukte für multidimensionale Konzepte

Umsetzung des multidimensionalen ModellsI relational: Star und Snowflake SchemaI MischformenI Vermeidung von Sematikverlusten


Documents

Data-Warehouse-Technologien · Dimensionales Attribut I Element der Menge der Attribute, die vom Primärattribut oder einem Klassiﬁkationsattribut bestimmt werden und nur Top D