28. června 2015

Jak se zbavit funkce VLOOKUP (SVYHLEDAT), DAX funkce pro detailní výpočty

Dnešní článek míří primárně na stranu uživatelů Excelu. Protože PowerBI je hlavně o tom, přinést uživatelům podobné možnosti, jaké máme v serverových produktech. K sepsání tohoto článku mě inspirovalo uživatelské školení u klienta. Tímto posílám pozdravy do Mladé Boleslavi :) Třeba i Vašim uživatelům tento článek pomůže PowerBI nástroje lépe uchopit na něčem, co je jim blízké. Funkce zde zmíněné jsou ale použitelné nejen pro ně. Bude se jednat o zajímavé DAX (Data Analysis Expressions) funkce v rámci PowerPivot modelu, případně tabulárního modelu SSAS.
Prvně nastínění problému a k čemu se funkce VLOOKUP používá. Jedná se o funkci, která dohledá do Excelové tabulky data z tabulky jiné. Za stejným účelem by si databázista napsal select s JOINem.
Na Excelové listy jsem si vytvořil 2 tabulky, číselník produktů
Produkt kód
Produkt název
Jednotková cena
A
Produkt A
10
B
Produkt B
20
C
Produkt C
30
a prodeje produktů v čase
Datum
Produkt
Kusy
Cena celkem
6/28/2015
A
5
6/29/2015
B
7
6/30/2015
C
5
7/1/2015
A
2
7/2/2015
B
7
7/3/2015
C
5
Potřeboval bych spočítat celkovou cenu prodeje, k tomuto ale budu potřebovat dohledat jednotkovou cenu. Přesně za tímto účelem by Excelový uživatel použil funkci VLOOKUP. Tato má 4 argumenty. Co hledám, kde to chci najít (porovnání probíhá na základě pouze prvního sloupečku), který sloupec chci vrátit a  typ shody (jestli hledám přesnou shodu, případně hledám intervalově). Nevýhody: Pokud nenajde, vrátí chybu, musím porovnávat vždy na základě shody v prvním sloupci a hlavně nad větším objemem dat je tato funkce velmi pomalá.
V našem scénáři by vzorec pro VLOOKUP vypadal následovně.
=VLOOKUP([@Produkt],Table1[#All],3,0)
Řešení v PowerPivot
Výhody řešení v PowerPivot jsou, že vyzbu uděláme jendou, použít můžeme opakovaně. Díky uložení dat v paměti také výrazně rychlejší výkon nad větším objemem dat.
V PowerPivotu nalinkujeme obě tabulky na záložce PowerPivot, add to data model. V diagramovém zobrazení natáhnu produkt kód na produkt a vytvořím tímto relaci mezi tabulkami. Pravím tlačítkem na tabulku prodeje dávám go to a dostávám se do náhledu detailních dat. Vybírám poslední prázdný sloupec a mohu použít funkci
RELATED (co chci vrátit)
Syntaxe je velmi jednoduchá. V DAXech se neodkazujeme na adresy buněk jako na Excelovém listu (A1, B1,...) Ale názvy tabulek ‘tabulka’ a sloupců [sloupec]. DAX výpočet pro dohledání jednotkové ceny by tedy vypadal následovně
=RELATED(PRODUKT[Jednotková cena])
A finální vzorec
=RELATED(PRODUKT[Jednotková cena])*[Kusy]
Datum
Produkt
Kusy
Celkem
6/28/2015
A
5
50
6/29/2015
B
7
140
6/30/2015
C
5
150
7/1/2015
A
2
20
7/2/2015
B
7
140
7/3/2015
C
5
150

Problém 2 – dohledání přes dva sloupce nad větším vzorkem dat
Dále mám dvě tabulky. Jednu s prodeji. Prodeje se konají v různé dny a různých měnách. Celá tabulka má 60 398 řádků
Měna
Datum prodeje
Částka
19
7/1/2005
3578.27
39
7/1/2005
3399.99
100
7/1/2005
3399.99
100
7/1/2005
699.0982
6
7/1/2005
3399.99
100
7/2/2005
3578.27
6
7/2/2005
3578.27
Druhá tabulka obsahuje kurzy pro danou měnu z daného dne. Chtěl bych přepočítat částku podle toho kdy k tomu došlo a v jaké měně. 14 264 řádků v kurzovním lístku

Měna
Datum kurzu
Kurz kurzu
3
7/1/2005
0.99980004
3
7/2/2005
1.000900811
3
7/3/2005
0.99960016
3
7/4/2005
1
V Excelu na listu bych použil pravděpodobně funkci SUMIFS, která umožní sečíst hodnotu ve sloupci na základě několika podmínek (počítám, že pro daný den a měnu bude pouze jeden stav).
Vzorec pro dohledání kurzu by vypadal takto
=sumifs(KURZY!C:C,KURZY!A:A,[@Měna],KURZY!B:B,[@[Datum prodeje]])
Výpočet trval 26 vteřin a to můj notebook není žádné ořezávátko :)
Místo toho bych mohl tabulky nalinkovat do PowerPivot modelu jako v prvním případě a použít funkci LOOKUPVALUE. V tomto případě tabulky ani nemusí být nalinkované
LOOKUPVALUE (co chci vrátit, kde hledám 1, co hledám 1, kde hledám 2, co hledám 2 atd)
Vzoreček by vypadal obdobně jako v případě sumifs
=LOOKUPVALUE(KURZY[Kurz kurzu],KURZY[Měna],[Měna],KURZY[Datum kurzu],[Datum prodeje])
Výsledek byl ale vrácen za 1 vteřinu, což je rozdíl :)
Závěr
V dnešním článku jsme si ukázali, jak nahradit funkci VLOOKUP z Excelu, která je nad většími objemy dat velmi pomalá za použítí funkcí DAX v PowerPivot modelu. Pokud byste chtěli demonstrace i vidět, můžete kouknout na následující doprovodné video ke článku
http://youtu.be/thngBhQxNrM

