neoral.cz

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
Diagram stromu hierarchie s uzly Ancestor, currentmember, Cousin a Descendant, který znázorňuje příbuzenské vztahy členů
Č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]
Výsledek MDX dotazu: pro každý měsíc roku 2005 a 2006 sloupce current, Ancestor (rok) a Ancestors jako množina {[Dim Date].[YQMD].[Year].&[…]}
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]
Výsledek MDX dotazu: krátký seznam tří jmen zaměstnanců a řádek All

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]
Výsledek MDX dotazu: roky 2005–2008 po čtvrtletích (2008 jen 1–3), ve sloupci x řetězce členů [Dim Date].[YQMD].[Quarter].&[rok]&[čtvrtletí], rok 2005 má null

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]
Výsledek MDX dotazu: měsíce roku 2005 a 2006 a sloupec x s unikátními názvy členů ve tvaru [Dim Date].[YQMD].[Date].&[2005-01-01T00:00:00]
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]
Výsledek MDX dotazu: seznam roku a měsíce od ledna 2005 do dubna 2006
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]
Výsledek MDX dotazu: seznam jmen zaměstnanců, některá jména se opakují
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]
Výsledek MDX dotazu: pro datum 2005-01-27 sloupce current, prevmember (2005-01-26), nextmember (2005-01-28), lag (2005-01-21) a lead (2005-02-02)

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é :)

Další v tématu Archiv

Archiv

MDX Tutorail 0. – Rozcestník

Rovnou na rozcestník zarolujte dolů Tutorial se nám již rozrostl do takových rozměrů, že navigace v rámci blogu může být obtížná. Pro rychlejší a přehlednější navigaci použijte tento rozcestník ROZCESTNÍK Relační databáze…

Archiv· MDX tutoriál 13

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…

Archiv· MDX tutoriál 12

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…