1. ledna 2016

MDX Tutorial 13 – KPI indikátory

Když píši tento článek, je 1.1.2016. Starý rok skončil, nový začíná. A jak skončil starý rok, tak i MDX tutorial se chýlí ke konci a tohle je jeho poslední díl (pokud si jako na koncertě nevykřičíte přídavek :)) Tutorial měl mít hlavně výukovou podobu, aby čtenáři snáze pochopili články o aplikovaném MDX. Takže nebojte, nějaký článek o MDX ze mě ještě vypadne (například mám v plánu článek o dynamickém zabezpečení SSAS pomocí MDX výrazů). Otázka pro publikum, líbí se Vám články typu tutorial. Chtěli byste nějaký další tutorial na jiné téma, když ano na které? Pište do komentářů, nebo do mailu.
V minulém díle jsme si ukázali, jak dojít k časovým kalkulacím téměr bez práce (http://www.neoral.cz/2015/12/mdx-tutorial-12-casove-kalkulace-jinak.html). Posledním bodem MDX výukové skládanky je definice KPI indikátorů v OLAP kostce. Kompletní seznam dílů najdete v rozcestníku (http://www.neoral.cz/2016/01/mdx-tutorail-0-rozcestnik.html)
KPI
Se zkratkou KPI se asi většina čtenářů již setkala. Z pohledu byznysu se jedná o “Key Performance Indicator” česky by se dalo přeložit jako “Klíčový Ukazatel Výkonu”. Aneb máme nějakou byznysovou metriku, která měří jak se firmě daří nedaří.
Z pohledu multidimenzionální OLAP kostky se jedná o objekt, který má nadefinovanou skutečnou hodnotu, cílovou hodnotu a výraz pro status (plníme, neplníme) a trend (roste, klesá, udržení předchozího stavu). Tabulární OLAP má v sobě KPI taky, ale nemá podporu pro trend.
Podobnou funkci jako KPI indikátory v kostce mohou plnit různé funkce klientských nástrojů, například podmíněné formátování v Excelu, nebo vizualizace indikátor v Reporting Services.
Výhodou KPI v kostce je centrální definice (přece jen některá měřítka by uživatelé nemuseli být schopni sami interpretovat, dva uživatelé by pro stejný stav mohli zvolit různé barvy), kdežto zelenou barvu na semaforu pochopí každý. KPI v kostce jsou pro některé klientské aplikace nativně podporované. Například v Excelu uvidíte značku semaforu a pod ní 4 zatržítka. V jiných aplikacích je bohužel potřeba stavy opětovně nadefinovat (např. že -1 znamená červený stavový ukazatel)
Definice a komponenty
KPI definujeme v designeru kostky na záložce 4té záložce KPIs. Máme zde okénka pro vepsání
Value Expression – představuje skutečnou hodnotu
Goal Expression – představuje cíl
Status Expression – status, vyžaduje stavy v rozmezí -1, 1
Status Indicator – semafor, smajlík, atd (smajlíky například Excel neumí)
Trend Expression – trend v rozmezí -1,1 pro určení trendu (-1 pokles, 0 udržení předchozího stavu, 1 růst)
Trend Indicator – šipka, obrácená šipka, barevná šipka...
A další doplňující vlastnosti nad rámec tutorialu.
Okénka jsou malá, a špatně se definice ladí, pokud nejdete úplně na jistotu. Doporučuji celou definici napsat pomocí počítaných členů jako MDX dotaz a pak do Visual studia pouze zkopírovat.
Jeden příklad lepší jak půlkilometru textu
Příklad
V tutorial kostce najdeme měřítko Reseller Sales které představuje skutečnost. Measure Quota představuje plán. Plánuje se na úrovni kvartálů, prodeje jsou na dny. Chtěl bych porovnat plán vs skutečnost a zobrazit trend, zda se jedná o růst proti loňsku.
Vytvořím si dotaz, na kterém budu ladit a postupně budu měnit výrazy v jednotlivých počítaných členech
WITH MEMBER [Skutecnost] AS
[Measures].[Reseller Sales]
MEMBER [Plan] AS
[Measures].[Quota]
MEMBER [Status] AS
1
MEMBER [Trend] AS
1
SELECT
{[Skutecnost]
,[Plan]
,[Status]
,[Trend]}
ON COLUMNS,
{[Dim Date].[Year].[Year]*
[Dim Date].[YQMD].[Quarter]}
ON ROWS
FROM [MDX Tutorial]


Skutečnost a plán je celkem jasný, tento je definován již v kostce, nám stačí spočítat status a trend
Klientská aplikace očekává pro status hodnotu -1 až 1. Bude se tedy jednat o jednoduchý CASE. Plnění nad 100%, splněné. Nad 85% skoro splněno (žlutá), pokud není skutečnost, ani plán prázdná buňka, jinak zbývá pokles.
MEMBER [Status] AS
CASE
WHEN DIVIDE([Skutecnost],[Plan]) >=1
THEN 1
WHEN DIVIDE([Skutecnost],[Plan]) >=0.85
THEN 0
WHEN [Skutecnost] = null OR [Plan] = null
THEN NULL
ELSE -1
END
Po nahrazení za zdrojová měřítka (počítané členy byly pouze pomocné, v kostce neexistují)
MEMBER [Status] AS
CASE
WHEN DIVIDE([Measures].[Reseller Sales],[Measures].[Quota]) >=1
THEN 1
WHEN DIVIDE([Measures].[Reseller Sales],[Measures].[Quota]) >=0.85
THEN 0
WHEN [Measures].[Reseller Sales] = null OR [Measures].[Quota]= null
THEN NULL
ELSE -1
END
Trend funguje na podobném principu. Měsíc s měsícem v daném byznysu moc porovnatelný není. Leden s prosincem nemusí mít ani zdaleka porovnatelnou výkonnost. Srovnat leden jednoho roku s lednem roku předchozího ale smysl dává. Pokud není aktuální období nebo loňský stav chci zobrazit prázdnou buňku. Pokud se jedná o růst 10% a více jedná se o růst. Pokud bude pokles více jak 10% pokles. V rozmezí -10% a 10% udržení předchozího stavu. Na meziroční růst se můžu odkázat zkratkou díky časovým výpočtům z minulého dílu tutorialu, nebo pomocí funkce parallelperiod (kapitola časové kalkulace http://www.neoral.cz/2015/11/mdx-tutorial-10-casove-funkce.html)
Aby bylo lépe vidět, co se děje, použiji funkci parallelperiod.
MEMBER [Trend] AS
CASE
WHEN ([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
) = NULL OR
[Measures].[Reseller Sales] = NULL
THEN NULL
WHEN DIVIDE(
[Measures].[Reseller Sales],
([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
)
)-1>=0.1
THEN 1
WHEN DIVIDE(
[Measures].[Reseller Sales],
([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
)
)-1<=-0.1
THEN -1
ELSE 0
END
Celý dotaz tedy bude vypadat
WITH MEMBER [Skutecnost] AS
[Measures].[Reseller Sales]
MEMBER [Plan] AS
[Measures].[Quota]
MEMBER [Status] AS
CASE
WHEN DIVIDE([Measures].[Reseller Sales],[Measures].[Quota]) >=1
THEN 1
WHEN DIVIDE([Measures].[Reseller Sales],[Measures].[Quota]) >=0.85
THEN 0
WHEN [Measures].[Reseller Sales] = null OR [Measures].[Quota]= null
THEN NULL
ELSE -1
END
MEMBER [Trend] AS
CASE
WHEN ([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
) = NULL OR
[Measures].[Reseller Sales] = NULL
THEN NULL
WHEN DIVIDE(
[Measures].[Reseller Sales],
([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
)
)-1>=0.1
THEN 1
WHEN DIVIDE(
[Measures].[Reseller Sales],
([Measures].[Reseller Sales],
parallelperiod(
[Dim Date].[YQMD].[Year]
,1
,[Dim Date].[YQMD].currentmember)
)
)-1<=-0.1
THEN -1
ELSE 0
END
SELECT
{[Skutecnost]
,[Plan]
,[Status]
,[Trend]}
ON COLUMNS,
non empty
{[Dim Date].[Year].[Year]*
[Dim Date].[YQMD].[Quarter]}
ON ROWS
FROM [MDX Tutorial]


Přidání do kostky
Ve visual studiu stačí zkopírovat aktivní část výpočtu do příslušných polí. Nastavit associated measure group (kde se má KPI zobrazovat) a nastavit název.
Nasadíme projekt a připojíme se z Excelu
Výstupy
Excel si tedy umí s KPI poradit hned. Pro reporting services, budeme muset napsat dotaz, který nám vrátí hodnotu Statusu, trendu atd. Existují zde 4 funkce pro value, goal, status a trend.
Dotaz vypisující jak se dařilo z kostky po nasazení by vypadal následovně
SELECT
{KPIValue("Reseller KPI")
, KPIGoal("Reseller KPI")
, KPIStatus("Reseller KPI")
, KPITrend("Reseller KPI")}
ON COLUMNS,
{[Dim Date].[YQMD].[Year]}
ON ROWS
FROM [MDX Tutorial]
A výsledná sada záznamů
V SSRS je potřeba nastavit pro hodnoty správnou grafickou reprezentaci např pro -1 u statusu červenou barvu.
Závěr
V dnešním dílu jsme si ukázali, jak definovat KPI indikátory v SSAS pomocí MDX jazyka a jak se na tyto indikátory poté dotazovat. Tímto končí MDX tutorial který měl výukovou formou projít od základních syntaxí jazyka až po jednodušší aplikace. Doufám, že pro Vás byl tutorial přínosný. Z oblasti SSAS budou následovat další články spíše aplikovaného charakteru kde Tutorial má posloužit jako základna.

5 komentářů:

  1. Výborný tutorial, nejlepší, na jaký jsem dosud narazil!
    Děkuji za jeho přípravu a publikování.
    Ať se daří, Leoš

    OdpovědětVymazat
    Odpovědi
    1. Díky Leoši,
      pozitivní zpětná vazba potěší a motivuje přijít s něčím dalším :)

      Vymazat
  2. Připojím se k Leošovi, přehledný, srozumitelný a praktický tutorial. Taky jsem lepší vysvětlení MDX s praktickými ukázkami nepotkal.

    Díky Filip

    OdpovědětVymazat
  3. Přidám se k Leošovi, stručný, srozumitelný a velmi praktický tutoriál. Taky jsem lepší nepotkal. Hodně mě pomohl.

    Díky Filip

    OdpovědětVymazat