Keď začnete v Power BI pracovať s väčšími dátami v jednotkách až desiatkach GB, tak narazíte hneď na niekoľko problémov. Najprv narazíte na veľkosť RAMky. Pri publikovaní do cloudu či na vnútrofiremný Power BI Report Server narazíte na limity veľkosti publikovaných súborov. Pri aktualizácii dát narazíte na príliš dlhé časy aktualizácie. A takisto aj nemožnosť riadne využívať pokročilejšie zabezpečenie dát. Preto si teraz ukážeme, ako to elegantne vyriešiť premigrovaním dátového modelu do SSAS Tabularu, resp. ako vytiahnuť dátový model z Power BI do SSAS Tabularu, a prerobiť report v Power BI Desktope tak, aby ste ho nemuseli zdĺhavo vytvárať odznova v SSAS Tabulare.

Pre tých, čo ste ešte nepracovali s SSAS Tabularom, tak si prečítajte najprv tento článok. V skratke, SSAS Tabular je serverový PowerPivot, a je použitý okrem iného aj v Power BI na zabezpečenie funkcionality dátového modelu aj Power Query. Preto je aj nasledujúca migrácia relatívne priamočiara.

Celý postup migrácie predpokladá, že máte nainštalovaný SSAS Tabular vo verzii 2017. Ak máte starší SSAS Tabular, tak transformácie z Power Query Vám nebudú fungovať, a budete ich musieť presunúť inde, ale vymyslieť iné náhradné riešenie. Plus pripomínam, že ide o neoficiálny spôsob, takže nie je garantované, že to takto bude fungovať vždy, resp. že to bude bezchybné.

V minulosti existovalo ľahšie riešenie ako toto popisované nižšie, pretože súbor PBIX obsahuje v sebe zálohu databázy podkladového SSAS Tabularu. Tú stačilo zobrať a obnoviť na SSAS Tabulare. Tento súbor je však už po novom skomprimovaný algoritmom XPress9 od Microsoft Research, a keď ho chcete použiť, tak si musíte zohnať na to dekompresor. Čo nie je zrovna jednoduché.

Využijeme teda štandardné nástroje v SQL Serveri a Power BI, aby sme vedeli vykonať operáciu úspešne.

Začneme teda s naším vzorovým súborom Power BI. Povedzme, že toto je náš report, ktorý má desiatky GB dát, a teda nie je publikovateľný ani do cloudu (jedine že by ste mali Power BI Premium), ani na vnútrofiremný Power BI Report Server. Riešením je teda vytiahnuť dátový model z tohto súboru PBIX na SSAS Tabular, a použitie Live pripojenia z Power BI do SSAS Tabularu. O Live pripojení píšem viac v mojej novej knihe o Power BI.

Otvoríme si teda tento vzorový súbor v Power BI Desktope. Následne si cez DAX Studio zistíme, na ktorom porte načúva SSAS Tabular zahrnutý v Power BI Desktope. V mojom prípade to bol tentokrát port 56182.

Potom si otvoríme najnovšie SQL Server Management Studio, a pripojíme sa v ňom na Analysis Services, na server s názvom “localhost:56182“, kde 56182 je číslo portu z predchádzajúceho kroku:

Po pripojení sa nám v Object Exploreri objaví podkladový SSAS Tabular, spolu s databázou s dátovým modelom, tabuľkami, a všetkým ostatným okolo:

Teraz by teoreticky stačilo spraviť backup tejto databázy, a potom jej restore na normálny SSAS Tabular. Problém je ale v tom, že Power BI Desktop využíva špeciálny druh SSAS Tabularu v tzv. powerpivotovom, resp. SharePoint móde, a z neho sa nedá spraviť záloha.

Preto využijeme ďalšiu vlastnosť SQL Server Management Studia, a to možnosť dať si vyskriptovať databázu. To nám vytvorí skript vo formáte JSON, ktorý potom vieme spustiť voči cieľovej inštancii SSAS Tabularu, a to nám tam vytvorí identickú databázu. Spravíte to tak, že kliknete pravým tlačítkom myši na názov databázy (to šialene dlhé divné číslo pod uzlom Databases v Object Exploreri), a tam dáte Script -> Script Database as -> CREATE To -> New Query Editor Window:

To vyrobí zhruba takýto nejaký skript:

V tomto skripte sú uložené všetky nastavenia databázy. A aj niekoľko ďalších vecí, ktoré Power BI interne z Tabularu využíva, ale nezobrazuje užívateľom, ako napr. tabuľky začínajúce na LocalDateTable. Tak to neplánujete využívať, tak to z výsledného Tabularu odmažte.

Zoberte teda tento skript, a pripojte sa v SQL Server Management Studiu na tú inštanciu SSAS Tabularu, do ktorej chcete premigrovať tento dátový model. Potom kliknite v Object Exploreri pravým tlačítkom myši na názov inštancie, a vyberte New Query -> XMLA (aj keď skript je v jazyku JSON, ale zadáva sa to tadeto):

Otvorí sa prázdne okno na zadanie dotazu. Doňho nakopírujte JSON skript vytvorený vyššie, a nezabudnite zmeniť názov databázy v časti create -> database -> name:

…na nejaký zmysluplnejší, napr. na “NasModel“:

Následne skript spustite, buď stlačením klávesu F5, alebo kliknutím na tlačítko Execute niekde hore v menu Management Studia. Po úspešnom dokončení skriptu by ste mali dostať takéto hlásenie, ktoré v prípade SSAS hovorí, že všetko prebehlo v poriadku (tak to proste vyzerá hlásenie “OK” v SSAS, aj keď to tak na prvý pohľad nedáva zmysel):

