Microsoft nám zasa splnil jeden tajný sen. Power Query už funguje aj v SQL Server Integration Services! A takisto aj v Azure Data Factory. Zastaralé Integration Services to rozširuje o silu Power Query, ktoré tam pôvodne malo byť už v SQL Serveri 2016. A to celkom mení hru na poli ETL nástrojov.

Power Query je ultimátne ETL pre Power BI, Excel a SSAS Tabular. Malo byť neoficiálne dostupné ako dátový zdroj pôvodne už v Integration Services v SQL Serveri 2016, len na poslednú chvíľu sa po ňom zľahla zem. A Microsoft tú myšlienku znova oživil, a pridal ho tam tentokrát už oficiálne. A vyzerá to tak, že to nebude exkluzivita len pre nový SQL Server 2019, ale aj pre niektoré zo starších verzií. A svojho sa dočkala aj cloudová verzia Integration Services, nachádzajúca sa v Azure Data Factory ako „SSIS IR“ – SSIS Integration Runtime.

Upozornenie: Ide o preview funkcionalitu, ktorú snáď dokončia v dohľadnom čase. Zatiaľ funguje iba v SQL Server Data Tools, resp. Visual Studiu, a v Azure Data Factory. Vzhľadom ale na to, ako fungujú connection manageri a custom komponenty v SSIS, by to nemal byť problém rozbehať aj na serveri SSIS, po prekopírovaní a zaregistrovaní príslušných DLL súborov.

Nové možnosti

Integrácia Power Query do Integration Services totálne mení hru. Power Query má samo osebe tonu transformácií, ktoré vie spraviť s dátami. Niečo vyše 700 alebo 800. A to všetko vie ešte medzi sebou kombinovať a vrstviť. A čo tam chýba, sa dá dorobiť buď cez M skript, alebo cez integráciu s R skriptom či Pythonom. A webové služby. A podobne. Malo však 2 nevýhody – výsledky z neho sa nedali vyliať do databázy, aspoň teda nie solídnym podnikovým postupom. Vedelo síce vyliať dáta do Excelu, a jeho cloudová verzia Power BI Dataflows aj do cloudu či Azure Datalake Gen2, ale to zrovna nie je vhodné pre podnikové ETL so spracovaním miliárd riadkov. A druhou nevýhodou bola takmer nulová možnosť monitoringu a logovania pre spätné dohľadanie problémov.

Naproti tomu Integration Services mali totálne chudobnú zásobu operácií, niečo okolo 20-30. A ešte chudobnejšiu zásobu dátových zdrojov v porovnaní s Power Query. Všetko sa tam de facto muselo dorábať v .NET-e, ale zasa vedelo dáta vyliať do klasických databáz. A logovať a monitorovať operácie. A swapovať na disk, ak nebolo pre nejakú operáciu dostatok pamäte. A preto aj napriek svojej zastaranosti zostalo doteraz voľbou číslo jedna v mnohých veľkých spoločnostiach. Lebo nič lepšie proste nebolo. Ak teda nepočítame cloud a jeho tisícky služieb.

Teraz však Microsoft dal oba svety dokopy, a pridal Power Query ako nový dátový zdroj do Integration Services. A súčasne aj ako transformáciu. Čím spojil ohromnú silu Power Query, s podnikovými funkciami Integration Services. Mňam.

Ako funguje Power Query v Integration Services

Na rozbehanie dátového zdroja Power Query v Integration Services potrebujete najnovšie SQL Server Data Tools, resp. Visual Studio 2017 a novšie, spolu s nainštalovaným rozšírením pre Integration Services, verzia 15.9.2 a novšia. Dajú sa stiahnuť na tomto odkaze.

Potom stačí vytvoriť klasický projekt pre Integration Services, pridať doň Data Flow, a prejsť doňho. V zozname dátových zdrojov sa objaví „Power Query Source“:

Pridáme ho teda do Data Flowu:

…a po rozkliknutí sa otvorí okno „Power Query Source Editor“, kde je potrebné zadať M skript:

