Ak ešte len začínate pracovať s PowerPivotom, resp. v jazyku DAX, tak sa pravdepodobne snažíte pri samoštúdiu napasovať Vaše znalosti z jazyka SQL do jazyka DAX. Oba jazyky majú spoločné to, že sa v nich pracuje s tabuľkami, stĺpcami a riadkami, ale tam podobnosti končia. Pretože jazyk SQL je aplikačný jazyk, a DAX je analytický jazyk. Pozrime sa teraz teda na to, ako prepísať základné operácie z jazyka SQL do jazyka DAX.

Nasledujúce príklady sú napísané voči nášmu vzorovému súboru PowerPivotu, na ktorý sa dá dotazovať buď Excelom, alebo cez DAX Studio, alebo – v prípade SSAS Tabularu – aj SQL Server Management Studiom alebo cez dizajnér Reporting Services.

Prvá vec, ktorá Vás prekvapí, bude to, že základné DAXové dotazy začínajú klauzulou EVALUATE, pričom SQL dotazy začínajú klauzulou SELECT. Čiže ak chcete vytiahnuť všetky záznamy z tabuľky, tak zadáte:

• v SQL: SELECT * FROM Objednavky
• v DAXe: EVALUATE Objednavky

Rýchle vysvetlenie: príkaz EVALUATE očakáva DAXový vzorec vracajúci fyzickú alebo vypočítanú tabuľku, takže dali sme mu fyzickú.

Keď chcete vybrať iba niektoré stĺpce z tabuľky – napr. preto, že pri DAXe platí oveľa viac ako pri SQL pravidlo, že čím chcete viac stĺpcov, tým je to pomalšie – tak zadáte:

• v SQL: SELECT [SalesOrderNumber], [Cena objednavky] FROM Objednavky
• v DAXe: EVALUATE SELECTCOLUMNS(Objednavky, “SalesOrderNumber”, [SalesOrderNumber], “Cena objednavky”, [Cena objednavky])

Rýchle vysvetlenie: funkcia SELECTCOLUMNS vracia iba zadané stĺpce zo zadanej tabuľky, plus voliteľne vie vrátiť ďalšie vypočítané stĺpce. Prvý parameter je tabuľka, a ďalšie páry parametrov sú “názov nového stĺpca vo výsledku” a “vzorec pre hodnoty v tomto stĺpci”

Filtrovanie riadkov v DAXe sa robí cez viaceré funkcie, a pre začiatočníkov bude najľahšia funkcia FILTER (pche, aké nečakané, že?). Je síce najpomalšia zo všetkých možností, ale pre SQLkárov je ihneď pochopiteľná. Ak teda chcete vybrať iba objednávky s cenou nad 100 eur, tak zadáte:

• v SQL: SELECT * FROM Objednavky WHERE [Cena objednavky] > 100
• v DAXe: EVALUATE FILTER(Objednavky, [Cena objednavky] > 100)

Rýchle vysvetlenie: Funkcia FILTER berie na vstupe v prvom parametri tabuľku, z ktorej odfiltruje len tie riadky, ktoré spĺňajú podmienku zadanú v druhom parametri funkcie. Výsledné riadky vracia ako tabuľku.

Zoskupovanie údajov sa najľahšie robí cez funkciu SUMMARIZE. Ak chcete napr. zoskupiť objednávky podľa produktov a vypočítať obrat za každý produkt (t.j. sumu cien príslušných objednávok), tak zadáte:

• v SQL: SELECT ProductKey, SUM([Cena objednavky]) AS Obrat FROM Objednavky GROUP BY ProductKey
• v DAXe: EVALUATE SUMMARIZE(Objednavky, [ProductKey], “Obrat”, SUM([Cena objednavky]))

Rýchle vysvetlenie: Funkcia SUMMARIZE berie na prvom parametri tabuľku, ktorú ide zoskupiť. Nasledujú stĺpce, podľa ktorých sa ide zoskupovať (my sme použili iba ProductKey), a potom páry parametrov “názov nového stĺpca vo výsledku” a “vzorec pre hodnoty v tomto stĺpci” – podobne ako pri funkcii SELECTCOLUMNS. Bližšie je funkcia SUMMARIZE vysvetlená v tomto článku.

Zoraďovanie údajov sa robí takmer identicky ako v jazyku SQL. Ak chcete zoradiť objednávky napr. podľa ceny objednávky od najvyššej po najnižšiu, tak napíšete:

• v SQL: SELECT * FROM Objednavky ORDER BY [Cena objednavky] DESC
• v DAXe: EVALUATE Objednavky ORDER BY [Cena objednavky] DESC

Ak chcete zoradiť údaje ešte počas výpočtu, pozrite si syntax funkcie TOPN (viď nižšie).

