Onko Power BI:n automaattisesta päivämäärähierarkiasta etua?

Miksi Power BI:ssä on yleensä hyvä ja usein välttämätöntä käyttää erillistä päivämäärätaulukkoa eli ns. aika-dimensiota?

Automaattinen päivämäärähierarkia

Jos Power BI -tiedoston tauluissa on päivämäärä-sarakkeita, kustakin päivämäärästä muodostuu automaattisesti hierarkia.

Hierarkian kieli määräytyy sen perusteella, mikä on mallin (model) kielenä Power BI Desktopin asetuksissa tiedoston luonnin yhteydessä.

Mallin kieli määritetään asetuksista File > Options and Settings > Options > GLOBAL > Regional Settings > Model language (Tiedosto > Asetukset ja vaihtoehdot > Valinnat > YLEINEN > Aluekohtaiset asetukset > Mallin kieli).

Mallin kieli vaikuttaa myös tekstikenttien lajitteluun. Jos mallin kielenä on suomi, Power BI osaa lajitella ”ääkköset” suomalaisittain aakkosten loppuun. Jos mallin kieli on englanti, ääkköset ovat erikoismerkkejä ja ne aakkostuvat ennen a-kirjainta eli esimerkiksi Äänekoski näkyy raporteissa ennen Akaata.

Huomaa! Mallin kieltä ei voi muuttaa jälkeenpäin, vaan se määräytyy tiedoston luontihetkellä.

Automaattisen päivämäärähierarkian etu

Automaattisesta päivämäärähierarkiasta on hyötyä ainoastaan silloin, kun haluat mahdollisimman vaivattomasti ja helposti muodostaa aikaperusteisia visualisointeja ja tehdä nopeita ad-hoc havaintoja datasta, joka muodostuu yhdestä taulusta.

Kustakin visualisoinnista voi poistaa hierarkian tasoja.

Lisäksi voi valita käytetäänkö hierarkiaa vai pelkkää päivämäärää.

Päivämäärähierarkiasta on enemmän haittaa kuin hyötyä, jos teet muutakin kuin pelkkää yksitauluisen datan ad-hoc tarkastelua ja sinulla on mahdollisuus käyttää raportointiin enemmän kuin pari minuuttia. Yleensä tietomalliin luodaan päivämäärätaulukko eli aika-dimensio.

Automaattisen päivämäärähierarkian puutteet ja haitat

Seuraavassa on listattu automaattisen päivämäärähierarkian puutteita ja ongelmia.

• päivämäärähierarkian kieltä ei voi tiedoston luonnin jälkeen vaihtaa,
• hierarkiaa ei voi muokata eli siihen ei voi lisätä omia ryhmittelyitä, kuten puolivuotiskausia, viikkoja, viikonpäiviä tai vaikkapa kalenterivuodesta poikkeavaa tilikautta,
• hierarkiaan ei voi lisätä erilaisia raportoinnissa tarvittavia aikajaksojen esitysmuotoja, kuten YYYY-MM (2020-02), YYYY-MMM (2020-Jan), YYYY-WW (2020-52) tai vaikkapa erikielisiä kuukausia (”tammi” ja ”Jan”)
• automatiikka muodostaa sivutuotteena jokaisesta päivämäärä-sarakkeesta keskusmuistiin näkymättömän eli Power BI Desktopin käyttöliittymästä piilotetun 7-sarakkeisen taulun, joka vie muistia ja vaikuttaa pahimmillaan raportin suorituskykyyn,
• jos tietomallissa on useita tapahtumatauluja (eli faktatauluja), aikaperusteisia visualisointeja ei saa toimimaan, mikäli visualisointiin valitaan laskennallisia tietoja useista eri tauluista,
• jotkut aika-älykkäistä DAX-funktioista (time-intelligence functions) eivät toimi oikein, jos päiväyssarakkeen datassa on katkoksia (eli datassa ei esiinny kaikkia vuoden päiviä),
• isoissa datatauluissa olevien päivämääräkenttien käyttö aika-älykkäissä funktoissa saattaa aiheuttaa suorituskykyongelmia.

Suorituskykyongelmien lisäksi merkittävin ongelma liittyy siihen, että hierarkiat ohjaavat aloittelevia Power BI -käyttäjiä luulemaan, että hierarkioita voi käyttää kaikissa raportointitilanteissa.

