26. května 2015

MDX tutorial – 3. počítané členy a currentmember

V minulém díle jsme si ukázali základní práci se setem (http://www.neoral.cz/2015/05/mdx-tutorial-2-prace-se-setem.html). Dnes se podívejme na  počítané členy a jak funguje jedna z nejdůležitějších funkcí v MDX currentmember
Začněme dotazem
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Počítané členy můžeme v MDX používat na různých úrovních.Na úrovni dotazu (v SSMS), připojení (sešit Excelu), uložený výpočet dostupný uživatelům kostky. Osobně preferuji postup: Napsat výraz s výpočtem v SSMS, na úrovni dotazu odladit, odladěný výpočet uložit v kostce. Počítané členy můžeme uložit do measures, nebo do dimenzí.
Základní počítaný člen v Measures
Syntaxe počítaného člena na úrovni dotazu (Query scoped) uloženého do Measures vypadá následovně:
WITH MEMBER [MEASURES].[x] AS výpočet
Název hierarchie Measures je zde nepovinný. Pokud nezadáme jinak, výpočet se uloží právě do measures. Stačí napsat:
WITH MEMBER [x] AS výpočet
Celý výraz vracející konstantu 1 by tedy mohl vypadat:
WITH MEMBER x AS
       1
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Hranaté závorky jsou nepovinné pokud název kalkulovaného členu není vyhrazené slovo, neobsahuje mezery, nezačíná číslem, ...
V počítaných členech pracujeme s tuply a sety, které jsme si definovali v první kapitole (http://www.neoral.cz/2015/05/mdx-tutorial-1-uvod.html). Pokud bych chtěl spočítat celkové prodeje, stačí sečíst buňku internetových prodejů (popsanou tuplem Internet sales), s buňkou prodejů přes obchodní síť (popsanou tuplem Reseller sales). V tomto případě můžeme obě buňky popsat pouze použitím osy měřítek.
Výsledný dotaz s komentáři
WITH MEMBER x AS
       [Measures].[Internet Sales] -- tuple 1
       + -- aritmetická operace
       [Measures].[Reseller Sales] -- tuple 2
,FORMAT_STRING = "currency" -- formátování
SELECT
       {
       [Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]
       ,x -- výpis počítaného měřítka
       }
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Format string kosmeticky naformátuje výsledek do mřížky management studia. Další formáty, které bychom mohli použít jsou formáty psané „Visual basic stylem“ (stejně jako vlastní formáty čísel v Excelu) např  „0“ pro celé číslo, „0.00“ číslo na dvě desetinná místa, „#,##0.00“ číslo na dvě desetiny s oddělenými řády čárkou atd. Kromě VB stylů můžeme napsat „standard“ pro standardní číslo (2 desetinná místa bez oddělení řádů), „percent“ pro procento, „currency“ pro měnu, a další.
Currentmember
Jednou z nejčastěji používaných funkcí v MDX je „currentmember“. SSAS databáze je hierarchická a chceme-li se v ní navigovat (nahoru, dolů, do stran, ...), musíme odněkud začít. A začneme právě od current membera. Naučit se pracovat s touto funkcí je KRITICKÉ.
Kterýkoliv člen hierarchie (member), má následující popisné vlastnosti (které můžeme vrátit stejnojmennou funkcí).
name – jméno
unique_name/uniquename – celý klíč ve tvaru [Dimenze].[Hierarchie].&[Klíč], (name column v SSAS)
member_key – klíč (key column vlastnost v SSAS)
Currentmember správně
Pokud bych do těla počítaného členu napíšete následující výraz, získáte seznam unikátních jmen členů atributové hierarchie Category
WITH MEMBER x AS
       [Dim Product].[Category].currentmember.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
Currentmember špatně
Nejčastější chyba při použití funkce currentmember, kterou je potřeba zničit hned v zárodku. Skoro stejný výraz jako minule...
WITH MEMBER x AS
       [Dim Product].[Category].[Category].currentmember.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]
... způsobí chybu:
#Error Query (2, 2) The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.
Co mu vadí? Přebývá název levelu. Červená část je navíc a špatně
[Dim Product].[Category].[Category].currentmember.uniquename
Pokud si jsme schopni ukázat správně na current member, můžeme se mimo jiné navigovat nahoru na parent (nadřazený prvek v hierarchii). Následující výraz vrátí unikátní jméno nadřízeného prvku „currentmembera“ v hierarchii, kterým je all member atributové hierarchie Category
WITH MEMBER x AS
       [Dim Product].[Category].currentmember.parent.uniquename
SELECT
       {x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category].[Category]}
ON ROWS
FROM [MDX Tutorial]

