Jak dělám kostky

 

Více společností?

Málokdy je firma jedna, se samostatným IČ. Obvykle jsou společnosti různě provázané pod jedním majitelem nebo je jedna hlavní, pár přikoupených apod.  Pro více společností v rámci holdingu můžu mít:

• A. Pro každou společnost samostatný datový sklad (databázi)
• B. Spojit společnosti do jednoho datového skladu (databáze)
• C. Kombinace

Ad A. Každá společnost samostatně – obvykle je to lepší varianta. Málokteré dvě společnosti jsou shodné. Pro každou společnost budou samostatné tranformace, ideálně úplně od sebe oddělené.
Například Útvary, Účty, Zboží, Skladové pohyby, Deník … se budou pro každou společnost plnit samostatně.
Nevýhoda je že se dost kódu musí opakovat. Napsal jsem si na to pomocný prográmek. Udržuji scripty jen pro jednu společnost (soubory sql v jednom adresáři) a on mi generuje scripty pro další společnosti (jiného adresáře) s tím že zamění názvy databází, zakomentuje/odkomentuje řádky apod.

Ad B. Spojit společnosti – podle mě má smysl jen pokud jsou jsou některé společnosti v rámci holdingu hodně stejné. Typicky obchodní společnosti se stejným sortimentem.
Tranformace pak musí vytvořit jednu tabulku pro obě společnosti. Například Útvary, Účty, Zboží, Skladové pohyby, Deník … budou pro tyto společnosti jen jednou plněné z obou společností.
Zde je velký problém napárování čísleníků na sebe.  To je na samostatný článek.
Obecně v datovém skladu raději nechávám duplicitně  s klíčem ze zdrojové databáze a jednotlivé atributy pak primárně beru z jedné ze společností, aby se přes ně spojily.

Rozhodnutí zda spojovat a co spojovat je bohužel naprosto klíčové. Změna znamená obvykle všechno zahodit a udělat znovu.

Pro koho?

Kdo je cílový uživatel? Pro koho se kostky dělají? Podle mě je klíčové postupovat přes vedoucí útvarů.

Vedoucí útvarů se vyznají v problematice,vědí co je podstatné, co se dá ovlivnit a co ne, co má smysl reportovat a jak. Případně to mohou delegovat.

Také určují kdo z podřízených má mít do kostek přístup.

Práva do kostek se řídí přes skupiny v active directory. Ne vždy odpovídá existující členění skupin v active directory, proto zakládám nové skupiny _OLAP_Spolecnost_Utvar_PripadneDalsiRozliseni
Výhodou je, že se pak dá nastavit analogicky i adresář s reporty apod.
Do active directory se dá vytvořit i připojení z SQL serveru (linked server) a dají se načíst všechny skupiny, kdo v jaké je apod. Dala by se z toho udělat pěkná kostka pro administrátory, ale ještě jsem se k tomu nedostal.

V PowerBI se bohužel nastavuje přímo na mail (myslím že se nedají vytvářet skupiny), což se mi nelíbí.

Typické kostky

Pro jednu společnost/holding potřebuji více kostek. Typicky:

• Účetní/Ekonomický/controlingový útvar
  • KostkaVýsledovka
    • Hospodářský výsledek
    • Plány na útvary
  • KostkaRozvaha
    • Zůstatky a obraty účtů
  • KostkaSaldo
• Logistika
  • KostkaSklady
    • Skladové pohyby
    • Stavy skladů
    • Predikce stavu skladu
• Obchod
  • KostkaTržby
    • Tržby
    • Forecast
• Výroba
  • KostkaVýrobníPříkazy
    • Operace a komponenty plánované (TPV)
    • Operace a komponenty spotřebované
  • KostkaKusovnik
    • Z jakých komponent se sklává výrobek
    • Do jakých výrobků jde komponenta
• Personální
  • KostkaDochazka

Jak?

Kostky nejsou implementace ERP. Těžko se uživatelům představují, obvykle nemají předchozí zkušenost. Udělat nejprve velkou analýzu, a pak uživateli dodat co bylo sepsáno, není podle mě vhodná strategie.

Mnohem lepší je postupovat prototypováním. Udělat základní kostku co nejrychleji aby se dala uživateli ukázat a pak s ním pracovat na rozšiřování.

