Andmeanalüüsi avaldised (DAX) Power Pivotis

DAX-i valemite mõistmine

DAX-i valemid on väga sarnased Exceli valemitega. Valemi loomiseks tippige võrdusmärk, millele järgnevad funktsiooninimi või avaldis ja soovitud väärtused või argumendid. DAX, nagu Excelgi, pakub laias valikus funktsioone, mida saab kasutada stringidega töötamiseks, kuupäevi ja kellaaegu sisaldavate arvutuste tegemiseks või tingimusväärtuste loomiseks.

Samas on DAX-i valemid teistsugused järgmistes olulistes punktides.

Kui soovite arvutusi kohandada rea kaupa, siis sisaldab DAX funktsioone selle reaväärtuse või seotud väärtuse kasutamiseks, et teha konteksti alusel varieeruvaid arvutusi.
DAX sisaldab funktsioonitüüpi, mis tagastab üksikväärtuse asemel tulemusena terve tabeli. Neid funktsioone saab kasutada teiste funktsioonide sisendina.
Ajateabe funktsioonid DAX-i korral lubatakse arvutusi kuupäevade vahemike abil ja võrreldakse tulemusi paralleelperioodide lõikes.

Kus DAX-i valemeid kasutada?

Power Pivoti valemeid saab kasutada arvutuslikes veergudes või arvutuslikel väljadel.

Arvutatud veerud

Arvutatud veerg on veerg, mis lisatakse olemasolevasse Power Pivoti tabelisse. Väärtuste veergu kleepimise või importimise asemel saate luua DAX-i valemi, mis määratleb veeru väärtused. Kui kaasate Power Pivoti tabeli PivotTable-liigendtabelisse (või PivotChart-liigenddiagrammi), saab arvutatud veergu kasutada nagu iga teist andmeveergu.

Valemid arvutatud veergudes on üsna Excelis loodavate valemite sarnased. Erinevalt Excelist ei saa aga luua eraldi valemit tabeli eri ridades, selle asemel rakendatakse DAX-i valem automaatselt kogu veerule.

Kui veerg sisaldab valemit, arvutatakse väärtus kõigi ridade kohta. Veeru tulemused arvutatakse kohe, kui valemi loote. Veeru väärtused arvutatakse uuesti ainult juhul, kui aluseks olevaid andmeid värskendatakse või kasutatakse käsitsi ümberarvutust.

Saate luua arvutuslikke veerge, mille aluseks on mõõdud ja teised arvutuslikud veerud. Vältige arvutuslikule veerule ja mõõdule sama nime panemist, kuna see võib anda ebaselgeid tulemusi. Veerule viidates on kõige parem kasutada täielikku veeruviidet, et vältida kogemata mõõdu rakendamist.

Üksikasjalikumat teavet leiate artiklist Arvutuslikud veerud lisandmoodulis Power Pivot.

Mõõdud

Mõõt on valem, mis on loodud kasutamiseks spetsiaalselt PivotTable-liigendtabelis (või PivotChart-liigenddiagrammis), mis kasutab Power Pivoti andmeid. Mõõtude aluseks võivad olla standardsed liitmisfunktsioonid (nt COUNT ja SUM), kuid samuti võite DAX-i abil määratleda oma valemi. Mõõte kasutatakse PivotTable-liigendtabeli väärtuste alal. Kui soovite arvutuslikud tulemid panna PivotTable-liigendtabeli teise alasse, kasutage hoopis arvutuslikku veergu.

Kui määratlete kindla mõõdu valemi, ei juhtu midagi enne, kui lisate mõõdu PivotTable-liigendtabelisse. Kui lisate mõõdu, väärtustatakse valemit iga PivotTable-liigendtabeli väärtuste alal oleva lahtri põhjal. Kuna tulemus luuakse iga rea- ja veerupäiste kombinatsiooni kohta, võib mõõdu tulemus igas lahtris erinev olla.

Teie loodud mõõdu määratlus salvestatakse koos selle lähteandmete tabeliga. See kuvatakse PivotTable-liigendtabeli väljade loendis ja on saadaval kõigi töövihiku kasutajate jaoks.

