Častokrát sa stretávam s problémom, že dáta, ktoré potrebujem analyzovať, sú síce uložené v databázach, ale v niektorých prípadoch nie sú pokope na jednom mieste. Sú napr. nasekané do jednotlivých databáz po jednotlivých rokoch, a predtým, ako sa dajú zanalyzovať, je potrebné spojiť dáta z viacerých databáz do jednej tabuľky. To sa dá spraviť pomocou jazyka SQL pri vyťahovaní dát, ale nie každému sa to chce písať. A keď nebodaj nie sú v správnom formáte a treba ich ešte predtým transformovať, tak je to cesta akurát tak pre masochistov. Preto si ukážeme, ako to vieme spraviť elegantne pomocou Editora dotazov, resp. Power Query v rámci Power BI. A nielen pre databázy, ale aj pre ľubovoľný iný dátový zdroj (Excel, Web, textové súbory, atď.).

Využijeme na to možnosť tvorby vlastných funkcií v Power Query, ktoré sa dajú spúšťať voči každému riadku zo zadaného číselníka. Má to tú výhodu, že môžete využiť zároveň s tým všetky funkcie na transformáciu, prípravu a spájanie dát, ktoré má v sebe Power Query, resp. Power BI, a zároveň celý takýto postup transformácií použiť postupne v cykle na každú databázu – a výsledku spojiť dáta z viacerých databáz do jednej tabuľky. Tým sa okrem iného vyhnete komplikovaným riešeniam, a množstvu zbytočne opakovaných importov, transformácií, a v konečnom dôsledku dátovému modelu plného kopy nepotrebných tabuliek.

Základom je vyrobiť si načítanie a transformácií dát z jednej tabuľky, a následne sparametrizovanie takéhoto postupu a zabalenie do vlastnej funkcie. A potom vytvorenie číselníka so zoznamom databáz, z ktorých treba tú istú tabuľku načítať/stransformovať, a vykonanie tejto funkcie oproti všetkým riadkom tohto číselníka. Pričom na každom riadku sa použije aktuálna hodnota z riadku ako vstup do našej funkcie.

Ako prvý krok si načítame dáta z SQL Servera, z prvej databázy, a z tabuľky, ktorú chceme spracovať, do Editora dotazov v Power BI. Kliknite na Získať údaje -> Databáza SQL Servera, a pripojte sa k SQL Serveru. V mojom prípade mám tieto 3 databázy, kde v každej je tabuľka objednávok pre daný rok:

Vyberieme teda tabuľku z prvej databázy, a namiesto tlačidla Načítať dáme tlačidlo Upraviť:

To nám otvorí Editor dotazov, aby sme si vedeli ešte dopredu upraviť dáta predtým, ako sa načítajú do dátového modelu. Načítaná tabuľka bude vyzerať takto:

Dáta sú skrátené na zopár riadkov kvôli tomu, aby sa to zmestilo na malé obrázky. Nemusíte sa ale báť, funguje to spoľahlivo aj na desiatky miliónov riadkov.

Ako ďalší krok si otvoríme Rozšírený editor:

To nám zobrazí zdrojový kód operácií v jazyku M pre aktuálne otvorený dotaz (lebo všetky novo načítavané tabuľky do modelu vytvoria v Power Query nový dotaz):

Ten si sparametrizujeme – v časti „Name=”data_2015″“ odstránime názov databázy, a nahradíme ho neskôr premennou. Táto premenná bude pochádzať z parametra novo vytvorenej funkcie. Funkciu vytvoríme tak, že zabalíme celý dotaz do funkcie takto (zmeny sú podčiarknuté červenou farbou):

Po stlačení tlačidla Hotovo sa dotaz zmení na funkciu, čo sa prejaví iným zobrazením, a zároveň symbolom „Fx“ vedľa názvu pôvodného dotazu:

Ak chcete, môžete si funkciu otestovať zadaním parametra a stlačením tlačidla „Vyvolať“.

My však potrebujeme túto funkciu vykonať oproti všetkým riadkom číselníka s názvom všetkých databáz, odkiaľ potrebujeme zlúčiť dáta. Preto si tento číselník vytvoríme cez funkciu Zadať údaje:

