A Power Pivot / Power BI automatikus dátum tábláinak buktatói

Mind a Power Pivot, mind a Power BI rendelkezik saját belső dátummal arra az esetre, ha mi olyan trehányak lennénk, hogy nem hoznánk létre egy saját dátum táblát, vagy nem jelölnénk meg a dátum táblánkat dátum típusúként. 

A dátum tábla ugyanis az idősor kalkulációk (Time intelligence) alapja. Ahhoz, hogy a Power Pivot / Power BI ki tudja számolni az előző év azonos időszakának a forgalmát, a Power Pivotnak / Power BI-nak tudnia kell hogy mi 2020. november 9.-nek az előző év azonos időszakbeli párja. Ezt pedig a dátum táblából olvassa ki. Ha mi létrehoztunk egy dátum táblát, akkor azt használja, ha nem, akkor ő létrehoz magának egyet mert e nélkül nem müködnek jól az idősor kalkuláló függvények. (SAMEPERIODLASTYEAR(), A DATEADD(), stb..)

Ezzel nem lenne semmi baj, csak sajnos a belső dátum tábla megoldása mellőz minden best practice-t, a teljesítménye borzalmas, és felhasználói szempontból is csapnivaló élményt nyújt... Mindezek miatt a most következő cikkben részletesen megvizsgáljuk a belső dátum táblák/oszlopok működését és megoldást keresünk az általuk generált problémákra. Mivel a Power Pivot és a Power BI eltérően kezeli a belső dátumot, ezért megnézzük mind a két eszközt.

Power BI

Ha a nem hozunk létre saját dátum táblát (vagy az Auto Date/Time (magyarul automatikus dátum/idő) szolgáltatást bekapcsolva hagyjuk) akkor Power BI minden egyes dátum oszlophoz amit az adatmodellben talál, legenerál egy saját belső dátum táblát.

Automatikus dátum/idő a Power BI-ban

Megj.: Anno ezt úgy vettük észre, hogy hatalmasra nőtt egy olyan Power BI desktop fájl mérete, amely csak egy relatíve kicsi táblát tartalmazott, de annak nagyon sok dátum típusú oszlopa volt, ráadásul volt köztük olyan amely 100 évet ölelt át.

Ha el akarjuk kerülni, hogy a Power BI egy halom dátum táblát gyártson akkor

1. Ki kell kapcsoljuk a fent említett automatikus dátum/idő szolgáltatást
2. És létre kell hoznunk egy saját dátum táblát és azt be kell állítanunk dátum típusúnak (Mark as Date table/megjelölés dátumtáblaként)

Ez utóbbi a legjobb gyakorlat, úgyhogy minden egyes Power BI desktop fájl létrehozásakor létre kell hoznunk egy saját dátum táblát és azt meg kell jelölni dátum táblaként. (Mark as Date Table)

Power Pivot

A Power Pivot még ennél is szerencsétlenebbül oldja meg a saját belső dátum táblájának generálását. A Power Pivot ugyanis dátum tábla helyett minden egyes dátum oszlop mellé létrehoz 4 számított oszlopot (Calculated Column) amely a dátum alapján meghatározza az év, a hónap, a negyedév nevét és a hónap oszlop rendezéséhez szükséges sorrend oszlopot. Ha pl az értékesítés táblámban van rendelés dátuma, teljesítés dátuma, szállítás dátuma, akkor e 3 oszlop mellé legenerál 12 saját belső oszlopot. El lehet képzelni, hogy milyen performancia problémákat okoz ez egy többmillió soros táblán, amelyben van mondjuk 10 dátum oszlop…

Szerencsére ki lehet kapcsolni az automatikus generálást az Excel Beállítások -> adatok -> a Dátum/idő oszlopok automatikus csoportosításának letiltásával:

A legjobb gyakorlat szerint Power Pivotban is saját dátum táblát hozunk létre, azt megjelöljük dátumtáblaként (Mark as Date Table) és az automatikus dátum/idő funkciót letiltjuk.

Összefoglalva: Mind Power Pivotban, mind Power BI-ban saját dátum táblát építünk (azt megjelöljük dátum táblaként), és nem hagyjuk hogy a Power Pivot vagy Power BI maga hozzon létre dátum táblákat/oszlopokat.