29. listopadu 2015

Power BI - Zkušenost z praktického nasazení

Tento pracovní týden byl dlouhý, tak si nejsem jistý zda bude dlouhý i tento blog. Za měsíc tomu bude rok, co Power BI nové generace spatřilo světlo světa. Za krátkou dobu ušlo opravdu velký kus cesty. Často se setkávám s otázkami a názory, zda se jedná o produkčně použitelnou technologii v korporátním prostředí. V Dixonu jsme použili Power BI pro reporting v době největší roční špičky tzv. Black Friday. S čím byly problémy? To jsou zkušenosti, o které bych se rád podělil v následujícím blogu.
Požadavky
Zpřístupnit klíčovým uživatelům informace o základních ukazatelích aktuální rok, budget, předchozí rok, srovnání. Přístup z PC, mobilu. Aktualizace dat každou hodinu + email se shrnutím základních informací. Všichni uživatelé, kteří budou mít k reportu přístup uvidí stejná data.
Po přečtení těchto požadavků, bylo jasné, že jedna technologie stačit nebude. Pro posílání emailem jediná volba Reporting Services. Ty to ale zatím úplně neumí na mobilních platformách, Datazen a Power BI zase neumí posílat reporty emailem.
Pro moblní nasazení bylo možno zvolit mezi Datazenem a Power BI. Na repoty bylo potřeba koukat odkudkoliv i bez VPN a protože s Datazenem zatím nemám mnoho zkušeností, sáhli jsme po Power BI. Pěkné rozhraní přes Web, mobilní aplikace dostupná na všech mobilních platformách (Windows jen na tabletech). Většina lidí z managementu má iOS, Android, sem tam Blackberry (Blackberry = smůla, bude muset stačit email)
Technické řešení
Tvorba multidimenzionální OLAP kostky nad datovým skladem pro účely Black Friday reportingu. Pro online a mobilní reporting Power BI, pro doručení mailem SSRS. Na SSAS serveru s kostkou nainstalována Power BI Personal Geteway k propojení On premises zdrojů s Cloudem. Aktualizace kostky probíhala po hodině. V Power BI desktopu jsem vytvořil logický model nad MDX dotazy z OLAP kostky. Chybějící výpočty jsem dodělával v DAX a věnoval se vizualizaci.
S čím byly problémy
Tvorba reportu
začal jsem s reportem ještě v době, kdy nešlo kopírovat celé stránky. Tento problém díky Listopadové aktualizaci zmizel. Přetrvávaly drobné problémy při detailnějším formátování některých vizualizací. To že chybí u vybraných vizualizací možnost změnit velikost písma zamrzí. Potom se člověk trápí s pozicováním, aby to nějak vypadalo a to písmo nevypadalo neúměrně velké k dalším objektům a hlavně aby se i ostatní objekty pěkně vešly.
Publikace do webu PowerBI.com
Reporty v SSRS se nedají sdílet, sdílet se dají pouze Dasboardy (v české lokalizaci Řídící panely). To co vystavíte v Dashboardu je také to, co je dostupné přes mobilní aplikaci. Zde mě docela vytrápilo, že jsem přidal několik grafů do dashboardu, vyhrál si s pozicováním a poté změnil obsah jednoho grafu. Jednalo se o zásahy ve stylu nahrazení měřítka „suma za celý den“ za „kumulace od začátku dne po aktuální hodinu (loňský rok)“. Po opětovné publikaci reportu do Power BI portálu zůstal tento graf rozhozen (something went wrong, click here to fix it). Několikrát to dospělo k tomu, že jsem vymazal celý obsah dashboardu a přišpendlil všechny objekty zpátky. Což člověka nebaví už napodruhé, natož napočtvrté. Hodilo by se mít možnost přišpendlit na Dashboard několik položek zároveň a hlavně aby se metadata „nerozbíjely“.
Správa
Po nasazení do cloudu mi chyběla jednoduchá možnost, přesměrovat z vývojové kostky na kostku produkční, jako je možno například v Reporting Services díky sdíleným datovým zdrojům.
Po nasazení reportu a přišpendlení vizualizací na dashboard můžeme sdílet. Sdílení funguje na jednotlivé emailové adresy, které musíte manuálně vkládat. Při 30ti adresách to už není nic moc, snad MS přijde s nějakým jiným, lepším způsobem řízení práv. Namátkou mě napadá integrace s AD, nastavení práv pro AD skupiny, nebo import seznamu uživatelů ze souboru.
Při spravování Dashboardu mi chyběla možnost učinit jiného uživatele administrátorem mého dashboardu/reportu/datasetu. U dashboardu je sice možno povolit uživatelům, aby sdíleli dashboard dál, ale pokud tohle učiníte, už se v seznamu práv nedá prakticky určit, zda jste uživateli v seznamu nasdíleli obsah vy přímo, nebo někdo, komu jste dashboard nasdíleli Vy.
Z Dashboardů, reportů a datasetů se dá udělat tzv. content pack, který pošlete dalším lidem. Když takhle někomu obsah nasdílíte, může jej upravovat dál. U datasetů vás ale bohužel vyzve k uložení kopie. Pokud máte dataset pouze nasdílený, nemůžete jej aktualizovat a jste odkázání na scheduler, nebo sdílejícího uživatele. Když uděláte kopii hrajete si na svém vlastním písečku.
Možnost, aby kdokoliv ze 3 adminů dataset aktualizoval chyběla velmi. Dopadlo to tak, že jsme se museli hlásit pod jedním účtem.
Scheduler / automatická aktualizace
Největší tragédií celého snažení byly aktualizace dat. V Power BI je možno fungovat buď v režimu koukám na živá data (přes SSAS konektor) nebo data aktualizuji a načítám je lokálně do modelu (Přes Personal Gateway) více informací ve článku o konektorech http://www.neoral.cz/2015/10/power-bi-konektory-na-on-premise-zdroje.html. Protože jsme používali data z multidimenzionální OLAP kostky (kde je živé připojení zatím ve fázi Preview a funguje pouze na Desktopu), museli jsme data aktualizovat. Časovač Vám umožní v rozbalovacím menu vybrat buď aktualizaci každý den, nebo týden. Při výběru dne můžete vybrat několik časů v průběu dne. Pouze ale v celou hodinu nebo o půl. Vyklikávám takhle časy vyberu osmý a STOP... Nemůžu dál. To si děláte legraci. Nejen že tu musím vyklikávat hodinovou aktualizaci výběrem 8mi časů, ale ani mě nepustíte za těch 8? Nu co už nějak to přežijeme, prostřídáme se a přes ten Peak to nastavíme vždy na 8 hodin dopředu a pak zase. Naštěstí jde nastavit notifikace, kdyby se aktualizace neprovedla. Taková byla teorie. Nebude to příjemné, ale přežijeme to. V průběhu testování jsme však zjistili, že přestože je aktualizace nastavená na půl. Jednou se spustila ve 30, jednou ve 32, jednou ve 40, jednou v 59 a několikrát vůbec... A měla tu drzost, že ani neposlala email (možná aktualizace neproběhla proto, že krátkou dobu před ní byla zavolána ruční aktualizace). Nakonec jsme to dopracovali do stavu, že jsme aktualizace prováděli ručně v době kdy bylo potřeba. Do budoucna by to chtělo možnost udělat šikovnou návaznost mezi zpracováním dat On Premises a aktualizací Power BI v cloudu, ale hlavně spolehlivý časovač s lepšími možnostmi volby frekvence aktualizace dat. U denních aktualizací by to mohlo být více méně jedno, ale pokud se snažíte o systém, který má zpoždění max hodinu a řekli jste uživatelům po půl to bude, jsou tyhle výkyvy hodně cítit.
S čím problémy nebyly
Zbytek :) Reporty se tvořily jednoduše, DAXy fungovaly jak měly. Power BI Personal Gateway aktualizovala data, když to člověk zavolal ručně. S mobilními aplikacemi také probém nebyl. Reporty se líbily :)
Závěr

