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:

https://drive.google.com/file/d/0B9ZohZ1CALKZUFY0YlVXRXhUWEU/view?usp=sharing

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.

Sentiment analýza přes PowerBI

Po delší odmlce se opět dostávám ke psaní článků. Výpadek byl způsoben větším množstvím práce a komunitními akcemi. 3 přednášky na konferenci TechEd a 2x WUG (Zlín, Ostrava).

I na těchto akcích jsem ukazoval demo sentiment analýza přes PowerBI nástroje. O co se jedná? Firmě by se mohlo hodit jaká je zpětná vazba k jejím službám, aby mohla své služby zlepšovat. Příklad z reálného života, měl jsem požadavek od jedné spolupracující firmy na vyhodnocení slovních komentářů k aplikaci v Android marketu, co se uživatelům aplikace líbí a nelíbí. Dalším použitím by mohla být analýza Tweetů, komentářů na Facebooku (PowerQuery má Facebook konektor už nějakou dobu).

Jak by se k problému dalo přistoupit? Buď můžete najmout člověka, který bude komentáře, statusy atd. číst a vyhodnocovat. Nebo na to pustíme stroje. Pro strojové zpracování dat se jedná o slušnou výzvu pro vývojáře. Naprogramovat aplikaci, která je schopná porozumnět lidské řeči, rozlišit ironii od upřímně mysleného komentáře není vůbec jednoduché. Chtělo by to nástroj, který je schopný pracovat s textem a pokud možno i učit se ze svých chyb. V Azure Microsoft nabízí službu Azure Machine Learning, která tomuto přesně vyhovuje. Službu naštěstí může použít i člověk, který Azure ML nikdy v životě neviděl a není programátor. Datoví vědci, kteří problematice rozumí a mají nezbytné IT schopnosti se mohou o své výtvory podělit a i na nich vydělávat peníze. Mnoho algoritmů je dostupných i zdarma pro každého.

Pokud byste si chtěli přidat nějakou funkcionalitu z Azure Marketplace, najdete ji na webu http://datamarket.azure.com/browse/data

Mezi algoritmy byste našli i Lexicon Based Sentiment Analysis (pod Machine learning) zde

https://datamarket.azure.com/dataset/aml_labs/lexicon_based_sentiment_analysis

Po registraci dát pořídit funkci. Problém s lexicon based (na základě slovníku) je, že bude reagovat pouze na anglický slovník. Česká data bych potřeboval prvně přeložit do angličtiny, abych mohl provádět Sentiment analýzu. A proč se omezovat na jeden jazyk? Můžeme pvně zjistit jakým jazykem je psaný komentář a tento přeložit do angličtiny, poté vyhodnotit sentiment.

Pro detekci jazyka a překlad můžeme použít MS Translator API v datamarketu, funkci naleznete zde https://datamarket.azure.com/dataset/bing/microsofttranslator

Celou následující demonstraci jsem předváděl na WUG v Brně, na video záznam se můžete podívat na následujícím linku http://www.wug.cz/zaznamy/264-Excel-a-Self-Service-nastroje-pro-Business-Intelligence v čase okolo: 2:50:00

Použiji jen trochu jiná data

Prvně si napíšu do Excelu data do tabulky k analýze. Můžeme zkoušet různé jazyky.

Komentář

Miluji svého šéfa, je to skvělý chlap

Včerejší nehoda na D1 mi radost neudělala, zkysnul jsem tam na 2 hodiny

Venku je dnes hnusně, zůstanu raději pod peřinou

Kolega evidentně není z nejbystřejších

To je dobré jak cyp

Je to čudné, něpáčí sa mi to

Ještě jednou mi zavoláte a budu si stěžovat

Horší oběd jsem v životě nejedl, zvracel jsem ještě několik hodin

čo bolo to bolo, terazky som majorom

čo vravíš ty somár?

I like the way you use PowerQuery

PowerView sucks, you can't change labels

Awwwwwssssssoooooomeeeeeeee

V PowerQuery vyberu, že chci přidat data z Excelové tabulky (Excel Data – From Table). Dotaz zavřu křížkem a zachovám změny. Přidám další zdroj dat From Azure – From Azure Data Marketplace a postupně vyklikám, že chci přidat referenci na funkce z Microsoft Translatoru – Detect (detekce jazyka, ve kterém byl komentář) a Translate pro překlad do angličtiny. Z Lexicon Based Sentiment Analýzy vyberu jedinou funkci Score. Tímto bychom měli vidět v seznamu PowerQuery dotaz Table1 a 3 referencované funkce, klikneme pravým talčítkem na Table1 a dáme Edit. Vidíme zpět náhled tabulky a jdeme na věc.

Prvně budu muset detekovat jazyk funkcí Detect. Přidávám sloupec (Add column - Add custom column) a píši vzorec

Detect([Komentář])

Potvrzuji a vyskakuje na mě tabulka, opravdu chcete ony citlivá data z dokumentu kombinovat z funkcí volanou z internetu. Musím označit data ze sešitu jako public, jinak dostanu chybovou hlášku. Rozbaluji tabulku, ruším prefix a dostávám následující výstup. Pravda, že ne vždy se musí trefit. Viz je to dobré jak cyp.

