Ein Data Warehouse (DWH) ist ein zentraler Bestandteil der Business Intelligence (BI) und dient der Speicherung, Organisation und Analyse großer Datenmengen. Die Struktur eines DWH basiert oft auf dem sogenannten Sternschema (Star Schema), das aus Dimensionen und Fact-Tables besteht. In diesem Blogbeitrag werden wir die verschiedenen Arten von Dimensionen und Fact-Tables detailliert betrachten, Beispiele geben und auch einige Skripte zur Veranschaulichung bereitstellen.
1. Dimensionen im Data Warehouse
Dimensionen sind Tabellen, die beschreibende Informationen enthalten und den Kontext für die Fakten in den Fact-Tables liefern. Sie sind die "Wer, Was, Wo, Wann" eines Data Warehouses. Es gibt verschiedene Arten von Dimensionen:
a) Reguläre Dimensionen
Reguläre Dimensionen sind die häufigsten und enthalten statische Daten, die sich selten ändern. Beispiele sind Produkt-, Kunden- oder Zeitdimensionen.
Beispiel: Produktdimension
CREATE TABLE Dim_Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10, 2),
SupplierID INT
);
b) Slowly Changing Dimensions (SCD)
SCDs werden verwendet, wenn sich die Daten in einer Dimension im Laufe der Zeit ändern. Es gibt drei Haupttypen: - SCD Typ 1: Überschreibt alte Daten mit neuen Daten. - SCD Typ 2: Behält alte Daten bei und fügt neue Zeilen für geänderte Daten hinzu. - SCD Typ 3: Fügt eine neue Spalte hinzu, um sowohl alte als auch neue Werte zu speichern.
Beispiel: SCD Typ 2 (Kundendimension)
CREATE TABLE Dim_Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Address VARCHAR(200),
City VARCHAR(50),
State VARCHAR(50),
StartDate DATE,
EndDate DATE,
IsCurrent BIT
);
c) Junk Dimensionen
Junk Dimensionen kombinieren mehrere kleine, nicht zusammenhängende Attribute, die keine eigene Dimension rechtfertigen.
Beispiel: Junk Dimension
CREATE TABLE Dim_Junk (
JunkID INT PRIMARY KEY,
PaymentMethod VARCHAR(50),
ShippingMethod VARCHAR(50),
OrderPriority VARCHAR(20)
);
d) Degenerierte Dimensionen
Degenerierte Dimensionen sind Attribute, die keine eigene Tabelle haben, sondern direkt in der Fact-Tabelle gespeichert werden.
Beispiel: Bestellnummer in einer Fact-Tabelle
CREATE TABLE Fact_Sales (
SalesID INT PRIMARY KEY,
OrderID INT, -- Degenerierte Dimension
ProductID INT,
CustomerID INT,
SalesAmount DECIMAL(10, 2)
);
e) Conformed Dimensions
Conformed Dimensions sind Dimensionen, die in mehreren Data Marts oder DWHs verwendet werden, um Konsistenz zu gewährleisten.
Beispiel: Zeitdimension
CREATE TABLE Dim_Time (
TimeID INT PRIMARY KEY,
Date DATE,
Day INT,
Month INT,
Year INT,
Quarter INT
);
2. Fact-Tables im Data Warehouse
Fact-Tables enthalten die quantitativen Daten (Fakten), die analysiert werden sollen. Sie sind mit Dimensionen über Fremdschlüssel verbunden.
a) Transaktions-Fact-Tables
Diese Tabellen speichern Daten auf Transaktionsebene, z. B. jede Verkaufs- oder Bestelltransaktion.
Beispiel: Verkaufs-Fact-Tabelle
CREATE TABLE Fact_Sales (
SalesID INT PRIMARY KEY,
ProductID INT,
CustomerID INT,
TimeID INT,
SalesAmount DECIMAL(10, 2),
Quantity INT,
FOREIGN KEY (ProductID) REFERENCES Dim_Product(ProductID),
FOREIGN KEY (CustomerID) REFERENCES Dim_Customer(CustomerID),
FOREIGN KEY (TimeID) REFERENCES Dim_Time(TimeID)
);
b) Periodische Snapshot-Fact-Tables
Diese Tabellen speichern Daten zu bestimmten Zeitpunkten, z. B. monatliche Kontostände.
Beispiel: Monatlicher Kontostand
CREATE TABLE Fact_AccountBalance (
BalanceID INT PRIMARY KEY,
AccountID INT,
TimeID INT,
BalanceAmount DECIMAL(10, 2),
FOREIGN KEY (TimeID) REFERENCES Dim_Time(TimeID)
);
c) Akkumulierte Snapshot-Fact-Tables
Diese Tabellen verfolgen den Fortschritt eines Prozesses über mehrere Schritte hinweg, z. B. eine Bestellung von der Platzierung bis zur Lieferung.
Beispiel: Bestellprozess
CREATE TABLE Fact_OrderProcess (
OrderID INT PRIMARY KEY,
ProductID INT,
CustomerID INT,
OrderDateID INT,
ShipDateID INT,
DeliveryDateID INT,
OrderAmount DECIMAL(10, 2),
FOREIGN KEY (ProductID) REFERENCES Dim_Product(ProductID),
FOREIGN KEY (CustomerID) REFERENCES Dim_Customer(CustomerID),
FOREIGN KEY (OrderDateID) REFERENCES Dim_Time(TimeID),
FOREIGN KEY (ShipDateID) REFERENCES Dim_Time(TimeID),
FOREIGN KEY (DeliveryDateID) REFERENCES Dim_Time(TimeID)
);
3. Beispiel: Sternschema in der Praxis
Angenommen, wir haben ein einfaches Data Warehouse für einen Online-Shop. Das Sternschema könnte wie folgt aussehen:
- Dimensionen:
Dim_Product(Produktinformationen)Dim_Customer(Kundeninformationen)Dim_Time(Zeitinformationen)
- Fact-Tabelle:
Fact_Sales(Verkaufstransaktionen)
SQL-Skript zur Erstellung des Schemas:
-- Dimensionen
CREATE TABLE Dim_Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10, 2)
);
CREATE TABLE Dim_Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Address VARCHAR(200),
City VARCHAR(50),
State VARCHAR(50)
);
CREATE TABLE Dim_Time (
TimeID INT PRIMARY KEY,
Date DATE,
Day INT,
Month INT,
Year INT
);
-- Fact-Tabelle
CREATE TABLE Fact_Sales (
SalesID INT PRIMARY KEY,
ProductID INT,
CustomerID INT,
TimeID INT,
SalesAmount DECIMAL(10, 2),
Quantity INT,
FOREIGN KEY (ProductID) REFERENCES Dim_Product(ProductID),
FOREIGN KEY (CustomerID) REFERENCES Dim_Customer(CustomerID),
FOREIGN KEY (TimeID) REFERENCES Dim_Time(TimeID)
);
4. Fazit
Dimensionen und Fact-Tables sind die Grundbausteine eines Data Warehouses. Durch die richtige Modellierung dieser Komponenten können Unternehmen effizient Daten analysieren und fundierte Geschäftsentscheidungen treffen. Die Wahl der richtigen Dimensionstypen und Fact-Tables hängt von den spezifischen Anforderungen des Unternehmens und den zu analysierenden Daten ab.