14. prosince 2015

MDX Tutorial 12 - Časové kalkulace jinak a téměř bez práce

V posledních 2 dílech jsme se bavili jednak o časových kalkulacích (http://www.neoral.cz/2015/11/mdx-tutorial-10-casove-funkce.html) a o příkazu Scope (http://www.neoral.cz/2015/12/mdx-tutorial-11-prikaz-scope.html). Jedná se už o pokročilejší témata, ke kterým se možná ne všichni odhodlaní čtenáři tutorialu dostanou. Nicméně časové kalkulace se hodí všem, většinou i pro všechna měřítka.
Dnešní díl se dá v základu z tutorialu vytrhnout, není na něm přímo závislý. Byť pochopení principů z dílů předchozích je určitě velkým plusem. Chcete časové kalkulace jako je třeba meziroční srovnání, případně kumulovaná suma od začátku roku (YTD) a nechce se Vám ani hnout prstem? Pak jste tu špatně, budete muset párkrát kliknout myší (ale o tolik víc toho zase dělat pro základní variantu nebudete muset)
Výhody přístupu
Bude se jednat o výpočty uložené v dimenzi raději než v Measures. Pokud bysme chtěli následující výpočty: Meziroční srovnání, Meziroční srovnání v %, Meziroční srovnání 2 roky zpátky, Meziroční srovnání 2 roky zpátky v procentech, kumulovaná suma od začátku roku (YTD), srovnání kumulací YTD meziročně, srovnání kumulací YTD meziročně v procentech... pro měřítko Částka bez DPH a ukládali bychom výpočty do Measures, museli bychom napsat 7 počítaných členů. Pokud bychom to stejné chtěli pro druhé měřítko Částka s DPH máme dalších 7 a jsme na 14ti počítaných členech. Daň, dalších 7 a už máme 21 počítaných členů, atd... Počet počítaných členů exponenciálně roste a tím se snižuje přehlednost kostky a je v takové kostce potom problém něco najít. Nabízí se tedy napsat, nebo naklikat univerzální počítaný člen a uložit jej v dimenzi tak, aby se definice nemnožily.
Z pohledu uživatele může být drobnou nevýhodou, že musí logicky přeponout do režimu, měřítko není mezi Measures, ale jako atribut dimenze. Po nějaké době si ale většinou zvyknou, Ti kteří si nezvyknou a nedomluví se s kontingenční tabulkou, těm raději nachystejte report v SSRS, PowerBI, nebo jiné reportovací technologii.
Prerekvizity
Musíte mít ve Vaší SSAS databázi a relevantní kostce „Časovou dimenzi“ s označenými úrovněmi jako atributy přes které chcete počítat. (Chcete-li dělat meziroční srovnání, budete potřebovat označit atribut rok jako „Year“, pokud budete chtít počítat WTD, budete potřebovat level týden, atd.
Tuto prerekvizitu nejsnáze splníte nastavením přes průvodce „Add business intelligence“
Čili na dimenzi Dim Date v našem projektu pravé tlačítko-Add business intelligence-Define dimension intelligence-dimension type „Time“, nastavit atributy, finish.
Základní provedení – Level 1
Generování přes průvodce. K časovým kalkulacím se dá dojít velmi jednoduše přes průvodce, proto skoro bez práce. Kliknete pravým tlačítkem na kostku, kam chcete časové kalkulace přidat, add business intelligence, define time intelligence (next), vyberte víceúrovňovou hierarchii na jehož currentmembera časová dimenze má reagovat a vyberte zatržítky požadované výpočty (next). V dalším kroku vyberte první měřítko (aby to fungovalo na vše posléze pozměníme script) (next). Souhrnná stránka, finish.
Co se stalo?
Do data source view průvodce přidal sloupec (YQMD Dim date calculations – spustil jsem na hierarchii YQMD) do tabulky s kalendářem s konstantou představující aktuální období (current dim date). Z tohoto sloupce do dimenze Dim Date vytvořil stejnojmennou hierarchii, pro kterou zakázal agregaci (IsAggregatable = false, přece jenom nemá smysl sumovat meziroční srovnání v procentech s YTD...). To že se má ve výchozím chování zobrazovat aktuální období zajišťuje default member který bere „Current dim date“ jako default. Mimo to se nám v kostce vygeneroval calculate script.
Průzkum přes Excel
Když se do kostky připojíme z Excelu můžeme vybrat Internet Sales jako measure, na řádky dát YQMD hierarchii a na sloupce YQMD Dim Date Calculations viz obrázek
Tam kde výpočet nedává smysl, člen píše NA. Pro vybrané měřítko počítá, bylo první v pořadí. Pro všechna jiná měřítka by se zobrazovalo NA všude.
Věci které se mi na první pohled nelíbí a chtěl bych je změnit? V prvé řadě „NA“, když výpočet nedává smysl.rRaději bych vracel prázdnou buňku. Chtěl bych také, aby výpočet fungoval pro všechny measures a to nikoliv pro statický seznam, ale i pro měřítka, která přidám časem respektive aby mi to nezačalo padat při nasazení, když měřítko odeberu. Zbývá tedy sáhnout do scriptu. Pro zjednodušení nechám ve scriptu v článku jen YTD, script vypadá následovně
CALCULATE;

/*

 Begin Time Intelligence script for the [Dim Date].[YQMD] hierarchy.

*/

Create Member
 CurrentCube.[Dim Date].[YQMD Dim Date Calculations].[Year to Date]
 As "NA" ;
 
Scope(
      {
        [Measures].[Internet Sales]
      }
) ;

/*Year to Date*/
 (
   [Dim Date].[YQMD Dim Date Calculations].[Year to Date],
   [Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
 )
 
 =

 Aggregate(
            { [Dim Date].[YQMD Dim Date Calculations].[Current Dim Date] }
            *
            PeriodsToDate(
                           [Dim Date].[YQMD].[Year],
                           [Dim Date].[YQMD].CurrentMember
            )
 ) ;
End Scope ;
Najdeme zde příkaz CALCULATE; který počítá hodnoty klíčových buněk, měl by v kostce zůstat první a doporučuje se jej nechat jak je pokud vyloženě nevíte co děláte.
Následuje důvěrně známý příkaz Scope z minulého dílu tutorialu. Který říká, měním hodnoty buněk pro měřítko Internet Sales. Dále definice počítaného člena a naplnění konstantou „NA“, když jsou splněné podmínky, přepis hodnoty v buňce meziročním srovnáním. Zde stačí dvě drobné úpravy, aby script fungoval lépe. Pro fungování pro všechny měřítka nahradíme Measures.[Internet Sales] za Measures.Members (pro všechny zhmotněná měřítka), případně Measures.Allmembers pro všechny měřítka včetně počítaných. Eventuelně můžete použít libovolný set měřítek.
Upravený script vypadá následovně
CALCULATE;
Create Member
 CurrentCube.[Dim Date].[YQMD Dim Date Calculations].[Year to Date]
 As NULL ;
 
Scope(
      {
        [Measures].allmembers
      }
) ;

/*Year to Date*/
 (
   [Dim Date].[YQMD Dim Date Calculations].[Year to Date],
   [Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
 )
 
 =

 Aggregate(
            { [Dim Date].[YQMD Dim Date Calculations].[Current Dim Date] }
            *
            PeriodsToDate(
                           [Dim Date].[YQMD].[Year],
                           [Dim Date].[YQMD].CurrentMember
            )
 ) ;
End Scope ;
Level 2 vytunění, přesun do samostatné dimenze
Takhle to víceméně funguje (u některých výpočtů je potřeba poladit aby nedocházelo k dělení nulou, ale to jsou malé věci pokud se ve skriptu vyznáte). Jediný problém je pro koncového uživatele je ony výpočty najít. Přece jen pod časovou dimenzí jsou trochu zakopané.
Posloupnost kroků nemusíte provést stoprocentně ve stejném pořadí a než se do podobných akcí pustíte s produkční kostkou, doporučuji zálohu projektu :)
Nejprve vytvoříme novou dimenzi, do které výpočty přesuneme. Pravé tlačítko na dimensions, new dimension, use existing table (next), vyberte jako zdrojovou dimenzi dimdate, jako klíčový atribut YQMD dim date calculations, name column můžete nechat prázdný jako na obrázku
V dalším kroku přejmenujte atribut na například Časové kalkulace pokud se bude jednat o českou kostku, v dalším kroku přejmenujte dimenzi například také Časové kalkulace. Finish.
Poté přidejte dimenzi do kostky, definice kostky cube structure, pravé tlačítko na dimensions add Cube dimension – Časové kalkulace. Vůbec se nemusíme trápit s vazbami na záložce dimension usage. Upravíme návaznosti v calculate scriptu. Stačí nahradit výskyt původního [Dim Date].[YQMD Dim Date Calculations] za [Časové kalkulace].[Časové kalkulace].
Přepneme se do Data source view, najdeme definici atributu a původní text Current Dim Date nahradíme například za Aktuální období. V Calculate scriptu všude kde se vyskytovalo Current Dim Date nahradíme stejným textem.
Původní atribut v dimenzi Dim Date můžeme smazat z definice kostky. Nasadíme projekt, zprocesujeme. Otevřeme novou dimenzi Časové kalkulace, vybereme klíčový atribut, nastavíme default member, zakážeme agregaci. Nasadíme.
Hotovo
Výsledný calculate zjednodušený script vypadá následovně
CALCULATE;

/*

 Begin Time Intelligence script for the [Dim Date].[YQMD] hierarchy.

*/

Create Member
 CurrentCube.[Časové kalkulace].[Časové kalkulace].[Year to Date]
 As NULL ;
 
Scope(
      {
        [Measures].allmembers
      }
) ;

/*Year to Date*/
 (
   [Časové kalkulace].[Časové kalkulace].[Year to Date],
   [Dim Date].[Year].[Year].Members,
[Dim Date].[Date Key].Members
 )
 
 =

 Aggregate(
            { [Časové kalkulace].[Časové kalkulace].[Aktuální období] }
            *
            PeriodsToDate(
                           [Dim Date].[YQMD].[Year],
                           [Dim Date].[YQMD].CurrentMember
            )
 ) ;
End Scope ;
/*

 End Time Intelligence script for the [Dim Date].[YQMD] hierarchy.

*/
Dimenze časové kalkulace je poté samostatná a struktura kostky je přehlednější. Může docházet ke zmatení v případě více kalendářů (Role playing dimenze) na který kalendář má vlastně takováto dimenze reagovat (je to svázané s currentmemberem.
Level 3 by byl napsat vše manuálně za použití příkazu Scope, to by ale bylo poněkud nad rámec tutorialu.
Závěr

V dnešním díle jsme si ukázali, jak s minimálním úsilím dojít k časovým kalkulacím do multidimenzionálních OLAP kostek (Tabulární model počítané členy v dimenzích neumí). V podnstatě je tento přístup na tutorialu nezávislý a není pro něj znalost MDX potřeba. Znalost jazyka je ale obrovskou výhodou minimálně k pasivnímu pochopení co vypadlo z průvodce, případně pokud byste chtěli calculate script modifikovat, odladit případné chyby atd.

9. prosince 2015

Power BI v Excelu, Excel v Power BI

Otázka na kterou by měl odpovědět dnešní článek zní: Můžu nějakým způsobem v powerbi.com zobrazovat obsah z Excelu, případně obráceně zovbrazovat v Excelu/Office pěkné vizualizace z Power BI? Odpověď na první část otázky ano, odpověď na druhou část otázky zní taky ano. Takže varování ministerstva zdravotnictví: Power BI a Excel způsobují závislost! Pokud tedy nevyvolají imunitní reakci. Můžete pozorovat sami na sobě: Naskočila Vám vyrážka, hůř se Vám dýchá? To bude imunitní reakce, opusťte tento článek. Cítíte vzrušení a chcete si šlehnout ať už Excelu či Power BI? Čtěte dále :)
Obsah z PowerBI.com v Excelu a dalších aplikacích Office
Nejedná se o žádnou novinku z posledního týdne, ale už nějakou dobu lze do Office aplikací vkládat obsah z PowerBI.com Pokud máte nainstalovaný Excel 2013 a novější (nejsem si jistý jak v Excelu 2010, kdyžtak mě doplňte), lze na záložce Insert vybrat položku Store a zde Power BI Tiles. Budete vyzváni k přihlášení a pokud se přihlásíte správně, uvidíte obrazovku podobnou této
Po výběru dashboardu můžete vložit obsah přímo do Excelového dokumentu. Šipky po stranách umožňují navigaci na další vizualizace z dashboardu případně filtrovat a aktualizovat data.
Mimochodem stejná možnost pro vložení Tiles je například v PowerPointu a zobrazuje živá data z Dashboardu.
Excelový obsah v PowerBI.com
Obrácený problém. Máte vymazlený report se všemi potřebnými daty a komplexními výpočty vytvořený v Excelu (ať holém, nebo s pomocí PowerPivot) a chtěli byste na onen obsah koukat z webového prohlížeče, mobilu na jednom místě s obsahem vytvořeným v Power BI. Hodily by se taky aktualizace z On Premises zdrojů. Máte klasický Excelový obsah jako například kontingeční tabulku na dalším obrázku?
Uložte jej do OneDrive, nebo OneDrive for Business. Poté v PowerBI portálu klikněte vedle datasets na plus, zvolte file, OneDrive Business, nebo OneDrive personal. Přihlaste se, zvolte soubor a dejte connect
Vyberte, zda chcete data importovat (jednorázově), nebo nalinkovat z OneDrive tak, aby když se změní ve OneDrive o tom Power BI vědělo.
Pokud obsah Excelového dokumentu vznikl v PowerPivot a vy máte nainstalovanou ve společnosti PowerBI gateway, nastavíte účty a časy pro aktualizace pak máte sice data on premises, ale reporty v cloudu o tom ví, stejně jako dashboardy na mobilu.
Závěr:

PowerBI a nemyslím nezbytně PowerBI do Excelu se s Excelem pěkně domluví. Obsah z Excelu můžeme publikovat v PowerBI.com a stejně tak v Excelu a dalších Office aplikacích používat obsah z PowerBI.com Přijdou si tedy na své jak závisláci na Excelu, tak závisláci na PowerBI. My kteří cítíme obě závislosti dostaneme dvojitou dávku, takže si ji užijte :)

8. prosince 2015

MDX Tutorial 11 – Příkaz Scope

Po časových kalkulacích (http://www.neoral.cz/2015/11/mdx-tutorial-10-casove-funkce.html) Vás vítám vás u dalšího dílu MDX tutorialu. Tentokrát s příkazem Scope v calculate scriptu. Zlí jazykové tvrdí, že pořádné MDX aplikace začínají právě příkazem Scope. Takže o čem je Scope assignement v MDX? Jaké jsou výhody zápisu s použitím Scope proti počítaným členům a jaké jsou nevýhody?
Na začátek syntaxe
Scope (Subcube);
THIS = výpočet;
END Scope;
Příkaz Scope přepisuje hodnoty buněk v „Subkostce“ definované příkazem.  Příkaz  This = říká přiřaď hodnotu do dané Subkostky. Jedná se o párový příkaz končící příkazem END Scope;
Výhody – počítané členy rozšiřují kostku o další prvek. Scope může přepisovat hodnoty v počítaných členech, ale také hodnoty „perzistentních měřítek“. V některých případech můžete vyřešit stejný problém jak přes Scope, tak přes počítaný člen. V nekterých případech můžete díky “Scope“ dosáhnout i lepšího výkonu. Přirovnání z transakčního světa, scope má zkompilovaný exekuční plán.
Nevýhody – narozdíl od počítaných členů se hůř ladí. Počítaný člen můžeme napsat prvně na úrovni dotazu (With member) a poté jej uložit do kostky (Create member currentcube). Takováto přímá kompatibilita se Scope příkazem neexistuje. Pokud chcete příkaz odladit. Napíšete jej prvně jako počítaný člen, a pak jej do calculate scriptu přepíšete. Toto může vést k tomu, že výpočet buď funguje, nebo ne :) Psaní Scope příkazů může být návykové. Proto někteří jedinci můžou sklouznout i k tomu, že přes Scope řeší to, co by se dalo jednoduššeji a přehledněji řešit počítaným členem. Přebrat kostku po někom takovém a zorientovat se je „radost“ :)
I když je odstavec nevýhody proti výhodám delší, při správném použití výhody přetrvávají. Jen pamatujte, že: „Scope je dobrý sluha, zlý pán“ :)
Scénáře použití a příklady
Přepis stávajícího měřítka
Úprava buněk tam, kde hodnota z DW nedává smysl, aneb zahlazení stop. Podívejme se na stávající kostu z projektu MDX Tutorial (link v rozcestníku http://www.neoral.cz/2015/11/mdx-tutorail-0-rozcestnik.html). Najdeme zde měřítko Quota, které představuje plán prodejů. Plánuje se na kvartál na zaměstnance. Kvůli zachování stejné granularity, aby se daly obě faktové tabulky propojit na stejnou časovou dimenzi jsou plány navázané k prvnímu dni v daném kvartálu. Na nižších úrovních než kvartál tedy porovnání nedává smysl. Viz obrázek
Mám dvě možnosti, buď nesmyslná čísla schovám. Nebo rozpočítám na smysluplná.
Varianta 1 schovám by se dala vyřešit počítaným členem a skrytím zdrojového měřítka a nebo jak tušíte. Příkazem Scope :)
SCOPE ([Measures].[Quota]);
   THIS = iif([Dim Date].[YQMD].currentmember.level.ordinal > 2
   ,null
   ,measures.currentmember
   );
END SCOPE;
Výsledek
Varianta 2 „smysluplné“ rozpočítání
Číslo nastavené pro celý kvartál chci rovnoměrně rozpočítat na dny s předpokladem, že dny mají stejnou váhu.


SCOPE ([Measures].[Quota],[Dim Date].[YQMD].[Date]);
   THIS = ([Dim Date].[YQMD].currentmember.parent.parent,[Measures].[Quota])/
   count(
   descendants([Dim Date].[YQMD].currentmember.parent.parent
   ,[Dim Date].[YQMD].[Date])
   );
END SCOPE;
Výstup
Závěr

V dnešním díle tutorialu jsme si ukázali, jak změnit hodnotu v buňkách OLAP kostek pomocí příkazu Scope. Příšte si ukážeme jak využít příkaz Scope pro časové kalkulace uložené v dimenzi. Což nám umožní, aby jeden výpočet fungoval pro všechny relevantní měřítka a nemusela se duplikovat logika.