MDX tutorial 5 – Setové funkce

Dnešní díl přichází opět trochu se zpožděním, nějak moc práce před změnou práce a je potřeba dodělat všechny resty :). Musím tedy využít toho, že se mi na pozadí procesuje kostka u klienta k sepsání dalšího dílu tutoriálu. V minulých dílech MDX tutoriálu jsme se zabývali tvorbou počítaných členů http://www.neoral.cz/2015/05/mdx-tutorial-3-pocitane-cleny.html a aplikací v SSRS http://www.neoral.cz/2015/07/mdx-tutorial-4-aplikace-mdx-v-ssrs.html

Dnes začneme jednu velmi rozsáhlou kapitolu kterou budu muset rozbít do několika podkapitol. Čekají nás funkce v MDX. Než se dostaneme k navigaci a práci s časem, začnu kategorií funkcí setových.

Jako výchozí dotaz beru následující skript, který vybere všechny kategorie, podkategorie a modely produktů dohromady s prodeji

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

NON EMPTY

       [Dim Product].[Category].[Category]*

       [Dim Product].[Subcategory].[Subcategory]*

       [Dim Product].[Category-Subcategory-Model-Product].[Model]

ON ROWS

FROM [MDX Tutorial]

Začneme řazením

Order ({Set}, výraz [, FLAG])

V MDX byste hledali order by klauzuli marně, řadí se zde funkcí Order. Prvním argumentem je zde množina kterou chcete seřadit, druhý výraz podle kterého chceme řadit a na závěr FLAG jakým stylem chceme řadit. Výchozím flagem je ASC, čili vzestupné řazení. Pokud napíšeme dotaz

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

NON EMPTY

       ORDER(

       [Dim Product].[Category].[Category]*

       [Dim Product].[Subcategory].[Subcategory]*

       [Dim Product].[Category-Subcategory-Model-Product].[Model],

       [Measures].[Reseller Sales]

       ,asc

       )

ON ROWS

FROM [MDX Tutorial]

výsledky se zdají setřízené podivně.

Kategorie	Podkategorie	Model	Reseller Sales
Accessories	Bike Racks	Hitch Rack - 4-Bike	$197,736.16
Accessories	Bottles and Cages	Water Bottle	$7,476.60
Accessories	Cleaners	Bike Wash	$11,188.37
Accessories	Helmets	Sport-100	$258,712.93
Accessories	Hydration Packs	Hydration Pack	$65,518.75
Accessories	Locks	Cable Lock	$16,225.22
Accessories	Pumps	Minipump	$13,514.69
Accessories	Tires and Tubes	Patch kit	$925.21
Bikes	Mountain Bikes	Mountain-400-W	$592,450.05
Bikes	Mountain Bikes	Mountain-500	$1,067,327.31
Bikes	Mountain Bikes	Mountain-300	$1,907,249.38
Bikes	Mountain Bikes	Mountain-100	$8,568,958.27
Bikes	Mountain Bikes	Mountain-200	$14,356,699.36
Bikes	Road Bikes	Road-750	$966,079.59
Bikes	Road Bikes	Road-450	$1,874,406.52
Bikes	Road Bikes	Road-150	$2,363,805.16

Pozornému oku ale neunikne, že data setřízená jsou. Modely jsou setřízeny v rámci nadřazeného prvku v hierarchii. Modely jsou setřízeny v rámci nadřazené podkategorie.

ASC – řadí vzestupně v rámci parenta (nadřízeného prvku v hierarchii)

DESC – sestupně v rámci parenta

BASC – vzestupně bez ohledu na parenta

BDESC – sestupně bez ohledu na parenta

Pokud bychom tedy chtěli Modely od nejprodávanějšího po nejméně prodávaný, dotaz by vypadal následovně

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

NON EMPTY

       ORDER(

       [Dim Product].[Category].[Category]*

       [Dim Product].[Subcategory].[Subcategory]*

       [Dim Product].[Category-Subcategory-Model-Product].[Model],

       [Measures].[Reseller Sales]

       ,BDESC

       )

ON ROWS

FROM [MDX Tutorial]

Výsledek:

Category	Subcategory	Model	Reseller Sales
Bikes	Mountain Bikes	Mountain-200	$14,356,699.36
Bikes	Road Bikes	Road-250	$9,377,457.68
Bikes	Mountain Bikes	Mountain-100	$8,568,958.27
Bikes	Road Bikes	Road-650	$7,442,141.81
Bikes	Touring Bikes	Touring-1000	$6,723,794.29
Bikes	Road Bikes	Road-550-W	$3,668,383.88
Bikes	Road Bikes	Road-350-W	$3,665,932.31
Components	Mountain Frames	HL Mountain Frame	$3,365,069.27
Bikes	Road Bikes	Road-150	$2,363,805.16
Bikes	Touring Bikes	Touring-3000	$2,046,508.26

Další analytickou úlohou, která je občas potřeba provést je zjištění nejlepších, nejhorších. Jako bychom v SQL psali SELECT TOP 10 Model FROM Tabulka ORDER BY ResellerSales DESC

MDX opět nemá TOP N klauzuli, nýbrž použijeme funkci

TopCount({Set}, N ,[výraz])

