Data Warehouse für 200+ Filialen: ein Modell, das Wachstum und Fusionen verkraftet

Das Datenmodell eines DWH ist eine der wenigen Entscheidungen, die sich nicht leicht revidieren lassen. Nach 2 Jahren in Produktion haben Sie Hunderte Reports, Tausende Queries, ETL-Pipelines, auf einer spezifischen Struktur gebaut. Ein Model-Refactor = praktisch Rewrite des ganzen Stacks. Deshalb lohnt sich eine vernünftige Wahl am Anfang.

Für Magistra (Netz von 200+ Apotheken) haben wir das DWH von Grund auf modelliert. Hier die Hauptentscheidungen, die uns Zeit gekostet haben — und die noch mehr Zeit gekostet hätten, wenn sie falsch gefallen wären.

Star Schema vs. Data Vault — Star hat gewonnen, aber knapp

Zwei Haupt-Dimensional-Modeling-Paradigmen:

• Star Schema (Kimball): Fact-Tables in der Mitte, Dimensionen drumherum, denormalisiert. Optimiert für BI-Queries, einfach abzufragen, intuitiv.
• Data Vault: Hub/Link/Satellite-Modell, normalisiert, historisch robust, aber Query-lastig — braucht eine semantische Schicht.

Für Magistra haben wir beides erwogen. Wir sind hybrid gegangen: Data Vault in der Raw-/Staging-Schicht, Star Schema in der Presentation-Schicht.

Data Vault unten, weil Source-Systeme turbulente Historie haben — Apotheken traten zu verschiedenen Zeiten bei, Organisationsstrukturen ändern sich, Lieferanten-Codes werden refaktoriert. DV absorbiert das, weil jede Änderung einfach eine neue Satellite-Zeile ist, kein Update.

Star oben, weil Finance, Marketing, Operations mit einfachem SQL reporten wollen, ohne mentale Karte von Hub/Link-Strukturen. Star ist intuitiv.

Problem 1: Filial-Fusionen

Magistra hat 3 kleinere Netze in den letzten 4 Jahren gekauft. Im Fusionsmoment bekommen zwei Apotheken mit eigenen IDs in anderem System eine geteilte ID im Hauptsystem. Wie modellieren im DWH?

Naiver Ansatz: Overwrite — Apotheke hat eine ID, Historie aller Transaktionen unter der neuen ID. Nein. Historische Vergleiche brechen. Manager fragt „was war Umsatz von Apotheke X 2023?“ und bekommt andere Zahlen als vor der Fusion.

Richtig: SCD Type 2 (Slowly Changing Dimension) in der Pharmacy-Dimension:

dim_pharmacy {
  pharmacy_key: 12345  (Surrogate Key)
  pharmacy_business_id: 'APOT-091'
  pharmacy_name: 'Apotheke Am Eck'
  region: 'Prag 10'
  valid_from: '2022-01-01'
  valid_to: '2024-03-15'
  is_current: false
  merged_into: 67890  (Pointer auf nächste SCD-Zeile)
}

Fact-Table referenziert pharmacy_key (Surrogate), nicht Business-ID. Historische Facts zeigen auf alte SCD-Zeilen, neue auf neue. Report „Umsatz Apotheke X 2023“ via Business-ID joint korrekt auch über alte Perioden.

Problem 2: Late-Arriving-Dimensions

Eine Apotheke registriert eine Transaktion im System, aber komplette Metadaten (Region-Zuordnung, Supervisor, Kategorie) kommen erst 3 Tage später aus dem HR-System. Bis dahin liegt die Transaktion im DWH mit leeren Dimensionen.

Lösung: Early-Arriving-Facts-Pattern. Pharmacy- Dimension hat „UNKNOWN“-Zeile (pharmacy_key = 0). Transaktion vor Metadaten referenziert 0. Kommen Metadaten, updated ETL Facts auf korrekten pharmacy_key.

Alternative: warten mit Facts-Load, bis Dim bereit. Heißt 3+ Tage Latenz für BI, inakzeptabel. Early-Arriving mit Late-Fix ist besser.

