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