Datový sklad pro 200+ poboček: model, který unese růst a fúze

Datový model DWH je jedno z mála rozhodnutí, které nelze lehce vrátit. Za 2 roky produkce máš stovky reportů, tisíce queries, ETL pipelines postavené na konkrétní struktuře. Refactor modelu = prakticky rewrite celého stack. Proto má cenu se na začátku rozhodnout rozumně.

Pro Magistru (síť 200+ lékáren) jsme modelovali DWH od nuly. Tady jsou hlavní rozhodnutí, která nás stála čas a která by stála ještě víc času, kdybychom je udělali špatně.

Star schema vs. Data Vault — rozhodl star, ale skoro ne

Dvě hlavní dimensional modeling paradigmata:

• Star schema (Kimball): fact tables uprostřed, dimension tables okolo, denormalizované. Optimalizované pro BI dotazy, jednoduché k dotazování, intuitivní.
• Data Vault: hub/link/satellite model, normalizovaný, historicky robustní, ale složitý pro dotazování — vyžaduje semantickou vrstvu.

Pro Magistru jsme uvažovali oboje. Vybrali jsme hybrid: Data Vault v raw/staging vrstvě, Star schema v presentation vrstvě.

Data Vault dole protože source systémy mají turbulentní historii — lékárny přicházely do sítě v různých časech, organizační struktury se měnily, dodavatelské kódy se refaktorovaly. DV model toto snáší, protože každá změna je prostě nový satellite row, ne update.

Star nahoře protože finance, marketing, operations chtějí reportovat jednoduchými SQL dotazy bez mentální mapy Hub/Link struktur. Star je intuitivní.

Problém 1: Fúze poboček

Magistra koupila 3 menší sítě za poslední 4 roky. V okamžik fúze dvě lékárny, které měly vlastní ID v jiném systému, dostávají jedno shared ID v hlavním systému. Jak to modelovat v DWH?

Naivní přístup: overwrite — lékárna má jedno ID, history všech transakcí je pod novým ID. NE. Historická comparison se rozbije. Manager se ptá „jaký byl obrat lékárny X v 2023“, a dostane jiná čísla než před fúzí.

Správně: SCD Type 2 (Slowly Changing Dimension) v pharmacy dimension:

dim_pharmacy {
  pharmacy_key: 12345  (surrogate key)
  pharmacy_business_id: 'APOT-091'
  pharmacy_name: 'Lékárna Na Rohu'
  region: 'Praha 10'
  valid_from: '2022-01-01'
  valid_to: '2024-03-15'
  is_current: false
  merged_into: 67890  (pointer na další SCD row)
}

Fact table referencuje pharmacy_key (surrogate), ne business ID. Historické facts ukazují na staré SCD řádky, nové na nové. Report „obrat lékárny X za 2023“ přes business ID joinuje správně i staré period.

Problém 2: Late-arriving dimensions

Lékárna zaregistruje transakci v systému, ale kompletní metadata (zařazení do regionu, supervisor, kategorie) přicházejí až po 3 dnech z HR systému. Do té doby je transakce v DWH s prázdnými dimensionami.

Řešení: early-arriving facts pattern. Pharmacy dimension má row „UNKNOWN“ (pharmacy_key = 0). Transakce, která přijde před metadata, facts referencuje 0. Když metadata přijdou, ETL updatuje facts na správný pharmacy_key.

Alternativa: čekat s loadem facts, dokud dim není ready. Ale to znamená latence 3+ dny pro BI, což není přijatelné. Early-arriving s late-fix je lepší.

Problém 3: Produktový katalog 50k SKU

Každá lékárna má 15-25k SKU z dodavatelského katalogu, ale centrální catalog pokrývá 50k+ SKU (některé sezónní, některé regional). Fact table (sales transactions) potřebuje ukazovat na product dimension.

Při 200 lékárnách × 25k SKU × 100 transakcí/den = ~500M řádků ročně ve fact_sales. Product dimension musí být indexovaná a odtlumenačená agresivně.

Co nám sedlo:

• product_key jako bigint surrogate — ne hash, ne natural key. Index-friendly.
• Denormalizovat často used attributes (category, supplier, pharmaceutical_group) přímo do fact_sales. Disk je levný, query latence drahá.
• Pre-computed aggregates na nejčastější dimenze (sales by category by month), ne jen raw facts.

Problém 4: Ceny se mění, retrospektivně

Cena produktu se mění denně. Někdy retrospektivně (errata dodavatele). Jak modelovat „obrat za leden 2024“, když dodavatel v březnu 2024 poslal ceník s opravou cen za leden?

Dvě legitimní interpretace:

• As-of-then: obrat v lednu = jak se to reportovalo tehdy. Accountingově správné.
• As-of-now: obrat v lednu = s nejnovějšími opravenými cenami. Pro trend analýzu lepší.

My jsme modelovali oboje. Fact_sales má dva price sloupce: price_at_time (immutable, as of sale) a price_current (overwrite při cenových korekcích). Reporty tagované „accounting“ používají price_at_time, „analytical“ price_current.

Problém 5: Tenant isolation vs. shared analytics

Magistra ma jedna centrální DWH, ale některé lékárny jsou franšízanti s právem vidět jen svoje data. Zároveň central management chce cross-pharmacy benchmarking („tvoje marže vs. average“).

Řešení: row-level security na dim_pharmacy. Franšízant přihlášený přes jeho Entra ID vidí v BI jen facts, kde pharmacy_region = jeho. Central management vidí vše. Benchmark dashboardy ukazují agregáty, ne per-pharmacy detail.

Co nefungovalo

Dva rozhodnutí, která jsme museli později revidovat:

• Original DW měl den jako grain fact_sales. Zbytečně agregace, ztráta granularity. Rewrite na transaction-level grain po 5 měsících. Pain — ale správný.
• Chtěli jsme jedna fact table pro all sales. Rozhodl problém mezi prescription a OTC — jiná schema, jiná dimensionality. Rozdělili jsme na fact_sales_prescription a fact_sales_otc. Dva facts, sdílená dimension, čistější model.

Co si z toho vzít

DW model je dlouhodobé rozhodnutí. Ne každé rozhodnutí musí být dokonalé, ale některá chyby jsou drahé:

• Použijte surrogate keys. Natural keys (business IDs) se mění, surrogate ne.
• SCD Type 2 na dimensionách, které mohou fúzovat. Lékárny, zákazníci, produktové kategorie.
• Modelujte history s respektem k business reality — fúze, split, rename.
• Grain fact table na nejnižší rozumnou úroveň. Lépe transaction než den, lépe den než měsíc.
• Denormalizujte často-dotazované attributes do fact table. Query latency > disk cost.

Magistra DWH po 8 měsících provozu obsahuje ~4 TB, má 40+ reportů v Power BI, průměrná dotazová latence pod 2 sekundy. Rozhodnutí na začátku byla to, co udělalo rozdíl.