Najnovší SSAS Tabular 2019 pridal najväčšiu novinku v jazyku DAX za posledné roky – calculation groups. Čiže výpočtové skupiny, ktoré vám umožnia zadefinovať jeden typ výpočtu raz, a použiť ho opakovane nad všetkými ostatnými merítkami. To vám značne zníži počet merítok v dátovom modeli. Ak ste teda doteraz ešte nepoužívali techniku supermerítok.

Výpočtové skupiny prídu vhod v prípadoch, keď viackrát opakujete ten istý typ výpočtu nad viacerými základnými merítkami. Čiže najmä pri časovej analýze, ale nielen pri nej. Povedzme, že máte v dátovom modeli základné merítka Obrat, Náklady a Zisk. A chcete ku každému z nich zadefinovať niekoľko verzií: aktuálna hodnota, hodnota pred rokom, medziročná zmena, YTD, atď.. Už len v tomto prípade by ste museli zadefinovať merítka pre všetky kombinácie základných merítok a ich verzií. Čo by v našom prípade bolo 3 základné merítka * 4 verzie = 12 merítok. To ešte nie je až také hrozné číslo, a dalo by sa to ešte prežiť. Čo však, ak máte report, kde máte povedzme 20 základných merítok, a k nim potrebujete 50 verzií? A nebodaj to zobraziť všetko naraz v jednej kontingenčke? 1000 merítok predsa do modelu nebudete tlačiť. Aj keď, videl som už aj také prípady…

Na vyriešenie tohto problému sa doteraz používala technika supermerítka. Tam stačilo namiesto 20 * 50 merítok zadefinovať iba 20 + 2 merítka. Čo je dosť nepomer voči 1000 merítkam. A najmä si nezaprasíte dátový model a všetky ponuky 1000 merítkami. Tejto technike sa ale budeme venovať niekedy inokedy v inom článku.

Tentokrát sa ale Microsoft nečakane vytiahol, a techniku supermerítka zoštandardizoval a spravil z nej novú funkcionalitu v dátovom modeli – calculation groups, aka výpočtové skupiny. A pridal do nej ďalšie bonusy, ktoré ešte viac zjednodušia pokročilú analytiku v jazyku DAX. A vďaka tomu sa jeho schopnosti už veľmi silno blížia schopnostiam jazyka MDX. S 5x menšou komplexnosťou jazyka.

Calculation groups v tomto momente fungujú v preview režime iba v SSAS Tabulare 2019, Azure Analysis Services a Power BI. A zatiaľ na to neexistuje žiaden oficiálny editor – dajú sa nastaviť iba cez API, alebo cez komunitný Tabular Editor. Vždy však lepšie, ako klincom do oka. Na rozbehanie potrebujete mať  dátový model nastavený aspoň na compatibility level 1470. Čo zatiaľ funguje iba v SSAS Tabulare 2019 a Azure Analysis Services. A vie s tým zatiaľ pracovať iba Power BI Desktop z apríla 2019 a novšie.

Ako nastaviť calculation groups

Calculation group sa dajú momentálne nastaviť cez užívateľské rozhranie iba v najnovšom Tabular Editore. Do SQL Server Data Tools to príde “neskôr, ako sa bude blížiť vydanie SQL Servera 2019”. Netuším, prečo Microsoft v poslednej dobe vydáva funkcionalitu bez UI, keď UI bola jeho silná stránka dlhé roky, a dorába podporu UI aj o pár mesiacov neskôr. Ale časy sa zjavne menia…

Na toto demo potrebujete mať nainštalovaný SSAS Tabular 2019 CTP 2.5 a novší. Otvoríme si teda Tabular Editor, a vytvoríme nový dátový model s compatibility levelom 1470:

Potom pridáme do modelu dátový zdroj, “naimportujeme” 3 tabuľky zo vzorovej databázy AdventureWorksDW2012, prepojíme ich, a označíme tabuľku DimDate ako časovú tabuľku:

Potom v tabuľke FactInternetSales vytvoríme klasické 3 merítka s týmito vzorcami:

Obrat := SUM(FactInternetSales[SalesAmount])

Naklady := SUM(FactInternetSales[SalesAmount])

