Valoda Data Analysis Expressions (DAX) līdzeklī Power Pivot

Data Analysis Expressions (DAX) sākumā izklausās mazliet biedējoši, bet neļaujiet nosaukumam jūs apmānīt. DAX pamati ir diezgan viegli saprotami. Vispirms — DAX NAV programmēšanas valoda. DAX ir formulu valoda. DAX var izmantot, lai definētu pielāgotus aprēķinus aprēķinātajām kolonnām un mēriem (pazīstami arī kā aprēķinātie lauki). DAX ietver dažas funkcijas, kas tiek izmantotas Excel formulās, kā arī papildu funkcijas, kas paredzētas darbam ar relāciju datiem un dinamiskās apkopošanas veikšanai.

Izpratne par DAX formulām

DAX formulas ir ļoti līdzīgas Excel formulām. Lai to izveidotu, ierakstiet vienādības zīmi, pēc tam funkcijas nosaukumu vai izteiksmi un visas nepieciešamās vērtības vai argumentus. Līdzīgi kā Excel, arī DAX nodrošina dažādas funkcijas, kuras var izmantot, lai strādātu ar virknēm, veiktu aprēķinus, izmantojot datumus un laiku, vai izveidotu nosacījumvērtības.

Tomēr DAX formulas atšķiras šādos svarīgos veidos:

• Ja vēlaties pielāgot aprēķinus pa rindai, DAX ietver funkcijas, kas ļauj izmantot pašreizējo rindas vērtību vai saistīto vērtību, lai veiktu aprēķinus, kas mainās atkarībā no konteksta.
• DAX ietver funkcijas tipu, kas atgriež tabulu kā tās rezultātu, nevis vienu vērtību. Šīs funkcijas var izmantot, lai nodrošinātu ievadi citām funkcijām.
• DAX laika informācijas funkcijasļauj veikt aprēķinus, izmantojot datumu diapazonus, un salīdzināt rezultātus paralēlos periodos.

Kur izmantot DAX formulas

Pievienojumprogrammā Power Pivot formulas var izveidot aprēķinātajās kolonnās vai aprēķinātajos laukos.

Aprēķinātās kolonnas

Aprēķinātā kolonna ir kolonna, kas tiek pievienota esošai Power Pivot tabulai. Tā vietā, lai ielīmētu vai importētu kolonnas vērtības, izveidojiet DAX formulu, kas definē kolonnas vērtības. Ja iekļausit Power Pivot tabulu rakurstabulā (vai rakursdiagrammā), aprēķināto kolonnu var izmantot tāpat kā jebkuru citu datu kolonnu.

Formulas aprēķinātajās kolonnās ir ļoti līdzīgas formulām, ko veidojat programmā Excel. Tomēr atšķirībā no programmas Excel dažādām tabulas rindām nevar izveidot atšķirīgu formulu. Tā vietā DAX formula tiek automātiski lietota visai kolonnai.

Ja kolonnā ir formula, vērtība tiek aprēķināta katrai rindai. Rezultāti kolonnai tiek aprēķināti, tiklīdz izveidojat formulu. Kolonnu vērtības tiek pārrēķinātas tikai tad, ja pamatā esošie dati tiek atsvaidzināti vai ja tiek izmantota manuāla pārrēķināšana.

Varat izveidot aprēķinātās kolonnas, kuru pamatā ir mēri un citas aprēķinātās kolonnas. Tomēr izvairieties aprēķinātajai kolonnai un mēram izmantot vienu un to pašu nosaukumu, jo tas var radīt mulsinošus rezultātus. Atsaucoties uz kolonnu, vislabāk izmantot pilno kolonnas atsauci, lai izvairītos no nejaušas mēra izsaukšanas.

Detalizētāku informāciju skatiet sadaļā Aprēķinātās kolonnas pievienojumprogrammā Power Pivot.

Pasākumi

Mērs ir formula, kas ir īpaši izveidota lietošanai rakurstabulā (vai rakursdiagrammā), kas izmanto Power Pivot datus. Mēri var būt balstīti uz standarta apkopošanas funkcijām, piemēram, COUNT vai SUM, vai arī varat definēt savu formulu, izmantojot DAX. Mērs tiek izmantots rakurstabulas vērtību apgabalā. Ja aprēķinātos rezultātus vēlaties novietot citā rakurstabulas apgabalā, tā vietā izmantojiet aprēķināto kolonnu.