Üksikasjalikumat teavet leiate artiklist Mõõdud lisandmoodulis Power Pivot.

Valemite loomine valemiriba abil

Power Pivot, nagu ka Excel, pakub valemite loomise ja redigeerimise hõlbustamiseks valemiriba ning automaatteksti funktsiooni, et vähendada tippimis- ja süntaksivigu.

Tabeli nime sisestamiseks Alustage tabeli nime tippimist. Valemi automaattekst sisaldab ripploendit, mis sisaldab kehtivaid nimesid, mis algavad nende tähtedega.

Veeru nime sisestamiseks Tippige nurksulg ja seejärel valige praeguse tabeli veergude loendist veerg. Mõne muu tabeli veeru puhul alustage tabelinime esimeste tähtede tippimist ja seejärel valige veerg ripploendist Automaattekst.

Lisateavet koos üksikasjalike juhistega valemite koostamiseks leiate artiklist Valemite koostamine arvutuste jaoks lisandmoodulis Power Pivot.

Näpunäiteid automaatteksti kasutamiseks

Valemi automaatteksti saab kasutada ka pesastatud funktsioonidega olemasoleva valemi keskel. Vahetult järjepunktile eelnevat teksti kasutatakse väärtuste kuvamiseks ripploendis ja kogu järjepunktile järgnev tekst jääb samaks.

Määratud nimed, mis te konstantide jaoks loote, ei ilmu küll automaatteksti ripploendis, kuid saate need tippida.

Power Pivot ei lisa funktsioonide lõppsulge ega sobita sulge automaatselt. Peate veenduma, et kõigil funktsioonidel on õige süntaks, muidu ei saa valemit salvestada ega kasutada.

Valemis mitme funktsiooni kasutamine

Funktsioone saab pesastada, s.t ühe funktsiooni tulemusi saab kasutada teise funktsiooni argumendina. Arvutatud veergudes saab pesastada kuni 64 funktsioonitaset. Pesastamine võib siiski valemite loomist ja tõrkeotsingut raskendada.

Paljud DAX-i funktsioonid on loodud kasutamiseks ainult pesastatuna. Need funktsioonid tagastavad tabeli, mida ei saa otse tulemuseks salvestada; see tuleb esitada tabelifunktsiooni sisendina. Näiteks funktsioonid SUMX, AVERAGEX ja MINX nõuavad esimese argumendina tabelit.

Märkus.: Mõõtudes kehtivad funktsioonide pesastamisele mõned piirangud tagamaks, et veergudevaheliste sõltuvuste jaoks vajalikud rohked arvutused ei mõjutaks jõudlust.

DAX-i ja Exceli funktsioonide võrdlus

DAX-i funktsiooniteek põhineb Exceli funktsiooniteegil, aga neil on palju erinevusi. Selles jaotises võtame kokku Exceli ja DAX-i funktsioonide erinevused ja sarnasused.

Paljudel DAX-i funktsioonidel on Exceli omadega sama nimi ja üldiselt sama käitumine, aga neid on muudetud eri tüüpi sisendite võtmiseks ja mõnel juhul võivad need tagastada mõnda muud tüüpi andmeid. Üldiselt ei saa DAX-i funktsioone mõningate muudatusteta Exceli valemites või Exceli valemeid Power Pivotis kasutada.
DAX-i funktsioonid ei kasuta viiteandmetena kunagi lahtreid ega vahemikke, vaid veerge ja tabeleid.
DAX-i kuupäeva ja kellaaja funktsioonid tagastavad andmetüübi datetime. Samas tagastavad Exceli kuupäeva ja kellaaja funktsioonid täisarvu, mis näitab kuupäeva seerianumbrina.
Paljud uued DAX-i funktsioonid tagastavad kas väärtustetabeli või kasutavad arvutuste alusena väärtustetabelit. Excelil aga pole tabelit tagastavaid funktsioone; ainult mõni funktsioon suudab töötada massiividega. Tervete tabelite ja veergude hõlbus rakendamine viiteandmestikuna on Power Pivotis uus võimalus.
DAX pakub uusi otsingufunktsioone, mis sarnanevad Exceli massiivi- ja vektoriotsingu funktsioonidega. DAX-i funktsioonid nõuavad siiski, et tabelite vahel oleks loodud seos.
Eeldatakse, et veeru andmed on alati samast andmetüübist. Kui andmed pole samast tüübist, muudab DAX kogu veeru andmetüübi kõigile väärtustele sobivaimaks.