Zisk := [Obrat] – [Naklady]

Model nasadíme na SSAS Tabular, a dáme ho spracovať / sprocesovať. Potom sa k nemu pripojíme cez Live pripojenie z Power BI Desktopu, a vytvoríme si kontingenčku, kde na riadkoch budú roky (stĺpec CalendarYear z tabuľky DimDate), a vedľa nich merítko Obrat:

Teraz by sme chceli zadefinovať jeho 4 verzie: aktuálna hodnota, hodnota pred rokom, medziročná zmena, YTD. A to nielen pre merítko Obrat, ale aj pre merítka Naklady a Zisk. A potenciálne aj ďalšie, ktoré nám neskôr pribudnú do dátového modelu. Vytvoríme preto novú výpočtovú skupinu cez Tabular Editor, nazvanú “Casove vypocty”, s atribútom nazvaným “Verzie”:

Kalkulačné skupiny vyzerajú na prvý pohľad ako jednostĺpcová tabuľka. A tak aj vo väčšine prípadov fungujú. Ten jeden stĺpec sa nazýva “atribút”, a jeho položky sa nazývajú “calculation items” – výpočtové položky. Každá položka reprezentuje jeden typ výpočtu, ktorý sa pri použití kalkulačnej položky použije v kontingenčke na tie výpočty, ktoré tam už sú. Kalkulačných skupín môžete pridať do dátového modelu koľko len chcete, ale atribút môžu mať iba jeden. Podobne ako pomocné tabuľky v prípade techniky supermerítka.

Zadefinujeme teda postupne 4 nové výpočtové položky pre ten atribút, pretože chceme mať 4 verzie výpočtov vyššie:

Vzorce pre ne budú takéto:

• pre položku “Aktualna hodnota”: SELECTEDMEASURE()
• pre položku “Hodnota pred rokom”: CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
• pre položku “Medzirocna zmena”:
  CALCULATE ( SELECTEDMEASURE(), ‘Casove vypocty'[Verzie] = “Aktualna hodnota” )
  – CALCULATE ( SELECTEDMEASURE(), ‘Casove vypocty'[Verzie] = “Hodnota pred rokom”)
• pre položku “YTD”: CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[FullDateAlternateKey]))

Zmeny v modeli nasadíme na server, a dáme ich spracovať / sprocesovať. Vzorce si vysvetlíme o chvíľu.

Prejdeme teraz do Power BI Desktopu, a dáme tlačítko Obnoviť. Do ponuky sa nám pridá naša výpočtová skupina, ktorá sa na prvý pohľad tvári ako bežná tabuľka s 1 stĺpcom:

Pridáme teda tento “stĺpec” s názvom “Verzia” do kontingenčky do oblasti stĺpcov. A výsledok – predtým Obrat po rokoch – sa nám rozšíri na všetky 4 verzie Obratu:

A keďže sa ten stĺpec navonok tvári vo väčšine prípadov ako každý iný stĺpec, tak môžeme aj filtrovať podľa jeho hodnôt, a rovnako ako aj v prípade supermerítka si zobraziť iba vybrané verzie, ktoré chceme:

A najlepšia vec – je to zadefinované všeobecne pre všetky merítka. Takže keď si tam do kontingenčky pridáme ešte napr. merítko Naklady, tak to bude vyzerať takto:

A vďaka tomu to nádherne zjednoduší dátový model.

A ako to funguje?

Všimnite si, že som v hore uvedených vzorcoch pre výpočtové položky použil funkciu SELECTEDMEASURE. Pre tých, ktorí ste už pracovali s jazykom MDX, je to ekvivalent jeho funkcie CurrentMember. Táto funkcia SELECTEDMEASURE vracia odkaz na merítko v aktuálnom kontexte výpočtu. V tom kontexte musí byť iba jedno merítko, lebo inak funkcia nevráti nič. Alebo chybu. Vďaka tejto funkcii sa vieme jednoducho odkazovať na “aktuálne počítané merítko”. Preto ho potom môžeme jednoducho dosadiť do funkcie CALCULATE a všeobecne spočítať ostatné verzie merítka, tak ako to vidíte v ďalších vzorcoch. V kontingenčke sa potom na priesečníku merítka s aktuálnou výpočtovou položkou použije daný všeobecný vzorec.

