Mitä DAX-kielen muuttujat mahdollistavat?

02.06.2020 Raportointi

Muuttujat ovat DAX-kielen (Data Analysis eXpressions) yksittäisistä ominaisuuksista merkittävin – ne ovat mahdollisuus siirtää mikä tahansa tila muistiin ja siirtää se käytettäväksi sekä toistettavaksi muualla. Muuttujat tekevät lausekkeista mobiileja. Muuttujista kannattaa olla innostunut, sillä ne mahdollistavat laatikon ulkopuolisen ajattelun. Itse asiassa voin maaninen loiste silmissä todeta, että muuttujat ovat kuin Tetriksen pelaamista muistilla!

Mitä muuttujat ovat?

Muuttujat ovat DAX:issa käytettäviä laskutoimituksen sisäisiä tiloja, jotka alustetaan omaan muistiinsa.

Puretaan tämä määritelmä osiin: Power BI:ssa voi käyttää kahta natiivia kieltä eli DAX:ia ja M:ää. Muuttujia voi käyttää näistä vain DAX-kielessä.

Kaikessa ohjelmoinnissa voidaan muistitilat jakaa lokaaleihin ja globaaleihin. Globaalit tilat ovat muistitiloja, joihin on pääsy muistitilan ulkopuolisillakin olioilla. Lokaaleihin muistitiloihin pääsee vain muistitilan sisäinen olio. Molemmille näistä on käyttötarkoituksensa. Power BI:ssa esimerkki globaalista muistitilasta on mittari – ja lokaalista muuttuja. Tiloina ne voivat olla sekä skalaareja että tauluja.

Muuttujat määritellään aina yhden laskutoimituksen sisällä, eikä niitä voi kutsua kyseisen laskutoimituksen ulkopuolella. Tämä ei ole mikään ongelma, koska viittasin DAX:in missä tahansa – siis globaalisti – kutsuttaviin laskutoimituksiin: mittareihin. Muuttujaa voidaan siis käyttää mittarin sisällä, mutta yhdessä mittarissa nimettyä muuttujaa ei voi käyttää toisessa mittarissa.

Koska muuttujat ovat tällä tavoin lokaaleja, voidaan saman niminen ja samalla tavalla toimiva muuttuja toteuttaa yhtäältä toisessa mittarissa. Mutta kuten jo totesin, tällainen käyttötarve kutsua globaalisti muistitilaa kuulostaa mittarilta eikä muuttujalta. Olen käyttänyt mittaria esimerkkinä muuttujan määrittely-ympäristöstä, mutta yhtä hyvin muuttuja voidaan määritellä sarakkeessa.

Miltä muuttuja näyttää?

Muuttujan määrittely aloitetaan kirjoittamalla var, jonka jälkeen annetaan muuttujalle nimi. Nimen jälkeen tulee muuttujan määrittely. Muuttujia voi määritellä allekkain, mutta ne on pidettävä suorituksen mukaisessa järjestyksessä. Jos ensimmäisenä tulee muuttuja, joka viittaa myöhemmin tulevaan muuttujaan, moottori herjaa tästä. Kun määritellään useampia muuttujia, jokaisen muuttujan eteen tulee var, mutta muuttujien väliin ei tule pilkkuja.

Muuttujien määrittely lopetetaan kirjoittamalla return, jonka jälkeen ne palautetaan käytettäviksi osana laskutoimitusta.

Mitä muuttujaan tallentuu?

Totesin jo muuttujan olevan lokaali – siis siihen ei voi viitata sen laskutoimituksen ulkopuolella, jossa se on määritelty. Tämän lisäksi muuttujaa ei alusteta sen käyttöympäristön kontekstiin, vaan sen määrittely-ympäristön kontekstiin. Muuttuja on siis mahdollista alustaa ohitse laskukaavan kontekstien. Mutta toisin kuin all-funktio, muuttuja ei ohita raportin konteksteja – muuttuja ei saa tietoaan suoraan tietomallista. All-funktio ohittaisi myös osittimen ja suodattimen rajaukset, mutta muuttuja ei tätä tee.

