18. srpna 2015

MDX tutorial 6. – Setové operace

V minulém díle tutorialu jsme se zaměřili na vybrané setové funkce. Čas od času člověk potřebuje provést i nějakou operaci mezi množinami. Nejprve trocha teorie a potom i pár praktických příkladů a modelových situací kde s množinovými operacemi přijdu nejčastěji do styku.
Připomínám, že pokud tutorial náhodou nečtete od začátku aktuální verze projektu je k dispozici 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 dále uzpůsobován potřebám tutorialu.
Na obrázku máme množiny A a B

Jaké máme množinové operace?
Operace
Popis
Funkce
Operátor
Sjednocení
Když dáme dohromady množinu A i B
Union
+
Rozdíl
Z množiny A vyloučíme množinu B
Except
-
Průnik
Co mají množiny společného
Intersect
Kartézský součin
Každý prvek množiny A s každým prvkem množiny B
Crossjoin
*

Sjednocení / Union({Set1},{Set2},[All])
Věřím, že SQL znalí čtenáři už operaci UNION někdy použili v SQL. Kdo se s touto úlohou zatím nesetkal, pak úkolem sjednocení je vrátit položky které jsou v obou množinách bez duplicit (Union) případně s duplicitami (Union All)
V minulém článku http://www.neoral.cz/2015/08/mdx-tutorial-5-setove-funkce.html jsme si ukázali funkci Topcount. Chtěl bych provést sjednocení množiny Deseti nej modelů produktů dle prodejnosti přes Internet a množiny top deseti přes obchodníky (Reseller).
Dva samostatné dotazy, které vrátí top 10 přes daný kanál prodeje máme zde
SELECT
       {}
ON COLUMNS,
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Reseller Sales]
       )
ON ROWS
FROM [MDX Tutorial]

SELECT
       {}
ON COLUMNS,
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Internet Sales]
       )
ON ROWS
FROM [MDX Tutorial]
Sjednocení se dá provést buď operátorem +, nebo funkcí Union. Plusko je jednodušší, tak používám primárně to. Po funkci sahám pouze pokud potřebuji „Union All“ Dotaz pluskovou syntaxí by vypadal následovně
SELECT
       {}
ON COLUMNS,
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Reseller Sales]
       )
       +
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Internet Sales]
       )
ON ROWS
FROM [MDX Tutorial]
Získáme

Rozdíl množin/ Except({Set1},{Set2},[All])
Co je v první množině a není ve druhé množině? Rozdíl se dá nejjednodušeji napsat znaménkem –
Takže na otázku co se prodávalo dobře přes Reseller a ne přes Internet si odpovíme
SELECT
       {}
ON COLUMNS,
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Reseller Sales]
       )
       -
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Internet Sales]
       )
ON ROWS
FROM [MDX Tutorial]

Průnik množin / Intersect({Set1},{Set2},[All])
Co se prodávalo dobře jak přes Internet tak přes Reseller se nedá napsat znaménkem, musíme jej vypsat jako funkci
SELECT
       {}
ON COLUMNS,
intersect(  
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Reseller Sales]
       )
       ,
       Topcount(
       [Dim Product].[Model].[Model]
       ,10
       ,[Measures].[Internet Sales]
       )
       )
ON ROWS
FROM [MDX Tutorial]

Kartézskému součinu jsme se věnovali v 2. díle tutorialu http://www.neoral.cz/2015/05/mdx-tutorial-2-prace-se-setem.html
Tolik k syntaxi základních operací, pojďme se podívata na
Aplikace, tipy, triky
Velmi častým požadavkem je, vyloučit vybrané skupiny/položky z reportu. Například chtěl bych vypsat prodeje všech modelů všech kategorií kromě Accessories „Bikes“ a to tentokrát například na řádcích
SELECT
       {[Measures].[Reseller Sales]}
ON COLUMNS,
non empty
       {
       /*Všechny kategorie*/
       [Dim Product].[Category].[Category]-
       /*Vyloučená množina*/
       [Dim Product].[Category].&[Bikes]
       }
       *[Dim Product].[Model].[Model]
ON ROWS
FROM [MDX Tutorial]
Nebo mě zajímají prodeje všeho, kromě kategorie Bikes, tudíž vyloučit množinu v klauzuli where
SELECT
       {[Measures].[Reseller Sales]}
ON COLUMNS
FROM [MDX Tutorial]
WHERE -{[Dim Product].[Category].&[Bikes]}

Případně mě zajímají všechny úrovně produktové hierarchie kromě nejnižšího levelu Product
SELECT
       {[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Product].[Category-Subcategory-Model-Product].members -
       [Dim Product].[Category-Subcategory-Model-Product].[Product]
ON ROWS
FROM [MDX Tutorial]
Chceme řadit data abecedně, jen chceme kategorii „Další“, aby se zobrazovala na konci nikoliv v prostředku dle abecedního řazení. Můžeme simulovat na kategorii Bikes kterou napřed – vyhodíme a poté ji plusem přidáme zpátky na konec :)
SELECT
       {[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       [Dim Product].[Category].[Category]-
       [Dim Product].[Category].&[Bikes]+
       [Dim Product].[Category].&[Bikes]
ON ROWS
FROM [MDX Tutorial]
Závěr

Bez setových operací v závěru kapitoly se v praxi nedá dost dobře fungovat. Tudíž je vhodné se je naučit. Časem se ale budete plusy a mínusy bouchat do MDX skriptů tak automaticky, že Vám to ani nepřijde

11. srpna 2015

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.