Ďalšia zaujímavosť je vo vzorci pre výpočtovú položku “Medzirocna zmena”. Tá počíta rozdiel medzi aktuálnou hodnotou merítka, a jeho hodnotou pred rokom. Aby sme tam nemuseli opakovať vzorce z predchádzajúcich výpočtových položiek, tak vieme využiť kvázirekurziu – odkázať sa na tie výpočtové položky. Za predpokladu, že nevznikne kruhová závislosť. Odkaz sa robí zmenou kontextu cez CALCULATE a filter – kde filter nastavíme na želanú výpočtovú položku, ako keby to bola obyčajná hodnota v stĺpci. Elegantné, mňam.

Výpočtové skupiny majú aj ďalšiu zaujímavú vlastnosť – Precedence:

Je to číslo od 0 vyššie, ktoré môžete nastaviť, ak chcete, aby sa viacero výpočtových skupín aplikovalo pri ich použití. Napr. pri počítaní niektorých nekumulatívnych ukazovateľov (napr. percentá, priemery, atď.) môžete vďaka tomu pridať výnimky, aby sa to za daných okolností počítalo/nepočítalo/počítalo inak. Túto vlastnosť nastavíte od nuly vyššie. Výpočtové skupiny s nižším číslom sa aplikujú vo finálnom výpočte skôr, a tie s vyšším číslom neskôr. Berte to “Precedence” ako “prednosť pri výpočtoch”.

Nové funkcie pre calculation groups

Do jazyka DAX nám pribudli tieto 4 nové funkcie, ktoré napr. už v Power BI boli neoficiálne na tajnáša niekoľko mesiacov:

• SELECTEDMEASURE() – vráti odkaz na merítko v aktuálnom kontexte výpočtu, resp. na aktuálne počítané merítko,
• SELECTEDMEASURENAME() – vráti názov aktuálne počítaného merítka, ako reťazec,
• SELECTEDMEASUREFORMATSTRING() – vráti formátovací reťazec aktuálne počítaného merítka,
• ISSELECTEDMEASURE( M1, M2, … ) – vráti pravdu, ak aspoň jedno z merítok špecifikovaných ako parametre je v aktuálnom kontexte výpočtu.

Nevýhody calculation groups

Táto funkcionalita však zatiaľ funguje iba v SSAS Tabulare 2019 a Azure Analysis Services, a neoficiálne aj v Power BI Desktope. Ako klient na prezeranie výsledkov je zatiaľ podporovaný iba Power BI Desktop, resp. ľubovoľný iný DAX klient. Keď si ten istý dátový model vyskúšate napr. v Exceli, tak to síce nevyhlási chybu, ale ani nevráti správne výsledky:

Ale je to predsa len stále v režime preview, takže asi to dovtedy nejako dorobia. V tomto prípade je problém v tom, že pri použití jazyka MDX na dotazovanie sa na dátový model – čo v skutočnosti používa práve Excel – zatiaľ tieto výpočtové skupiny ešte nie sú podporované. Predpokladám však, že do finálnej verzie to ešte dorobia. Veď predsa nenechajú svoj najúspešnejší produkt v histórii na hanbu…

Aktualizácia 19.7.2020: Výpočtové skupiny už boli medzičasom plne dokončené, a fungujú už aj pri prístupe cez jazyk MDX, čiže aj pri zobrazovaní v Exceli.

A čo ostatní?

A čo ostatní, ktorí ešte používajú všetky staršie verzie PowerPivotu? Ako to majú rozbehať? V tomto prípade je potrebné použiť techniku supermerítka. Ktorá je prácnosťou zhruba rovnako “prácna” ako calculation groups. Veď práve z toho to s najväčšou pravdepodobnosťou vzniklo. O technike supermerítka budem snáď písať v niektorom z ďalších článkov. Dovtedy môžete ísť napr. na náš kurz SSAS Tabularu či na pokročilý kurz Power BI, kde sa dopodrobna venujem aj supermerítkam, aj calculation groups 🙂