Následující výraz vrátí kromě internetových prodejů po zkompletování tuplu s odkazem na parent prodeje nadřazeného prvku v hierarchii
WITH MEMBER x AS
/*Tuple vracející prodeje nařízeného prvku v hierarchii*/
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent -- nadřízený prvek
       ,[Measures].[Internet Sales] --měřítko
       )
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Pokud bychom chtěli dát dohromady procentuální podíl aktuálního člena v rámci nadřízeného prvku v hierarchi stačilo by napsat:
WITH MEMBER x AS
       [Measures].[Internet Sales] -- aktuální prodeje
       /
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) -- prodeje nadřízeného prvku v hierarchii
,Format_string = "percent"
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Je potřeba ošetřit chybu pro allmembera, který nad sebou nemá žándý „parent“, abychom nedělili prázdnou buňkou. Zde musíme použít syntaxi, která vývojářům zvyklým na T-SQL, nejde přes klávesnici. Můžeme porovnávat s NULL hodnotou přes operátor =.
WITH MEMBER x AS
iif((
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) = NULL – test, je-li dělitel null
       ,NULL-- pokud ano null, jinak...
       ,[Measures].[Internet Sales]
       /
       (
       [Dim Product].[Category-Subcategory-Model-Product].parent
       ,[Measures].[Internet Sales]
       ) -- ... dělení stejně jako minule
       )
,Format_string = "percent"
SELECT
       {[Measures].[Internet Sales]
       ,x}
ON COLUMNS,
NON EMPTY
       {[Dim Product].[Category-Subcategory-Model-Product].members}
ON ROWS
FROM [MDX Tutorial]
Currentmember nás bude provázet v našem MDX putování odteď napořád :) Vstřebejme tedy jeho použití a ještě se v dnešním článku podívejme na:
Počítaný člen uložený v dimenzi
který je v principu podobný, počítanému členu uloženému do measures. Odvoláváme se na buňky popsané tuplem, případně množiny buněk v setu. Jediný rozdíl je v tom, kam počítaný člen uložíme.  Jako umístění volíme parenta, pod kterým chceme počítaný člen zobrazit. Výhodou počtítaných členů v dimenzi je to, že fungují pro všechny měřítka. Tohoto ĺze s výhodou využít například pro časové kalkulace (meziroční porovnání by se hodilo jak pro internet sales, reseler sales, tak další měřítka). O tom ale někdy potom :)
Zápis obecně
WITH MEMBER [dimenze].[hierarchie].&[parent].[název počítaného členu] as výpočet
Příklad tvorby počítaného člena, který sečte prodeje kategorie accessories a bikes pro libovolné měřítko uložený pod All memberem.
WITH MEMBER [Dim Product].[Category-Subcategory-Model-Product].[All].[Core products] AS
       (
       [Dim Product].[Category-Subcategory-Model-Product].[Category].&[Bikes]
       --tuple pro Bikes, když nepoužiji měřítko, vezme měřítko v aktuálním řezu
       )
       +
       ([Dim Product].[Category-Subcategory-Model-Product].[Category].&[Accessories])
       --tuple pro kategorii Accessories
SELECT
       {[Measures].[Internet Sales]
       ,[Measures].[Reseller Sales]}
ON COLUMNS,
NON EMPTY
       {
       [Dim Product].[Category-Subcategory-Model-Product].[Category].allmembers
       --funkce members by vrátila pouze perzistentní členy, bez kalkulovaných
       }
ON ROWS
FROM [MDX Tutorial]

Závěr:

V dnešní kapitole jsme si ukázali, jak vytvořit jednoduché počítané členy v measures i v diemnzích. Ukázali jsme si, jak pracovat s funkcí currentmember. Také jsme si řekli, kterých nejčastějších chyb se při použití funkce currentmember vyvarovat. 

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í.

11. května 2015

Co přinese SQL Server 2016

4-8.5 proběhla v Chicagu konference Microsoft Ignite. Zde bylo mimo jiné odtajněno, co nového přinese nová verze SQL Serveru, jejíž veřejně dostupné CTP by mělo vyjít v létě. Máme se na co těšit a to nejen z pohledu Business Intelligence. Pokud byste chtěli podívat na Datasheet a Blog k nové verzi, případně se přihlásit k upozornění, že CTP je dostupné pro veřejnost: http://tinyurl.com/menb7t4
Nová verze staví na třech pilířích.

