26. května 2015

MDX tutorial – 3. počítané členy a currentmember

V minulém díle jsme si ukázali základní práci se setem (http://www.neoral.cz/2015/05/mdx-tutorial-2-prace-se-setem.html). Dnes se podívejme na  počítané členy a jak funguje jedna z nejdůležitějších funkcí v MDX currentmember
Začněme dotazem
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Počítané členy můžeme v MDX používat na různých úrovních.Na úrovni dotazu (v SSMS), připojení (sešit Excelu), uložený výpočet dostupný uživatelům kostky. Osobně preferuji postup: Napsat výraz s výpočtem v SSMS, na úrovni dotazu odladit, odladěný výpočet uložit v kostce. Počítané členy můžeme uložit do measures, nebo do dimenzí.
Základní počítaný člen v Measures
Syntaxe počítaného člena na úrovni dotazu (Query scoped) uloženého do Measures vypadá následovně:
WITH MEMBER [MEASURES].[x] AS výpočet
Název hierarchie Measures je zde nepovinný. Pokud nezadáme jinak, výpočet se uloží právě do measures. Stačí napsat:
WITH MEMBER [x] AS výpočet
Celý výraz vracející konstantu 1 by tedy mohl vypadat:
WITH MEMBER x AS
       1
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Hranaté závorky jsou nepovinné pokud název kalkulovaného členu není vyhrazené slovo, neobsahuje mezery, nezačíná číslem, ...
V počítaných členech pracujeme s tuply a sety, které jsme si definovali v první kapitole (http://www.neoral.cz/2015/05/mdx-tutorial-1-uvod.html). Pokud bych chtěl spočítat celkové prodeje, stačí sečíst buňku internetových prodejů (popsanou tuplem Internet sales), s buňkou prodejů přes obchodní síť (popsanou tuplem Reseller sales). V tomto případě můžeme obě buňky popsat pouze použitím osy měřítek.
Výsledný dotaz s komentáři
WITH MEMBER x AS
       [Measures].[Internet Sales] -- tuple 1
       + -- aritmetická operace
       [Measures].[Reseller Sales] -- tuple 2
,FORMAT_STRING = "currency" -- formátování
SELECT
       {
       [Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]
       ,x -- výpis počítaného měřítka
       }
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Format string kosmeticky naformátuje výsledek do mřížky management studia. Další formáty, které bychom mohli použít jsou formáty psané „Visual basic stylem“ (stejně jako vlastní formáty čísel v Excelu) např  „0“ pro celé číslo, „0.00“ číslo na dvě desetinná místa, „#,##0.00“ číslo na dvě desetiny s oddělenými řády čárkou atd. Kromě VB stylů můžeme napsat „standard“ pro standardní číslo (2 desetinná místa bez oddělení řádů), „percent“ pro procento, „currency“ pro měnu, a další.
Currentmember
Jednou z nejčastěji používaných funkcí v MDX je „currentmember“. SSAS databáze je hierarchická a chceme-li se v ní navigovat (nahoru, dolů, do stran, ...), musíme odněkud začít. A začneme právě od current membera. Naučit se pracovat s touto funkcí je KRITICKÉ.
Kterýkoliv člen hierarchie (member), má následující popisné vlastnosti (které můžeme vrátit stejnojmennou funkcí).
name – jméno
unique_name/uniquename – celý klíč ve tvaru [Dimenze].[Hierarchie].&[Klíč], (name column v SSAS)
member_key – klíč (key column vlastnost v SSAS)
Currentmember správně
Pokud bych do těla počítaného členu napíšete následující výraz, získáte seznam unikátních jmen členů atributové hierarchie Category
WITH MEMBER x AS
       [Dim Product].[Category].currentmember.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Currentmember špatně
Nejčastější chyba při použití funkce currentmember, kterou je potřeba zničit hned v zárodku. Skoro stejný výraz jako minule...
WITH MEMBER x AS
       [Dim Product].[Category].[Category].currentmember.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
... způsobí chybu:
#Error Query (2, 2) The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.
Co mu vadí? Přebývá název levelu. Červená část je navíc a špatně
[Dim Product].[Category].[Category].currentmember.uniquename
Pokud si jsme schopni ukázat správně na current member, můžeme se mimo jiné navigovat nahoru na parent (nadřazený prvek v hierarchii). Následující výraz vrátí unikátní jméno nadřízeného prvku „currentmembera“ v hierarchii, kterým je all member atributové hierarchie Category
WITH MEMBER x AS
       [Dim Product].[Category].currentmember.parent.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]

Následující výraz vrátí kromě internetových prodejů po zkompletování tuplu s odkazem na parent prodeje nadřazeného prvku v hierarchii
WITH MEMBER x AS
/*Tuple vracející prodeje nařízeného prvku v hierarchii*/
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent -- nadřízený prvek
       ,[Measures].[Internet Sales] --měřítko
       )
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Pokud bychom chtěli dát dohromady procentuální podíl aktuálního člena v rámci nadřízeného prvku v hierarchi stačilo by napsat:
WITH MEMBER x AS
       [Measures].[Internet Sales] -- aktuální prodeje
       /
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) -- prodeje nadřízeného prvku v hierarchii
,Format_string = "percent"
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Je potřeba ošetřit chybu pro allmembera, který nad sebou nemá žándý „parent“, abychom nedělili prázdnou buňkou. Zde musíme použít syntaxi, která vývojářům zvyklým na T-SQL, nejde přes klávesnici. Můžeme porovnávat s NULL hodnotou přes operátor =.
WITH MEMBER x AS
iif((
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) = NULL – test, je-li dělitel null
       ,NULL-- pokud ano null, jinak...
       ,[Measures].[Internet Sales]
       /
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) -- ... dělení stejně jako minule
       )