23. června 2015

Záznam z přednášky Azure Global Bootcamp - Fenomén BigData v Cloudu

25.4.2015 proběhl v Brně Azure Global Bootcamp, kde jsem přednášel na téma Big Data. Zejména, na jaké scénáře danou technologii nepoužívat :) 
Link zde: 
http://www.wug.cz/zaznamy/269-Global-Azure-Bootcamp-2015-Fenomen-Big-Data-v-cloudu

19. června 2015

Master Data Services – přehled a novinky v SQL 2016

Před nějakou dobou jsem psal článek o novinkách, které se chystají v SQL 2016. CTP2 je venku, tak je čas na testování. Nejvíce se sice těším na nové SSRS, ale tyto zatím vyzkoušet nemůžu z důvodu chybějících vývojových nástrojů. Tak si vezmu na mušku technologii Master Data Services (MDS), která nás provází od SQL 2008 R2, přesto ji spousta lidí zatím nevidělo v akci, nebo dokonce nevědí o co jde. To by měl napravit dnešní článek. První část článku věnuji představení MDS, v druhé rozebereme co nového v SQL2016
Představení MDS
MDS jsou technologií na správu Master dat společnosti. Běží jako webová služba hostovaná v IIS (Internet Information Services), databázové úložiště má v SQL Serveru. Ovládat se dá buď přes webové rozhraní, nebo od SQL 2012 přes Excelový doplněk. Dostupná je v edicích Business Intelligence a Enterprise (Což snad neznechutí další čtení lidem se standardní edicí :) ) Snad nikoho také neurazí kreativní chvilka s obrázkem dole.