Deeper Insights Across Data (volně přeloženo: jak hlouběji prohlédnout do vašich dat)
Začínám z této strany, protože pro BI přináší nejvíce novinek. Můžeme se těšit na:
Vylepšené Reporting services (SSRS)
Jsem doslova nadšený. Od 2008 R2 se SSRS nepohnuly příliš z místa a vcelku pravidelně slýchám dotazy, zda s nimi bude Microsoft něco dělat. Případně jestli jsou SSRS mrtvá technologie? PowerView přece jen ani zdaleka nedokáže SSRS plnohodnotně nahradit. SSRS se údajně dočkají výrazné modernizace a bohatých vizualizačních možností. SSRS is not dead!! Stokrát sláva. I když dokud neuvidím, neuvěřím :)
Mobilní BI
Microsoft koupil společnost Datazen, leadera na trhu s mobilním BI a aplikacemi jak pro iOS, Android tak Windows. Můžeme se tedy můžeme těšit na pořádné integrované mobilní BI. Stávající zákazníci s Enterprise edicí můžou Datazen používat již dnes pokud splní podmínky (http://tinyurl.com/pjvyocu) mohou používat Datazen od verze 2008 nahoru.
Rozšíření SSIS
Další strhující novinka ze které skáču po strop. V SSIS půjde používat mimo nástrojů z verzí minulých PowerQuery. Takže co si odladíme na desktopu v Excelu půjde automatizovat v SSIS. Další výhodou je obrovská škála konektorů pro PowerQuery.
PowerQuery pro analýzy a reporting
Budeme moci načítat data do modelů a reportů opět přes PowerQuery.
Polybase
Správa a dotazování do relačních i nerelačních dat jednoduše přes T-SQL bylo zatím běžným smrtelníkům zapovězeno. Polybase byla dostupná pouze v rámci appliance Parallel Data Warehouse a kombinovala data z relační databáze s daty z Hadoopu. Nyní se dostupnost rozšíří i na SQL Server.
Vestavěné „Advanced analytics“ do SQL Serveru
Akvizice Revolution analytics přináší algoritmy pro prediktivní analýzu a „R“ do SQL Serveru. Pokud jste datový vědci, udělejte si sami. Pokud nejste, stále je tu Microsoft Azure Marketplace.
Vylepšení MDS (Master Data Services)
Až 15krát rychlejší add-in. Lepší kontrola nad rolemi a více administrátorů. Opakovaná použitelnost entit napříč modely. To jsou novinky, které slibuje datasheet. MDS jsem si oblíbil již ve verzi 2012 a pokud se budou hýbat v 2016 rychleji, bude to jedině dobře.
Mission Critical Performance
Real-time Operational Analytics & In-Memory OLTP
In-Memory OLTP technologie v některých případech zvyšuje výkon transakčních systémů i 30krát. Pro analýzy bude možno nad in memory tabulkami navíc tvořit column store indexy a výkon tentokrát analytického systému to posune dál.
 Vysoká dostupnost
Další vylepšení pro AlwaysOn. Až 3 synchronní repoliky pro automatický failover napříč doménami. Podpora pro DTC & SSIS. Automatický failover na základě zdraví databáze.
Zabezpečení
Funkce Always Encrypted posouvá dál možnosti TDE (Transparent Data Encryption). Novinkou je, že data budou posílána zašifrována i po síti, což bylo slabé místo TDE.
Row Level Security – řízení přístupů na úrovní řádků. Funkce je již dostupná v SQL Azure Database.
Query data Store
Umožní ukládat, monitorovat a ladit exekuční plány s celou historií.
Další:
Nativní JSON, Temporal Database, Dynamic Data Masking, Enhanced Database Caching
Hyperscale Cloud
Přináší vylepšení pro hybridní scénáře, zjednodušení v některých oblastech a konzistenci z pohledu vývoje, správy, přístupů
Hybridní scénáře pro SSIS
Integrace Azure Data Factory s SSIS, konektivita SSIS na cloudové zdroje.
PowerBI nad on-premise daty
Možnost reportovat přes PowerBI nástroje přestože citlivá data neopustí firmu (šifrovaně pomocí Always Encrypted)
Stretch database
Odlejte si historická data do cloudu, ať nezabírají místo u Vás. Půjde kombinovat s Always Encrypted a Row Level Security.
Další:
Vylepšení Backup To Azure, zjednodušená migrace on-premise SQL Serveru a přidání Azure Repliky v Always On
Závěr
Microsoft toho slibuje opravdu hodně nového. Po delší době se dá říci, že zde máme opět BI edici. Osobně se nejvíce těším na překopané SSRS, PowerQuery v SSIS a reportingu, Operational Analytics a mobilní BI, no vlastně všechny BI věci :)

V Azure navíc přibyde k aktuální SQL Database (database as service) Data Warehouse as service, v němž půjde škálovat síla výpočetního výkonu v okamžicích, kdy to potřebujete. Novinky vypadají zajímavě, realitu uvidíme v létě a poté v produkci :)

8. května 2015

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.