Materiál k cvičení INS týden #3, sezona 2023/2024

Načítání dat v Power BI

Aby bylo možné v Power BI provádět analytiky, sestavovat reporty a prezentovat story, je potřeba nejprve načíst data z datových zdrojů. Power BI má zabudovanou širokou paletu konektorů pro tento účel. V tomto cvičení budou blíže představené vybrané z nich. Na seznam všech konektorů se lze dostat přes kartu Home → Get data → More...

02powerbiconnectorspng Fig — Dialog s výběrem konektorů k načtení různých typů dat

Q1: Na základě názvu konektorů, u kolika z nich máte tušení, jaká data by mohly zpracovávat, příp. o kolik z vyjmenovaných služeb znáte?

Flat file (CSV, TXT)

Nejtriviálnějším datovým zdrojem pro zpracování je tzv. plochý soubor, který logicky odpovídá jedné databázové tabulce. Typickým zástupcem této skupiny jsou CSV (Comma-Separated Values) soubory, které na každém řádku mají prostým textem zapsán jeden záznam a jednotlivé hodnoty jsou, jak napovídá název, oddělené symbolem čárky. Jako oddělovač však může být obecně zvolen libovolný symbol, byť sémanticky nekorektně, a je pouze na uživateli, aby použitý symbol v datech správně rozpoznal. Občas se používá přípona TSV pro soubory, ve kterých je oddělovačem tabulátor. V české lokalizaci se velmi často setkáte se symbolem středníku jako oddělovačem z důvodu používání čárky ve významu desetinného oddělovače v číslech.

Po výběru File → Text/CSV z nabídky konektorů (předchozí obr.) bude uživatel dialogovým oknem vyzván k výběru požadovaného souboru. Dialog je klasické okno průzkumníka a lze se tedy dostat například i na síťové disky, případně zařízení na lokální síti. Po výběru jednoho souboru dojde k automatické detekci jeho struktury. V dialogovém okně aplikace nabídne výpis exportovaných názvů atributů a několik prvních zpracovaných řádek. Pokud nedošlo ke správnému parsování souboru, je možné změnit přes možnosti v horní části použitou znakovou sadu, použitý oddělovací symbol a způsob detekce datových typů. Při změně nastavení parsování se náhled přegeneruje. Stisknutí tlačítka Load provede načtení dat do interního datového modelu.

Další běžné soubory (XLS, PDF)

Jelikož je Power BI jakousi nadstavbou nad jádrem aplikace MS Excel, snad nepřekvapí, že načítání sešitů z této aplikace je poměrně jednoduché. Po volbě File → Excel Workbook a výběru příslušného souboru se zobrazí dialogové okno se seznamem jednotlivých datových listů sešitu a jejich náhled. Uživatel zaškrtne checkbox u listů, které chce importovat jako jednotlivé tabulky.

Trochu divokou kartou je možnost parsování PDF souborů File → PDF. PDF soubor je typicky spíše dokument obsahující vysázený text a množství vložených objektů - tabulky, obrázky. Právě tabulky se z PDF souboru snaží příslušný Power BI parser získat, ale z mé zkušenosti je úspěšnost relativně malá. Často dojde k různým posunům v datové matici nebo k načtení řady zbytečných prázdných hodnot. Podobně jako při načtení Excelovského sešitu má uživatel k dispozici seznam detekovaných tabulek s jejich náhledem a má možnost si jejich podmnožinu naimportovat do modelu projektu.

Další běžné soubory se zvláštní strukturou (JSON)

Aplikace Power BI nabízí poměrně jednoduchý způsob zpracování JSON souborů. Jedná se o soubory s hierarchickou strukturou vystavěnou pomocí klíč-hodnota dvojic. Hodnotou může být text, číslo, pravdivostní hodnota nebo vnořený JSON objekt. Hodnota může být prostá, nebo násobná (array). Nejpalčivější problém při zpracování tohoto typu souboru je zploštění hierarchické struktury do 2D tabulky.

Po načtení souboru se rovnou otevře transformační dialog, který by se u ostatních datových zdrojů otevřel při stisknutí tlačítka Transform Data namísto Load. Detailní představení tohoto dialogu bude v cvičení tematicky zaměřeném na transformaci a čištění dat. V případě práce s JSON souborem je podstatné, že v některých hodnotách jsou barevně zvýrazněná slovo Record a List, což značí, že na tomto místě je vnořený JSON objekt, resp. array.

Odkazovaný ukázkový soubor obsahuje informace získané z API pro hru Starcraft II, popisující 200 herních účtů. Při načtení do PowerBI se automaticky provedou operace rozkládající vstupní data do 2D tabulky. Všimněte si pojmenování sloupců, kde je uchována informace, jakou cestou se k atributu při zpracování došlo. Například sloupec dateCrawled byl v kořenu původního souboru, atribut league.league_key.season_id byl zanořen pod klíči league a league_key.

※ Posloupnost transformačních operací je vidět v pravé části Power Query Editoru. Více v samostatném cvičení.

Další neběžné konektory (složka)