Komentář	Code
Miluji svého šéfa, je to skvělý chlap	cs
Včerejší nehoda na D1 mi radost neudělala, zkysnul jsem tam na 2 hodiny	cs
Venku je dnes hnusně, zůstanu raději pod peřinou	cs
Kolega evidentně není z nejbystřejších	cs
To je dobré jak cyp	sk
Je to čudné, něpáčí sa mi to	sk
Ještě jednou mi zavoláte a budu si stěžovat	cs
Horší oběd jsem v životě nejedl, zvracel jsem ještě několik hodin	cs
čo bolo to bolo, terazky som majorom	sk
čo vravíš ty somár?	sk
I like the way you use PowerQuery	en
PowerView sucks, you can't change labels	en
Awwwwwssssssoooooomeeeeeeee	en

Přidávám obdobně další sloupec a píši vzorec s funkcí Translate v pořadí co chci přeložit, do jakého jazyka z jakého jazyka

Translate([Komentář],"en",[Code])

Rozbaluji tabulku bez prefixu a získávám přeložená data

Komentář	Code	Text
Miluji svého šéfa, je to skvělý chlap	cs	I love my boss, he's a great guy
Včerejší nehoda na D1 mi radost neudělala, zkysnul jsem tam na 2 hodiny	cs	Last night's accident on D1 me happy, I was stuck there for 2 hours
Venku je dnes hnusně, zůstanu raději pod peřinou	cs	Out today is bad, you'd better stay under the covers
Kolega evidentně není z nejbystřejších	cs	A colleague is clearly not of the brightest
To je dobré jak cyp	sk	It is a good idea as cyp
Je to čudné, něpáčí sa mi to	sk	It's strange to me that něpáčí
Ještě jednou mi zavoláte a budu si stěžovat	cs	You call me one more time and I'll complain
Horší oběd jsem v životě nejedl, zvracel jsem ještě několik hodin	cs	The worse I've ever eaten lunch, I threw up a few more hours
čo bolo to bolo, terazky som majorom	sk	what it was was I his terazky
čo vravíš ty somár?	sk	What vravíš you a donkey?
I like the way you use PowerQuery	en	I like the way you use PowerQuery
PowerView sucks, you can't change labels	en	PowerView sucks, you can''t change labels
Awwwwwssssssoooooomeeeeeeee	en	Awwwwwssssssoooooomeeeeeeee

Zbývá vyhodnotit sentiment za použití funkce Score. Přidávám nový sloupec a píši vzorec

Score([Text])

rozbaluji Result bez prefixu a získávám sentiment, škála od -1 (negativní) do 1 pozitivní, 0 neutrál. Desetinné číslo se záporným znamínkem spíš negativní, atd.

Komentář	Code	Text	result
Miluji svého šéfa, je to skvělý chlap	cs	I love my boss, he's a great guy	1
Včerejší nehoda na D1 mi radost neudělala, zkysnul jsem tam na 2 hodiny	cs	Last night's accident on D1 me happy, I was stuck there for 2 hours	1
Venku je dnes hnusně, zůstanu raději pod peřinou	cs	Out today is bad, you'd better stay under the covers	-0.333333333
Kolega evidentně není z nejbystřejších	cs	A colleague is clearly not of the brightest	1
To je dobré jak cyp	sk	It is a good idea as cyp	1
Je to čudné, něpáčí sa mi to	sk	It's strange to me that něpáčí	-1
Ještě jednou mi zavoláte a budu si stěžovat	cs	You call me one more time and I'll complain	-1
Horší oběd jsem v životě nejedl, zvracel jsem ještě několik hodin	cs	The worse I've ever eaten lunch, I threw up a few more hours	-1
čo bolo to bolo, terazky som majorom	sk	what it was was I his terazky	0
čo vravíš ty somár?	sk	What vravíš you a donkey?	0
I like the way you use PowerQuery	en	I like the way you use PowerQuery	1
PowerView sucks, you can't change labels	en	PowerView sucks, you can''t change labels	0
Awwwwwssssssoooooomeeeeeeee	en	Awwwwwssssssoooooomeeeeeeee	0

Pravda je, že to Score ne vždy trefilo (stížnost na zásek na D1 (ale zde pochybyl už překladač). Chtělo by to tedy stejně ruční validaci a aby se stroj mohl učit z vlastních chyb.

Závěr

K tomuto nápadu mě přivedl článek Chrise Webba (blog: http://cwebbbi.wordpress.com  v angličtině), nicméně jsem jej musel modifikovat s překladem do češtiny. Stěžejním bodem dnešního článku není, že sentiment analýza přes Excel je nejúžasnější věc na světě. Vidíte, že to nefunguje na 100% (byť je to fajn a lepší než číst dlouhé texty a snažit se sám detekovat jazyk a překládat a i to score se hodí). Stěžejní myšlenkou je, že se můžu z PowerQuery připojit na programové API od třetí strany a uživatelsky využívat službu Machine Learning aniž bych byl Datový vědec (Data Scientist)

Záznam přednášky z WUG: Excel a Self Service Business Intelligence nástroje

Záznam přednášky ke shlédnutí a stažení zde:
http://www.wug.cz/zaznamy/264-Excel-a-Self-Service-nastroje-pro-Business-Intelligence
Co dodat, tohle byl krátký blog :)