Analysis services – výběr vhodného typu modelu

Do SQL Serveru 2008 byla volba jednoduchá, Analysis services podporovaly pouze model multidimenzionální a nebylo moc z čeho vybírat. Časem se to ale začalo komplikovat, prvně přišel PowerPivot poté Tabular a jak to tak bývá, čím víc možností, tím těžší výběr. Klienti a účastníci kurzů se mě často ptají, potažmo i historicky ptali v dobách začátků PowerPivot. Je PowerPivot lepší než SSAS? To teď máme zrušit staré multidimenzionální modely a přebouchat veškerou logiku do PowerPivotu? A obdobné dotazy dostávám s tabularem. Má cenu předělávat stávající multidimenzionální řešení do tabularu? Nejen na tyto dotazy by měl odpovědět tento článek.

Průřez typy

Multidimenzionální model

Dostupnost – dostupný od verze SQL Serveru 2005 v té podobě v jaké jej vidíme dnes v edici standard a vyšších

Historicky vzniknul v době, kdy ještě nebyla tak dostupná operační paměť, tudíž se hledaly způsoby jak efektivněji uložit data na disku. Pro dosažení vysokého výkonu se přišlo s úložištěm MOLAP, do „živých“ dat se dá koukat díky úložišti ROLAP. Coby nejstarší model je nejlépe zdokumentovaný, nejrozšířenější, umí řekl bych 100% funkcí a je to ten ke kterému se srovnává.

Jedinou nevýhodou multidimenzionálního modelu je jeho relativní složitost. Při tvorbě je potřeba nastavit docela dost věcí, aby byl model nastaven „optimálně“. Pro výpočty, dotazování, zabezpečení se používá jazyk MDX. Naučit se MDX si žádá nějaký čas a přece jen pro vývojáře zvyklé pracovat s placatými tabulkami a teorií množin, jedná se o přechod bolestný :) I proto jsem začal psát na blogu MDX tutorial, jehož první díl najdete zde http://www.neoral.cz/2015/05/mdx-tutorial-1-uvod.html

Tudíž jedinou nevýhodou multidimenzionálního modelu je jeho relativní složitost a z toho plynoucí vysoké nároky na vývojáře, aby to bylo udělané dobře, v případě některých kalkulací aby to vůbec bylo udělané :) I když i to se dá vyřešit stylem. Pokud to nevím, tak jim řeknu, že to nejde :)

PowerPivot

PowerPivot poprvé spatřil světlo světa někdy kolem roku 2010 v dobách Excelu 2010 a SQL Server 2008 R2. Jendá se o zjednodušené Analysis services v Excelu, která se dá provozovat i serverově. Jedná se na pozadí také o tabulární model. Funguje to tak, že v Excelu vytvoříte model a o tento se můžete podělit tím, že hotový dokument včetně vizualizací vypublikujete do dokumentové knihovny SharePointu. Licenční nároky na serverové řešení jsou z těch vyšších. Budete potřebovat minimálně BI edici SQL Serveru a Sharepoint Enterprise (o zobrazení se starají Excel Services). Alternativou může PowerBI v Office 365

Při tvorbě modelu se data načtou do paměti, kde se nad nimi provádí operace. Pro tvorbu výpočtů se používá jazyk DAX, který je pro tvorbu výpočtů výrazně jednodušší než MDX (zápis vypadá jako Excelový vzorec). Klíčová omezení jsou maximální velikost souboru, který se dá otevřít v prohlížeči tedy 2GB. Tedy Modelem je vlastně Excelový soubor a tento je také jednotkou zabezpečení. Nedají se zde tvořit role kde v rámci jednoho modelu uvidí různí uživatelé různá data jako v případě serverových modelů. Dále je zde problém s kompatibilitou verzí. Model, který vytvoříte v Excelu 2010 vás po otevření v Excelu 2013 vyzve k upgradu. A zpětně ve 2010 již model nemůžete editovat. Stejně tak člověk bez nainstalovaného doplňku nemůže data z modelu číst. PowerPivot se dá načíst do Visual studia jako tabulární projekt a tímto závislost na nutnosti mít nainstalovaný doplněk odpadá, dají se řešit role, partitioning... Funkční omezení jsou shodná s Tabularem

Tabulární model