Power BI je použitelné i v produkčním prostředí. Má však  poměrně velké rezervy zejména v oblasti správy. Nejvíce nás vytrápil nespolehlivý scheduler aktualizací. Něco jiného je ale když potřebujete aktualizovat data pouze denně, tam bych obavy neměl. Zapracovat by bylo také potřeba na security modelu. I přes tyto problémy co jsme měli mám Power BI velmi rád a věřím, zítra dám produktovému týmu zpětnou vazbu a věřím, abychom za další rok všichni měli ještě lepší Power BI :)  

22. listopadu 2015

Power BI novinky – Říjen, Listopad 2015

Není tajemstvím, že Power BI team jede šíleným tempem. Jednou měsíčně update pro Power BI Desktop, každý týden service update a mobilní aplikace taky nespí.
Novinek se nastřádalo docela hodně, tak bych rád vypíchnul ty nejzajímavější. Soustředit se budu hlavně na PowerBI desktop novinky. Je zde totiž vidět trend, nejdřív je funkce přidána v rámci týdenního service update a pak se objeví i v měsíčním updatu PowerBI Desktopu. Listopadový update mi přijde stěžejní, ale pěkně popořadě.
 Říjen
Vlastní vizualizace
Za jednu z nejzajímavějších říjnových novinek považuji vlastní vizualizace (Custom Visuals) o kterých jsem psal podrobně zde http://www.neoral.cz/2015/10/power-bi-vlastni-vizualizace.html. Po měsíci používání přetrvávají dvě drobné vady. Ta méně bolestivá je, že se zatím nedají zobrazovat na mobilu. Ta bolestivější je, že je při každém kliknutí na report musíte pořád dokola povolovat. A to protože „můžou obsahovat nebezpečný obsah.“ Což je stejně otravné jako aktualizace ve Windows 10.
Tvorba reportů
Přibylo tlačítko vložit vizualizaci z pásu karet. Jedná se ale o prázdnou vizualizaci, typ musíte vybrat později sami.
Vylepšené výchozí řazení. Pokud budete používat například sloupcový graf, bude se řadit vzestupně podle toho co dáte na osu kategorií, dáte-li řadit podle měřítka, setřídí se automaticky sestupně.
Pokud pracujete s Funnel grafy, dočkali jsme se bublinové nápovědy a popisků dat. Přibyla možnost řazení v průřezech (Slicerech), stejně jako možnost měnit velikost písma.
Budíky byly rozšířeny o možnost nastavit Min, Max a cílovou hodnotu.
Bylo vylepšeno pozicování popisků dat tak, aby se popisky nepřekrývaly. V Power BI Desktopu je také možno zapnout funkce, které jsou zatím ve fázi „Preview“
Zde najdete možnost využití KPI indikátorů z tabulárního modelu SSAS, potažmo PowerPivotu. Jedná se pouze o použití existujícího KPI, nikoliv o nástroj, kde je KPI možno tvořit.
Nové konektory na datové zdroje
Přibyly konektory pro Azure DocumentDB, Mailchimp
DirectQuery pro SQL Server/Azure SQL Database
Přibyla možnost dotazovat se do SQL Serveru a Azure SQL databáze bez duplikování dat v modelu formou přímého dotazu.
Listopad
Listopadová aktualizace PowerBI desktopu mě nadchla hned několika novinkami.
Datová konektivita
SSAS Multidimenzional – Živé připojení
Už nějakou dobu bylo možno koukat živými připojením do Tabulárního modelu SSAS, v Říjnu přibylo připojení do SQL Databáze a v Listopadu můžeme konečně koukat živým připojením do SSAS Multidimenzionálních kostek. Třikrát sláva. Vyslechl jsem si nejeden dotaz, zda Microsoft s Multidimenzionálními kostkami do budoucna nepočítá. Evidentně počítá a to je dobře. Funkce je zatím dostupná pouze v Power BI Desktopu jako „Preview“. Než bude možno koukat online z Cloudu do On Premises SSAS Multidimenzionální databáze si ještě chvíli počkáme. Pravděpodobně kvůli nutnosti aktualizovat SSAS konektor o kterém jsem psal zde http://www.neoral.cz/2015/10/power-bi-konektory-na-on-premise-zdroje.html.
Co se zdrojů týče, je možno pracovat s R Skripty (po akvizici Revolution Analytics bude R integrováno do SQL Serveru – CTP 3.0 už tuto funkčnost obsahuje). Nyní je možno R  využívat i v rámci Power BI. Direct Query dotaz do SQL Server/Azure SQL Db/Azure SQL Data Warehouse byl povýšen z Preview funkce na produkční. Další konektory jsou na Azure Data Lake Store a Marketo a v neposlední řadě na SAP Hana.
Tvorba reportů
Osa Play u XY grafů
Tato funkce byla dostupná v PowerView, závislost dvou veličin na sobě se dala též přehrát v čase.
Slicery (Průřezy)
Slicery je možno dělat horizontální, zapínat zda je Slicer ve výchozím chování určen pro výběr jedné položky, nebo více.
Interakce mezi vizualizacemi
Zcela zásadní věc. Pokud máte alespoň dvě vizualizace na plátně, můžete vybrat jak se mají chovat ve vztahu k ostatním. Mám například koláčový graf a sloupcový graf. Když kliknu na výseč koláče, může to v sloupcovém grafu buď zvýraznit podíl mého výběru (v minulosti výchozí a jediné chování), nebo to nemusí udělat nic (grafy jsou nezávislé) nebo se dá koláčový graf přeponout na filtr. Po kliknutí na koláč se zobrazí jen relevantní sloupce a velikost je omezena právě výběrem z koláče.
Kopírování stránek
Možnost zkopírovat stránku jako celek mi hodně chyběla, takže děkuji :)
Podpora obrázků a KPI
S těmito objekty je možno pracovat v Tabulkách, maticích a kartách. KPI se dají vytáhnout z SSAS/PowerPivot, obrázky se dají volat například přes URL
Možnost měnit velikost písma
V kartách, tabulkách a maticích. Zdánlivě něco, co zde mělo být již dlouhou dobu. Nebylo ale už je možno stejně jako možnost kontrolovat jednotky v popiscích os u grafů. Opět děkuji :)
Datové modelování
Při použití sloupců typu datum se dát datumy automaticky seskupovat do roků, kvartálů a tyto jdou poté drilovat v grafech.
A další novinky...
Říjen – kompletní seznam
Listopad  – kompletní seznam
Mobilní aplikace
Zde stojí za zmínku, že PowerBI mobilní aplikace se dá integrovat s Windows Intune, takže se dá ze strany společnosti lépe vynutit, kontrola nad přístupem k datům přes mobil. Patří sem například
·        Požadavek na PIN pro přístup do BI aplikace
·        Požadavek aby zařízení splňovalo compliance omezení
·        Omezení kopírování dat z aplikace, nebo pořizování screenshotů
Excel 2016
V Excelu 2016 přibyla možnost publikovat soubor do Power BI. Po publikaci můžete dále pracovat s Excelem a změny se budou promítat v Power BI podle natavení aktualizací dat. Aby Power BI služba viděla na Excelový dokument (publikace), je vyžadován OneDrive for Business, kde je soubor fyzicky uložen. Podrobnosti zde http://blogs.msdn.com/b/powerbi/archive/2015/09/24/publish-to-power-bi-from-excel-16.aspx
Komunita
Ve světě vznikají Power BI user Groups, nejbližší je v Rakousku. Uživatelé PowerBI se mezi sebou můžou scházet a vyměňovat si informace, zkušenosti, atd. http://blogs.msdn.com/b/powerbi/archive/2015/11/03/power-bi-user-groups-are-here.aspx?wt.mc_id=WW_CE_BI_OO_SCL_TW Meli byste zájem o Power BI user group v České republice?
Závěr

