12. června 2015

PowerPivot DAX a časové funkce - 1. část

Nezávisle na typu byznysu, hodí se vybraná měřítka analyzovat v čase. Je vcelku jedno, jestli se jedná o příjmy, výdaje, výrobu, počty návštěv, prodané kusy... Výpočty jako meziroční srovnání, kumulované sumy třeba i v kombinaci s porovnáním se hodí univerzálně. Jak se s těmito výpočty vypořádat přes DAX? O tom by měl být tento článek.
Budu vycházet z Accessové databáze AdventureWorks Data Warehouse (jen jsem naimportoval vybrané tabulky do Accessu a zjednodušil strukturu). Nepotřebujete tedy ani SQL Server, abyste mohli zkoušet. Dokonce ani Access. Databáze ke stažení zde:
V modelu vytvořím 3 měřítka (Internet sales, Reseller sales a Quota). Vytvořím časovou hierarchii s úrovněmi Rok, Kvartál, Měsíc, Datum (YQMD). Důležitou prerekvizitou je označení časové tabulky jako kalendáře, aby nám fungovala časová logika. Provádí se v okně PowerPivot na záložce design, mark as date table. Je potřeba pouze vybrat sloupec s datovým typem Date. Hned na úvod musím říct, že se v PowerPivotu a Tabulárním modelu SQL Server Analysis services hůř pracuje s týdnem ve srovnání například s modelem multidimenzionálním.
Prerekvizity splněny, ještě vyklikám počáteční stav kontingenční tabulky s YQMD hierarchií na řádcích, měřítkem Reseller sales v oblasti hodnot a můžeme se pustit do výpočtů krok po kroku.
Row Labels
Reseller sales
2005
$8,065,435.31
2006
$24,144,429.65
2007
$32,202,669.43
2008
$16,038,062.60
Grand Total
$80,450,596.98
Calculate
Ještě než se pustím do samotných výpočtů, musím zmínit jednu z nejzásadnějších funkcí v celém DAX a tou je funkce Calculate. Její význam je podobně stěžejní jako currentmember v MDX (o funkci currentmember v MDX jsem blogoval zde: http://www.neoral.cz/search/label/MDX). Calculate nám umožňuje provést výpočet ve změněném kontextu. Když se podíváte na tabulku nahoře, zde je kontextem pro výpočet aktuální rok (např. 2005). Při časových funkcích budeme chtít tento kontext měnit, např u meziročního srovnání budu chtít počítat se stejným měřítkem, ale za stejné období předchozího roku.
Meziroční srovnání v procentech
Období samy mezi sebou nemusí být porovnatelné z důvodu cyklu byznysu v jakém společnost funguje. Například ve školícím středisku je začátek roku slabší (neschválené rozpočty na školení), konec roku znatelně silnější (snahy utratit z rozpočtu zbytky :)) Prosinec a Leden jsou hned vedle sebe, ale jsou neporovnatelné. Proto je lepší porovnávat období meziročně.
Pro meziroční srovnání budu muset prvně zjistit, jaké byly prodeje ve stejném období předchozího roku (rok 2008 s rokem 2007, první kvartál s prvním kvartálem předchozího roku atd.). Na stejné období předchozího roku si ukážu nejjednodušeji přes funkci SAMEPERIODLASTYEAR, očekává jediný argument a tím je sloupeček obsahující datum z kalendářní tabulky (prerekvizitou je označení časové tabulky jako časové). Cílovou tabulku volím FactInternetSales, jméno kalkulovaného člena Reseller YoY% Vzorec zatím vypadá následovně:
=SAMEPERIODLASTYEAR(DimDate[Date])
Tento DAX výraz mi zatím vrátí pouze časový údaj se stejným obdobím předchozího roku (v MDX bych řekl set). Tento časový údaj budu potřebovat použít jako filtr ve funkci calculate, která mi spočítá výpočet nad změněným kontextem stejného období předchozího roku. Výpočet
=CALCULATE([Reseller sales],SAMEPERIODLASTYEAR(DimDate[Date]))
Data:
Row Labels
Reseller sales
Reseller YoY%
2005
$8,065,435.31
3
$3,193,633.97
4
$4,871,801.34
2006
$24,144,429.65
8065435.305
1
$4,069,186.04
2
$4,153,820.42
3
$8,880,239.44
3193633.969
4
$7,041,183.75
4871801.337
2007
$32,202,669.43
24144429.65
2008
$16,038,062.60
32202669.43
2009
16038062.6
Grand Total
$80,450,596.98
80450596.98
Stačí podělit aktuální prodeje, versus předchozí rok a naformátovat číslo jako procento, odečíst jedničku, aby bylo číslo vyjádřené pouze jako nárůst/pokles a ne poměr aktuální vs předchozí.
Vzorec:
=[Reseller sales]/CALCULATE([Reseller sales],SAMEPERIODLASTYEAR(DimDate[Date]))-1
Data:
Row Labels
Reseller sales
Reseller YoY%
2005
$8,065,435.31
#NUM!
1
-100.00 %
2
-100.00 %
3
$3,193,633.97
#NUM!
4
$4,871,801.34
#NUM!
2006
$24,144,429.65
199.36 %
1
$4,069,186.04
#NUM!
2
$4,153,820.42
#NUM!
3
$8,880,239.44
178.06 %
4
$7,041,183.75
44.53 %
2007
$32,202,669.43
33.38 %
2008
$16,038,062.60
-50.20 %
2009
-100.00 %
2010
-100.00 %
Grand Total
$80,450,596.98
0.00 %
Zbývá ošetřit dělení prázdnou buňkou. Problém vyřešíme obdobně, jako v Excelu funkcí If. Oproti worksheetové funkci if v DAX není možno mít ve 2 a 3 větvi funkce jiné datové typy, nemůžu tedy použít „“ pro prázdnou buňku. Místo toho použiji formátově neutrální funkci blank(), pro test na prázdnou buňku funkci isblank(). Pro větší přehlednost použiji web www.daxformatter.com pro naformátování vzorečku do čitelnější podoby
Vzorec:
=
IF (
    ISBLANK (
        CALCULATE (
            [Reseller sales],
            SAMEPERIODLASTYEAR ( DimDate[Date] )
        )
    ),
    BLANK (),
    [Reseller sales]
        / CALCULATE (
            [Reseller sales],
            SAMEPERIODLASTYEAR ( DimDate[Date] )
        )
        - 1
)
Pro roky budoucí, které nemají zatíbm data by vyšlo, že se jedná o 100% pokles. Chci tedy ještě testovat zda v daném období existuje skutečnost, když ne, prázdná buňka přes. Použiji funkci OR.
Finální vzorec:
=
IF (
    OR (
        ISBLANK ( [Reseller sales] ),
        ISBLANK (
            CALCULATE (
                [Reseller sales],
                SAMEPERIODLASTYEAR ( DimDate[Date] )
            )
        )
    ),
    BLANK (),
    [Reseller sales]
        / CALCULATE (
            [Reseller sales],
            SAMEPERIODLASTYEAR ( DimDate[Date] )
        )
        - 1
)
Data:
Row Labels
Reseller sales
Reseller YoY%
2005
$8,065,435.31
3
$3,193,633.97
4
$4,871,801.34
2006
$24,144,429.65
199.36 %
1
$4,069,186.04
2
$4,153,820.42
3
$8,880,239.44
178.06 %
4
$7,041,183.75
44.53 %
2007
$32,202,669.43
33.38 %
2008
$16,038,062.60
-50.20 %
Grand Total
$80,450,596.98
0.00 %
Kumulované sumy od začátku období až po období aktuální
Ani meziroční srovnání nemusí být vždy vypovídající, jeden rok je silnější březen, duben slabší. Další rok si to prohodí. Kumulovaná suma příjmů od začátku roku do dubna však porovnatelná je. Funkce TOTALYTD spočítá sumu od začátku roku až do bodu v čase, kde se nacházíte, nový rok začíná znova. Ekvivaletně fungují funkce TOTALQTD, TOTALMTD, jen na jiných úrovních.
TOTALYTD očekává dva argumenty, měřítko se kterým chceme počítat a kde najde datum (tabulka sloupec, nutno označit dopředu jako časovou tabulku).
Reseller YTD by se počítalo jako vzorec:
=TOTALYTD([Reseller sales],DimDate[Date])
Data:
Row Labels
Reseller sales
Reseller YTD
2005
$8,065,435.31
$8,065,435.31
3
$3,193,633.97
$3,193,633.97
4
$4,871,801.34
$8,065,435.31
2006
$24,144,429.65
$24,144,429.65
1
$4,069,186.04
$4,069,186.04
2
$4,153,820.42
$8,223,006.46
3
$8,880,239.44
$17,103,245.90
4
$7,041,183.75
$24,144,429.65
2007
$32,202,669.43
$32,202,669.43
2008
$16,038,062.60
$16,038,062.60
Grand Total
$80,450,596.98
Výsledná tabulka například zobrazuje, jak ResellerYTD roste. První kvartál je sumou prvního kvartálu. Druhý sumou 1+2, třetí 1+2+3 atd.
Závěr

Časových vzorců je celá řada. V první části jsme se podívali na srovnání mezi obdobími a kumulované sumy. Tímto ale nekončí, ještě budeme muset řešit semiaditivní chování (co znamená semiaditivní jsem popisoval ve článku http://www.neoral.cz/2015/05/semi-aditivni-meritka-v-ssas-pomoci-mdx.html) případně výpočty nad klouzavým obdobím. V DAX se jen složitěji pracuje s týdenní logikou a to pravděpodobně kvůli rozdílnému začátku týdne v různých státech. Jinak se člověk nepopsaný ani jedním analytickým modelem snáz naučí pracovat s DAX, než třeba s MDX.

Žádné komentáře:

Okomentovat