Má to jednu velkou nevýhodu. Udělat základní kostru kostky je vcelku lehké, v podstatě za pár dnů mám co ukázat.
Jenže to dotažení je obvykle dost pracné. Změny často vyžadují úpravy na stupních systémech, analýzy, komunikaci a to je prostě hodně času (=peněz).

Vrstvy – vertikální členění

Transormacemi data zpracovávám po vrstvách. Rozdělení na vrstvy je velmi rozumné a má spoustu výhod (přehlednost, rozbití velkých tranformací do řady menších, standardizace apod.).

Vždy kompletně naplním jednu vrstvu, pak jdu teprve na druhou. Tím pádem mám vrstvy konzistentní.
Plním vždy jedním směrem, nikdy pozadu. Neboli nikdy nepoužívám tabulku z vyšší vrstvy. Ta pro mě neexistuje.

Každá vrstva má samostatné schéma (prefix), takže tabulky ze stejné vrstvy jsou u sebe.

Standardně používám tyto vrstvy:

• STAGE (o5) – Tabulky ze zdrojových systémů načtené co nejjednodušeji a nejrychleji. Jen sloupce a řádky které potřebuji, minimum joinů. Občas grupování. Nesmí zatěžovat zdrojový systém!
  Občas je třeba přidat index do zdrojovýho systému (typicky na datum případu)
  Tabulky ve stage mají na začátku název zdrojového systému či společnosti.  Třeba stage.HeliosDC, o5TitulEMG apod.
• STAR – (o8) – Dimenzní a faktové tabulky s korektními vazbami v rámci STAR schémat. V podstatě datawarehouse. Plnení této vrstvy je nejdůležitější a nejsložitější.
  Nejprve napočítávám dimenzní tabulky a pak teprve faktové.
  Žádný sloupec není null.
  Standardně pak joinuji dimenze, tak aby se ve faktovce nevyskytnul prvek neexistující v dimenzi. Přes isnull (coalesce) dohazuji výchozí prvek dimenze N/A (klíč buď -1 nebo “).
  Ve star je dimenze a faktovka jen jednou star.DimDatum, star.DimUtvar, star.DimOrganizace. star.FactVysledovka, star.FactSkladovePohyby.
  Musím už tady dát pozor na pojmenování sloupců. Nesmí být sloupec Pocet, Castka, Sleva. Protože až se bude dělat kostka, nepůjde v PowerBi (SSAS) udělat ukazatel  se stejným názvem jako je název sloupce.
  Proto používám pro ukazatele dvouslovné názvy. Tedy PocetMj, CastkaUhrady, CenaCelkem. Ukazatel by měl uprostřed minimálně mezeru (Cena celkem), což je ok.
  
• MARTxxx – (o9) – Ze STAR faktová tabulka a zkopírované dimenzní tabulky jen se záznamy vyskytujícími se ve faktové tabulce. Na rozdíl od STAR která je jedna, je datamartů více.
  Datamart na sql serveru mám oddělený v samostatném schématu. Typicky mEko (ekonomika), mLog (logistika).
  Dimenzní tabulky jsou tedy pro každý datamart nové. Třeba v datamartu Logistika je mLog.DimDatum obsahující data jen za roky, kdy byly skladové pohyby.
  Datamart je přímo podklad pro kostku.
  Do datamartu ještě někdy data grupuji. Je to hlavně kvůli PowerBi kde je omezení na 1G komprimovaných dat, což odpovídá cca 40mio záznamům. Nad toto množství je třeba vyšší licence PowerBi která je 3x dražší.
• VERIFY – konktrolní procedury, které posílají mailem pokud je v datech něco špatně.

Takže STAGE -> STAR -> MART -> VERIFY

Oblasti – horizontální členění

Výsledek transformací jsou kostky. Ale nechci mít jednu velkou transformaci která poběží několik hodin. Protože:

• Když někde něco spadne, nebudu mít napočteno nic
• Špatně se mi bude sledovat co jak dlouho trvá
• Uživatel nebude moc v průbehu dne napočítat jednu kostku

Problém je v tom, že některé kostky mají společné dimenze/tranformace a musím s tím počítat od začátku, už při vytváření tranformací.

Není bohužel žádné univerzální pravidlo jak to udělat. Obvykle mám jednu samostatnou transormaci pro jeden útvar (logistiku,ekonomický,…)