Kuten tilanteissa, joissa tietomallissa on useita erilaisia tapahtumia sisältäviä tauluja (ns. faktatauluja), kuten tilaukset (Orders) ja budjetti (Budget). Jos visualisointiin valitaan aikajakso jommastakummasta taulusta (esimerkiksi Orders), visualisointiin pystytään laskemaan lukuja vain samasta taulusta.

Yllä olevaan esimerkkimatriisiin on valittu (1) vuosi-kuukausi -hierarkia Orders-taulusta, joten samasta taulusta laskettu (2) tilaussumma näytetään oikein, mutta toisessa taulussa olevat (3) budjetti-luvut näyttävät virheellistä lukua. Jos aikajakso valittaisiin budjetti-taulusta, puoliestaan tilaustaulusta laskettu luku näyttäisi väärää tulosta.

Yllä kuvattu matriisi sekä muut aikaperusteiset visualisoinnit saadaan toimimaan, kun tietomalliin luodaan erillinen päivämäärätaulukko eli aika-dimensio, se kytketään oikein tilaus- ja budjettitauluihin ja raporteissa käytetään aika-dimensiossa olevia aikajaksoja.

Automaattisen päivämäärähierarkian muodostamat piilotetut taulukot ja niiden poistaminen

Päiväysautomatiikka muodostaa jokaisesta datassa olevasta päivämäärästä keskusmuistiin piilotetun 7-sarakkeisen taulun. Jos taulut olisi mahdollista saada Power BI:ssä esiin, niiden sisällöt näyttäisivät tältä:

Kussakin piilotetussa päivämäärätaulussa on niin monta riviä kuin sarakkeen aikaisimman ja viimeisimmän päiväyksen välillä on vuorokausia. Jos Power BI -tiedostossa on esimerkiksi 2-rivinen taulu, jossa on päivämäärät 1.1.1900 ja 31.12.9999, taustalle muodostuneessa piilotetussa taulussa on lähes 2,9 miljoonaa riviä, sillä piilotettu taulu sisältää kaikki päivien välillä olevat päivämäärät.

Kun poistat Power BI -tiedoston asetuksista päiväysautomatiikan, piilotetut taulut häviävät keskusmuistista ja niiden muistitila vapautetaan.

Automatiikka pitää ehdottomasti poistaa kaikista tietomalleista, joissa automaattinen hierarkia korvataan omalla päivämäärätaulukolla! Se poistetaan tiedostokohtaisesti komennolla File > Options and Settings > Options > CURRENT FILE > Data load > Auto date/time (Tiedosto > Asetukset ja vaihtoehdot > Valinnat > NYKYINEN TIEDOSTO > Tietojen lataaminen > Automaattinen päivä ja aika). Voit poistaa asetuksen myös globaalisti Power BI Desktopista poistamalla asetuksen GLOBAL > Data Load (YLEINEN > Tietojen lataaminen) -ryhmästä.

Päivämäärätaulukko eli aika-dimensio

Aiemmin luetellut automaattisen päiväyshierarkian puutteet ja haitat voi kiertää tuomalla tai luomalla Power BI -tiedostoon erillisen aika-dimension.

Päivämäärätaulukko on taulu, joka sisältää kaikki päivämäärät raportoinnassa tarvitulta aikaväliltä. Kalenterissa tulee olla omina sarakkeinaan kaikki ne erilaiset ryhmittelyt ja esitysmuodot, joita raporteissa tarvitaan. Esimerkiksi erikieliset kuukaudet sekä tarvittaessa viikonpäivät.

Kalenterissa pitää olla yksi päivämääräsarake, eikä kyseisessä sarakkeessa saa olla katkoksia tai tyhjiä soluja. Lisäksi kukin päivä saa esiintyä taulussa vain kerran.

Kalenterin tuonti tai luonti

Kalenterin voi toteuttaa kolmella eri tekniikalla.

1. Lataamalla kalenteritaulun Power BI:hin jostain olemassa olevasta tietolähteestä, kuten esimerkiksi tietovarastosta tai tiedostosta,
2. Luomalla kalenteritaulun Power Queryssä.
3. Luomalla kalenteritaulun Power BI Desktopissa DAX-kielellä.

Seuraavassa on linkkejä muutamiin aiheeseen liittyviin ohjeartikkeleihin:

• Build a Reusable Calendar Table with Power Query, Matt Allington
• Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns, Reza Rad
• Creating a simple date table in DAX, SQLBI
• Date table template, SQLBI

Kalenterin määritykset