První otázka zní, co to jsou Master Data (MD). Volně přeložená definice z Wikipedie říká: „Master Data reprezentují objekty byznysu, které jsou schváleny a sdíleny v rámci firmy.“
Na Master Data  databázi by se tedy dalo dívat jako na centrální úložiště dat, které konsoliduje na jedno místo, co víme o dané entitě. Například některé informace o zákazníkovi máme v CRM, něco v účetním systému, něco v objednávkovém systému. MDS by umožnili mít jednu vyčištěnou verzi všech dostupných informací co víme o zákazníkovi. Mohl by fungovat i automatický sběr ze systemů do MDS a distribuce zpátky do zdrojů, pokud se něco změní.
MDS můžou také sloužit jako zdrojový systém pro data, která v žádném zdrojovém systému nemáte. Například jakým způsobem se chcete dívat na Vaše produkty (např. pro účely reportingu), organizační strukturu ve firmě, obecně mapování. V čem je MDS technologie výjimečná a jiná od datového skladu. Tvořit mapování „jak chcete reportovat produkty“ v datovém skladu by bylo odpovědností BI vývojáře. Ten o těchto věcech ale zpravidla nerozhoduje. Tuto úlohu má pod palcem nějaký uživatel, ze strany byznysu. Neajťák (v literatuře se můžeme setkat s rolí Data Steward). Tito mají tendence psát věci do Excelu. Ajťák obráceně zpozorní v situaci, kdy něco tak kritického, jako Master Data společnosti se válí v Excelu ideálně na Flash disku onoho klíčového uživatele :)
MDS uspokojí obě strany, protože kombinují to nejlepší z obou světů. Díky Excelovému doplňku může Data Steward Master Data spravovat. Ajťák je spokojený, protože data jsou na serveru, kde se pravidelně zálohují a jsou k nim řízeny přístupy. Oba spokojeni a proto zde máme dva usměvavé smajlíky na prvním obrázku :)
MDS jsou technologie, která umužňuje řešit i všechny nezbytné procesy okolo správy Master Dat. Patří sem: Zadávání dat, následné úpravy, sledování změn (kdo to spáchal, kdy to spáchal), verzování, vynucování business rules, vracení transakcí, tvorba hierarchií, řízení přístupů k daným zdrojům, možnost dávkově nahrát data a vytáhnout je ze subscription views.
Základní pojmy MDS
Model – si můžete představit jako kontejner na data. Je to něco podobného jako databáze v rámci SQL Serveru (byť fyzická databáze je jen jedna). Model je kontejnerem na entity
Entita – objekt byznysu, pro nějž chceme spravovat MD. V SQL Serveru by se entita dala přirovnat k tabulce. Každá entita má atributy a tyto mezi sebou můžou tvořit hierarchie. I Entity můžou tvořit hierarchie. (Entita kategorie je nadřízená entitě Produkt)
Atribut – popisná vlastnost entity, čili sloupec. Každý antribut může obsahovat sadu memberů
Member – jeden člen atributu v entitě. Například v entitě produkt, v atributu název školení „GOC342“
Použití MDS
Jaká je tedy logická posloupnost a ovládání MDS. V centrální správě založíte model. Mohli byste tvořit i entity přes webové rozhraní, ale přes Excelový doplněk je to jednodušší. Stačí vyplnit tabulku v Excelu včetně hlavičky a naplnit ji daty a vytvořit po připojení na MDS novou entitu, vybrat klíčový sloupec a volitelně sloupec se jménem. Tímto vznikne nová enita. I kdybychom Excel zavřeli a otevřeli nový. Jsme schopni data pro danou entitu načíst a editovat.
Pokud bychom chtěli k datům přistupovat přes SQL, v databázi se tabulky jmenují strašidelným automaticky generovaným názvem mdm.tbl.... Pokud bychom chtěli k datům přistupovat pohodlněji. Je potřeba vytvořit v centrální správě „subscription view“. Mimo to, že se můžeme dotazovat do view s normálním názvem (pokud jsme jej normálně pojmenovali), nalezneme zde i sledování kdo záznam vložil a kdy, kdo jej  naposledy změnil a kdy, verze atd.
Jedno video lepší než tisíc slov, takže pokud chcete vidět MDS v akci koukněte na video na konci článku. Prvně se ale podívejme pro ty, kteří již MDS viděli na...
Co nového ve verzi 2016
Zvýšení výkonu
Pokud jste v minulých verzích chtěli zadat desetitisíce záznamů přes Excel, bylo to minimálně na jedno kafe, když ne rovnou obě. Excelový doplněk byl přepsán a umožňuje spravovat větší entity. Další výkonové zlepšen můžete dosáhnout díky datové kompresi, což snižuje náročnost na čtení z disku. Je také defaultně zapnutá Dynamic Content Compressions v IIS, což zrychluje xml response. Oba typy komprese se doporučují vypnout v případě, že máte problém s vytížeností procesoru. Tomuto komprese nikterak nepřidá. Do agenta přibyly dva nové joby, pro správu indexů a logu MDS. Údržba MDS indexů běží defaultně týdně.
Rozšířené možnosti pro zabezpečení
Super user funkce dává stejné možnosti jako server admin v minulých verzích. Problém v předchozích verzích byl, že admin mohl být pouze jeden. Tato potíž odpadá. Navíc práva lze rozdělit na samostatný read, create, update a delete. Předchozí verze měla pouze read, update (mohl vkládat, mazat, přepisovat).
Správa transakčního logu
transakční log se dá čistit v definovaných intervalech, nebo na základě harmonogramu. Lze definovat jak dlouho mají MDS ukládat transakce, problémy s validací a jak dlouho si pamatovat dávky.
Závěr slovem
Master Data Services jsem si za poslední roky velmi oblíbil zejmena za účely mapovacích tabulek, případně jako zdrojový systém dat, pro které není lepší zdroj. Adaptace uživateli probíhala také vcelku příjemně. Extra se mi osvědčila kombinace uživatel zadá data (ruční vstupy, změny mapování) přes MDS a v PowerPivotu zvaliduje data. Není spokojen, může dál upravovat, je spokojen druhý den se ETL procesem propasuje do tradičních BI struktur Nová verze použitelnost MDS ještě zvyšuje.
Video na závěr
jak vypadá nové uživatelské rozhraní MDS a jak se technologie používá...


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.