Muuttujat ovat kuitenkin dynaamisempia kuin all-, earlier– tai earliest-funktiot. Muuttuja voidaan määritellä ohi kontekstin ja lisäksi siihen voidaan tallentaa tietty kontekstimäärittely tai laskutoimitus. Muuttujaan voidaan suunnitella määriteltäväksi oikeastaan minkälainen tila vain! Vain mielikuvitus ja predikaattilogiikka ovat tässä rajoina.

Miten DAX-muuttujia käytetään?

Herätelläkseni mielikuvitustanne ja ideoinnin nälkää yleisemminkin, esittelen esimerkkejä tavoista, joilla itse käytän muuttujia.

1. Skalaariarvon pakottaminen

Helppo tapa hyödyntää muuttujaa on tallentaa siihen skalaariarvo. Skaalariarvo tarkoittaa yksittäistä arvoa. Skalaariarvon vastakohtana DAX:issa voi ajatella olevan taulun: funktiot palauttavat pääosin joko taulun tai skalaarin. Muuttujaan voidaan siis määritellä dynaaminen lauseke, jossa skalaari määrittyy. Näin arviointi voidaan saattaa valmiiksi ja palauttaa arvioinnista pelkkä skalaari laskutoimitukseen.

Tämä mahdollistaa kaksi asiaa. Ensinnäkin voidaan suorittaa arviointi etukäteen valmiiksi omassa kontekstissaan, jolloin vain lopputulos eli skalaari palautetaan. Toiseksi iteroivasta funktiosta voidaan tallentaa muistiin pelkkä skalaari. Tällä on seurauksia, joita käsittelen tekstissäni myöhemmin, pohtiessani miten moottori saadaan suostuteltua suhdevertailuun Boolen logiikan yhteydessä.

2. Erilliseen muistiin arvioiminen

Yksi monimutkaisempia ongelmia on edellisen arvon palauttaminen Power BI:ssa. Kuten monimutkaisempiin ongelmiin yleensä, on tähänkin monia vaihtoehtoisia ratkaisuja. Yksi tapa on alustaa muistiin eri arviointikerroilla eri arvo. Tällöin arvioidaan muuttujan muistiin edellinen arvo, jotta siihen voidaan täsmäyttää itse laskutoimituksessa.

Ratkaisin tällaisella logiikalla seuraavan asiakaskategorioita koskevan skenaarion. Asiakkaat jaotellaan kategorioihin ja siirtymät kategoriasta toiseen kirjataan ylös. Näin pidetään kirjaa siitä, mihin kategoriaan asiakkaat ovat kuuluneet missäkin vaiheessa. Jotta voidaan tarkastella siirtymiä kategoriasta toiseen, pitää pystyä palauttamaan kullekin asiakkaalle edeltävä kategoria.

Ongelmana on arvioida edellinen arvo – siis tasan yksi kategoriasiirtymä taaksepäin. Muotoilen itselleni ongelman kahteen vaiheeseen: ensin palauttaisin edellisen kategorian päiväyksen ja toisessa vaiheessa voisin käyttää tätä päivämäärää palauttamaan itse kategorian.

Pohtiessani tätä asetelmaa oivalsin, että pyydän asiakkuuskategoriaa kahdessa eri kontekstissa: nykyisessä ja aiemmassa. Tätä varten minun pitäisi alustaa erilliseen muistiin aiemman kategorian päivämäärä. Tuomalla tämän arvon laskutoimitukseen voin käyttää Boolen logiikkaa kategorian päivämäärän täsmäytykseen.