V tomto okne premenujte stĺpec na názov „Databaza“, a zadajte doňho pod seba názvy všetkých databáz. Potom premenujte naspodu okna aj tabuľku napr. na „ZoznamDatabaz“, a kliknite na tlačidlo OK:

To vytvorí číselník ako ďalší dotaz v Editore dotazov:

Pre tento číselník teraz spustíme našu funkciu. Tu si treba dať pozor, že funkcia sa nevolá názvom, ktorý ste uviedli v Rozšírenom editore, ale názvom dotazu, do ktorého sme tú funkciu zabalili. Našom prípade sa tá funkcia volá „objednavky“.

Spustíme teda funkciu pre každý riadok číselníka. Choďte v hlavnom menu do záložky Pridať stĺpec a kliknite na tlačidlo Vlastný stĺpec:

Zadajte názov nového stĺpca, pod neho zadajte vzorec vo formáte „=funkcia(stlpec)“ a stlačte tlačidlo OK:

Je možné, že na Vás vyskočí nasledujúce upozornenie:

V tom prípade kliknite na „Pokračovať“ a vyplňte nasledujúce okno napr. takto (hodnotou „Verejné“, inak nebudete môcť kombinovať údaje ďalej, vivat regulácie EU – musíte to vyplniť správnou hodnotou, ak chcete spojiť dáta z viacerých databáz), a stlačte tlačidlo Uložiť:

K číselníku sa pridá ďalší stĺpec s tabuľkou s dátami z každého vyvolania funkcie:

Tú tabuľku ale musíme ešte rozbaliť na jednotlivé stĺpce. Kliknite teda na malú ikonku v pravom hornom rohu stĺpca „tabulka“, potom zrušte začiarknutie pri nastavení „Použiť ako predponu pre pôvodný názov stĺpca“ a stlačte OK:

Daný stĺpec sa rozbalí na všetky tie stĺpce, ktoré boli v pripájanej tabuľke. Zároveň sa pôvodné riadky z číselníka zduplikujú pre každú vloženú tabuľku toľkokrát, koľko bolo riadkov v danej pripájanej tabuľke. Vďaka tomu budete vedieť, ktorý riadok došiel z ktorej databázy. Výsledok bude vyzerať napr. takto:

Na konci stačí výslednú tabuľku už len premenovať, a v hlavnom menu ísť do záložky Domov a kliknúť na Zavrieť a použiť:

A máte hotové dáta v dátovom modeli, a môžete veselo analyzovať všetko naprieč všetkými databázami:

Toto riešenie má 2 výhody:

• Nemusíte do dátového modelu importovať X tabuliek z X rokov a potom ich spájať cez DAX do výslednej tabuľky, takže bude zaprvé jednoduchší, a zadruhé bude zaberať iba polovicu pamäte
• Každý rok nemusíte myslieť na to, ktoré všetky tabuľky treba doimportovávať, spojiť s predchádzajúcimi a pod., ale stačí do pôvodného číselníka iba dopísať ďalší riadok – názov novej databázy pre daný rok. A to je obrovské množstvo ušetrenej práce a času. Stačí len jednorazovo spojiť dáta z viacerých databáz, a potom už len udržiavať číselník

Takže, aj takéto veci sa dajú stvárať v Power Query, resp. Editore dotazov. Ide síce o mierne zložitejšie riešenie, ako sa dajú spojiť dáta z viacerých databáz, ako keby ste to robili klasickým spôsobom po jednej tabuľke. Na druhej strane sa vyplatí v tom, že je do budúcnosti oveľa lepšie udržiavateľnejšie a odbremení Vás od kopy nezáživnej práce. A vyhnete sa chybám typu, že zabudnete naimportovať jednu z X tabuliek, keď to po ďalšom a ďalšom roku budete robiť zas a zas. A aj to je jeden zo znakov profesionálneho riešenia – nielen to spraviť, ale aj spraviť to tak, aby s tým neskôr nebolo kopec práce. A práve preto je Power Query naozajstná „power“ technológia, s ktorou sa dajú robiť divy 🙂