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.


Žádné komentáře:

Okomentovat