Tempo Power BI teamu je vražedné, generují funkce rychleji, než stíhám testovat :). Každopádně mě těší, že se rozšiřuje základna zdrojů s živým připojením včetně multidimenzionálních modelů SSAS a SQL Server databází. Celkově se zvyšuje ovladatelnost, kontrola nad vizualizacemi ať už se jedná o kosmetické záležitosti jako velikost písma, nebo se jedná o to které vizualizace budu ovládat při kliknutí na graf. Skvělá práce, jen tak dál :)

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.

8. listopadu 2015

MDX Tutorial 9. – Navigace v hierarchii 2. část

V minulém dílu tutorialu jsme se začali věnovat navigačním funkcím (práce s přímým příbuzenstvem v hierarchii http://www.neoral.cz/2015/10/mdx-tutorial-8-navigace-v-hierarchii-1.html). Dnes navážeme funkcemi pro přístup k příbuzným vzdálenějším. Začneme obrázkem, kde se vše točí kolem current membera
Členové směrem nahoru nad currentmemberem jsou ancestors, linie potomstva descendants, prvek na stejné úrovni pod jiným parentem Cousin. Přehledná tabulka s výčtem funkcí:
Funkce
Popis (návratová hodnota member/set)
Ancestor(Member, Level | Distance)
Člen z úrovně / vzdálenosti nad current (member)
Ancestors(Member, Level | Distance)
Člen z úrovně/vzdálenosti nad current (set 1 prvek)
Ascendants(Member)
Linie nad memberem(set)
Descendants(member|set,level|distance, [flag])
Linie položky pod memberem z úrovně/hloubky (set)
Cousin(Member1, Member2)
položka se stejným pořadovým číslem pod jiným parentem (member)
Následující dotaz ná vrátí časovou hierarchii na úrovni měsíců a počítaný člen který vrátí jméno currentmembera
WITH MEMBER x AS
       [Dim Date].[YQMD].currentmember.name
SELECT
       {x}
ON COLUMNS,
       {[Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]}
ON ROWS
FROM [MDX Tutorial]
Ancestor/Ancestors
Funkce Ancestor vrátí člena z linie nahoru jako member. Ancestors vrátí jednoprvkovou množinu z linie nahoru. Rozdíl mezi nimi je, že Ancestor můžete použít v tuplu, Ancestors v rámci agregační funkce.
WITH MEMBER [current] AS
       [Dim Date].[YQMD].currentmember.name
MEMBER [Ancestor] AS
       Ancestor(
       [Dim Date].[YQMD].currentmember
       ,[Dim Date].[YQMD].[Year])
       .name
MEMBER [Ancestors] AS
       SetToStr(
       Ancestors(
       [Dim Date].[YQMD].currentmember
       ,[Dim Date].[YQMD].[Year])
       )
SELECT
       {[current]
       ,[Ancestor]
       ,[Ancestors]}
ON COLUMNS,
       {[Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]}
ON ROWS
FROM [MDX Tutorial]

Jaké by mohlo být použití těchto funkcí? Například výpočet poměr aktuálního prvku versus nadřízený prvek v hierarchii o několik úrovní, potažmo na konkrétní úrovni.
Ascendants
Vrátí celou linii směrem nahoru. Ideální funkce například na zobrazení linie nad zaměstnancem v rámci organizační struktury. Následující dotaz vrátí celou linii nad Amy Alberts v databázi Adventure Works
SELECT
       {}
ON COLUMNS,
ascendants(
       [Dim Employee].[Parent Employee Key].&[290]
       )
ON ROWS
FROM [MDX Tutorial]

Cousin
Funkce Cousin vrátí prvek, který má stejné pořadové číslo jako aktuální člen, ale pod jiným parentem. Použitelná by tato funkce mohla být například pro některé časové scénáře. To by šlo ale i jednodušeji časovými funkcemi (práci s časem se budeme věnovat samostatně v dalším díle)
WITH MEMBER x AS
       COUSIN( [Dim Date].[YQMD].currentmember
       , [Dim Date].[YQMD].currentmember.parent.prevmember )
       .uniquename
SELECT
       {x}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Quarter]