Definējot precīza mēra formulu, nekas nenotiek, kamēr mērs netiek pievienots rakurstabulā. Pievienojot mēru, formula tiek novērtēta katrā rakurstabulas apgabala Vērtības šūnā. Tā kā rezultāts tiek izveidots katrai rindu un kolonnu galveņu kombinācijai, mēra rezultāts katrā šūnā var būt atšķirīgs.

Izveidotā mēra definīcija tiek saglabāta kopā ar tā avota datu tabulu. Tā parādās rakurstabulas lauku sarakstā un ir pieejama visiem darbgrāmatas lietotājiem.

Detalizētāku informāciju skatiet sadaļā Mēri pievienojumprogrammā Power Pivot.

Formulu izveide, izmantojot formulu joslu

Power Pivot, tāpat kā Excel, nodrošina formulu joslu, kas atvieglo formulu izveidi un rediģēšanu, kā arī automātiskās pabeigšanas funkcionalitāti, lai samazinātu rakstības un sintakses kļūdas.

Lai ievadītu tabulas nosaukumu Sāciet rakstīt tabulas nosaukumu. Formulu automātiskā pabeigšana nodrošina nolaižamo sarakstu, kurā iekļauti derīgi nosaukumi, kas sākas ar šiem burtiem.

Lai ievadītu kolonnas nosaukumu Ierakstiet iekavu un pēc tam pašreizējās tabulas kolonnu sarakstā izvēlieties kolonnu. Kolonnai no citas tabulas sāciet rakstīt tabulas nosaukuma pirmos burtus un pēc tam automātiskās pabeigšanas nolaižamajā sarakstā izvēlieties kolonnu.

Papildinformāciju un detalizētu informāciju par formulu veidošanu skatiet sadaļā Aprēķinu formulu izveide pievienojumprogrammā Power Pivot.

Padomi par automātiskās pabeigšanas izmantošanu

Formulu automātisko pabeigšanu varat izmantot esošas formulas vidū ar ligzdotām funkcijām. Teksts, kas ir tieši pirms ievietošanas punkta, tiek izmantots, lai nolaižamajā sarakstā parādītu vērtības, bet viss teksts pēc ievietošanas punkta paliek nemainīts.

Konstantēm izveidotie definētie nosaukumi netiek rādīti automātiskās pabeigšanas nolaižamajā sarakstā, bet tos var ierakstīt.

Power Pivot nepievieno funkciju aizverošās iekavas un automātiski nesaskaņo iekavas. Pārliecinieties, vai katra funkcija ir sintaktiski pareiza, citādi formulu nevar saglabāt vai izmantot. 

Vairāku funkciju izmantošana formulā

Varat ligzdot funkcijas, kas nozīmē, ka vienas funkcijas rezultāti tiek izmantoti kā citas funkcijas arguments. Aprēķinātajās kolonnās varat ligzdot līdz 64 funkciju līmeņiem. Tomēr ligzdošana var apgrūtināt formulu izveidi un problēmu novēršanu.

Daudzas DAX funkcijas ir paredzētas izmantošanai tikai kā ligzdotas funkcijas. Šīs funkcijas atgriež tabulu, kuru rezultātā nevar tieši saglabāt; Tā ir jānodrošina kā ievade funkcijai TABLE. Piemēram, visām funkcijām SUMX, AVERAGEX un MINX ir nepieciešama tabula kā pirmais arguments.

DAX funkciju un Excel funkciju salīdzinājums

DAX funkciju bibliotēkas pamatā ir Excel funkciju bibliotēka, taču bibliotēkām ir daudz atšķirību. Šajā sadaļā ir apkopotas Excel funkciju un DAX funkciju atšķirības un līdzības.

• Daudzām DAX funkcijām ir tāds pats nosaukums un tā pati vispārīgā darbība kā Excel funkcijām, taču tās ir modificētas, lai veiktu atšķirīgus ievades tipus, un dažos gadījumos var atgriezt citu datu tipu. Parasti jūs nevarat izmantot DAX funkcijas Excel formulās vai Excel formulas pievienojumprogrammā Power Pivot bez dažām izmaiņām.
• DAX funkcijas nekad neizmanto šūnas atsauci vai diapazonu kā atsauci, bet DAX funkcijas izmanto kolonnu vai tabulu kā atsauci.
• DAX datuma un laika funkcijas atgriež datu tipu datetime. Turpretim Excel datuma un laika funkcijas atgriež veselu skaitli, kas attēlo datumu kā sērijas numuru.
• Daudzas no jaunajām DAX funkcijām atgriež vērtību tabulu vai veic aprēķinus, pamatojoties uz vērtību tabulu kā ievadi. Turpretī programmā Excel nav funkciju, kas atgriež tabulu, bet dažas funkcijas var strādāt ar masīviem. Iespēja vienkārši sniegt atsauces uz pilnām tabulām un kolonnām ir jauns līdzeklis pievienojumprogrammā Power Pivot.
• DAX nodrošina jaunas uzmeklēšanas funkcijas, kas ir līdzīgas masīva un vektora uzmeklēšanas funkcijām programmā Excel. Taču DAX funkcijām ir nepieciešams, lai starp tabulām būtu izveidota relācija.
• Paredzams, ka kolonnā esošajiem datiem vienmēr būs vienāds datu tips. Ja dati nav viena tipa dati, DAX maina visu kolonnu uz datu tipu, kas vislabāk pielāgo visas vērtības.

