12. května 2015

MDX tutorial – 2. práce se setem

V první části tutorialu jsme se podívali na základní pojmy a syntaxi. V dnešní části se zaměříme na práci se setem.  Začněme definicí. Set je množina tuplů stejné dimenzionality. Set píšeme do složených závorek.
V minulé kapitole jsme skončili  se setem na řádcích, který vracel vyjmenované roky.
Celý select máme zde:
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]
V reálu ale nebudeme chtít všechny položky vyjmenovávat. Budeme je chtít vypsat dynamicky. Zde je potřeba zavést dva nové pojmy.
Atributová hierarchie – je taková, která je tvořena pouze atributem, má dvě úrovně souhrn All a jednotlivé položky.
Vlastní hierarchie, nebo taky víceúrovňová – má uživatelsky nadefinované úrovně, jejichž počet se může lišit.
V grafickém zobrazení v management studiu poznáte atributové hierarchie (6 čtverečků) a vlastní hierarchie (pyramida) podle obrázku – příklad z dimDate

Pokud bychom vypsali na řádky výraz {[Dim Date].[Year]} Vrátí nám pouze Allmember. Celkový souhrn.
Funkce Members, allmembers
Vrátí množinu všech členů. Pokud bychom použili funkci na řádky a brali data z atributové hierarchie „Year“
Například výraz {[Dim Date].[Year].members} vrátí all membera a všechny roky z dané atributové hierarchie. Funkce members použitá obdobným způsobem na hierarchii vlastní, vrátí všechny členy ze všech úrovní hierarchie. Tedy výraz {[Dim Date].[YQMD].members} vrátí All member, roky, kvartály, měsíce i dny v hierarchickém uspořádání.
Co když ale chceme vrátit pouze členy z některé konkrétní úrovně? Stačí do výrazu přidat Level, funkce members se poté stává nepovinnou.
Výraz na řádcích {[Dim Date].[Year].[Year].members} vrátí všechny roky, stejně jako výraz {[Dim Date].[Year].[Year]}
Funkce allmembers funguje podobně jako members. Rozdíl mezi nimi je v tom, že members nevrací kalkulované členy z dimenzí a měřítek. Allmembers vrací vše včetně kalkulovaných členů. Pokud bychom na sloupcích použili výraz {[Measures].members} získáme všechny měřítka v kostce, které načítáme ze zdroje, nikoliv členy kalkulované, vznikající v kostce (např. meziroční srovnání a jiné časové kalkulace). Výraz {[Measures].allmembers} vrátí všechny měřítka včetně těchto kalkulovaných členů. Funkce Measuregroupmeasures("Internet Sales") vrátí všechny měřítka ze skupiny měřítek „Internet sales“.
Vraťme se před funkci allmembers a measuregroupmeasures zpátky k našemu výrazu s funkcí members
Náš aktuální select:
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
       {[Dim Date].[Year].[Year]}
ON ROWS
FROM [MDX Tutorial]

Výsledek

NON EMPTY
Ve výsledku by nám mohly vadit roky, ve kterých nedošlo k prodejům. Tyto můžeme vyřadit funkcí NON EMPTY před osou, ze které chceme null hodnoty vyřadit. Chceme odfiltrovat řádky, které mají NULL pro osu měřítek. Non empty tedy napíšeme před osu řádků. S funkcí non empty pracuje i generátor MDX dotazů pro reporting services. NON EMPTY ale někdy nestačí. Nahraďme roky za produkt a přidejme další měřítko „Reseller sales“. Rok pouze zakomentuji, později se k němu vrátím (komentáře provádíme obdobně jako v SQL -- zakomentuje zbytek řádku /*komentář od do v rámci řádku, nebo přes více řádků*/)
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       --{[Dim Date].[Year].[Year]}
       [Dim Product].[Product].[Product]
ON ROWS
FROM [MDX Tutorial]

Funkce Non empty jako dvě slova vyfiltrovala záznamy, kde kombinace obou měřítek byla null. Pokud bych chěl vrátit na výstup obě měřítka, ale vyfiltrovat jen podle měřítka internet sales, mohl bych použít funkci nonempty psanou dohromady
NONEMPTY
Má dva argumenty, množinu kterou chceme filtrovat a množinu měřítek, které nemají být prázdné. V našem případě tedy
NONEMPTY(
       [Dim Product].[Product].[Product]
       ,[Measures].[Internet Sales]
       )
vyfiltruje produkty na takové, které se prodávaly přes internet bez ohledu na osu sloupců. Funkci NONEMPTY psanou dohromady někdy s oblibou kombinuji s funkcí measuregroupmeasures.
Teď víme, jak se zbavit prázdných. Vraťme se zpět k dotazu s kalendářem.Další otázka by mohla znít, jak dostanu vedle roku další sloupec? Zajímal by mě například kvartál. Prvně několik cest které nás do cíle nedovedou.
Dotaz
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Date].[Year].[Year]
       ,[Dim Date].[Quarter].[Quarter]}
