V posledních 2 dílech jsme se bavili jednak o časových kalkulacích (http://www.neoral.cz/2015/11/mdx-tutorial-10-casove-funkce.html) a o příkazu Scope (http://www.neoral.cz/2015/12/mdx-tutorial-11-prikaz-scope.html). Jedná se už o pokročilejší témata, ke kterým se možná ne všichni odhodlaní čtenáři tutorialu dostanou. Nicméně časové kalkulace se hodí všem, většinou i pro všechna měřítka.
Dnešní díl se dá v základu z tutorialu vytrhnout, není na něm přímo závislý. Byť pochopení principů z dílů předchozích je určitě velkým plusem. Chcete časové kalkulace jako je třeba meziroční srovnání, případně kumulovaná suma od začátku roku (YTD) a nechce se Vám ani hnout prstem? Pak jste tu špatně, budete muset párkrát kliknout myší (ale o tolik víc toho zase dělat pro základní variantu nebudete muset)
Výhody přístupu
Bude se jednat o výpočty uložené v dimenzi raději než v Measures. Pokud bysme chtěli následující výpočty: Meziroční srovnání, Meziroční srovnání v %, Meziroční srovnání 2 roky zpátky, Meziroční srovnání 2 roky zpátky v procentech, kumulovaná suma od začátku roku (YTD), srovnání kumulací YTD meziročně, srovnání kumulací YTD meziročně v procentech... pro měřítko Částka bez DPH a ukládali bychom výpočty do Measures, museli bychom napsat 7 počítaných členů. Pokud bychom to stejné chtěli pro druhé měřítko Částka s DPH máme dalších 7 a jsme na 14ti počítaných členech. Daň, dalších 7 a už máme 21 počítaných členů, atd... Počet počítaných členů exponenciálně roste a tím se snižuje přehlednost kostky a je v takové kostce potom problém něco najít. Nabízí se tedy napsat, nebo naklikat univerzální počítaný člen a uložit jej v dimenzi tak, aby se definice nemnožily.
Z pohledu uživatele může být drobnou nevýhodou, že musí logicky přeponout do režimu, měřítko není mezi Measures, ale jako atribut dimenze. Po nějaké době si ale většinou zvyknou, Ti kteří si nezvyknou a nedomluví se s kontingenční tabulkou, těm raději nachystejte report v SSRS, PowerBI, nebo jiné reportovací technologii.
Prerekvizity
Musíte mít ve Vaší SSAS databázi a relevantní kostce „Časovou dimenzi“ s označenými úrovněmi jako atributy přes které chcete počítat. (Chcete-li dělat meziroční srovnání, budete potřebovat označit atribut rok jako „Year“, pokud budete chtít počítat WTD, budete potřebovat level týden, atd.
Tuto prerekvizitu nejsnáze splníte nastavením přes průvodce „Add business intelligence“
Čili na dimenzi Dim Date v našem projektu pravé tlačítko-Add business intelligence-Define dimension intelligence-dimension type „Time“, nastavit atributy, finish.
Základní provedení – Level 1
Generování přes průvodce. K časovým kalkulacím se dá dojít velmi jednoduše přes průvodce, proto skoro bez práce. Kliknete pravým tlačítkem na kostku, kam chcete časové kalkulace přidat, add business intelligence, define time intelligence (next), vyberte víceúrovňovou hierarchii na jehož currentmembera časová dimenze má reagovat a vyberte zatržítky požadované výpočty (next). V dalším kroku vyberte první měřítko (aby to fungovalo na vše posléze pozměníme script) (next). Souhrnná stránka, finish.
Co se stalo?
Do data source view průvodce přidal sloupec (YQMD Dim date calculations – spustil jsem na hierarchii YQMD) do tabulky s kalendářem s konstantou představující aktuální období (current dim date). Z tohoto sloupce do dimenze Dim Date vytvořil stejnojmennou hierarchii, pro kterou zakázal agregaci (IsAggregatable = false, přece jenom nemá smysl sumovat meziroční srovnání v procentech s YTD...). To že se má ve výchozím chování zobrazovat aktuální období zajišťuje default member který bere „Current dim date“ jako default. Mimo to se nám v kostce vygeneroval calculate script.
Průzkum přes Excel
Když se do kostky připojíme z Excelu můžeme vybrat Internet Sales jako measure, na řádky dát YQMD hierarchii a na sloupce YQMD Dim Date Calculations viz obrázek
Tam kde výpočet nedává smysl, člen píše NA. Pro vybrané měřítko počítá, bylo první v pořadí. Pro všechna jiná měřítka by se zobrazovalo NA všude.
Věci které se mi na první pohled nelíbí a chtěl bych je změnit? V prvé řadě „NA“, když výpočet nedává smysl.rRaději bych vracel prázdnou buňku. Chtěl bych také, aby výpočet fungoval pro všechny measures a to nikoliv pro statický seznam, ale i pro měřítka, která přidám časem respektive aby mi to nezačalo padat při nasazení, když měřítko odeberu. Zbývá tedy sáhnout do scriptu. Pro zjednodušení nechám ve scriptu v článku jen YTD, script vypadá následovně
CALCULATE;
/*
Begin Time Intelligence script for the [Dim Date].[YQMD] hierarchy.
*/
Create Member
CurrentCube.[Dim Date].[YQMD Dim Date Calculations].[Year to Date]
As "NA" ;
Scope(
{
[Measures].[Internet Sales]
}
) ;
/*Year to Date*/
(
[Dim Date].[YQMD Dim Date Calculations].[Year to Date],
[Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
)
=
Aggregate(
{ [Dim Date].[YQMD Dim Date Calculations].[Current Dim Date] }
*
PeriodsToDate(
[Dim Date].[YQMD].[Year],
[Dim Date].[YQMD].CurrentMember
)
) ;
End Scope ;
Najdeme zde příkaz CALCULATE; který počítá hodnoty klíčových buněk, měl by v kostce zůstat první a doporučuje se jej nechat jak je pokud vyloženě nevíte co děláte.
Následuje důvěrně známý příkaz Scope z minulého dílu tutorialu. Který říká, měním hodnoty buněk pro měřítko Internet Sales. Dále definice počítaného člena a naplnění konstantou „NA“, když jsou splněné podmínky, přepis hodnoty v buňce meziročním srovnáním. Zde stačí dvě drobné úpravy, aby script fungoval lépe. Pro fungování pro všechny měřítka nahradíme Measures.[Internet Sales] za Measures.Members (pro všechny zhmotněná měřítka), případně Measures.Allmembers pro všechny měřítka včetně počítaných. Eventuelně můžete použít libovolný set měřítek.
Upravený script vypadá následovně
CALCULATE;
Create Member
CurrentCube.[Dim Date].[YQMD Dim Date Calculations].[Year to Date]
As NULL ;
Scope(
{
[Measures].allmembers
}
) ;
/*Year to Date*/
(
[Dim Date].[YQMD Dim Date Calculations].[Year to Date],
[Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
)
=
Aggregate(
{ [Dim Date].[YQMD Dim Date Calculations].[Current Dim Date] }
*
PeriodsToDate(
[Dim Date].[YQMD].[Year],
[Dim Date].[YQMD].CurrentMember
)
) ;
End Scope ;
Level 2 vytunění, přesun do samostatné dimenze
Takhle to víceméně funguje (u některých výpočtů je potřeba poladit aby nedocházelo k dělení nulou, ale to jsou malé věci pokud se ve skriptu vyznáte). Jediný problém je pro koncového uživatele je ony výpočty najít. Přece jen pod časovou dimenzí jsou trochu zakopané.
Posloupnost kroků nemusíte provést stoprocentně ve stejném pořadí a než se do podobných akcí pustíte s produkční kostkou, doporučuji zálohu projektu :)
Nejprve vytvoříme novou dimenzi, do které výpočty přesuneme. Pravé tlačítko na dimensions, new dimension, use existing table (next), vyberte jako zdrojovou dimenzi dimdate, jako klíčový atribut YQMD dim date calculations, name column můžete nechat prázdný jako na obrázku
V dalším kroku přejmenujte atribut na například Časové kalkulace pokud se bude jednat o českou kostku, v dalším kroku přejmenujte dimenzi například také Časové kalkulace. Finish.
Poté přidejte dimenzi do kostky, definice kostky cube structure, pravé tlačítko na dimensions add Cube dimension – Časové kalkulace. Vůbec se nemusíme trápit s vazbami na záložce dimension usage. Upravíme návaznosti v calculate scriptu. Stačí nahradit výskyt původního [Dim Date].[YQMD Dim Date Calculations] za [Časové kalkulace].[Časové kalkulace].
Přepneme se do Data source view, najdeme definici atributu a původní text Current Dim Date nahradíme například za Aktuální období. V Calculate scriptu všude kde se vyskytovalo Current Dim Date nahradíme stejným textem.
Původní atribut v dimenzi Dim Date můžeme smazat z definice kostky. Nasadíme projekt, zprocesujeme. Otevřeme novou dimenzi Časové kalkulace, vybereme klíčový atribut, nastavíme default member, zakážeme agregaci. Nasadíme.
Hotovo
Výsledný calculate zjednodušený script vypadá následovně
CALCULATE;
/*
Begin Time Intelligence script for the [Dim Date].[YQMD] hierarchy.
*/
Create Member
CurrentCube.[Časové kalkulace].[Časové kalkulace].[Year to Date]
As NULL ;
Scope(
{
[Measures].allmembers
}
) ;
/*Year to Date*/
(
[Časové kalkulace].[Časové kalkulace].[Year to Date],
[Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
)
=
Aggregate(
{ [Časové kalkulace].[Časové kalkulace].[Aktuální období] }
*
PeriodsToDate(
[Dim Date].[YQMD].[Year],
[Dim Date].[YQMD].CurrentMember
)
) ;
End Scope ;
/*
End Time Intelligence script for the [Dim Date].[YQMD] hierarchy.
*/
Dimenze časové kalkulace je poté samostatná a struktura kostky je přehlednější. Může docházet ke zmatení v případě více kalendářů (Role playing dimenze) na který kalendář má vlastně takováto dimenze reagovat (je to svázané s currentmemberem.
Level 3 by byl napsat vše manuálně za použití příkazu Scope, to by ale bylo poněkud nad rámec tutorialu.
Závěr
V dnešním díle jsme si ukázali, jak s minimálním úsilím dojít k časovým kalkulacím do multidimenzionálních OLAP kostek (Tabulární model počítané členy v dimenzích neumí). V podnstatě je tento přístup na tutorialu nezávislý a není pro něj znalost MDX potřeba. Znalost jazyka je ale obrovskou výhodou minimálně k pasivnímu pochopení co vypadlo z průvodce, případně pokud byste chtěli calculate script modifikovat, odladit případné chyby atd.