Power BI nabízí mimojiné i možnost zpracovat určitou staticky definovanou složku. Příslušná volba se nachází pod File → Folder. Po výběru složky se zobrazí dialog s tabulkou obsahující metadata souborů obsažených v dané složce. Všimněte si, že jeden z atributů je datového typu Record a lze ho transformovat stejným způsobem jako JSON objekt. Kromě standardních možností Load a Transform je v tomto případě nabízena ještě možnost Combine. Touto cestou je možné, pokud datové soubory ve složce jsou homogenní, spojit obsah souborů do jednoho logického celku. Tato možnost je nicméně dostupná i jinou cestou přes Power Query.

Vzdálené zdroje (webová stránka, API)

Zajímavou možností je zpracovávat odpověď na HTTP dotaz z veřejného API rozhraní. Výsledky často mají podobu JSON souboru, který lze transformovat postupem výše. Pokud neexistuje specifický konektor, lze použít obecný webový konektor Other → web.

Ve volbě basic je možné zadat pouze URL adresu API endpointu. Po přepnutí na advanced je dále možné specifikovat hlavičku požadavku. Pro komplexnější nastavení authentikace je potřeba využít dialog Data Source Settings (viz dále).

Úkol: Načtěte v PowerBI odpověď na libovolný SPARQL dotaz.

Postup: Pokud nemáte žádný nápad, zkuste najít inspiraci například na SPARQL endpointu pro Wikidata. V horní části stránky je tlačítko Examples poskytující některé návodné dotazy. Po stisknutí tlačítka Execute Query (bílý trojúhelník v modrém čtverci) se dotaz vykoná a nad výsledky bude tlačítko Link → SPARQL endpoint, které vám umožní získat přepis URL adresy. Adresu lze použít v Power BI jako by se jednalo o běžnou stránku.

Pravděpodobně data dostaneme v XML formátu, který není triviální vhodně načíst. Jelikož se jedná teprve o úvodní cvičení, problému se vyhneme tím, že si vyžádáme odpověď ve formátu CSV.

02web sparqlpng

Stejným způsobem se lze pokusit o načtení dat přímo z webové stránky. Tato funkcionalita je relativně nespolehlivá, podobně jako PDF konektor. Vypozoroval jsem, že logika parseru spočívá v nalezení Table tagu na stránce a jeho vytěžení. Přístup je nepoužitelný u dynamicky načítaných stránek (načítání tabulky přes AJAX). Pokud je tabulka obalena JS logikou například pro stránkování, dojde pouze k načtené první (defaultně zobrazené) stránky.

Pro ilustraci tohoto problému můžete vyzkoušet některá mnou udržovaná data:

Relační databáze (PostgreSQL)

※ text je přejatý z loňska, možná už je situace jiná

Power BI nabízí konektor k řadě databází, např. Oracle, MySQL, MS SQL Server. Nicméně při pokusu o použití na univerzitních databázích se mi nepodařilo navázat úspěšně spojení. Chybová hláška se odkazovala na problém s certifikáty.

Na Stack Overflow jsem nalezl alternativní řešení, jak se k univerzitní PostgreSQL (přihlašovací informace oficiálně na předmětu DB2) databázi připojit. Řešení spočívá v nainstalování ODBC databázového driveru a použití konektoru Other → ODBC. V dialogu je poté zvolen None jakožto Data source name a v Advanced options se do Connection Stringu vloží řetězec Driver={PostgreSQL Unicode};Server=DB_IP_ADRESA. V následujícím dialogu je nutné upřesnit uživatelský účet a heslo k databázi a dále vložit upravený Credential Connection String PORT=5432;DATABASE=DB_NAME;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;HOST=DB_IP_ADRESA;COMPATIBLE=2.2.3.0;USER ID=DB_LOGIN;PASSWORD=DB_HESLO;SSLMODE=require. Pokud bylo vše vyplněno správně, objeví se seznam tabulek daného uživatele a stejně jako při importu z Excelovského sešitu lze vybrat jejich podmnožinu. Při importu se automaticky detekují relace mezi entitami a zapíší se i do datového modelu v Power BI.

Stejným postupem lze kromě celých databázových tabulek importovat jen výsledek libovolného SQL Select dotazu, který bude zadán v Power BI při vytváření spojení v textovém poli pod Connection Stringem.

Změna konfigurace konektorů v projektu

Pro načtené soubory (nebo obecně data získaná nějakým konektorem) si Power BI uchovává metadata o jeho zpracování, tedy umístění a způsob parsování. Pokud dojde ke změně dat ve vstupním souboru, projeví se to při dalším refreshi v Power BI. Negativním důsledkem toho je, že není dobré zdrojové soubory přemisťovat nebo přejmenovávat. Došlo by pak k chybě při refreshi a tím by se předčasně ukončil celý proces obnovení dat. Nepodařilo se mi zjistit, jestli je možné toto provázání se zdrojovým souborem nějakým způsobem potlačit a uchovat si v projektu dále neměnnou kopii dat.

Pokud by došlo k situaci, že je nutné aktualizovat odkaz na soubor nebo jinak změnit nastavení použitých konektorů, je k dispozici příslušný dialog na kartě Home → Transform Data (roleta) → Data Source Setting. Zde je přehled všech konektorů a tlačítky Change Source a Edit Permissions lze změnit uložené nastavení.