Spouštění na vyžádání (během dne) je obvykle na začátku podceňováno. Podle mě by mělo být možné u každé kostky.
Mám vytvořený report v reporting services, kterým uživatelé pouští job napočítávající kostky. U jobu mám na začátku a na konci mailování že bylo spuštěno/skončeno. Odesílá se jen v průběhu pracovní doby.
Problém s nočním přepočtem je, že uživatel uvidí změnu až druhý den.
Je třeba špatně nastavený číselník. Uživatel opraví. Málokdo dokáže predikovat jak se v kostce projeví. V kostce uvidí druhý den. Je to jinak než chtěl, opraví a zase čeká na další den. V době uzávěrek je to pro uživatele hodně nepříjemné.

Jmenné konvence (Naming conventions)

Obecně:

• Ideální je podle mě používat jednotné číslo (i když s tím někde bojuji). DimUtvar nikoli DimUtvary, DimUzivatel nikoli DimUzivatel, FactSklPohyb nikoli FactSklPohyby
• Zkratky nyní spíš používám než nepoužívám. Občas si i vymyslím.
• Nepoužívám diakritiku, mezery (to rozhodně ne) a exotické znaky v databázi ale používám diakritiku v kostkách.

Databáze

• Databáze pojmenovávám KostkyxSpolecnost, kde x je verze. Například Kostky2Nabla.

Tabulky:

• Názvy tabulek mám standardně česky. Anglicky jen na projektech kde to má smysl.
• Vyhýbám se podtržítkům, používám konvenci PrvniPismenoVelke. Psát podtržítka je pomalejší a hůře čitelné.
  Tedy například stage.HeliosDenik, star.DimSklad.sql, stage.DokladPrvotni
• Začínám vždy podstatným jménem a pak teprve přídavným. Tedy nikdy ne PrvniDoklad, UcetniDenik ale zásadně DenikUcetni, DokladPrvotni
• Tabulky dimenzní začínají Dim, Tabulky faktové začínají Fact. Toto neplatí pro stage, v ní začínají tabulky názvem zdrojového  systému.
• Snažím se hodně o unikátnost, aby se dala tabulka fulltextově najít (a třeba zaměnit).

Sloupce:

• Tabulka má vždy primární klíč a jmenuje se vždy ID.  Dříve jsem měl různá pravidla ale ID je prostě nejlepší.
• Primární klíč je vždy int (bigint, pokud se nevlezu) – toto je věc kterou možná přehodnotím. U char může být problém s některými znaky, lekými/malými písmeny a teoreticky výkoností – pořeboval bych si udělat nějaké výkonostní testy.
• Hodnoty primárního klíče:
  • Přebírám z původního systému – toto používám nejčastěji . Například z Heliosu je to cislo_subjektu, z eshopu ID apod.
  • Přebírám z původního systému s unifikací. Unifikaci dělám přičtením čísla 10000000000, 20000000000 atd.
    Toto používám pokud jdou data za více společností, třeba za dvou Heliosů, aby se mi nepotkaly.
    Takže jedna společnost má cislo_subjektu + 10000000000, druhá má cislo_subjektu + 20000000000 atd.
    Bohužel ti znamená že nestačí int ale je třeba bigint. Přijde mi to nicméně jednodušší a přehlednější než míít dva sloupce.
  • Generovaný (identity1,1) – pokud z nějakého důvodu nemůžu použít předhcozí. Například pokud dimenze hraje na historii.
• Datum ukládám jako int (ISO datum) tedy RRRRMMDD, například 20201025. Zde teoreticky zvažuji změnu na date.
  SSAS i PowerBi s tím nemají problém, jen se bojím případných jiných reportingových nástrojů.
• Pro čísla používám buď money, nebo numeric (19,7) – pro skladové pohyby money nestačí.
• Cizí klíče nedělám na databázi, ani je nekontroluji.

Procedury/transformace

• Pojmenovávám dbo.nsp_ například stage.nsp_HeliosDenik.sql, nsp_DimSklad.sql tedy na začátek dávám nsp_
• Málokdy dělám inkrementální plnění tabulek. Jen pokud je to nezbytné. Musí se dát pozor na parameter sniffing u uložených procedur.
• Osvědčilo se mi místo inkrementálního plnění mít data ve dvou tabulkách. Jedna tabulka stará data, obvykle zagrupovaná a přepočítávaná jen o víkendu. A druhá tabulka s novými daty aktualizovaná denně.