Na rozdiel od iných verzií Power Query, tu zatiaľ nie je možnosť vyklikať si transformácie. Viete si ich však vyklikať v Exceli, Power BI Desktope, SSAS Tabulare či iných klonoch Power Query, a potom prekopírujete M skript do tohto okna. My použijeme M skript pre Ultimátnu časovú tabuľku v Power Query:

Nedávajte však hneď potom tlačítko OK, pretože je potreba nakonfigurovať ešte 2 veci – Connection Managerov, a mapovanie na výstupné stĺpce.

Connection Managerov nastavíte v ľavom menu, cez položku „Connection Managers“. Tam je potrebné zadefinovať, tak isto ako aj pre iné dátové zdroje v SSIS, samostatných Connection Managerov typu Power Query, pre každý dátový zdroj, ktorého sa chytá Power Query. Je tam zabudovaná aj automatická detekcia dátových zdrojov, vpravo dole, cez tlačítko „Detect Data Source“:

Ak ho stlačíte, tak sa to zo zadaného M skriptu pokúsi vyzistiť všetky podporované dátové zdroje, a pridá ich sem. Ak tam sú, tak ich pridá, a ak tam nie sú (ako v prípade našej tabuľky, ktorá sa vyrába skriptom zo vzduchu), vypíše niečo takéto:

To v našom prípade nie je problém. Ale v prípade klasického ťahania dát z databáz tam treba pridať Connection Managerov klasickým spôsobom, ako pre všetky iné dátové zdroje v SSIS. Konfigurácia Connection Managera pre Power Query, napríklad pre Oracle, vyzerá takto:

Nastavenia sú zjavne jasné pre všetkých, ktorí už nejaký ten deň pracujú s SSIS, takže si ich nebudeme podrobne rozoberať. A ak by náhodou nie, tak detailný postup nájdete v oficiálnej dokumentácii.

Po zadefinovaní všetkých Connection Managerov a namapovaní všetkých dátových zdrojov Power Query na nich, je potrebné spraviť ešte druhý krok – namapovať výstup skriptu na výstupné stĺpce v data flowe SSIS. To spravíme opäť v ľavom menu, v položke Columns:

Ak tam nič nevidíte, tak si prejdite predchádzajúce kroky ešte raz. Niekedy tam tie stĺpce vidieť až po kliknutí na to tlačítko „Detect Data Source“.

Po namapovaní stĺpcov kliknite na OK. A hotovo 🙂

Spúšťame Power Query v Integration Services

Ideme to teda vyskúšať, a spravíme to tak, že pridáme do Data Flowu ešte ďalšiu operáciu – „Row Count“. Prepojíme ich, a potom nastavíme Data Viewer medzi ne:

Po spustení balíčka SSIS sa zobrazí debugovacie okno Data Viewera, a zobrazí naše riadky:

A tadáááá, funguje 🙂

Takže takto funguje integrácia Power Query s Integration Services. Jednoducho, ako keby to bol iný dátový zdroj. Zaujímavé ešte bude sledovať, ako to bude vyzerať vo finálnej verzii, pretože Power Query štandardne ukladá prihlasovacie údaje k dátovým zdrojom do profilu užívateľa, a balíčky SSIS dovnútra seba. Takže ak to tam integrujú takto aj pre Power Query, tak to na serveri bude bežať bez problémov. Inak to bude potrebné pre nastavenie automatického spúšťania balíčkov SSIS nastaviť po nasadení aj na serveri SSIS. Ja osobne dúfam v tú jednoduchšiu, prvú možnosť.

To je k tejto novinke všetko, a nám nezostáva nič iné, len dúfať, že to stihnú dokončiť ešte do vydania SQL Servera 2019. A že to bude dostupné aj pre staršie verzie SSIS, aspoň od 2016 vyššie. Tak či inak, už teraz je nadovšetko jasné, že to bude pecka 🙂

Michal Chmelár

Autor, tréner a expert na PowerPivot, Power BI a jazyk DAX. Založil som tento web, aby som pomohol dostať PowerPivot a Power BI do širšieho povedomia, a aby som ľuďom ukázal, že aj komplexné analytické problémy idú riešiť jednoducho. Po nociach vzývam Majstra Yodu a tajne plánujem ovládnutie vesmíru.