Laskemalla ensin vain oikean päivämäärän ennakoin mahdollisesti tulevia ongelmia: palautan liian aikaisen kategorian tai sama aiempi kategoria toistuu jokaiselle päivämäärälle. Kuvassa olevalla kaavalla laskin edellisen arvon päivämäärän. Päivämäärän palauttamiseen ei voi käyttää determinitiivistä eli aina saman arvon palauttavaa funktiota, koska kaikki asiakkaat eivät vaihda synkronoidusti kategoriaa. Tämä rajaa pois dateadd-tyyppiset funktiot.

Kun tarkistin kaavan palauttavan aina halutun päivämäärän, vaihdoin return:in jälkeen tulevaksi seuraavan:

CALCULATE( MIN(CUSTOMER_SEG[segment_name]),
FILTER(ALLEXCEPT(CUSTOMER_SEG, CUSTOMER_SEG[CUSTOMER_ID]), CUSTOMER_SEG[rundate] = pv))

Nopea selitys ratkaisuna toimineesta kaavasta: min-funktio on vain pakottamassa palautettavaksi skalaariarvon. Itse suodattamissa käytetään allexcept-funktiota pitämään asiakastunnus vakiona. Näin yksi asiakas ei saa toisen asiakkaan edeltävää segmenttiä. Lopuksi rundate-arvoksi pakotetaan muuttujassa edelliseksi arvoksi valittu päivämäärä.

3. Muuttujat voivat viitata toisiinsa

Muuttujat voivat yhden laskukaavan sisällä viitata toisiinsa. Tämä mahdollistaa dynaamisuuden lisäämisen kaavaan: ensimmäinen muuttuja voi asettaa kategorian ja jälkimmäinen muuttuja valita halutun arvon kategorian sisällä.

Kun arvoksi pakotetaan edeltävä vuosi, voidaan tämän jälkeen seuraavassa muuttujassa valita tästä vuodesta haluttu päivämäärä. Tässä esimerkissä korostuu, että jokainen muuttuja voi sisältää oman logiikkansa. Näin muuttujia voidaan käyttää laskutoimituksen valmisteluun.

Muistutan vielä, että vaikka muuttujia voi käyttää sen laskukaavan valmisteluun, johon ne on sisällytetty, ei niitä voi käyttää globaalisti kaikkien laskukaavojen preparointiin. Muuttujia ei siis voi käyttää makrojen tapaan – mutta mittareita voisi käyttää.

4. Syntaksisokeri

Hadley Wickhamilta kuulin viisauden, että kaikkea koodia lukee vähintään kaksi eri henkilöä: koodin laatija ja sama henkilö tulevaisuudessa. Tämä tautologialta kuulostava letkautus osuu arkaan paikkaan; minä en muista edes kuukauden päästä, mitä olen aiemmin kirjoittamassani koodissani ajatellut. Kaikenlaista, olettaisin, mutta takuuseen en voi mennä.

Oman koodin kommentointi on vasta puoli ruokaa. Toinen puolikas on rakentaa koodin arkkitehtuuri sellaiseksi, että se on helposti omaksuttavissa muillekin. Arkkitehtuurin tulee jakaantua selkeisiin moduuleihin, jotka ovat helposti miellettäviä omina kokonaisuuksinaan. Yksi tapa on jakaa eri logiikkakokonaisuudet omiin muuttujiinsa.

Varsinkin ehdolliset logiikat on hyvä jakaa osasiin, joiden logiikka on luettavissa muuttujista. Arviointijärjestykseen järjestellyistä muuttujista on helppo lukea logiikan juoksutusta. Toisaalta myös yksinkertaisia logiikoita, joissa kuitenkin käytetään paljon sisäkkäisiä funktioita, on luontevampi lukea muuttujina:

DAX

5. Optimointi

Kun samaa lauseketta toistetaan, voidaan laskukaava optimoida muuttujalla. Kun lauseke sijoitetaan muuttujaan, se arvioidaan vain kerran. Eritoten rekursiiviset rakenteet, kuten ehdolliset logiikat, toistavat samaa lauseketta yhä uudelleen. Vaikka DAX:ia on pyritty optimoimaan osittaisella laiskalla arvioinnilla, voi sitä silti auttaa optimoimisessa muuttujilla.