Joinovanie tabuliek sa v DAXe robí úplne inak ako v jazyku SQL. Dôvodom je to, že dáta sú z pohľadu dotazu automaticky zjoinované podľa prepojení v dátovom modeli, a zároveň môžete robiť joiny iba podľa týchto prepojení – či už aktívnych, alebo neaktívnych. Ak by ste chceli zjoinovať tabuľky Objednávky a Produkty, a vybrať povedzme meno produktu (z tabuľky Produktov), a číslo a sumu objednávky (z tabuľky Objednávky), tak zadáte:

• v SQL: SELECT p.[ProductName], o.[SalesOrderNumber], o.[Cena objednavky] FROM Objednavky o JOIN Produkty p ON o.ProductKey = o.ProductKey
• v DAXe: EVALUATE SELECTCOLUMNS(Objednavky, “ProductName”, RELATED(Produkty[ProductName]), “SalesOrderNumber”, [SalesOrderNumber], “Cena objednavky”, [Cena objednavky])

Rýchle vysvetlenie: keďže je už všetko automaticky zjoinované, tak netreba robiť join v dotaze. Len pri prepise z jazyka SQL do jazyka DAX musíte dodržať pravidlá pre DAXové funkcie, ktoré pracujú zvyčajne nad jednou tabuľkou. Preto, keď v tomto prípade vyberáme dáta z tabuľky Objednavky (prvý parameter funkcie SELECTCOLUMNS), tak všetky ostatné stĺpce z iných tabuliek tam dotiahneme cez funkciu RELATED, resp. RELATEDTABLE a X-kové funkcie.

Ak chcete robiť iné typy joinov, tak použijete:

• pre LEFT JOIN a RIGHT JOIN – funkciu GENERATEALL
• pre CROSS JOIN – funkciu CROSSJOIN
• pre CROSS APPLY – funkciu GENERATE
• pre OUTER APPLY – funkciu GENERATEALL

Naddotazy a poddotazy sa v DAXe robia skladaním vhodných tabuľkových funkcií do seba. Čiže napr. ak by ste chceli vybrať iba objednávky s cenou nad 1000 eur, a tie potom zosumarizovať podľa zákazníka, tak napíšete:

• v SQL: SELECT [CustomerKey], SUM([Cena objednavky]) AS [Obrat] FROM (SELECT [CustomerKey], [Cena objednavky] FROM Objednavky WHERE [Cena objednavky] > 100) AS podvysledok GROUP BY [CustomerKey]
• v DAXe: EVALUATE SUMMARIZE(FILTER(Objednavky, [Cena objednavky] > 100), [CustomerKey], “Obrat”, SUM([Cena objednavky]))

Rýchle vysvetlenie: Najprv si funkciou FILTER vyfiltrujeme riadky spĺňajúce zadanú podmienku, a potom ich zoskupíme pomocou funkcie SUMMARIZE. To ide spraviť preto, lebo funkcia FILTER vracia tabuľku, a funkcia SUMMARIZE očakáva na vstupe tabuľku, hoci aj vypočítanú.

Výber prvých alebo posledných riadkov sa v jazyku SQL robí v každej databáze inak. V DAXe sa robí cez funkciu TOPN. Čiže ak chcete vybrať 3 najlepšie objednávky podľa ceny objednávky, tak napíšete:

• v SQL pre SQL Server: SELECT TOP 3 * FROM Objednavky ORDER BY [Cena objednavky] DESC
• v DAXe: EVALUATE TOPN(3, Objednavky, [Cena objednavky], DESC)

Rýchle vysvetlenie: Funkcia TOPN berie na vstupe počet výsledných riadkov, tabuľku, triediaci stĺpec alebo DAXový výraz, a smer triedenia. Podrobnejšie je vysvetlená v tomto článku.

To je teda v skratke, ako sa prepisujú dotazy z jazyka SQL do jazyka DAX, resp. ako sa myslí v jazyku DAX v porovnaní s jazykom SQL. Tu som Vám uviedol iba tie najľahšie prípady, aby ste s tým vedeli ihneď začať pracovať. Pretože tak ako aj v iných jazykoch, aj tu sa dajú niektoré operácie napísať viacerými spôsobmi, v závislosti od toho, ktorý je v danej situácií vhodnejší. A čítať 20-stranový článok sa Vám asi nechce 🙂 Takže niektoré z týchto prípadov si nechám do jedného z budúcich článkov o optimalizácii DAXových dotazov, ktorá sa používa pri veľkých dátach. Každopádne, ak máte “iba” milióny riadkov, tak to nemusíte riešiť, pretože hrubá sila PowerPivotu to aj v tom najmenej efektívnom prípade spracuje takmer okamžite. A pomocou týchto základných prípadov by ste mali byť schopní prepísať väčšinu bežných dotazov z jazyka SQL do jazyka DAX.

Našli ste tu všetky operácie, ktoré ste hľadali? Ak nie, napíšte dole do komentárov, a postupne budem podľa toho rozširovať tento článok o ďalšie prípady.