Testování dat

Je klíčové aby byly v kostce data dobře.

Pro kontroly používám:

• Kontrolu na celkový součet. Pokud je suma ukazatele v ERP stejná jako v kostce, je kostka obecně správně. Problém pak může vzniknout pouze na atributu dimenze.
  Například celkový hospodářský výsledek, stav skladu jako součet pohybů apod.
• Kontrola za rok –  pokud nejde kontrola na celkový součas, je možno omezit jen na rok, třeba nějaký proběhlý.
  Například hospodářský výsledek za minulý rok, Tržby za minulý rok.
• Testovací lavice – Pro každou kostku má vytipované konkrétní doklady a nad nimi report kde je zafiltrováno na ten doklad. Report zobrazuje všechny ukazatele. Zároveň přidán screen z ERP.
  Na první pohled pak vidím, zda je doklad v kostce správně.
• Speciální kontroly – speciality tipu porovnání dvou datasetů apod.

To že je něco špatně zjišťuji:

• Reportem – třeba celková kontrola, kontrola za rok, testovací lavice. Vyžaduje spustit report, takže jen pokud něco měním, nebo se uživatelům něco nezdá.
• Mailing – kontrolní uložená procedura která pošle mailem že je něco špatně. Tato procedura bohužel docela trvá ale vyplatí se. Dělám to jako samostatnou vrstvu VERIFY.
• Spadnutí – pokud je něco klíčové a nevadí mi že přepočet nedojede, prostě nechám kostku nápočet spadnout. Přijde mi mailem info o spadnutí a já opravím vstupní data.
  Typicky název útvaru, číslo zboží nechávám schválně not null. Pokud mi přijde null hodnota, je něco hodně špatně.

Dokumentace

Dokumentace je pro mě osobně problém. Velký problém.

Pro uživatele potřebuji:

• Seznam kostek.
• Jak se připojí / vytvoří si report.
• Popis kostek. Tj popis ukazatelů/dimenzí. Aby uživatelé mohli zjistit věděli jak je jaký ukazatel spočítáný, z jakých dat.
• Kde najdou reporty a jaké.
• Kde si mohou spustit přepočet kostek.
• Jak jsou nastavena práva
• atd.

Pro sebe potřebuji totéž plus:

• Popis zdrojových systémů / procesů. Co v nich jsou za data, na co dát pozor, jaké vazby použít apod.
• Popis transformací – hoodně komentuji ve scriptech. Pořád je to málo.
• Popis reportů – jen pokud je report nějak standardizovaný. Podle mě by se neměly popisovat reporty ale kostky.

Technicky jsem zkoušel všechno možné. Namátkou:

• Word – to asi nemá smysl komentovat. Jakmile se udělá
• Google Doc – problém s linkováním mezi dokumenty, nejde prolinkovat na lokální soubor
• Teamsy – je to docela hezké, ale prostě tam uživatelé nechodí (což docela i chápu)
• Enterprise architekt – moc hezké ale docela komplikované. Navíc já nepotřebuji relační diagramy tabulek a VŠECH jejich sloupců, to mám ve scriptech. Já potřebuji spíše schémata, a hlavně obrázky.
• DokuWiki – moc hezké pokud se tomu dá hoodně času a hodně se ohne
• Videa – toto je podle jedna z cest! Jen ta práce s tím je děsně velká a chce to určitou rutinu. a umět stříhat (bohužel jsem objevil DaVinci Resolve pozdě a naučil jsem se ve FlashBack, a musím se přeučit)
• Kostku nad metadaty kostek – taky jedna z cest. Dokážu načíst z SSAS seznam kostek, ukazatelů, dimenzí, atributů přes linked server a DAX ale nemám to dotažené. Taky je špatné že se musí pro každou kostku udělat samostatný linked server.
• atd.

Co se mi aktuálně nejvíce osvědčilo je draw.io desktopový klinet.
Ukládám nativně přímo do souboru *.html (nikoli v *.drawio) do kořenového adresáře, kam se dostanou uživatelé a linkuji na reporty.
Dám sem pak nějakou ukázku. Nicméně to schéma nahoře je dělané stejným způsobem, jen tam nejsou prolinky na další stránky/schémata.

Technologie

