17. listopadu 2015

MDX Tutorial 10 – Časové funkce


MDX Tutorial 10 – Časové funkce
Postupem času jsme se propracovali s tutorialem po dvou kapitolách o navigaci k jedné z mých nejoblíbenějších kapitol. Čekají nás časové funkce. Jaké se řeší scénáře? Pokud v datovém skladu pracujeme se stavovými veličinami, bavíme se o tzv. „Semi-Aditivních“ funkcích, kterým jsem se pověnoval mimo tutorial zde: http://www.neoral.cz/2015/05/semi-aditivni-meritka-v-ssas-pomoci-mdx.html
Když pominu semiaditivitu, tak určitě se dost často hodí nějaké to porovnání mezi obdobími. Například meziroční srovnání (rok s rokem, 1 kvartál s 1 kvartálem předchozího roku, Leden 2015 s Lednem 2014, atd.). Dále často využívají kumulované výpočty od začátku období do data. Například kumulovaná suma prodejů od začátku roku. Případně práce s klouzavým obdobím. Průměrná cena akcie/komodity za posledních 14 dní. Suma prodejů za klouzavý rok...
Na všechny tyto aplikace je MDX vybaveno a to velmi dobře.
Všechny scénáře jako prerekvizitu vyžadují označenou časovou dimenzi (Time intelligence) a vybrané úrovně se kterými budete chtít ve výpočtech pracovat. Nejjednodušší způsob je průvodce „Add business intelligence“ na časovou dimenzi.
Pojďme se na jednotlivé kategorie funkcí podívat
Nejprve dotaz ze kterého budu vycházet
SELECT
       {[Measures].[Reseller Sales]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
Výsledek
Časová logika se bude opět odrážet od currentmembera (kapitola 3 http://www.neoral.cz/2015/05/mdx-tutorial-3-pocitane-cleny.html)
Takže nižší úroveň časové dimenze beru z víceúrovňové hierarchie YQMD
Porovnání s předchozím obdobím
PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )
Funkce vrací člen hierarchie posunutý o N zpátky/vpřed. Level je úroveň, o kterou se chceme posunout (např. rok), numeric expression je počet období o které se chceme posunout zpátky. Member je člen od kterého se chceme posunout. Zde se jedná zpravidla o currentmembera
Jdeme spočítat prodeje ve stejném období předchozího roku
WITH MEMBER [Sales PY] AS
       ([Measures].[Reseller Sales], -- prodeje
       ParallelPeriod
             ([Dim Date].[YQMD].[Year] -- level o ktery se chceme posunout
             ,1 -- posun o jeden rok zpet
             ,[Dim Date].[YQMD].currentmember -- posun od currentmembera
             )
       )
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales PY]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
Meziroční srovnání by se počítalo jako
WITH MEMBER [Sales YoY] AS
       /*tupple pro aktualni obdobi*/
       [Measures].[Reseller Sales]
       -
       /*tupple pro lonsky rok*/
       ([Measures].[Reseller Sales], -- prodeje
       ParallelPeriod
             ([Dim Date].[YQMD].[Year] -- level o ktery se chceme posunout
             ,1 -- posun o jeden rok zpet
             ,[Dim Date].[YQMD].currentmember -- posun od currentmembera
             )
       )
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales YoY]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

Meziroční srovnání v procentech by se dalo spočítat pomocí funkce DIVIDE(Dělenec, Dělitel). Výhodou proti klasickému operátoru / je, že je ověřeno dělení 0/prázdnou buňkou. Funkce nevrátí chybu, vrátí místo toho NULL hodnotu
Neodladěné meziroční srovnání by se tedy počítalo
WITH MEMBER [Sales YoY%] AS
Divide(
       /*tupple pro aktualni obdobi*/
       [Measures].[Reseller Sales]
       ,
       /*tupple pro lonsky rok*/
       ([Measures].[Reseller Sales], -- prodeje
       ParallelPeriod
             ([Dim Date].[YQMD].[Year] -- level o ktery se chceme posunout
             ,1 -- posun o jeden rok zpet
             ,[Dim Date].[YQMD].currentmember -- posun od currentmembera
             )
       )
       )
       -1
,Format_string = "Percent"
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales YoY%]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
Odladěné
WITH MEMBER [Sales YoY%] AS
iif(([Measures].[Reseller Sales], -- prodeje
       ParallelPeriod
             ([Dim Date].[YQMD].[Year] -- level o ktery se chceme posunout
             ,1 -- posun o jeden rok zpet
             ,[Dim Date].[YQMD].currentmember -- posun od currentmembera
             )
       ) = null, null,
Divide(
       /*tupple pro aktualni obdobi*/
       [Measures].[Reseller Sales]
       ,
       /*tupple pro lonsky rok*/
       ([Measures].[Reseller Sales], -- prodeje
       ParallelPeriod
             ([Dim Date].[YQMD].[Year] -- level o ktery se chceme posunout
             ,1 -- posun o jeden rok zpet
             ,[Dim Date].[YQMD].currentmember -- posun od currentmembera
             )
       )
       )
       -1
       )