Ak nemáte SQL Server 2017, ale len 2016, tak budete musieť pred spustením skriptu spraviť ešte nasledujúce zmeny:

• zmeniť vlastnosť compatibilityLevel na začiatku skriptu na 1200
• odstrániť všetky bloky pomenované “variations“:
  
• odstrániť všetky tabuľky, ktorých názov začína na LocalDateTable a DateTableTemplate
• odstrániť všetky bloky v časti “relationships“, ktoré definujú prepojenia medzi odstránenými tabuľkami v predošlom bode a zvyškom modelu

Ak sa vám teda podarilo úspešne spustiť skript, a vytvoriť databázu v SSAS Tabulare, či už 2017 alebo 2016, tak v Object Exploreri by ste mali vidieť novo vytvorenú tabularovú databázu, so všetkými tabuľkami aj pripojeniami:

Databáza ale ešte nie je funkčná, a treba ju najprv sprocessovať. Vo väčšine prípadov vám to ale nepôjde hneď, pretože napr. pripojenia na databázy využívajú typ pripojenia pod aktuálne prihláseným užívateľom, čo server nepodporuje. Bude preto potrebné upraviť ešte pripojenia.

Pripojenia upravíte tak, že si v Object Exploreri, pod uzlom Connections, postupne rozkliknete každé jedno z nich, a v jeho vlastnostiach zmeníte vlastnosť Impersonation Info na takú hodnotu, ktorá bude fungovať vo vašom prostredí. Ja som to pre jednoduchosť zmenil na “Use the service account“, čiže aby sa to pripájalo do databázy účtom, pod ktorým beží samotný SSAS Tabular:

V SSAS Tabulare 2016 ešte budete musieť zmeniť aj Connection string-y, a to tak, aby boli platné. Pretože to čo tam vidíte – provider “Microsoft.PowerBI.OleDb” a mashup XXXX, je v skutočnosti serverové Power Query, ktoré ešte v 2016-ke nebolo. A v tej vlastnosti mashup sú uložené transformácie Power Query v jazyku M, skomprimované a zakódované do base64 (zaujímavé riešenie, ale budiž). A teda nemôžete ho využívať, prídete o transformácie z Power Query, a namiesto toho sa musíte pripájať priamo na dané databázy, odkiaľ ste pôvodne čerpali dáta. Preto upravte vo verzii 2016 tieto connection stringy na niečo iné, čo vo vašom konkrétnom prípade bude fungovať.

Potom vyskúšajte sprocessovať databázu. Kliknite pravým tlačítkom na názov databázy, potom na Process:

…a v ďalšom okne na OK:

Ak vám to prebehne, tak super. Ak nie, tak skúste popátrať, v čom je problém. Prípadne si stiahnite najnovšie SQL Server Data Tools, a v nich si dajte vytvoriť nový projekt pre SSAS Tabular s možnosťou “Import from Server (Tabular)“, kde vyberiete databázu na servri, ktorú ste vytvorili. Tam skúste cez editor Power Query poupravovať operácie tak, aby vám to zbehlo. A ak by to ešte aj tak nezbiehalo, tak si pozrite tento článok od objaviteľa pôvodného riešenia, možno tam ešte nájdete niečo čo vám pomôže.

Keď sa vám to podarí rozbehať a sprocessovať, tak nastáva posledná úloha – napojiť to na report v Power BI Desktope.

Najprv si otvoríme čistý Power BI Desktop. Potom ho napojíme cez Live pripojenie na SSAS Tabular, a grafy/vizuály/tabuľky zo starého reportu prerobíme do nového.

Otvorte si teda nový Power BI Desktop. V hlavnom menu vyberte Načítať údaje -> Databáza služby SQL Server Analysis Services:

Potom sa otvorí okno, kde zadajte názov svojho servera SSAS Tabular, a vyberte voľbu “Pripojiť naživo“, a stlačte OK:

V ďalšom kroku vyberte svoj tabulárny model, a kliknite na OK. To nám pripojí Power BI Desktop na našu “vzdialenú” inštanciu SSAS Tabularu. Čiže dátový model aj dáta budú na serveri, kde je zvyčajne kopec RAMky aj procesorov. A report v Power BI si ich podľa potreby bude od neho vyžiadavať. To má okrem iného výhody v lepšej manažovateľnosti aj bezpečnosti dátového modelu, a viete nastaviť aj automatickú aktualizáciu dát na serveri (buď pre celý dátový model, pre tabuľky, alebo aj len pre tie partície tabuliek, pre ktoré sa zmenili dáta). Viac o tomto režime píšem vo svojej knihe.

Grafy a ostatné veci z pôvodného reportu budete musieť naklikať a nastaviť pre všetky grafy odznova. Power BI Desktop momentálne nepodporuje kopírovanie grafov medzi dvoma Power BI Desktopmi. Teoreticky by to mohlo ísť hacknutím súboru PBIX, ale to si asi nechám na dlhé zimné večery, keď náhodou nebudem vedieť čo s časom 🙂

Týmto by mala byť dokončená migrácia dátového modelu na SSAS Tabular. Na prvý pohľad to možno vyzerá komplikovane, ale ak v tom už máte prax, tak sa to dá zvládnuť celé do 5-10 minút. A to je oveľa pohodlnejšie a rýchlejšie, ako vytvárať všetko niekoľko hodín odznova. Navyše v editore SSAS Tabularu vo Visual Studiu, ktorý nie je zrovna pohodlný. Ale, samozrejme, ak chcete, môžete to vytvoriť celé odznova, ak nemáte na práci nič lepšie 🙂

Takto to teda ide spraviť jednoducho a pomerne rýchlo. A aj vďaka tomuto vám už bráni o jednu vec menej pri spracovaní veľkých dát v Power BI 🙂