Set je množina prvků za kterých chceme ty nejlepší, N je kolik, výraz podle kterého chceme vyhodnocovat (řazení probíhá bez ohledu na parenta)

Nejlepších 10 modelů podle internetových prodejů bych získal dotazem

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

NON EMPTY

       TOPCOUNT(

       [Dim Product].[Category].[Category]*

       [Dim Product].[Subcategory].[Subcategory]*

       [Dim Product].[Category-Subcategory-Model-Product].[Model],

       10

       ,[Measures].[Reseller Sales]

       )

ON ROWS

FROM [MDX Tutorial]

BottomCount({Set}, N ,[výraz])

Funguje obdobně jako TopCount, jenom z druhé strany. Pokud umažeme NON EMPTY dotaz vrátí 10 nejhorších včetně modelů, které se neprodávaly

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

       BOTTOMCOUNT(

       [Dim Product].[Category].[Category]*

       [Dim Product].[Subcategory].[Subcategory]*

       [Dim Product].[Category-Subcategory-Model-Product].[Model]

       ,10

       ,[Measures].[Reseller Sales]

       )

ON ROWS

FROM [MDX Tutorial]

Pokud by nám v dotazu před výrazem na řádcích NON EMPTY zůstalo, nevrátil by dotaz nic protože NON EMPTY se vyhodnotí až po BOTTOMCOUNT. Chceme-li tedy 10 nejslabších modelů, které se ale prodávaly musíme použít funkci NONEMPTY psanou dohromady. Dotaz by vypadal

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

       BOTTOMCOUNT(

             NONEMPTY(

             [Dim Product].[Category].[Category]*

             [Dim Product].[Subcategory].[Subcategory]*

             [Dim Product].[Category-Subcategory-Model-Product].[Model]

             ,[Measures].[Reseller Sales]

             )

       ,10

       ,[Measures].[Reseller Sales]

       )

ON ROWS

FROM [MDX Tutorial]

Pokud bychom chtěli získat z množiny N prvků ze začátku, případně z konce bez předchozího setřídění, můžeme použít funkce

Head({Set}, N ) – množina N prvků od začátku

Tail({Set}, N) – množina N prvků od konce

K čemu může být dobré vybírat N prvků bez ohledu na řazení? Tohle se může hodit při práci se semi-aditivními  měřítky stejně jako funkce Item. Práci se semi-aditivními měřítky a tudíž aplikaci těchto funkcí jsem popsal v článku http://www.neoral.cz/2015/05/semi-aditivni-meritka-v-ssas-pomoci-mdx.html

{Set}.Item(0) – vybere první položku množiny, funkce item převádí set na membera výběrem n-tého prvku

Další častou úlohou kterou čas od času, ne-li denně potřebujeme v SQL je filtrování dat. Už jsme si ukázali jak funguje WHERE klauzule, ale co když potřebuji vyfiltrovat data na modely s prodeji většími než je určitá částka, nebo najít modely obsahující klíčové slovo (SQL Like ekvivalent)

Pro tyto účely použijeme funkci Filter

Filter ({Set} ,  výraz)

Set je množina kterou chceme vyfiltrovat, výraz je podmínka, která musí být splněna. Z výkonových důvodů doporučuji funkci filter používat až na nonempty data zvlášť pokud se jedná o větší množství kombinací buněk. Pokud nedostanete Out of Memory exception počkáte si dlouho pokud nonempty nad většími daty nepoužijete.

Pokud bych chtěl najít modely s prodeji většími než 30 000, dotaz by vypadal následovně

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

       filter(

             nonempty(

                    [Dim Product].[Category].[Category]*

                    [Dim Product].[Subcategory].[Subcategory]*

                    [Dim Product].[Category-Subcategory-Model-Product].[Model]

                    ,[Measures].[Reseller Sales])

             ,[Measures].[Reseller Sales]>30000

       )

ON ROWS

FROM [MDX Tutorial]

Pokud bych chtěl použít v MDX ekvivalent SQL Like (který MDX opět nezná), potřebuji vlastně ve výskytu slova hledat řetězec. Zde můžu použít VBA funkci InStr(kde, co, [počátek hledání]). Pokud InStr najde, vrátí číselně pozici na kterém znaku text začíná, pokud nenajde vrátí 0. Dotaz pro filtrovaný seznam modelů které v textu obsahují klíčové slovo by vypadal

SELECT

       {[Measures].[Reseller Sales]}

ON COLUMNS,

       filter(

             nonempty(

                    [Dim Product].[Category].[Category]*

                    [Dim Product].[Subcategory].[Subcategory]*

                    [Dim Product].[Category-Subcategory-Model-Product].[Model]

                    ,[Measures].[Reseller Sales])

             , InStr([Dim Product].[Category-Subcategory-Model-Product].currentmember.name,"bike")>0

       )

ON ROWS

FROM [MDX Tutorial]

Závěr:

V dnešní kapitole jsme začali se setovými funkcemi. Ukázal jsem ty které sám nejčastěji používám a zdaleka to není všechno. Kostka je zprocesovaná :) a je opět skoro půlnoc. Vše napsat jedním dechem se nedá, takže v práci se sety budeme pokračovat v dalším dílu. Posvítíme na množinové operace a možná přidáme i nějaké další setové funkce.