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