DAX datu tipi

Power Pivot datu modelī varat importēt datus no daudziem dažādiem datu avotiem, kas var atbalstīt dažādus datu tipus. Importējot vai ielādējot datus un pēc tam tos izmantojot aprēķinos vai rakurstabulās, dati tiek pārvērsti par vienu no Power Pivot datu tipiem. Datu tipu sarakstu skatiet sadaļā Datu tipi datu modeļos.

Tabulas datu tips ir jauns datu tips programmā DAX, kas tiek izmantots kā ievade vai izvade daudzām jaunām funkcijām. Piemēram, funkcija FILTER pieņem tabulu kā ievadi un izvada citu tabulu, kurā ir tikai tās rindas, kas atbilst filtra nosacījumiem. Kombinējot tabulu funkcijas ar apkopošanas funkcijām, var veikt sarežģītus aprēķinus dinamiski definētās datu kopās. Papildinformāciju skatiet sadaļā Apkopojumi pievienojumprogrammā Power Pivot.

Formulas un relāciju modelis

Power Pivot logs ir apgabals, kurā varat strādāt ar vairākām datu tabulām un savienot tabulas relāciju modelī. Šajā datu modelī tabulas ir savstarpēji saistītas ar relācijām, kas ļauj izveidot korelācijas ar kolonnām citās tabulās un izveidot interesantākus aprēķinus. Piemēram, varat izveidot formulas, kas summē saistītas tabulas vērtības, un pēc tam saglabāt šo vērtību vienā šūnā. Vai, lai kontrolētu rindas no saistītās tabulas, varat lietot filtrus tabulām un kolonnām. Papildinformāciju skatiet sadaļā Tabulu relācijas datu modelī.

Tā kā tabulas var saistīt, izmantojot relācijas, rakurstabulās var iekļaut datus arī no vairākām kolonnām, kas ir no dažādām tabulām.

Tomēr, tā kā formulas var darboties ar veselām tabulām un kolonnām, aprēķini ir jāizstrādā citādi nekā programmā Excel.

• Parasti kolonnā esošā DAX formula vienmēr tiek lietota visai kolonnas vērtību kopai (nekad tikai dažām rindām vai šūnām).
• Power Pivot tabulās vienmēr jābūt vienādam kolonnu skaitam katrā rindā, un visās kolonnas rindās jābūt vienāda tipa datiem.
• Ja tabulas savieno relācija, jums ir jānodrošina, lai abās kolonnās, kas tiek izmantotas kā atslēgas, lielākoties būtu vienādas vērtības. Tā kā Power Pivot neievieš attiecinošo integritāti, ir iespējams, ka atslēgas kolonnā ir neatbilstošas vērtības, un tik un tā tiek izveidota relācija. Tomēr tukšu vai neatbilstošu vērtību esamība var ietekmēt formulu rezultātus un rakurstabulu izskatu. Papildinformāciju skatiet sadaļā Uzmeklēšana Power Pivot formulās.
• Saistot tabulas, izmantojot relācijas, tiek palielināts tvērumu jeb kontekstu, kurā tiek novērtētas formulas. Piemēram, rakurstabulas formulas var ietekmēt jebkuri filtri vai rakurstabulas kolonnu un rindu virsraksti. Varat rakstīt formulas, kas manipulē ar kontekstu, bet konteksts var izraisīt rezultātu izmaiņas tā, kā to neesat paredzējis. Papildinformāciju skatiet sadaļā Konteksts DAX formulās.

Formulu rezultātu atjaunināšana

Datu atsvaidzināšana un pārrēķināšana ir divas atsevišķas, taču saistītas darbības, kas jāsaprot, izstrādājot datu modeli, kurā iekļautas sarežģītas formulas, liels datu apjoms vai dati, kas iegūti no ārējiem datu avotiem.

