18. října 2015

MDX tutorial 8 – Navigace v hierarchii 1. část

V minulém dílu tutorialu jsme probarali agregační funkce (http://www.neoral.cz/2015/10/mdx-tutorial-7-agregacni-funkce.html) jako nezbytnou prerekvizitu k tomu, co přichází dnes. Postupem času jsme se prokousali k akčnímu tématu a tím je navigace v hierarchii. Jedná se o skupinu funkcí, která bych řekl má v OLAP kostkách snad nejšiřší použití.
Všechno začíná currentmemberem. Ten je naším středem vesmíru od kterého navigace bude začínat, okolo currentmembera jsou další členové hierarchie v různých příbuzenských vazbách. Většina funkcí dává smysl hlavně ve víceúrovňových hierarchiích.
Nad currentmemberem je jeho parent. Currentmember má pouze jednoho parenta. Potomci currentmembera jsou children. Sourozenci siblings.
Navigační funkce se dají obecně rozdělit na dva typy, funkce vracející member (používají se jako souřadnice v tuplu, nebo funkce vracející množinu prvků. Množinové navigační funkce zpravidla kombinujeme s funkcemi agregačními probranými v dílu číslo 7.
Seznam funkcí pro přístup k nejbližším příbuzným. V kulatých závorkách je napsáno, zda funkce vrací member, nebo set.
Funkce
Popis
member.parent
nadřízený člen (member)
member.children
veškeré potomstvo o úroveň níž (set)
member.firstChild
první potomek (member)
member.lastchild
poslední potomek (member)
Siblings
všichni sourozenci pod stejným parentem (set)
member.firstsibling
první sourozenec (member)
member.lastsibling
poslední sourozenec (member)
Parent
Funkce parent nás odkáže v hierarchii na nadřízený prvek. Použítím by mohlo být například procenta v rámci nadřízené skupiny. Na začátek dotaz, ze kterého budu vycházet.
WITH MEMBER x AS
       [Dim Product].[Category-Subcategory-Model-Product].currentmember
       .name
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]
Dotaz vrací prvek z hierarchie category-subcategory-model-product, jeho prodeje a ve sloupci x odkaz na něj
Odkaz na nadřízený prvek v hierarchii
WITH MEMBER x AS
       [Dim Product].[Category-Subcategory-Model-Product].currentmember.parent
       .name
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]

Prodeje nadřízeného prvku
WITH MEMBER x AS
       (
       [Dim Product].[Category-Subcategory-Model-Product].currentmember.parent
       ,[Measures].[Reseller Sales])
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]
Procenta nadřízeného prvku
WITH MEMBER x AS
DIVIDE(
       [Measures].[Reseller Sales],
       (
       [Dim Product].[Category-Subcategory-Model-Product].currentmember.parent
       ,[Measures].[Reseller Sales])
       )
,format_string="percent"
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]

Children
Setová funkce children vrátí veškeřé podřízené prvky z hierarchie a je použitelná například v kombinaci s funkcí avg pro výpočet “průměru v dané skupině” Pokud bychom chtěli porovnávat akutální prvek s průměrem prvků na stejné úrovní, použili bychom spíš funkci “siblings”, což je to stejné jako “parent.children”, případně funkcí “siblings”. Obecně pro práci s navigačními funkcemi pro ladící účely doporučuji funkci settostr, která převede množinu na řetězec.
WITH MEMBER x AS
       settostr(
       [Dim Product].[Category-Subcategory-Model-Product].currentmember.children
       )
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]

Po odladění můžeme spočítat průměr v dané skupině
WITH MEMBER x AS
       avg([Dim Product].[Category-Subcategory-Model-Product].currentmember.children
       ,[Measures].[Reseller Sales]
       )
SELECT
       {[Measures].[Reseller Sales]
       ,x}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category-Subcategory-Model-Product].members}
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]
Ostatní funkce by byly použitelné hlavně, ale nejen v časových souvislostech. Dotaz, ze kterého budu vycházet je následující
WITH MEMBER x AS
       "sem napište výraz"
SELECT
       {x}
ON COLUMNS,
       [Dim Date].[YQMD].[Date].&[2007-10-16T00:00:00]
ON ROWS
FROM [MDX Tutorial]
Začátek a konec měsíce
WITH MEMBER zacatek AS
       [Dim Date].[YQMD].currentmember.firstsibling.name
MEMBER konec AS
       [Dim Date].[YQMD].currentmember.lastsibling.name
SELECT
       { zacatek,
konec }
ON COLUMNS,
       [Dim Date].[YQMD].[Date].&[2007-10-16T00:00:00]
ON ROWS
FROM [MDX Tutorial]
Začátek roku a kumulovaná suma prodejů od začátku roku
WITH MEMBER zacatek AS
       [Dim Date].[YQMD].currentmember.parent.parent.parent.firstchild.firstchild.firstchild.name
MEMBER sumaodzacatku AS
       SUM
       (
       [Dim Date].[YQMD].currentmember.parent.parent.parent.firstchild.firstchild.firstchild:
       [Dim Date].[YQMD].currentmember
       ,[Measures].[Reseller Sales]
       )
SELECT
       { [Measures].[Reseller Sales]
       ,zacatek
       ,sumaodzacatku }
ON COLUMNS,
       [Dim Date].[YQMD].[Date].&[2007-10-16T00:00:00]
ON ROWS
FROM [MDX Tutorial]

Začátek roku i kumulovaná suma by se daly získat i jednoduššeji, ale o tom opět někdy potom :)
Závěr

Navigace v hierarchiích multidimenzionálních OLAP kostek je jedním z nejdůležitějších principů MDX jazyka. Syntaxe není složitá, vše je jen o tom, uvědomit si, kde se v hierarchii nacházíte, kam se chcete dostat a jestli funkce vrací member/set. I většina časových výpočtů se dá realizovat právě přes navigační funkce. Navigačních funkcí je hodně a proto s nimi budeme pokračovat i v dalším díle tutorialu.

Žádné komentáře:

Okomentovat