,Format_string = "Percent"
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales YoY%]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

Další srovnání mezi obdobími funguje na obdobném principu. Pro mezikvartální srovnání bychom mohli použít stejnou funkci parallelperiod, jenom bychom level rok nahradili za level kvartál.
Kumulace
Další častou aplikací jsou kumulované výpočty v čase, dost často ještě v kombinaci s meziročním srovnáním. Např. Suma prodejů od Ledna 2015 do Listopadu 2015 vs Suma prodejů od Ledna 2014 do Listopadu 2014. Nejprve ale základní vzorec.
Kumulace od začátku do datumu
Počítalo by se jako sum nad setem NULL:aktuální člen
WITH MEMBER [x] AS
       sum(
       NULL: --počátek
       [Dim Date].[YQMD].currentmember -- aktuální člen
       ,[Measures].[Reseller Sales] --measure
       )
SELECT
       {[Measures].[Reseller Sales]
       ,[x]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

Nejobecnější funkcí pro kumulaci, která provede restart když se změní úroveň, je funkce
PERIODSTODATE( [«Level»[, «Member»] ] )
Funkce vrací množinu prvků od začátku úrovně po člena. Jakmile dojde ke změně názvu na úrovni level. Dojde k vyprázdnění množiny
Kumulovaná suma od začátku roku (Year To Date)  by se tedy spočítala jako
WITH MEMBER [Sales YTD] AS
Sum( -- agregační funkce
       Periodstodate(
             [Dim Date].[YQMD].[Year] -- úroveň kdy dojde k restartu
             ,[Dim Date].[YQMD].currentmember -- po kterého člena
       )
       , [Measures].[Reseller Sales] -- measure
)
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales YTD]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
Téhož efektu bych dosáhl se zjednodušenou variací funkce PeriodsToDate s předdefinovaným levele funkcí YTD( [«Member»] )
WITH MEMBER [Sales YTD] AS
Sum( -- agregační funkce
       YTD( -- Variace PeriodsToDate s předdefinovaným levelem
             [Dim Date].[YQMD].currentmember -- po kterého člena
       )
       , [Measures].[Reseller Sales] -- measure
)
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales YTD]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
Další variace funkce PeriodsToDate jsou funkce
QTD – Restart na začátku kvartálu
MTD – Restart na začátku měsíce
WTD – Restart na začátku týdne
Funkce PeriodsToDate je ale nejobecnější. Můžeme dělat kumulaci v rámci finanční periody, případně hodiny když bude potřeba.
Klouzavé období
LASTPERIODS( «Index»[, «Member»] )
Vrací množinu N (index) posledních prvků před a včetně člena (member)
Pro ilustraci
WITH MEMBER [x] AS
settostr(
       LASTPERIODS(
             3 --poslední 3
             ,[Dim Date].[YQMD].currentmember  --od currentmembera
       )
)
SELECT
       {[Measures].[Reseller Sales]
       ,[x]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

Suma za poslední 3 měsíce by se tedy počítala
WITH MEMBER [Sales 3M Sum] AS
Sum(
       LASTPERIODS(
             3 --poslední 3
             ,[Dim Date].[YQMD].currentmember  --od currentmembera
       )
       ,[Measures].[Reseller Sales] --measure
)
SELECT
       {[Measures].[Reseller Sales]
       ,[Sales 3M Sum]}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

POZOR
Funkce Lastperiods je závislá na levelu parametru member. Pokud je currentmember měsíc a index v lastperiods 3, vrací funkce poslední 3 měsíce. Pokud je currentmember den, vracela by stejně nastavená funkce poslední 3 dny. Pro obecnou kalkulaci je tedy s tímto chováním potřeba počítat.
Závěr
Časové kalkulace jsou jedny z nejčastějších výpočtů v kostkách vůbec, je tedy dobré jim rozumět. Podívali jsme se na všechny nejčastější scénáře kromě stavových veličin, kterým jsem se dopodrobna věnoval v samostatném článku o semiaditivitě (link na začátku dnešního článku). Časové kalkulace jsou potřebné pro většinu měřítek v kostce. Meziroční srovnání se hodí jak pro částku, tak pro Marži, tak pro Náklady... V současném pojetí se počítaný člen vždy odkazoval na konkrétní měřítko. Jde to ale i jinak. V MDX lze udělat jednu obecnou časovou kalkulaci, která bude dostupná pro všechny měřítka a bude uložená v dimenzi. Tomuto tématu se budu věnovat v samostatném článku. Tento je již poměrně rozsáhlý a navíc ještě musíme jako prerekvizitu projít příkaz Scope v calculate skriptu kostky.

Žádné komentáře:

Okomentovat