Star - Schema - BS 7 Augsburg

Star - Schema
Name
1
AnPr
Klasse
Datum
OLAP vs. OLTP
In den RDBMS Konfigurationen unterscheidet man zwei verschiedene Grundtypen:
OLTP: OnLine Transactional Processing ist für die Transaktionsprozesse und somit zur funktionalen Abbildung bzw. Unterstützung der eigentlichen Geschäftsprozesse gedacht. Hier finden wir folgende grundlegende
Eigenschaften wieder:
 Redundanzfreie Abbildung aller Daten im Datenmodell
 Sehr hohe Ausfallsicherheit (meistens durch gespiegelte Systeme)
 Viele User und Transaktionen werden unterstützt
OLAP: OnLine Analytical Processing ist für die Gewinnung von Erkenntnissen aus den Daten konzipiert. Es
greifen im Regelfall keine direkten Interfaces auf die OLAP Datenbanken zu, welche direkt wieder den Geschäftsprozess beeinflussen. Wir finden bei OLAP folgende Eigenschaften:
 Unterstützung von Entscheidungen (mittels Analysetools, „Dashboards“ etc.)
 Abfrageoptimierte Abbildung relevanter Daten
 Sehr hoher Speicherbedarf
 Wenige User und Transaktionen
OLAP Datenbanken finden wir im Regelfall in sogenannten „Datamarts“, in denen große Mengen an Informationen für Analysezwecke bereitgehalten werden.
Das Hauptproblem der OLTP Architekturen ist die Vermeidung von Redundanzen und die damit einhergehende Notwendigkeit von Joins für fachlich relevante Abfragen. Diese Joins können bei intensiveren Datenanalysen zu erheblichen Performanceeinbußen führen, wodurch die OLTP Datenbanken mitunter für schnelle
Datenanalysen nicht mehr zu gebrauchen sind.
2
Der Weg der Daten
Die „Datenwahrheit“ bleibt in dem OLTP Datenbanken, da hier die Geschäftsprozesse laufen. Von dort aus
werden sie mittels eines ETL Prozesses (Extract Transfer Load) in die gewünschte Form gebracht. Hierbei
werden die ursprünglichen Daten nicht verändert, sondern in eine neue Datenbank kopiert.
Mitunter werden in den Analysedatenbanken historische Informationen benötigt, welche zeitlich weiter zurückgreifen, als es die OLTP Datenbanken ermöglichen. Hierbei werden teilweise auch Zwischenschritte eingebaut, um eine saubere Historie zu ermöglichen. Meist wird bereits eine Veränderung der Daten in Richtung
ANPR_StarSchema_v03.docx
Seite 1
Star - Schema
AnPr
Zwischenstatus durchgeführt – man geht von den quellsystemgetriebenen Datenmodell hin zu einem fachlich
getriebenen Datenmodell („Corporate Data Model“) hin, welches auch historisch vorliegt. Man bezeichnet
diese Datenhaltung meist als „Datawarehouse“, kurz DWH. Von dort werden die Daten in die analyseoptimierte Struktur, dem „Datamart“ weitergeleitet. Ziel ist es am Ende aber immer, die Daten derart bereitzustellen, dass Analysen möglichst einfach und vor allem schnell zu ermöglichen.
3
Das Star Schema
Üblicherweise werden in Schemabilder die Analyseplattformen als Würfel („Cubes“) dargestellt. Dies soll das
Grundkonzept der Analysen verdeutlichen. Im Regelfall ist man an Geschäftszahlen interessiert(wie bspw.
„Sales“). Diese müssen nun nach gewissen Gesichtspunkten
zusammengefasst werden. Diese Gesichtspunkte können
bspw. die Zeit sein – also „Wie viel Sales wurde im ersten
Quartal 2014 gemacht?“. Eine andere benötigte Information ist
„Welcher Betreuer hat am meisten verkauft?“. Diese Sichtweisen kann man sich als Dimensionen vorstellen. Wenn man sich
nun die Informationen in drei Dimensionen aufteilt – sagen wir
Betreuer, Kunde und Datum, dann würde dies wie ein Würfel
aussehen, der aus vielen kleinen Einzelwürfeln besteht. Jeder
Einzelwürfel trägt die Information des Salesbetrages zu der zugehörigen Zeit, dem Betreuer und dem Kunden.
Nun kann man in den drei verschiedenen Dimensionen Summen bilden und könnte somit verschiedene Aspekte des Sales analysieren. In der Realität gibt es natürlich mehr als nur drei Dimensionen, da wir auch nach
Branchen, Personen usw. gruppieren möchten. Da dies aber grafisch nicht darstellbar ist, beschränkt man sich
bei Erklärungen meist auf drei Dimensionen.
Die tatsächliche Abbildung auf der Datenbank erfolgt im sogenannten „Star Schema“, in dem die Faktentabelle im Zentrum
steht und die Dimensionstabellen wie Sternenarme außen herum
geordnet sind.
Die Schlüssel der Dimensionen bilden wiederum den kombinierten Schlüssel der Faktentabelle. In den Dimensionstabellen stehen Werte und Eigenschaften, welche für die Spätere Auswertung und Einschätzung interessant sind, wie bspw. Kundennamen
oder Rechtsformen. Die Faktentabelle trägt (neben den Schlüsseln) lediglich die Betragsfelder, welche den eigentlichen Kern
der Analyse darstellen.
Wie man bereits sieht, ist das Star Schema immer auf einen bestimmten Analysebereich ausgerichtet. Die
Anforderungen für ein solches Datenmodell kommen also von den Personen, welche Auswertungen durchführen müssen, nicht von den Systemen, welche die Daten bereitstellen.
Eine leicht veränderte Version des Star Schemas ist das sogenannte „Snowflake Schema“. Dieses berücksichtigt, dass es Dimensionen gibt, die direkt voneinander abhängen. So würde man im Snowflake Schema die
Branche nicht am Fakt „Sales“ hängen, sondern an die Dimensionstabelle der Firma, da eine Firma in unserem
Quelldatenmodell immer einer bestimmten Branche zugeordnet ist. Dies hat den Vorteil, dass weniger Redundanzen entstehen, allerdings den Nachteil, dass wieder mehr Joins notwendig werden.
In der Praxis gilt es noch zusätzliche Probleme zu meistern. Ein Beispiel wären die „Slowly Changing Dimensions“. Dies sind Änderungen in den Dimensionen – wenn bspw. für einen Kunden der Betreuer wechselt.
Hier müssen die Änderungen dergestalt in den Datenhaushalt eingebaut werden, dass der Auswertende sinnvolle Ergebnisse erhält. Dies bedeutet, dass es für einen Betreuerwechsel je nach Auswertungsnotwendigkeit
unterschiedliche Lösungsansätze gibt. Da dies jedoch den Inhalt sprengen würde, sei hier auf das Internet
verwiesen.
Seite 2
AnPr
4
Star - Schema
Aufgabenstellung
Um das Ganze praktisch zu untermauern,
soll nun ein Star Schema erstellt und bewirtschaftet werden. Als Datenbasis für das im
vorausgegangenen Kapitel bereits vorgestellte Schema die nebenstehenden Tabellen.
Wie wir schon erahnen können, wird das
Umstellen auf ein Star Schema nicht sehr
viel bringen, da die Datenbasis vergleichsweise einfach aufgebaut ist. Trotzdem wird ein Performancegewinn bei den Abfragen zu erwarten sein.
4.1 Die Dimensionstabellen
Da die Faktentabelle die Schlüssel aus den Dimensionstabellen bezieht, beginnen wir mit den Dimensionstabellen.
Tabelle DimPartner:
Spaltenname:
PartnerID
PrimAnsprechpersonName
Struktur:
INT (Primary
Key)
VARCHAR(60)
PrimGeschl
CHAR(1)
Quelle:
1:1 aus Partner.
Nachname + Vorname aus Tabelle Person.
Bei Firmen: erste. auffindbare Person der Firma (also
Primärer Ansprechpartner). Bei Privatkunden: Person.
Aus Person (Datensatzauswahl siehe oben)
Tabelle DimBetreuer:
Spaltenname:
BetreuerID
Struktur:
INT (Primary
Key)
VARCHAR(60)
CHAR(1)
Quelle:
1:1 aus Partner.BetreuerID.
Quelle:
Bei Firmen 1:1 aus Firma. Bei Privatkunden Wert „-1“
Name
Struktur:
INT (Primary
Key)
VARCHAR(100)
RechtsForm
CHAR(5)
Name
Geschl
Nachname + Vorname aus Tabelle Person
Aus Person (Datensatzauswahl siehe oben)
Tabelle DimFirma:
Spaltenname:
FirmaID
Bei Firmen 1:1 aus Firma. Bei Privatkunden Wert „Privatkunde“
Bei Firmen 1:1 aus Firma. Bei Privatkunden Wert
„PRV“
Tabelle DimBranche:
Spaltenname:
BrancheID
Bezeichnung
Struktur:
INT (Primary
Key)
VARCHAR(100)
Quelle:
Bei Firmen 1:1 aus Branche. Bei Privatkunden Wert „1“
Bei Firmen 1:1 aus Branche. Bei Privatkunden Wert
„Privatkunden“
Seite 3
Star - Schema
AnPr
Tabelle DimDatum: Die Datumstabelle ist eine besondere Tabelle, da die Daten für die Auswertungszwecke
speziell aus dem Datumswert erzeugt werden. Auch werden die Keys eigens für das Star Schema erstellt:
Spaltenname:
DatumID
Datum
Tag
Wochentag
Monat
Quartal
Jahr
Struktur:
INT (Primary
Key)
DATE
TINYINT
TINYINT
TINYINT
TINYINT
SMALLINT
Quelle:
AUTO INCREMENT
1:1 aus Bestellung
Errechnet aus Datum
Errechnet aus Datum
Errechnet aus Datum
Errechnet aus Datum
Errechnet aus Datum
4.2 Die Faktentabelle
Basierend auf den Dimensionkeys kann nun die Faktentabelle definiert werden:
Tabelle FctSales:
Spaltenname:
DatumID
PartnerID
BetreuerID
BrancheID
FirmaID
Porto
BestBetrag
Struktur:
INT (Primary
Key)
INT (Primary
Key)
INT (Primary
Key)
INT (Primary
Key)
INT (Primary
Key)
DECIMAL(7,2)
DECIMAL(10,2)
Quelle:
DimDatum
DimPartner
DimBetreuer
DimBrache
DimFirma
Summe aus Bestellung
Summe Betrag aus Bestellung
4.3 Die Skripte
Erstellen Sie nun ein Skript, welches die Datenbankstruktur bereitstellt. Anschließend ein Skript, welches die
Daten aus dem existierenden HardWhere Datenmodell in das Star Datenmodell migriert. Hierzu reichen in
unserem Fall normale SQL Mittel.
Empfehlung:
Bewirtschaften Sie zuerst die Dimensionstabellen, anschließend lediglich die Keys der Faktentabelle. Erst zum
Schluss werden über ein Updatestatement die Faktenbeträge übernommen.
Hilfestellung für Update aus einem Select:
UPDATE Zieltabelle, (
SELECT z.KeyFeld, q.WerteFeld FROM Quelltabelle q, Zieltabelle z
WHERE z.KeyFeld = q.KeyFeld) Quelle
SET Zieltabelle.WerteFeld = Quelle.WerteFeld
WHERE Zieltabelle.KeyFeld = Quelle.KeyFeld;
Hier wird die Zieltabelle mittels „SET“ geupdated. Die Daten kommen aus einer Quelltabelle und werden
mittels einem Subselect ermittelt (dieses müssen Sie natürlich auf die Problemstellung anpassen).
Seite 4
AnPr
5
Star - Schema
Die Abfragen
Für folgende Abfragen sollen Sie einmal über das Star Schema gehen und einmal die Informationen direkt aus
dem Quelldatenmodell beziehen und jeweils die Performance notieren.
Aufgabenstellung:
Ergebnis:
Quartal mit den meisten Umsätzen
4/2012
Betreuer mit den meisten Umsätzen
Becker Petra
Branche mit den meisten Umsätzen
Gastronomie,
Restaurants
Zeit OLAP:
Zeit OLTP:
Der Betreuer, welcher in der Branche 132 im Becker Petra
ersten Quartal 2009 den meisten Umsatz gemacht hat.
Der Kunde, welcher den meisten Umsatz ge- Riemer Josef
macht hat
Der Kunde (Ansprechpartner), welcher im Schneider Hildezweiten Quartal 2012 den meisten Umsatz ge- gard
macht hat.
Seite 5
Star - Schema
6
AnPr
Lizenz
Diese(s) Werk bzw. Inhalt von Maik Aicher (www.codeconcert.de) steht unter einer
Creative Commons Namensnennung - Nicht-kommerziell - Weitergabe unter gleichen
Bedingungen 3.0 Unported Lizenz.
Seite 6