ON ROWS
FROM [MDX Tutorial]

selže, protože to jsem uvedl na řádky je neplatný set. Rok a kvartál zde mají jinou dimenzionalitu, protože jsou z různých atributových hierarchií. Je to jako bychom definovali set {(x1),(x2),(z1)}
Kdybychom vzali data z hierarchie víceúrovňové, dotaz proběhne, ale roky a kvartály jsou vypsané pod sebou, ne vedle sebe.
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Date].[YQMD].[Year]
       ,[Dim Date].[YQMD].[Quarter]}
ON ROWS
FROM [MDX Tutorial]

Když chceme dostat v MDX dva sloupce vedle sebe, musíme použít operaci CROSSJOIN mezi těmito hierarchiemi.
CROSSJOIN
Databázisti zpozorněli. Crossjoin, to jsem nikdy nechtěl a v T-SQL jsem se tomu vyhýbal. Tady ne a na začátek musím říct, že CROSSJOIN v MDX nefunguje úplně stejně jako v T-SQL. CROSSJOIN = kartézský součin dvou množin, kombinace každý s každým. Když jej ale provedeme mezi atributy ze stejné dimenze, funguje zde tzv „AUTOEXISTS“. Kostka vrátí pouze platné kombinace. Tudíž nebojme se crossjoinu nic. Teď k syntaxi. Crossjoin můžeme psát jako funkci, nebo operátorem *. Pravděpodobně si zvyknete na hvězdičku stejně jako já, protože je to výrazně rychlejší zápis a také pohodlnější. Napřed crossjoin správně.
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[Year].[Year]*
       [Dim Date].[Quarter].[Quarter]
ON ROWS
FROM [MDX Tutorial]

Funguje jako víno. Hned poté crossjoin špatně
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[YQMD].[Year]*
       [Dim Date].[YQMD].[Quarter]
ON ROWS
FROM [MDX Tutorial]

Dotaz způsobí chybu a pokud si ji přečteme zjistíme, že crossjoin se nedá provádět mezi různými úrovněmi stejné hierarchie. Každá hierarchie v crossjoin funkci může být použita pouze jednou ať je atributová, nebo víceúrovňová.
WHERE - Filtrování dat
WHERE má s T-SQL opět společný pouze název. Funguje ale jako filtr v Excelu. Nedefinujete, že sloupec = hodnota, jen vybíráte položky, které chcete zobrazit (definujete set). Pokud bych chtěl zobrazit prodeje pro kategorii „Bikes“ napsal bych to takto
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[Year].[Year]*
       [Dim Date].[Quarter].[Quarter]
ON ROWS
FROM [MDX Tutorial]
WHERE [Dim Product].[Category].&[Bikes]
Bikes a accessories
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[Year].[Year]*
       [Dim Date].[Quarter].[Quarter]
ON ROWS
FROM [MDX Tutorial]
WHERE
       {[Dim Product].[Category].&[Bikes]
       ,[Dim Product].[Category].&[Accessories]}

Ale pozor, v MDX můžete mít jednu hierarchii pouze na jedné ose. V Excelu pokud byste ze seznamu polí dali roky na řádky a poté je ze se znamu dali do filtru, sebere Vám je z osy řádků. Dotaz špatně:
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[Year].[Year]*
       [Dim Date].[Quarter].[Quarter]
ON ROWS
FROM [MDX Tutorial]
WHERE
       {[Dim Date].[Year].&[2005]}
Jeden způsob, co by se dal použít by bylo na řádky vypsat roky, které chceme. V reportech se to ale řeší jiným způsobem a sice poddotazem.
Poddotazy
Poddotaz v MDX může využívat pouze osu sloupců a vytvoří subkostku (zbytek multidimenzionálního prostoru po filtraci, omezený na výběr v poddotazu)
SELECT
       {[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Date].[Year].[Year]*
       [Dim Date].[Quarter].[Quarter]
ON ROWS
FROM
(SELECT {[Dim Date].[Year].&[2005]} ON COLUMNS FROM
[MDX Tutorial]
)
      
Vnořený dotaz udělá subkostku pro rok 2005, z této vnější dotaz vybere zbytek. Takto se to používá v reportech u parametrizovaných dotazů. O tom potom :) Čili někdy v dalším dílu.
Závěr

V dnešním dílu jsme se podívaly na záladní práci s množinou. K množinám se ještě vrátíme v dalších dílech se setovými funkcemi a sety nás budou provázet po celou dobu našeho MDX putování.

Žádné komentáře:

Okomentovat