Datumovou dimenzi dělám v DAXu. Jak v Power BI tak v Analysis services.
Dlouho jsem používal tabulku ve zdrojových datech (MS SQL, Keboola). Ale pomocí DAX je to operativnější a rychlejší (většinou).
DAX Scriptů pro vytvoření datumové dimenze se dá najít hodně. Většinou anglicky a ne vždy obsahují všechny atributy, které používám. Hlavně neobsahují české svátky. Zda je den pracovní nebo nepracovní je klíčové pro různé ukazatele produktivity.
Dimenzi mám standardně nazvanou DimDatum, púřípadně anglicky DimDate.
Všechny moje dimenze vždy začínají Dim. Pak jsou dimenzní tabulky u sebe a je jasné že se jedná o dimenze.
Výsledek
Takto vypadá dimenze pro uživatele. Vlevo Excel, vpravo Power BI. RKMD je RokKvarálMěsícDen. Analogicky RMD, RTD.
Obrázek je ilustrativní, názvy sloupců i samotná data měním jak script upravuji! Například pro hierarchie nově raději používám @ než §.
Ukazatele (Počet dnů pracovních, Počet dnů kalendářních apod.) do dimenze nedávám. Proto mám speciální faktovou tabulku FactUkazatelePomocné.
Takto vypadá podkladová tabulka.
Popis pro uživatele
DimDatum je základní dimenze obsahující na nejnižší úrovni den a pak různé hierarchie na ním (Měsíc, Rok, Týden, Měsíc v roce apod.).
Typicky se datum používá ve sloupcích tabulky, nebo v řádcích grafu. Rok se často používá ve filtru.
Pokud je v názvu atributu:
- „v“ – znamená to pořadové číslo. Třeba Měsíc v roce je 1,2 ..12. Bez ohledu na rok. Zatímco Měsíc je 2020-01, 2020-02 … 2022-12. Tyto atributy se používají v grafech pro srovnání měsíců v rámci různých roků. Na řádky Měsíc v roce a do sloupců Rok.
- „index“ – je to pořadové číslo vzhledem k dnešnímu datu. Velmi praktické.
Například „Měsíc index“ bude pro aktuální měsíc 0, minulý měsíc -1, předminulý -2, budoucí +1 atd. Dá se pak udělat report jen za aktuální měsíc, nebo od minulo měsíce apod. Analogicky pro roky, dny apod. - „§“ – jedná se o hierarchii. Hierarchie jsou „naskládání“ atributů za sebe. Třeba Rok/Měsíc/Den. Nemusí se v reportu vybírat jeden atribut za druhým. Doporučuji primárně používat. V reportu v tabulce se pak rovnou zobrazují +. Přes pravé tlačítko se pak dát použít drill down (zanoření)/drill up (vynoření).
| Atribut | Popis | Příklad dat |
|---|---|---|
| §RKMD | Hierarchie Rok/Kvartál/Měsíc/Datum. Viz popisy atributů níže. | 2021/2021Q1/2021-01/2021-01-01 |
| §RMD | Hierarchie Rok/Měsíc/Datum. Viz popisy atributů níže. | 2021/2021-01/2021-01-01 |
| §RTD | Hierarchie Rok/Týden/Datum. Viz popisy atributů níže. | |
| Bude (Budoucnost) | Rychlé omezení na Minulost/Budoucnost. if ([Date] <= TODAY() ,“Bylo“,“Bude“) Dnešní den je Bude, není ještě ukončený. Předpokládám, že se napočítává brzy ráno za minulý den. Je možno upravit na < pokud se přepočítává před půlnocí. Dá se použít i Den index (Bylo <0 Bude >=0) | Bylo/Bude |
| Date | Datum ve formátu rrrr-mm-dd. Používám nejraději. Primární klíč. Datový typ Date. | …,2023-10-25,2023-10-26,2023-10-27,… |
| Datum | České datum ve formátu dd.mm.rrrr. Datový typ Date. Někde jsem nechával Text a třídění dle Date. | …,25.10.2023,26.10.2023,27.10.2023,… |
| Den index | Aktuální den má číslo 0, včerejšek -1, předvčerejšek -2, zítřek +1 apod. Je to super pokud chci zafiltrovat jen na aktuální datum (nebo včerejší). Celé číslo | …,-2,-1,0,1,2,… |
| Den v týdnu | Den v týdnu česky. Třídit podle „Den v týdnu č“! Možná Den týdne? Nebo jen Den? | pondělí,úterý,…,neděle |
| Den v týdnu č | Číslo dne v týdnu. Spíše pro výpočty než pro uživatele. Možná Den č. | 1,2…,7 |
| Den v týdnu zkr | Den v týdnu na 2 písmena. Třídit podle „Den v týdnu č“! Možná Den zkr? | po,út,st,čt,pá,so,ne |
| Kvartál | Kvartál. Obsahuje i rok na začátku. | …,2021Q1,2021Q2,… |
| Kvartál index | Aktuální kvartál má číslo 0, minulý -1, budoucí +1 apod. | …,-2,-1,0,1,2,… |
| Kvartál v roce Možná Kvartál/Rok? | Kvartál bez roku. Je nutné použít současně s atributem Rok, jinak bude hodnota za všechny roky! | Q1,Q2,Q3,Q4 |
| Měsíc | Měsíc s rokem ve formátu RRRR-MM. | …,2021-01,2021-02,2021-03,… |
| Měsíc index | Aktuální měsíc má číslo 0, minulý -1, budoucí +1 apod. | …,-2,-1,0,1,2,… |
| Měsíc v roce | Měsíc česky. Třídit podle „Měsíc v roce č“ Je nutné použít současně s atributem Rok, jinak bude hodnota za všechny roky! | leden,únor,…,prosinec |
| Měsíc v roce č | Číslo měsíce v roce, včetně úvodní nuly. | 01,02,…,12 |
| Měsíc v roce zkr | Třípísmenná zkratka měsíce. | led,úno,bře,dub,kvě,čvn,čvc,srp,zář,říj,lis,pro |
| Pracovní | Zda je den pracovní nebo není. Zohledněny jsou i posunující se svátky. Odpovídá tedy plánovacímu kalendáři. | Ano/Ne |
| Rok | Rok ve formátu rrrr | 2001,2022,…,2030 |
| Rok index | Aktuální rok má číslo 0, minulý -1, budoucí +1 apod. | …,-2,-1,0,1,2,… |
| Týden | Týden včetně roku ve formátu rrrrTtt | 2001T01, 2001T02,…,2030T59 |
| Týden index | Aktuální týden má číslo 0, minulý -1, budoucí +1 apod. | …,-2,-1,0,1,2,… |
| Týden se dnem | Číslo týdne s počátečním datem. Je to stejné jako Týden, ale lépe se orientuje. | 2001-01-01 T01,2001-01-07 T02,…, 2030 T59 |
| Týden v roce | Číslo týdne ve formátu Ttt | T01, T02,…,T59 |
Script
Chci aby fungovalo jak v Power BI, tak v SSAS (analysis services) tabular.
V SSAS mi nefungovaly proměnné (VAR), moc jsem to neřešil, bez proměnných je to stručnější. Do SSAS se kopíruje bez názvu, od znaku rovnítka.
- Posuvné svátky (velký pátek a velikonoce) jsou fixně od 2021 do 2030.
- OD a DO je počáteční a konečné datum. Dají se dát fixně, nebo mix/max z faktových dimenzí. Ve scriptu je to Fact1[Date], Fact2[Date]
Pokud je jen jedna faktová tabulka je možno vyhodit to MINX. Nebo lépe dát tam tu tabulku dvakrát (až bude více faktovek). - V SSAS (analysis services) se na kopíruje od prvního rovnítka (název se mění v properties). Je třeba se přepnout do Data view a pak Table > New calculated table.
- Počty dnů mi vycházejí lépe jako ukazatele, nikoli v řádcích datumové tabulky
- U WEEKNUM je 21 což mi odpovídá číslování týdnů v pracovním kalendáři/outlooku.
- „Primární klíč“ je sloupec Date a je to datum.
Kdysi jsem používal primární klíč int YYYYMMDD (RokMěsícDen-20230101, 20230102 …). Výhodou je možnost přidání prvku s ID = -1 Neznámo/nezadáno. Jinak samé nevýhody. Rozdíl v rychlosti date/int je zanedbatelný. Viz Choosing between Date or Integer to represent dates in Power BI and Tabular.
DimDatum = ADDCOLUMNS ( --[Date] je hodnota datumu, row context
CALENDAR (
"2001-01-01" --Přepsat atributy z faktových tabulek a nahradit: MINX( {MIN(Fact1[Date]), MIN( Fact2[Date])},[Value] ) -- nejmenší datum z faktových dimenzí, nezaokrouhleno
,"2030-12-31" --Přepsat atributy z faktových tabulek a nahradit: DATE ( YEAR ( MINX( { MAX(Fact1[Date]),MAX(Fact1[Date])},[Value] )) , 12, 31 ) --největší datum z faktových tabulek, zaokrouhleno na konec roku (vždy do 31.12)
) -- PS pokud je jedna faktová tabulka, klidně ji uvést 2x. Min/Max bude stejné a bude se pak lépe upravovat script
,"Datum", FORMAT ( [Date], "DD.MM.YYYY" ) --raději používám Date. Možná formát "YYYY-MM-DD"?
,"DatumInt", FORMAT ( [Date], "YYYYMMDD" ) --moc nepoužívám
,"Rok", YEAR ( [Date] )
,"Měsíc", YEAR ( [Date] ) & "-" & FORMAT ( [Date], "MM" )
,"Měsíc v roce", FORMAT ( [Date], "mmmm" )
,"Měsíc v roce č", FORMAT ( [Date], "MM" )
,"Měsíc v roce zkr", FORMAT ( [Date], "mmm" )
,"Kvartál", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" )
,"Kvartál v roce", "Q" & FORMAT ( [Date], "Q" )
,"Týden", FORMAT ( [Date], "YY" ) & "" & "T" & FORMAT(WEEKNUM( [Date] ,21), "00" )
,"Týden v roce", "T" & FORMAT(WEEKNUM( [Date] ,21), "00" )
,"Týden se dnem",FORMAT ( [Date]-WEEKDAY ( [Date] ,2)+1, "YYYY-MM-DD" ) & " " & "T" & FORMAT(WEEKNUM( [Date] ,21), "00" )
,"Den v týdnu č", WEEKDAY ( [Date] ,2)
,"Den v týdnu", FORMAT ( [Date], "dddd" )
,"Den v týdnu zkr", FORMAT ( [Date], "ddd" )
,"Bude", if ([Date] <= TODAY() ,"Bylo","Bude") -- dnešní den je Bude, není ještě ukončený. Předpokládám že se napočítává brzy ráno za minulý den.
,"Pracovní", IF (
WEEKDAY ( [Date] ,2) in {6,7} --soboty a neděle
|| [Date] in {DATE(YEAR ( [Date] ), 1, 1),DATE(YEAR ( [Date] ), 5, 1),DATE(YEAR ( [Date] ), 5, 8) ,DATE(YEAR ( [Date] ), 7, 5),DATE(YEAR ( [Date] ), 7, 6),DATE(YEAR ( [Date] ), 09,28 ),DATE(YEAR ( [Date] ), 10, 28),DATE(YEAR ( [Date] ), 11, 17),DATE(YEAR ( [Date] ), 12, 24),DATE(YEAR ( [Date] ), 12, 25),DATE(YEAR ( [Date] ), 12, 26)} -- svátky v přesný den a měsíc. Viz https://cs.wikipedia.org/wiki/%C4%8Cesk%C3%BD_st%C3%A1tn%C3%AD_sv%C3%A1tek
|| [Date] in DATATABLE ("Date", DATETIME,{/*velikonoce*/ {"2001-01-16"},{"2002-04-01"},{"2003-04-21"},{"2004-04-12"},{"2005-03-28"},{"2006-04-17"},{"2007-04-09"},{"2008-03-24"},{"2009-04-13"},{"2010-04-05"},{"2011-04-25"},{"2012-04-09"},{"2013-04-01"},{"2014-04-21"},{"2015-04-06"},{"2016-03-28"},{"2017-04-17"},{"2018-04-02"},{"2019-04-22"},{"2020-04-13"},{"2021-04-05"},{"2022-04-18"},{"2023-04-10"},{"2024-04-01"},{"2025-04-21"},{"2026-04-06"},{"2027-03-29"},{"2028-04-17"},{"2029-04-02"},{"2030-04-22"} /*velký pátek*/,{"2016-03-25"},{"2017-04-14"},{"2018-03-30"},{"2019-04-19"},{"2020-04-10"},{"2021-04-02"},{"2022-04-15"},{"2023-04-07"},{"2024-03-29"},{"2025-04-18"},{"2026-04-03"},{"2027-03-26"},{"2028-04-14"},{"2029-03-30"},{"2030-04-19"} }) -- posouvající se svátky (velikonoce, velký pátek) od 2001 do 2030
,"Ne","Ano") -- možná je lepší "Nepracovní","Pracovní" ?
,"Den index",DATEDIFF(TODAY(),[Date],DAY) --Index?Idx?Offset?Pořadí?Seq?ID?č?
,"Týden index",DATEDIFF(TODAY(),[Date],WEEK)
,"Měsíc index",DATEDIFF(TODAY(),[Date],MONTH)
,"Kvartál index",DATEDIFF(TODAY(),[Date],QUARTER)
,"Rok index",DATEDIFF(TODAY(),[Date],YEAR)
--,"Počet dnů v měsíci",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1 -- Počet (pracovních) dnů v měsíci/roce apod dělám ukazateli, nikoli jako sloupec.
)Jak dostat do Power BI/SSAS
Power BI – vypnout na datasetu v properties Auto date/time. To nesouvisí s datumovou dimenzí, ale zmenšuje to velikost datasetu.
Nakopírovat script:
- Power BI – Vlevo jak jsou ikonky přepnout na Table view a zvolit New table.
- SSAS – z menu Table / New calculated table. Nebo je dole v seznamu tabulek ikona f. Nakopírovat script, od znaku = dále. Přejmenovat dole tabulku na DimDatum.
- Ve scriptu přepsat názvy faktových tabulek s jejich datumovým sloupcem (musí být typu date).
Aby se datum generovalo od nejnižšího data faktových tabulek do nejvyššího data faktových tabulek (zaokrouhleno na konec roku).
Označit tabulku jako datumovou:
- Power BI – Pravé tlačítko na tabulce a Mark as date table. Sloupec vybrat Date (jiný stejně nejde).
- SSAS – V menu Table/Data/Mark as date table. Potvrdit sloupec Date.
Navázat vazby na faktové tabulky – Vlevo se přepnout na Model. Přetažením provázat odpovídající sloupce. Ve faktové tabulce musý být datový typ date.
Změnit properties sloupců. Vlevo se přepnout na Model. U každého atributu nastavit:
- Display folder = Atributy.
- Sumarize by (dole v Advanced) nastavit na NONE (kde to jde)
- U Date nastavit Data time format na yyyy-MM-dd (nebo jiný )
- Změnit podle potřeby Sort by column.
- Datum – podle DatumInt (nebo Date?)
- Měsíc v roce – podle Měsíc v roce č.
- Měsíc v roce zkr – podle Měsíc v roce č.
- Den v týdnu (Po,Út,St) – podle Den v týdnu č (1,2,3)
- Den v týdnu zkr – podle Den v týdnu č (1,2,3)
Přidat hierarchie. Vlevo vybrat Model. Pravé tlačítko na Rok, Create hierarchy. Přes pravé tlačítko přidat další sloupečky.
Přejmenovat hierarchii (musí se vpravo ve stromu doubleclick). Místo Rok Hierarchy používám na začátek hierarchií § (nebo @).
V DimDatum vytvářím standardně hierarchie:
- §RMD – Rok/Měsíc/Datum
- §RKMD – Rok/Kvartál/Měsíc/Datum.
Jiné zdroje/linky
Datumová dimenze pomocí Bravo for Power BI. Je open-source program, které umí „vylepšit“ existující dataset. Mimo jiné umí přidat datumovou dimenzi Date. V Power BI otevřít dataset (zkopírovaný, ne ostrý!), pustit Bravo, připojit k datasetu a aplikovat (někdy je nutno dvakrát). Pro Dim Datum je to super na inspiraci.
Další linky:
- Microsoft Vytváření tabulek kalendářních dat v Power BI Desktopu.
- radacad.com – All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI . Hezký a použitelný script + návod.
- Month-related calculations
- sqbi – Reference Date Table in DAX and Power BI
Závěrečné poznámky
Pro hodiny/minuty je nutná samostatné dimenze Čas. Rozhodně nepatří do datumové dimenze. Až budu mít chvíli tak udělám další článek se scriptem pro Dim Čas.
Pokud je ve faktové tabulce více datumů (například faktura vydaná má datum případu, datum splatnosti), je možno:
- Udělat dvakrát vazbu na Dim Datum. Jedna vazba bude hlavní a druhá vedlejší.
- Udělat tabulky dvě. „Dim Datum“ a „Dim Dat. splatno“. Toto rozhodně preferuji.
- Viz také Two Design Approaches to Handling Multiple Date Dimensions.
U každé dimenze mi názvy atributů začínají zkratkou dimenze. Výjimka je DimDatum, kde to nedělám.
Například DimKategorie má atributy Kat Název, Kat Lvl1, Kat Lvl2, Kat Směr apod.
Dimenze DimDoklad má atributy Dokl Číslo, Dokl Pořadač, Dokl Třída apod.
Nemám rád české datum ve formátu 25.10.1972. Raději v reportech používám 1972-10-25. Přijde mi to prostě čitelnější.
Atributy se snažím pojmenovávat aby byly co nejkratší. V reportech se dlouhé názvy zobrazují hůře.
Nejsem si jistý jak moc používat hodnoty Ano/Ne.
Třeba Pracovní může být Ano/Ne nebo Pracovní/Nepracovní. Ano/Ne má výhodu že se lépe používá v DAX (při změně názvu hodnoty se nemusí měnit). Ale v reportu (tabulce/grafu) je sloupec s hodnotami Ano/Ne hůře čitelný. Musím se dívat na záhlaví. A ve slicerech pak musím mít záhlaví jinak vidím jen Ano/Ne. Když tam bude Pracovní tak je to jasné. Ve filtrech zrovna tak.
Do DimDatum rozhodně nepatří otvírací doby prodejen apod. To je třeba v samostatné tabulce (Pokud budu mít dvě prodejny a jedna z nich mimořádně zavře, jak to tady udělám?), kde bude ProdejnaID, Datum, Otevřeno (A/N) případně počet hodin.
Napsat komentář
Pro přidávání komentářů se musíte nejdříve přihlásit.