Preferuji databázi MS SQL server v kombinaci s analytickým serverem (SSAS).
Nově dělám kostky už tabular místo multidimensional. Tabular mi přijdou výrazně rychlejší, ale dost věcí tam nefunguje (podbarvování ukazatelů, doubleclick na hodnotě, writeback, logování atd. atd.)

Pro prohlížení dat:

• Excel (kontingenční tabulky připojené do analytického serveru)
• PowerBI – pořád mu nemůžu přijít na chuť. Je po podle mě jiná filosofie proti Excelu, místo velké kontingenční tabulky je třeba udělat řadu menších reportů.
• Kyubit  – je to levná alternativa PowerBI. Není to pořád ideální.
• Reporting services MS SQL serveru (SSRS). Tady mě štve že parametry nejdou prohledávat (kombobox místo listbox). Často mám v dimenzi tisíce řádků (někdy i statisíce) a to se fakt nedá použít.

Pro načítání a zpracovávání dat (ETL) používám uložené procedury. Spouštěné v job manageru MS SQL serveru.

Vyhýbal jsem se integration services (SSIS), ale  musím revidovat postoj. Přímé selecty do xlsx, txt jsou super, ale je poměrně komplikované je nastavit s omezenými právy.

Nedávno jsem byl nucen pracovat s Keboola/Snowflake a nejsem z toho moc nadšený.
Dělání transformací ve webovém rozhraní místo v management studiu je mírně řečeno otravné. Nemožnost podívat se na tabulku bez načtení do sandboxu neskutečně zdržuje. Aby se dalo fulltextově prohledávat, musel jsem udělat report v PowerBI z metadat kebooly.

Architektura

Ze zdrojů načítám data do jedné databáze na MS SQL serveru, databáze Kostky.

Pro větší množství dat je vhodný samostatný server (dále jen BI server), nicméně to vyžaduje novou samostatnou licenci MS SQL.
Obvykle se pak používají servery jako „balanční“, kdy se hledá kombinace, co by na jakém serveru mělo běžet, aby se to vzájemně nebilo. Na tomto serveru mohou běžet i nějaké menší databáze, nebo i klidně velké. Moje zkušenost je, že nejvíce zatěžují transformace, samotné procházení reportů až tolik ne.

Preferuji umístění databáze na server kde běží SSAS. Sice je pomalejší načítání dat přes linked server, ale zase je rychlejší procesování kostky.

Pokud je načítání dat na BI server komplikované či pomalé, na zvážení je přenášet zálohu celé databáze na tento server (dále nazývám MIRROR). Má to několik velkých výhod a nevýhod:

• + Přenos databáze je rychlý. 150G databáze (bez logu) má velikost zálohy 23G, samotné zálohování trvá 12minut, obnovení 30minut, přenos dle rychlosti spojení.
• + Databázi stajně musím zálohovat a když ji pravidelně obnovuji ověřuji tím zálohu.
• + Data mám zafixovaná v čase. To se velmi hodí při kontrolách dat v kostce.
  Kontrola stavů skladů v kostce na ERP (kde se pořád mění) se dělá hůře.
• + Na BI serveru se nemusím krotit, mám tam všechna data a můžu si zkoušet cokoli.
• + Produkční server kde běží ERP není absolutně nijak ovlivněn
• – Vyžaduje to režii na údržbu (rozchození, úpravy)
• – Pokud chcípne zálohování /přenos / obnovení nemám napočítaná data (resp čerstvá data).

Takto je možno přenášet i zálohu například eshopu i když běží třeba na MySQL. Než řešit nějaké CSV, Excely, napojení a pak to dokola měnit, je to mnohem jednodušší.

PS Pro zálohování databází na MS SQL se mi osvědčil SQL Backup Master.

Praktická je možnost nasazení developer edition MS SQL serveru. Na ni nesmí běžet uživatelé, ani ostré transformace, nicméně se dá použít pro vývoj, nebo jako záložní.

Logování

V SSAS multidimenzionálních bylo skvělé logování.
V tabular se musí přes extended events (jsou i jiné způsoby, ale toto jediné mi zafungovalo korektně. Jen se musí udělat job který to nahodí po restartu serveru).

Je to pro mě nicméně klíčové. Potřebuji vědět kdo používá jakou kostku a jak často. Kdo jeaktuálně v kostkách apod.

Kostka používání kostek je první kterou dělám.