Problem 3: 50k-SKU-Produktkatalog

Jede Apotheke hat 15-25k SKUs aus dem Lieferantenkatalog, aber der zentrale Katalog umfasst 50k+ SKUs (manche saisonal, manche regional). Fact-Table (Verkaufstransaktionen) muss auf Product-Dimension zeigen.

Bei 200 Apotheken × 25k SKUs × 100 Transaktionen/Tag = ~500M Zeilen/Jahr in fact_sales. Product-Dimension muss aggressiv indiziert und dehydriert werden.

Was uns gepasst hat:

• product_key als bigint Surrogate — kein Hash, kein Natural Key. Index-freundlich.
• Häufig genutzte Attribute (category, supplier, pharmaceutical_group) direkt in fact_sales denormalisieren. Disk ist billig, Query-Latenz teuer.
• Pre-computed Aggregates auf häufigste Dimensionen (Sales by Category by Month), nicht nur rohe Facts.

Problem 4: Preise ändern sich rückwirkend

Produktpreise ändern sich täglich. Manchmal rückwirkend (Lieferanten-Errata). Wie modellieren „Umsatz Januar 2024“, wenn der Lieferant im März 2024 eine Preisliste mit Korrekturen für Januar schickte?

Zwei legitime Interpretationen:

• As-of-then: Januar-Umsatz = wie damals gemeldet. Accounting-korrekt.
• As-of-now: Januar-Umsatz = mit neuesten korrigierten Preisen. Besser für Trendanalyse.

Wir haben beides modelliert. fact_sales hat zwei Price-Spalten: price_at_time (immutable, zum Verkauf) und price_current (überschrieben bei Preiskorrekturen). „Accounting“-getaggte Reports nutzen price_at_time, „analytical“ price_current.

Problem 5: Tenant-Isolation vs. geteilte Analytics

Magistra hat ein zentrales DWH, aber manche Apotheken sind Franchise mit Recht, nur eigene Daten zu sehen. Gleichzeitig will Central-Management Cross-Pharmacy-Benchmarking („Ihre Marge vs. Durchschnitt“).

Lösung: Row-Level-Security auf dim_pharmacy. Franchise per Entra ID eingeloggt sieht im BI nur Facts mit pharmacy_region = seine. Central-Management sieht alles. Benchmark-Dashboards zeigen Aggregate, kein Per-Pharmacy- Detail.

Was nicht funktionierte

Zwei Entscheidungen, die wir später revidieren mussten:

• Ursprünglich hatte DW Tages-Granularität in fact_sales. Unnötige Aggregation, verlorene Granularität. Nach 5 Monaten auf Transaction-Level-Granularität umgeschrieben. Schmerzhaft — aber korrekt.
• Wir wollten eine fact_sales für alle Sales. Problem riss zwischen Rezept und OTC — anderes Schema, andere Dimensionalität. Split auf fact_sales_prescription und fact_sales_otc. Zwei Facts, geteilte Dimensions, sauberer.

Zum Mitnehmen

DW-Modeling ist eine Langzeitentscheidung. Nicht jede Wahl muss perfekt sein, aber manche Fehler sind teuer:

• Surrogate Keys verwenden. Natural Keys (Business-IDs) ändern sich, Surrogates nicht.
• SCD Type 2 auf Dimensionen, die fusionieren können. Apotheken, Kunden, Produktkategorien.
• Historie mit Respekt vor Business-Realität modellieren — Fusionen, Splits, Umbenennungen.
• Fact-Table-Granularität auf niedrigstem sinnvollem Level. Transaction über Tag, Tag über Monat.
• Häufig abgefragte Attribute in die Fact-Table denormalisieren. Query-Latenz > Disk-Kosten.

Nach 8 Monaten Magistra-DWH-Betrieb: ~4 TB Daten, 40+ Power-BI-Reports, durchschnittliche Query-Latenz unter 2 Sekunden. Die Entscheidungen am Anfang haben den Unterschied gemacht.