Datu atsvaidzināšana ir process, kurā darbgrāmatā esošie dati tiek atjaunināti ar jauniem datiem no ārēja datu avota. Varat atsvaidzināt datus manuāli noteiktos intervālos. Vai, ja esat publicējis darbgrāmatu SharePoint vietnē, varat ieplānot automātisku atsvaidzināšanu no ārējiem avotiem.

Pārrēķināšana ir formulu rezultātu atjaunināšanas process, lai atspoguļotu visas izmaiņas pašās formulās un lai atspoguļotu šīs izmaiņas pamatā esošajos datos. Pārrēķins var ietekmēt veiktspēju šādos veidos:

• Aprēķinātajā kolonnā formulas rezultāts vienmēr ir jāpārrēķina visai kolonnai, kad maināt formulu.
• Mēram formulas rezultāti netiek aprēķināti, kamēr mērījums nav novietots rakurstabulas vai rakursdiagrammas kontekstā. Formula tiek pārrēķināta arī tad, ja maināt kādu rindas vai kolonnas virsrakstu, kas ietekmē datu filtrus, vai arī kad manuāli atsvaidzināt rakurstabulu.

Formulu problēmu novēršana

Kļūdas, rakstot formulas

Ja, definējot formulu, rodas kļūda, formulā var būt sintaktiska kļūda, semantiska kļūda vai aprēķina kļūda.

Sintaktiskās kļūdas ir visvieglāk atrisināt. Parasti tajās ir trūkstošas iekavas vai komats. Lai saņemtu palīdzību par atsevišķu funkciju sintaksi, skatiet DAX funkciju atsauci.

Otra veida kļūda rodas, ja sintakse ir pareiza, bet vērtībai vai kolonnai, uz kuru ir atsauce, nav jēgas formulas kontekstā. Šādas semantiskas un aprēķinu kļūdas var rasties kāda no šīm problēmām:

• Formula norāda uz neesošu kolonnu, tabulu vai funkciju.
• Formula šķiet pareiza, bet, kad datu programma izgūst datus, tā atrod tipa neatbilstību un rada kļūdu.
• Formula funkcijai nodod nepareizu skaitu vai parametru tipu.
• Formula norāda uz citu kolonnu, kurā ir kļūda, un tāpēc tās vērtības nav derīgas.
• Formula atsaucas uz kolonnu, kas nav apstrādāta, kas nozīmē, ka tajā ir metadati, bet nav faktisku datu, ko izmantot aprēķiniem.

Pirmajos četros gadījumos DAX atzīmē ar karodziņu visu kolonnu, kurā ir nederīgā formula. Pēdējā gadījumā DAX pelēko kolonnu, lai norādītu, ka kolonna ir neapstrādātā stāvoklī.

Nepareizi vai neparasti rezultāti, vērtējot vai kārtojot kolonnu vērtības

Vērtējot vai kārtojot kolonnu, kurā ir vērtība NaN (nevis skaitlis), iespējams, tiks parādīti nepareizi vai neparedzēti rezultāti. Piemēram, ja aprēķins dala 0 ar 0, tiek atgriezts NaN rezultāts.

Tas ir tāpēc, ka formulu programma veic kārtošanu un sarindošanu, salīdzinot skaitliskās vērtības. tomēr NaN nevar salīdzināt ar citiem skaitļiem kolonnā.

Lai nodrošinātu pareizus rezultātus, varat izmantot nosacījuma priekšrakstus, izmantojot funkciju IF, lai pārbaudītu NaN vērtības un atgrieztu skaitlisku vērtību 0.

Saderība ar analīzes pakalpojumu tabulu modeļiem un DirectQuery režīmu

Kopumā DAX formulas, kas tiek veidotas pievienojumprogrammā Power Pivot, ir pilnībā saderīgas ar analīzes pakalpojumu tabulveida modeļiem. Tomēr, ja migrējat savu Power Pivot modeli uz analīzes pakalpojumu instanci un pēc tam izvietojat modeli DirectQuery režīmā, pastāv daži ierobežojumi.

• Dažas DAX formulas var atgriezt atšķirīgus rezultātus, ja modelis tiek izvietots DirectQuery režīmā.
• Dažas formulas var radīt validācijas kļūdas, kad izvietojat modeli DirectQuery režīmā, jo formulā ir DAX funkcija, kas netiek atbalstīta attiecībā pret relāciju datu avotu.

Papildinformāciju skatiet analīzes pakalpojumu tabulveida modelēšanas dokumentācijā SQL Server 2012 BooksOnline.