6. Suhdevertailu Boolen logiikassa

DAX:issa ei Boolen vertailuissa voi käyttää suhdearviointia eli esimerkiksi pienintä, isointa, ensimmäistä tai viimeistä arvoa. Toisin sanoen funktiot kuten min, max, firstnonblank tai lastnonblank eivät tule kysymykseen. Näin moottori pyrkii minimoimaan kuormaansa, kun sen ei tarvitse käsitellä suuria joukkoja.

Tämä rajoitus voidaan kuitenkin kiertää tallentamalla vertailussa käytettävä kiinnekohta, kuten suurin tai viimeisin, muuttujaksi. Muuttujaa voidaan käyttää Boolen logiikassa, koska se palauttaa skalaariarvon.

Näin Boolen arvon arviointiin ei tuoda sisempää arviointia, jossa arvioidaan joukosta esimerkiksi suurinta tai viimeisintä arvoa, vaan yhtälöön tuodaan skalaariarvo. Kun muuttuja on arvioitu etukäteen, se palauttaa vain skalaarin kuormittamatta moottoria joukolla.

Perinteinen tapa kiertää tämäntyyppiset ongelmat on ollut se, että liputetaan halutut arvot etukäteen esimerkiksi uusin arvo -lipulla. Muuttujiin siirtyminen lisää logiikkaan dynaamisuutta, koska muuttujat arvioidaan vasta suorituksen alussa. Jos liput on laskettu sarakkeiksi, vähentää muuttujien käyttö myös muistinvarausta.

7. Edistynyt käyttö – muuttuja kontekstina

Tähän asti olemme käsitelleet muuttujien määrittelyä laskutoimituksen alussa. Mikään ei kuitenkaan pakota määrittelemään muuttujaa laskukaavan alussa. Siirtämällä muuttujan määrittelyn laskutoimituksen sisään voidaan muuttuja tallentaa tietyssä kontekstissa. Tällöin muuttujaan tallentuu kontekstin mukainen arvo. Tämä mahdollistaa muuttujan käyttämisen earlier-funktion tapaan, jossa sisempään kontekstiin tuodaan ulommasta kontekstista elementti.

Tällöin muuttujia käytetään earlier-funktion sijasta. Pidän tätä mielekkäänä skenaariona useammasta syystä. Ensinnäkin earlier-funktioiden hahmottaminen on lukijalle vaikeampaa, lisäksi earlier-funktio vaatii iteroitavan sarakkeen, kun taas muuttuja ei sisällä tällaisia varauksia. Jatkoperehtymislinkeissä SQLBI:n artikkeli DAX:ista esittelee laajemmin tätä käyttötapaa.

Käsittelin DAX-kielen muuttujia myös Aureoliksen Mikkiskaffeilla. Video alla:

Jatkoperehtymistä varten:

Vesa Tikkanen käsittelee Coffee Break -videollaan muuttujia sangen ansiokkaasti.

SQLBI:n artikkeli Variables in DAX on nopeasti syvään päähän sukeltava katsaus. Kaikki heidän muuttujia käsittelevät artikkelinsa löytyvät täältä.

The definite quide to DAX -kirja on edellistä pehmeämpi sukellus muuttujiin – ja muutoinkin raamattu Power BI -kehittäjälle.

Curbalin video DAX:ista.

Muuttujien käyttäminen debuggaamisessa – Guy in a Cube -kanava sai vieraakseen asiantuntija Marco Russon.

 

Ilmoittaudu webinaariin: Power BI – tehosta käyttöä ja ota toiminnallisuudet haltuun

Tervetuloa kuulemaan, miten raportointia hyödynnetään tiedolla johtamisessa. Webinaarimme aiheena on Microsoft Power BI:n käyttö datan raportoinnissa ja analysoinnissa.

power bi

Jaa tämä artikkeli