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.