,Format_string = "percent"
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Currentmember nás bude provázet v našem MDX putování odteď napořád :) Vstřebejme tedy jeho použití a ještě se v dnešním článku podívejme na:
Počítaný člen uložený v dimenzi
který je v principu podobný, počítanému členu uloženému do measures. Odvoláváme se na buňky popsané tuplem, případně množiny buněk v setu. Jediný rozdíl je v tom, kam počítaný člen uložíme.  Jako umístění volíme parenta, pod kterým chceme počítaný člen zobrazit. Výhodou počtítaných členů v dimenzi je to, že fungují pro všechny měřítka. Tohoto ĺze s výhodou využít například pro časové kalkulace (meziroční porovnání by se hodilo jak pro internet sales, reseler sales, tak další měřítka). O tom ale někdy potom :)
Zápis obecně
WITH MEMBER [dimenze].[hierarchie].&[parent].[název počítaného členu] as výpočet
Příklad tvorby počítaného člena, který sečte prodeje kategorie accessories a bikes pro libovolné měřítko uložený pod All memberem.
WITH MEMBER [Dim Product].[Category-Subcategory-Model-Product].[All].[Core products] AS
       (
       [Dim Product].[Category-Subcategory-Model-Product].[Category].&[Bikes]
       --tuple pro Bikes, když nepoužiji měřítko, vezme měřítko v aktuálním řezu
       )
       +
       ([Dim Product].[Category-Subcategory-Model-Product].[Category].&[Accessories])
       --tuple pro kategorii Accessories
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {
       [Dim Product].[Category-Subcategory-Model-Product].[Category].allmembers
       --funkce members by vrátila pouze perzistentní členy, bez kalkulovaných
       }
ON ROWS
FROM [MDX Tutorial]

Závěr:

V dnešní kapitole jsme si ukázali, jak vytvořit jednoduché počítané členy v measures i v diemnzích. Ukázali jsme si, jak pracovat s funkcí currentmember. Také jsme si řekli, kterých nejčastějších chyb se při použití funkce currentmember vyvarovat. 

1 komentář: