MDX tutorial – 1. Úvod

Díky absenci jakýchkoliv materiálů na dané téma v češtině, rozhodl jsem se dát dohromady tutoriál, který jazyk MDX demistifikuje, naučí Vás základy a možná i více. Tutoriál vyžaduje základní znalost SSAS.
MDX znamená MultiDimensional eXpressions. Jedná se o dotazovací jazyk do OLAP (OnLine Analytical Processing) kostek postavených na technologii analytických služeb SQL Serveru. Podobně jako u jazyka SQL se z něj stal mezinárodní standard a tudíž MDX můžete použít i na jiných platformách.
Použití

1. Rozšíření funkcí OLAP kostek – výpočty, KPI indikátory, ... zde se bez MDX neobejdete
2. Dotazování, reporting – MDX se používá pro dotazování jak do Multidimenzionálních kostek, tak je použitelný proti Tabulárním modelům. Zde existují generátory MDX kódu, ale tyto mají své hranice
3. Zabezpečení – se dá řešit i bez MDX, pokud ale potřebujete práva řízené daty ve vašem modelu (např máte dimenzi zaměstnanec, potřebujete aby každý zaměstnanec viděl pouze svá data, zaměstnanců je 500), místo abyste vyklikávali spoustu rolí, uděláte roli 1 dynamickou a práva nastavíte MDX výrazem.

Demo data
Projekt s demo kostkou můžete stáhnout zde http://tinyurl.com/pxfnrkw, postaveno nad demo databází AdventureWorksDW2012 z Codeplexu http://tinyurl.com/qd3bbg
Projekt může být v průběhu času uzpůsobován potřebám tutorialu.
Základní pojmy a syntaxe
MDX jazyk používá pro výběr dat klauzule SELECT, FROM, WHERE... Což má sice společné s SQL, ale zde veškerá podobnost končí. Nejhorší co může začátečník udělat je, že bude nad MDX přemýšlet jako v SQL. V databázi jsou data uložená jinak, tudíž i data z ní musíme vybírat jinak. Lépe uděláte, když budete při MDX dotazování přemýšlet jako Excel napojený na kostku. Excel je koneckonců klient, který do kostky posílá právě MDX dotazy.
Kostka
Je zjednodušené označení pro multidimenzionální databázi (či její část). Odkud pochází název? Představte si, že chcete analyzovat nějaké číslo, například prodej, v čase a podle produktu. Číslům se kterými chceme počítat, v případě prodejů agregační funkci suma, říkáme v kostkách měřítka. Čas a produkt jsou entity, které onomu číslu dodávají nějaký rozměr (dimenzi). Když řeknu prodeje za 4 miliony, možná Vás napadnou doplňující otázky, kdy, kde, čeho, komu... Odpovědi na tyto elementární otázky právě představují dimenze.
Když tedy máme jednu osu prostoru měřítko, a dvě další osy dimenze, máme osy celkem 3 a to nám dává 3 dimenziální prostor – kostku. V OLAP kostkách je těchto dimenzí více, ve skutečnosti každý atribut dimenze představuje osu prostoru (v čase by to byly rok, měsíc, kvartál, den, ...). Máme tedy multidimenzionální prostor. To je ale příliš dlouhé označení a i lépe zní: "Připoj se do kostky." Než: "Připoj se do multidimenzionálního prostoru." Také se člověk ten multidimenzionální prostor těžko představuje :)
Cell (buňka)
SSAS kostky drží hodnoty v buňkách. V Excelu pracujeme s buňkami také a je tedy ideálním klientem.
Tuple
Popisuje buňku v prostoru. Můžete si to představit podobně jako v matematice ve škole. Například bod na následujícím obrázku bychom pospali jako souřadnice v kulatých závorkách (x,y), nebo (2,4)

Podobně to funguje v kostce. Bod popsaný osami Category a Year bychom zapsali následovně (Components, 2015)

Při referencích členů se odkazujeme ideálně celým názvem. Názvy obsahující mezery a speciální znaky píšeme do hranatých závorek. Pro odkaz na kategorii Bikes by se dalo napsat třeba jen [Bikes], v tom případě je ale trošiku loterie odkud kostka kola vrátí. Např. [12] může vrátit 12-tý měsíc, 12-tý den v měsíci, zaměstnance s osobním číslem 12, atd.
Celá reference se provádí buď voláním přes jméno (name column), nebo lépe přes klíč (key column, může být i složený). Kvůli složitosti názvů, doporučuji místo psaní v management studiu tahat příslušné členy, čímž docílíme volání přes klíč.
Volání přes klíč [dimenze].[hierarchie].[volitelně level].&[klíč]
Volání přes klíč př: [dim product].[Category].&[1]
Volání přes jméno: [dim product].[Category].[Bikes]
Při popisu buňky pomocí tuplu nejsme téměř nikdy schopni vypsat všechny souřadnice (vypisujte 100 os voláním přes klíč). Analysis services tedy musí tyto chybějící souřadnice doplnit. Při vyhodnocení odkazu na buňku postupují SSAS následujícím způsobem. Excel dělá jako klient to stejné.
Neúplný tuple

1. Explicitně zadané souřadnice
2. Aplikace defaultních memberů z dimenzí (pokud takovéto existují)
3. All member – poukud není zadána dimenze (např. produkt), posčítá měřítko pro vešechny členy v dané dimenzi
4. First – pokud předchozí kroky nejsou aplikovatelné (např u měřítek, které nemají all membera) kostka vrátí z dané dimenze to první co jí přijde pod ruku.

Select from kostka vrátí v našem případě první měřítko, posčítané přes všechny dimenze. Kdyby byl někde nastaven default member, byl by použit pro omezení. Defaultní membery dává smysl nastavit například u verzí plánů, aby se různé verze/scénáře nesčítaly dohromady.

Set (množina)

Množina buněk se stejnou dimenzionalitou. Sety píšeme do složených závorek oddělené čárkami.

Jednoduchý set s jednou osou {x1,x2,x3,...}.

Dvě osy platný set {(x1,y1),(x2,y2),(x3,y3)}

Neplatný set z důvodu rozdílné dimenzionality {(x1,y1,z1),(x2,y2)} – u popisu druhé buňky chybí souřadnice z

Závorky

Závorky dokáží začátečníkům pěkně zamotat hlavu. A popravdě zamotat hlavu můžou nejen začátečníkům.

Hranaté závorky – [] pouze u názvů

Složené závorky – {} pouze u setů

Kulaté závorky – zbytek (souřadnice v tuplu, funkce, přednost matematických operací atd.)

Aplikace

Vytyčili jsme si základní pojmy a teoreticky bychom měli být schopni popisovat buňky pomocí tuplů a volat vícero buněk v setech. Pojďmě se podívat na aplikaci krok za krokem. Budu postupně klikat kontingenční tabulku a psát ekvivalentní

otaz v MDX a upozorním na případné problémy.

Nejprve špatně

SELECT
    [Measures].[Internet Sales]
FROM [MDX Tutorial]
Tento dotaz způsobí chybu
Executing the query ...
Error (Data mining): Either the user, NEORAL\Jiri, does not have permission to access the referenced mining model, MDX Tutorial, or the object does not exist.
Error (Data mining): Either the user, NEORAL\Jiri, does not have permission to access the referenced mining model, MDX Tutorial, or the object does not exist.
Execution complete

Tato chyba je úplně mimo mísu. Excel od nás očekává osy na které budeme chtít vypsat jednak měřítka a dimenzionální členy. To stejné chybí našemu MDX dotazu.
Osy
MDX jazyk podporuje až 127 os, placaté management studio první dvě, řádky a sloupce podobně jako v Excelu. Osy se nedají přeskakovat.
První osou jsou sloupce, dá se na ně odkazovat buď názvem COLUMNS, nebo číselm 0, nebo AXIS(0). Druhou osou jsou řádky ROWS, číselně 1. Osobně preferuji volání přes názvy os. Jediným důvodem je přehlednost.
Správný dotaz pro vyřešení našeho problému by tedy byl
SELECT
    [Measures].[Internet Sales]
ON COLUMNS
FROM [MDX Tutorial]

V další fázi bych chtěl kromě prodejů přes internet vrátit prodeje resellerů
SELECT
    [Measures].[Internet Sales]
    ,[Measures].[Reseller Sales]
ON COLUMNS
FROM [MDX Tutorial]

Dotaz způsobí chybu parseru:

Parser: The statement dialect could not be resolved due to ambiguity.

Nerozumí co po něm chceme, dotaz je zadaný nejednoznačně. Problém je se závorkami, čárka se používá jako oddělovač jak v setu, tak v tuplu.
Otázka na Vás zní: Pracujeme zde s Tuplem, nebo se Setem? Podle toho se budou odvíjet závorky (a že znám takové, kteří mají se závorkami problémy po několika letech psaní MDX :))
Pro odpověď na otázku si odpovězme otázkou. Pracuju s jednou buňkou, kterou popisuji několika souřadnicemi (tuple), nebo popisuji množinu několika buněk, které chci vrátit? (set)
Jak vidíte na obrázku, dotaz nám má vracet několik buněk, tedy pracujeme se setem a závorky budou složené.

Dotaz správně
SELECT
    {
    [Measures].[Internet Sales]
    ,[Measures].[Reseller Sales]
    }
ON COLUMNS
FROM [MDX Tutorial]

Chtěl bych modifikovat zadání a vypsat prodeje přes internet v jednotlivých letech viz obrázek.

Roky jsou vypsané na řádky, tudíž bych optřeboval druhou osu. Prodeje roku 2005 bych vrátil následujícím dotazem
SELECT
    {[Measures].[Internet Sales]}
ON COLUMNS,
    {[Dim Date].[Year].&[2005]}
ON ROWS
FROM [MDX Tutorial]
Abych dostal roky všechny, stačilo by dopsat množinu let oddělené čárkami.
SELECT
    {[Measures].[Internet Sales]}
ON COLUMNS,
    {[Dim Date].[Year].&[2005]
    ,[Dim Date].[Year].&[2006]
    ,[Dim Date].[Year].&[2007]
    ,[Dim Date].[Year].&[2008]}
ON ROWS
FROM [MDX Tutorial]

Pro pokrytí našeho dotazu to stačí. Nicméně pokud bychom pracovali s produktovou dimenzí a měli 3000 produktů, vypisovat produkty jeden po druhé voláním přes klíč.. Cítíte, že by to nebylo ono. K tomuto bychom použili některou množinoovu funkci (např. members). Podrobněji se budeme v funkci members věnovat v dalším díle tutorialu.

Závěr kapitoly:

První část klade důraz na pochopení základních pojmů, práci s osami, množinami a tuply. V dalších částech nás čekají pokročilejší techniky stavějící na těchto základních kamenech.