SQL Server 2012 přišel s možností nainstalovat tabulární instanci analysis services. Je postaven na stejných principech, jako PowerPivot, ale nepotřebujete k němu SharePoint a jedná se o serverové řešení bližší multidimenzionálnímu modelu. Ve zkratce Tabular kombinuje jednoduchost PowerPivotu a výhody serverového modelu. Pro rychlost dat načítá data do paměti (v surové podobě jak jsou ve zdroji, neaplikují se zde žádné agregace). Typ úložiště může být buď in memory, nebo direct query,případně kombinace obojího. Tabular umí cca 80% funkcí, kde funkční omezení jsou následující.

·        podpora pouze vazby 1:N, dá se ohnout aby dělal vazbu 1:1 pouze s jednosměrnou filtrací.

·        parent child hierarchie sice jsou k dispozici, ale pouze přes DAX funkce. Kde musíte pro kažodu úroveň zvlášť napsat vzorec. Tedy pro 50 úrovní by se jednalo o 50 vzorců, které byste museli napsat. Oproti tomu Multidimenzionální model si je schopen rekurzivně načíst všechny úrovně sám

·        hůře se zde pracuje s týdenní časovou logikou, což je dáno způsobem, jakým se definuje kalendář (americký vs evropský týden)

·        nedají se vytvářet počítané členy v dimenzích (chybí mi časové kalkulace v dimenzi, které fungují na všechny měřítka)

·        nedají se zde nastavovat defaultní membery, uživatel by mohl omylem zagregovat i to, co nemá

·        nejde provádět Writeback

·        překlady dat se musí provádět přes DAX

·        KPI indikátory nemají možnost definovat trendové šipky, jestli se jedná o nárůst, nebo pokles

Shrnutí, porovnání, scénáře

Multideminzionální model umí všechno, Tabular s PowerPivotem ne. První stěžejní otázka zda má smysl předělávat stávající fukční vyhovující řešení z multidimenzionálu do PowerPivotu/Tabularu? Nedává to absolutně žádný smysl. Nepřinese to nic navíc, naopak můžeme přijít o některé klíčové funkce a můžete dokonce pohořet s výkonem. Tabular drží data 1:1 vůči zdroji v paměti a teprve pak nad nimi provádí výpočty.

Nad PowerPivotem a Tabularem má smysl uvažovat v případě nového vývoje. Po PowerPivotu sahám v situacích, kdy potřebuji model, který má v rukou uživatel a je schopen si ho sám spravovat, případně aktualizovat data. Přece jen na co dělat pro tří členný tým serverový model. A kdyby řešení mělo přerůst z lokálního řešení na serverové, můžu jej jednoduše zmigrovat do tabularu. PowerPivot taky s oblibou používám na prototypování, abych s klíčovým uživatelem probral funkčnost. Přece jen vývoj jde rychle a čím dřív dostanu zpětnou vazbu tím lépe. Problém je, že dokud uživatel nevidí, nedá dobrou zpětnou vazbu.

Pokud chci serverové řešení kde bude kontrola na straně IT/BI týmu. Rozhodovací strom vypadá zhruba následovně (ne nutně v daném pořadí)

Jakou má klient verzi a edici SQL Serveru. Pokud se jedná starší 2012 a/nebo standard edici, je „odsouzen“ k multidimenzionálnímu modelu. Což nemusí být nutně špatně

Nebude mi chybět některá z vyjmenovaných funkcí? Zejména defaulty, many to many vazby, práce s týdnem, parent child hierarchie?

Velikost dat, jsem schopen nacpat celý model do paměti a když to udělám, bude to dost rychlé? Multidimenzionální model přestože bere data z disku může být rychlejší, protože data předagregovává. Z 10 milionů řádků pro jeden den se stane jedna buňka. Sečíst 365 buněk za rok z disku může být rychlejší, než udělat stejnou operaci na 3,65 miliardami řádků v paměti. Tabulární instance se bude taky delší dobu restartovat, musí data načíst z disku do paměti.

Pokud jsem došel až sem, můžu si zvolit v čem to budu dělat raději :)

Závěr

Tabulární model a PowerPivot multidimenzionální model nenahrazují. Mohou být vhodnou alternativou v závislosti na aplikaci, kterou řešíte. Každopádně jsou jednodušší na naučení se. Nicméně pokud jste se již prokousali „multidimenzionálním peklem a MDX jazykem“ můžete u něj klidně zůstat. Jste-li SSAS nepoznamenaní, tabular může být alternativa. Alternativa, která má však své hranice o kterých je dobré vědět. Osobně v řešeních kde potřebuji vědět, že je neprůstřelné sahám zpravidla po multidimenzionálním modelu, pro „aplikační“ lokální modely po PowerPivotu.