DAX-i andmetüübid

Andmeid saab Power Pivoti andmemudelisse importida paljudest eri andmeallikatest, mis võivad toetada erinevaid andmetüüpe. Kui impordite või laadite andmeid ja kasutate neid seejärel arvutustes või PivotTable-liigendtabelites,teisendatakse andmed mõnda Power Pivoti andmetüüpi. Andmetüüpide loendit saate vaadata lehelt Andmetüübid andmemudelites.

Tabeli andmetüüp on DAX-i uus andmetüüp, mida kasutatakse paljude uute funktsioonide sisendi või väljundina. Näiteks funktsioon FILTER võtab sisendiks tabeli ja väljundiks on teine tabel, mis sisaldab ainult filtri tingimustele vastavaid ridu. Tabelifunktsioone liitmisfunktsioonidega kombineerides saate teha keerukaid arvutusi dünaamiliselt määratletud andmekomplektidega. Lisateavet leiate artiklist Liitmised lisandmoodulis Power Pivot.

Valemid ja relatsioonmudel

Power Pivoti aken on ala, kus saab töötada mitme andmetabeliga ja ühendada tabelid relatsioonmudelis. Selle andmemudeli raames ühendatakse tabelid üksteisega seoste kaudu, mis võimaldavad luua korrelatsioone teiste tabelite veergudega ja moodustada huvitavamaid arvutusi. Näiteks saate luua valemeid, mis summeerivad seotud tabeli väärtused ja salvestavad selle väärtuse ühte lahtrisse. Samas saate rakendada seotud tabelist pärinevate ridade kontrollimiseks filtreid tabelitele ja veergudele. Lisateavet leiate artiklist Tabelitevahelised seosed andmemudelis.

Kuna tabeleid saab seoste abil linkida, võivad PivotTable-liigendtabelid sisaldada ka andmeid mitmest erinevast tabelist pärinevatest veergudest.

Kuna aga valemid töötavad ainult tervete tabelite ja veergudega, peate arvutused koostama teisiti kui Excelis.

Üldiselt rakendatakse DAX-i valem veerus tervele veeru väärtusekomplektile (mitte kunagi mõnele reale või veerule).
Power Pivoti tabelitel peab alati olema sama veergude arv igal real ja kõik veeru read peavad sisaldama sama andmetüüpi.
Kui tabelid on seose kaudu ühendatud, eeldatakse, et tagate kahe võtmena kasutatava veeru väärtuste sobimise vähemalt valdavas osas. Kuna Power Pivot ei jõusta viidete terviklust, on võimalik luua seos mittesobivate väärtustega võtmeveeruga. Tühjade või sobimatute väärtuste leidumine võib siiski mõjutada valemite tulemusi ja PivotTable-liigendtabelite väljanägemist. Lisateavet leiate artiklist Otsingud Power Pivoti valemites.
Kui seote tabelid seoste abil, suurendate sellega oma valemite väärtustamise ulatust või konteksti. Näiteks PivotTable-liigendtabelis olevaid valemeid võivad mõjutada kõik tabeli filtrid ja veeru- ning reapäised. Saate kirjutada valemeid, mis konteksti töötlevad, kuid kontekst võib ka tulemeid ootamatul viisil mõjutada. Lisateavet leiate artiklist Kontekst DAX-valemites.

Valemite tulemuste värskendamine

Andmevärskendus ja ümberarvutamine on kaks eraldiseisvat, kuid omavahel seotud toimingut, mille toimimise mõistmine on vajalik keerukaid valemeid, suuri andmehulki või välistest andmeallikatest pärinevaid andmeid sisaldava andmemudeli loomisel.

Andmevärskendus on teie töövihiku andmete värskendamine välisest andmeallikast pärinevate uute andmetega. Andmeid saab värskendada käsitsi teie määratud intervalliga. Kui olete töövihiku avaldanud SharePointi saidil, saab ajastada ka automaatvärskenduse välistest allikatest.

Ümberarvutamine on valemite tulemuste värskendamise protsess, et kajastada muudatusi valemites ja aluseksvõetud andmetes. Ümberarvutamine võib jõudlust mõjutada järgmisel moel.

Arvutatud veeru puhul tuleb valemit muutes selle tulemus alati terve veeru puhul ümber arvutada.
Mõõdu puhul ei arvutata valemi tulemusi enne, kui mõõt paigutatakse PivotTable-liigendtabeli või PivotCharti konteksti. Valem arvutatakse ümber ka juhul, kui muudate andmefiltreid mõjutavat rea või veeru päist või värskendate käsitsi PivotTable-liigendtabelit.

Valemite tõrkeotsing

Vead valemite kirjutamisel

Kui saate valemi määratlemisel veateate, võib valem sisaldada süntaksiviga, semantilist viga või arvutusviga.

Süntaksivead on kõige lihtsamad lahendada. Enamasti on sel juhul tegu puuduva sulu või semikooloniga. Konkreetse funktsioonide süntaksi osas abi saamiseks lugege DAX-funktsioonide teatmikku.

Teist tüüpi vead tekivad juhul, kui süntaks on küll õige, ent viidatud veerus asuv väärtus pole valemi kontekstis asjakohane. Selliste semantika- ja arvutusvigade põhjused võivad olla järgmised:

valem viitab veerule, tabelile või funktsioonile, mida pole olemas;
valem näib olevat õige, ent kui andmemootor toob andmed, leiab see tüüpide lahknevuse ja põhjustab vea;
valem edastab funktsioonile vale arvu või vale tüüpi parameetreid;
valem viitab mõnele muule veerule, mis sisaldab viga, ja seetõttu on selle väärtused sobimatud;
valem viitab veerule, mida pole töödeldud (sellel on küll metaandmed, kuid see ei sisalda tegelikke andmeid, mida arvutuste jaoks kasutada).

Esimesel neljal juhul tähistab DAX terve sobimatut valemit sisaldava veeru. Viimasel juhul kuvab DAX selle veeru tuhmina, et osutada veeru töötlemata olekule.

Valed või ebatavalised tulemused veeruväärtuste järjestamisel

Sellise veeru reastamisel või järjestamisel, mis sisaldab väärtust PA (Pole arv), võite saada valed või ootamatud tulemid. Näiteks juhul, kui arvutus jagab nulli (0) nulliga (0), tagastatakse tulem PA.

Põhjus on selles, et valemimootor kasutab järjestamiseks ja reastamiseks arvväärtuste võrdlust; PA-tulemeid ei saa võrrelda teiste veerus leiduvate arvudega.

Õigete tulemite tagamiseks võite kasutada IF-funktsiooni sisaldavaid tingimuslauseid, et testida veergu PA-väärtuste osas ja tagastada arvulise 0-väärtuse.

Ühilduvus analüüsiteenuste tabelimudelite ja DirectQuery režiimiga

Üldiselt ühilduvad Power Pivotis loodavad DAX-i valemid täielikult analüüsiteenuste tabelimudelitega. Siiski on mõned piirangud juhul, kui migreerite oma Power Pivoti mudeli analüüsiteenuste eksemplari ja juurutate mudeli seejärel DirectQuery režiimis.

Mõni DAX-i valem võib mudeli DirectQuery režiimi juurutamisel anda teistsuguseid tulemusi.
Mõni valem võib mudeli DirectQuery režiimi juurutamisel põhjustada valideerimistõrkeid, kuna valem sisaldab DAX-i funktsiooni, mis pole relatsioonilises andmeallikas toetatud.

Lisateabe saamiseks vaadake analüüsiteenuste tabelimudelite dokumentatsiooni SQL Server 2012 võrguraamatutes.