Kun olet tuonut tai luonut kalenterin, sille pitää suorittaa muutamia toimenpiteitä.

1. Yhdistä kalenteritaulu data- eli faktatauluihin.
2. Merkitse se kalenteritauluksi ja poista aiemmin artikkelissa mainittu automatiikka, jotteivat piilotetut kalenteritaulut vie muistia.
3. Määritä tekstisarakkeiden (kuten kuukaudet) lajitteluperusteet.

Kalenterin eli aika-dimension yhdistäminen fakta-tauluihin

Muodosta relaatiot kalenterin päivämääräkentän (kuvassa Date) ja data-taulujen (eli faktataulujen) päivämääräkenttien välille (kuvassa CallDate, BudgetDate ja OrderDate).

Jos et tunne relaatioiden ja ns. dimensionaalisen tähtimallinnuksen perusperiaatteita, opiskele aiheeseen liittyvää teoriaa, sillä Power BI -mallit ja DAX-kieli toimivat parhaiten tähtimallin kanssa. Yllä olevasta esimerkkikuvasta on poistettu kaikki muut dimensiot, jotta kuvassa korostuisi Date-taulun rooli suhteessa faktatauluihin.

Jotta pystyt muodostamaan taulujen välille yhden-suhde-moneen (one-to-manyt) -relaatiot, saatat joutua muuntamaan esimerkiksi kuukausi-perusteisen budjetti-taulun kuukausi-kentän päivämääräksi (BudgetDate). Tyypillisesti pidempää aikajaksoa kuvaavat kentät (kuten 01/2020) konvertoidaan joko kuukauden ensimmäiseksi (1.1.2020) tai viimeiseksi (31.1.2020) päiväksi, jotta kenttä voidaan yhdistää kalenteritaulun päivämäärään.

Merkitse päivämäärätaulukoksi

Siirry Data-näkymässä kalenteritauluun ja määritä taulu kalenteriksi komennolla Table Tools > Mark as Date table (Taulukkotyökalut > Merkitse päivämäärätaulukoksi) ja valitse taulun päiväyssarake. Päivämäärätaulukoksi hyväksytän vain sellainen taulu, minkä päivämäärä-sarakkeessa on katkeamaton päivämääräsarja ilman tyhjiä soluja.

Kun olet määrittänyt kalenterin, muista poistaa päiväysautomatiikka Power BI Desktopin asetuksista.

Lajittelujärjestykset ja perusmuotoilut

Muista määrittää myös kaikkien tekstimuotoisten sarakkeiden lajittelujärjestykset valitsemalla kukin tekstisarake ja valitsemalla minkä numeerisen sarakkeen perusteella kyseinen sarake lajitellaan. Power BI ei osaa automaattisesti lajitella tekstimuotoisia kuukausia tai esimerkiksi viikonpäiviä, jos malliin on luotu oma aika-dimensio.

Muista lajittelujärjestysten lisäksi myös muotoilla Date-sarake lyhyeen päiväysmuotoon Column tools (Saraketyökalut) -välilehdellä olevien muotoiluvaihtoehtojen avulla. Voit halutessasi käyttää myös mukautettuja muotoilukoodeja.

Faktataulujen päiväysten piilotus

Yleensä faktatauluista on hyvä piilottaa relaatioiden muodostamiseen käytetyt id-kentät (eli viiteavaimet), kuten tässä tapauksessa erilaiset päivämääräkentät (OrderDate, CallDate, BudgetDate), jottei ne näy raportointinäkymässä. Jos valitset esimerkiksi matriisiin vahingossa päivämäärään Order-taulusta, matriisiin ei pysty laskemaan tuloksia muista faktatauluista (ks. aiempi kuva tässä artikkelissa).

Kun kentät on piilotettu faktatauluista ja raportointinäkymässä ainoa mahdollinen näkyvillä oleva päiväys on kalenteritaulun päiväys, visualisoinneista saa helpommin toimivia ja esimerkiksi samassa matriissa eri faktatauluista lasketut luvut näkyvät oikein.

Power BI mallitiedosto

Koska aika-dimenstio edellyttää melko paljon määrittelyitä, luo itsellesi Power BI -mallitiedosto, jossa on valmiiksi kalenteri ja tarvittavat määritykset. Monissa Power BI:tä käyttävissä organisaatioissa on Power BI -mallitiedosto tai -tiedostoja. Selvitä sellaisten olemassaolo, jottet tee turhaa työtä.