ON ROWS
FROM [MDX Tutorial]

Descendants
Bez této funkce bych pravděpodobně nemohl fungovat. Vrátí množinu prvků z úrovně, nebo vzdálenosti pod vybraným členem. Má dvě varianty. Buď jako druhý argument definujeme level, nebo vzdálenost. Třetí argument flag je volitelný.
Začnu variantou s levelem.
DESCENDANTS( «Member»[, «Level»[, «Desc_flags»]] )
Ta se hodí zejména pokud se potřebujete dostat na libovolnou úroveň bez ohledu na to, kde se v hierarchii momentálně nacházíte. Nejčastěji tento scénář používám při práci s časem, když se chci dostat na úroveň datumu (aplikace v článku o semiaditivních měřítkách http://www.neoral.cz/2015/05/semi-aditivni-meritka-v-ssas-pomoci-mdx.html)
Zde pro přehlednost jen jednoduchá syntaxe
WITH MEMBER x AS
settostr(
       DESCENDANTS(
       [Dim Date].[YQMD].currentmember
       ,[Dim Date].[YQMD].[Date]
       )
       )
SELECT
       {x}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       [Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]

Variantu se “hloubkou”
DESCENDANTS( «Member»,«Distance»[, «Desc_flags»] )
používám často v SSRS grafech, kde chci mít možnost vybírat na jaký detail má být graf zobrazen. Následující dotaz zobrazí data na úroveň měsíce, pokud bychom místo 3 napsali 2, bylo by to na kvartál, 1 na rok...
SELECT
       {}
ON COLUMNS,
       [Dim Date].[Year].[Year]*
       DESCENDANTS(
       [Dim Date].[YQMD].[All]
       ,3 /*zde by byl místo konstanty parametr*/
       )
ON ROWS
FROM [MDX Tutorial]

Variantu s v Flagy používám hlavně u parentchild hierarchií, flag self_and_after umožní vrátit úroveň kterou vybereme a všechny úrovně pod. Linii pod Welckerem Brianem v AdventureWorks bych získal
SELECT
       {}
ON COLUMNS,
       descendants(
       [Dim Employee].[Parent Employee Key].&[277]
       ,0
       ,self_and_after)
ON ROWS
FROM [MDX Tutorial]

Abych nemusel psát extra kapitolu kvůli navigaci v rámci stejné úrovně vpřed vzad, přidám ještě tyto navigační funkce
Funkce
Funkce
Popis (návratová hodnota member/set)
member.prevmember
Předchozí člen (member)
member.nextmember
Následující člen (member)
member.lag(N)
předchozí člen o N prvků (member)
member.lead(N)
následující člen o N prvků (member)
WITH MEMBER [current] AS
       [Dim Date].[YQMD].currentmember.name
MEMBER [prevmember] AS
       [Dim Date].[YQMD].currentmember.prevmember.name
MEMBER [nextmember] AS
       [Dim Date].[YQMD].currentmember.nextmember.name
MEMBER [lag] AS
       [Dim Date].[YQMD].currentmember.lag(6).name
MEMBER [lead] AS
       [Dim Date].[YQMD].currentmember.lead(6).name
SELECT
       {[current],[prevmember],[nextmember],[lag],[lead]}
ON COLUMNS,
       [Dim Date].[YQMD].[Date].&[2005-01-27T00:00:00]
ON ROWS
FROM [MDX Tutorial]

Závěr:

Dnes to bylo poměrně hodně funkcí a pojal jsem to spíš syntakticky, než každou funkci s podrobnějším příkladem. Více jsem rozebral funkci Descendants, bez které si aplikované MDX nedokážu dost dobře představit. Tímto končí kapitola o navigačních funkcích, příště se budu věnovat práci s časem. Pokud bude čas zajisté :)