Materiál k cvičení INS týdny #6-8, sezona 2023/2024

Datové modelování v Power BI

Vytvoření kvalitního a účelného datového modelu je jednou z nejdůležitějších úloh, který může datový analytik vykonat pro své kolegy a uživatele. Vhodný model usnadní orientaci v dostupných datech a značně zjednoduší tvorbu reportů a jejich interpretaci.

Následující text je pojat formou postupného rozšiřování příkladu z předchozích týdnů.

📝 Jako výchozí datový soubor je použit osoby.csv, ve kterém jsou jednotlivé záznamy o pacientech s COVID-19 v ČR. V datasetu jsou vybrány sloupce datum, vek, pohlavi, okres_lau_kod.

📝 Druhým datovým souborem, s kterým se bude pracovat, je číselník NUTS/LAU kódů cz-nuts prevodnik.pdf.

Vytvoření číselníku krajů a okresů

Spojení do jedné tabulky

Jak již bylo demonstrováno v Načítání dat v Power BI, lze načíst některé PDF soubory obsahující tabulky a pokusit se data z nich zpracovat. V případě tohoto souboru je potřeba extrahovat dvě (případně tři, pokud chceme i hlavičku) části původní tabulky a spojit je do jedné.

Momentálně jsou data rozdělena do tří query a je potřeba je vhodně sloučit do jedné. Zkontrolujte, že všechny query obsahují stejný počet sloupců a jejich význam je stejný.

04appendpng

※ Původní query jsou živé a nelze je odstranit. Změna v původních query se projeví i v odvozené query.

※ Pokud by bylo potřeba spojit tabulky vedle sebe (operace JOIN v SQL), je k dispozici operace Merge Queries.

Přejmenování sloupců

Momentálně jsou názvy sloupců na druhé řádce sloučené tabulky. Je potřeba nejprve odstranit přebytečné řádky a následně názvy sloupců vynést z datové matice.

Omezení dat pouze na kódy krajů a okresů

Pro další práci je potřeba převést číselník do stavu, že primárním klíčem bude sloupec s LAU kódy obsahující pouze okresy, resp. kraje. U každého bude informace o jeho názvu a názvu nadřazeného kraje.

Následujícím krokem je odfiltrování záznamů, které neodpovídají okresu nebo kraji (to jsou takové, které nemají formát CZxxx nebo CZxxxy). Nejjednodušším kritériem bude počet znaků kódu, nicméně tato podmínka není v nabízeném seznamu filtrů. Bude potřeba založit nový odvozený sloupec, který naplníme vhodným výrazem pro výpočet délky kódu.

※ Pokud by došlo k problému při výpočtu, bude v příslušné buňce červený text Error a po jeho označení se zobrazí dole detailnější popis chyby.

※ Filtrace je stále platná, protože v posloupnosti operací proběhla v době, kdy sloupec existoval.

Q1: Zkuste pro vytvoření sloupce s délkou kódu použít možnost Add Column → Column From Examples. Jaký je výsledek?

Doplnění názvů krajů pro okresy

V dalším kroku je potřeba rozkopírovat název krajů do příslušných záznamů okresů. Abychom si ulehčili práci, je vhodné použít nástroj Transform → Fill → Down, který za každou prázdnou buňku doplní hodnotu nejbližší buňky s existující hodnotu ve stejném sloupci v řádku nad ní. Aby toto v našem případě zapracovalo, je potřeba nahradit prázdný text v buňkách za klíčové slovo "null".

Manuální změna kódů

Posledním problémem je neaktuálnost dat. Kódy CZ061x a CZ062x již neexistují a byly nahrazeny kódy CZ063x a CZ064x. Jelikož nejsou u nástroje Replace Values k dispozici regulární výrazy, je potřeba si pomoci vytvořením odvozeného sloupce a volbou vhodné funkce Text.Replace(input, old, new).

※ Výraz nejprve nahradí všechny výskyty CZ062 za CZ064 a následně je aplikována stejná funkce nad novými daty pro záměnu výskytů CZ061 za CZ063.

Uložení změn

Nyní by v prostředí PowerBI měla v pravé části být vidět datová tabulka s názvem NUTS číselník obsahující 3 atributy. Kromě toho jsou v modelu vidět i pomocné tabulky. Tyto tabulky lze skrýt kontextovou volbou Enable load nad příslušnou query v Power Query Editoru.

Zavedení vztahu mezi tabulkami

Ze znalosti dat víme, že mezi načtenými tabulkami existuje logická vazba. Konkrétně se jedná o NUTS/LAU kód, který se vyskytuje v obou tabulkách. Abychom tuto expertní znalost předali Power BI, je potřeba přejít do Model view (3. ikona v levém sloupci v hlavním Power BI oknu). Zde vidíme schema ERA modelu. Pro zavedení relace je potřeba přetáhnout název atributu z jedné tabulky na příslušný atribut v tabulce druhé. Většinou se tímto relace vhodně nastaví. Manuálně lze v případě problémů změnit zvolené atributy a kardinalitu relace.

PowerBI by měl automaticky detekovat, že se jedná o 1:N relaci a nastavit ji jako aktivní.

Po zavedení relace je možné ve vizualizaci použít atributy z obou tabulek a příslušné spojení (JOIN) proběhne na pozadí. Lze tedy například vizualizovat počet případů dle názvu kraje či okresu.

Q2: Sestavte vizuál vykreslující počty případů dle názvu kraje. Jsou data vykreslena správně?

Q3: Porovnejte, jak se změní vizuál, pokud by se vazba nastavila jako neaktivní.

Hierarchie v atributech, drill down

Atributy kraj a okres mají v tomto případě zvláštní vzájemný význam - hierarchii. Podobně jako Power BI vytváří automaticky hierarchické členění pro atributy typu datum (rok, kvartál, měsíc, den v měsíci), lze manuálně podobnou hierarchickou strukturu vytvořit i pro tento případ. Vyvoláním kontextové nabídky nad hierarchicky nejvyšším atributem (v tomto případě kraj) a výběrem New Hierarchy se založí pseudoatribut ve stejné tabulce, do kterého lze tažením přiřadit hierarchicky nižší atributy.

※ I když je tato možnost na všech pohledech, z nějakého důvodu funguje přetahování atributů do hierarchie pouze v model view.

04hierarchypng

Pokud vytvořený pseudoatribut použijeme ve vizualizaci, v její horní části se zobrazí několik nových ikon. První z nich zapíná tzv. drill down mód, což je běžná OLAP operace. V tomto režimu se po kliknutí do vizualizace provede drill down na zvolené podmnožině dat. Například lze takto detailněji zkoumat rozdělení pacientů po jednotlivých okresech Plzeňského kraje. Alternativou k tomuto (ikona dvou paralelních šipek dolů) je hierarchicky sestoupit u všech hodnot, nikoliv pouze u vybrané.

Vyřešení pražského problému

Patrně jste ve vizualizaci zaznamenali výskyt sloupce s popiskem (Blank) při vizualizaci dle krajů či okresů. Jedná se o problém v datech, který nehledě na to, které strana má morální odpovědnost za nesrovnalost, je nutné námi vyřešit. V datech o COVID se udává kód CZ0100 ve smyslu "okres Praha" a tento kód není číselníkem respektován. Ten uvádí pouze kódy CZ0101, CZ0102,... pro městské části Praha 1, Praha 2, ...

Jelikož nemůžeme jednoduše v PDF souboru zavést kód CZ0100, ani není možné rozlišit v COVID datech, do jaké městské části nakažený spadá, je nejpřímočařejší operace založit novou tabulku o rozměrech 1x3 obsahující identické názvy sloupců jako číselník a jednu řádku popisující CZ0100.

Power Query Editor → Home → Enter Data. Po vytvoření je nutné tabulku připojit k stávajícímu číselníku Power Query Editor → Home → Append Queries.

Q4: Jak se změní vizualizace po přidání nového záznamu do číselníku? Kolik záznamů je nyní v kategorii (Blank)?

※ Jednotlivé záznamy započítané ve vybraném sloupci lze zobrazit kliknutím na sloupec, nebo obecně prvek vizuálu, a zvolením kontextové nabídky Show data point as table. Tím se otevře nová tabulka, z které se lze vrátit zpět do vizualizace tlačítkem Back to report.

Hvězdicové schema, fakta a dimenze

Při řešení datových skladů často dochází, mnohdy spontánně, k tvorbě ERA modelu s paradigmatem hvězdicového, resp. vločkového schematu. V tomto paradigmatu existuje jedna centrální tabulka faktů, které je obklopena tabulkami dimenzí. V našem případě roli faktové tabulky zastupuje seznam COVID případů a první z dimenzních tabulek je dimenze lokační - číselník krajů/okresů. Pokud bychom chtěli pokračovat s modelováním v tomto paradigmatu, je vhodné agregovat data v tabulce faktů. Zde se konkrétně nabízí možnost uvádět souhrnné počty pacientů dle dne, věku, pohlaví a okresu. Transformaci do této podoby je možné provést vhodným provedením Power Query Editor → Home → Group By.

Druhým krokem by bylo převedení hodnot datumu, věku a pohlaví jako reference do dimenzních tabulek.

Kalendář

Běžným postupem bývá pro projekt vytvořit jednu sdílenou dimenzní tabulku obsahující jako záznamy jednotlivé dny a příslušná metadata k nim — například o jaký kvartál se jedná, jestli to je víkend či svátek, atd. Některá z těchto metadat jsou implicitně dostupná v Power BI samotnou volbou datového typu datum.

Pro vyzkoušení vygenerujeme kalendář s informací o tom, zda daný den byl víkendový. Ze znalosti kontextu lze tušit, že tato informace může být v analýze těchto dat významná, protože se o víkendech méně testuje.

Na hlavní obrazovce v report view je možnost založit novou tabulku Modeling → New Table. V příkazovém řádku je potřeba definovat, o jakou tabulku se jedná. Vhodným pozorováním nabízených funkčních volání (případně konzultací manuálu) lze nalézt podezřele pojmenovanou funkci Calendar(). Ta slouží k vygenerování tabulky o jednom sloupci, ve kterém bude posloupnost datumů dle volby uživatele. Konkrétně příkaz cal = CALENDAR("2020-01-01", "2023-12-31") vygeneruje 1461 záznamů odpovídajících datům v letech 2020-2023.

S tabulkou je možné dále pracovat. Při volbě Table Tools → New Column je možné zadat vzorec podobný excelovému víkend = WEEKDAY([Date], 2) > 5, který vrátí pravdivostní hodnotu pravda pro soboty a neděle.

Po nastavení relace v schematu je možné s atributem víkend pracovat ve vizualizacích.

Q5: Vytvořte sloupec den_v_týdnu obsahující pořadové číslo dne v týdnu. Přidejte možnost zobrazení textového přepisu těchto hodnot.

Možné způsoby řešení Číslo dne v týdnu lze získat úpravou předchozícho výpočtu pomocí funkce WEEKDAY(...)
Textový popisek lze získat vytvořením nové tabulky 7×2, do které data ručně vepíšeme a v ERA modelu připojíme na sloupec `den_v_týdnu`.
Případně lze vytvořit nový sloupec ke stávající tabulce a použít funkci FORMAT(datum, "dddd")

Q6: Vytvořte vizuál popisující počet případů dle dne v týdnu a barevně rozlišující víkendové dny. Prvky vizuálu intuitivně seřaďte.

Možné řešení je na obrázku níže. Řazení prvků lze provést v kontextové nabídce Sort Axis schované za ikonou nad vizualizací. Řadit lze jen podle dat obsažených ve vizualizaci, v tomto případě počtu případů a názvu dne v týdnu. K řazení podle pořadí dnů v týdnu je potřeba přidat do vizualizace pomocný sloupec obsahující ordinální čísla dnů v týdnu a řadit dle něj. Jelikož se vnitřně jedná o agregaci několika stejných čísel, je potřeba u pomocného sloupce volit funkci Min nebo Max.

04weekdayspng Fig — Vizualizace počtu případů dle dne v týdnu. Barevně označená ikona umožňující nastavit pořadí sloupců. Dále označen prostor, do kterého umístit data, podle kterých má být řazeno.

Věkové skupiny

U věků bývá častou operací zařazení záznamu do nějaké věkové skupiny. Tento koncept se často označuje v angličtině jako binning. V Power BI lze tento koncept realizovat pomocí vytvoření nového sloupce, který bude obsahovat výslednou věkovou skupinu. Resp. vhodnějším přístupem bude chápání celočíselného věku jako indexu do dimenzní (převodní) tabulky s věkovými skupinami.

Prvním krokem je založení nové tabulky Modeling → New Table a pomocí volání funkce GENERATESERIES(0, 120, 1) lze poměrně jednoduše naplnit sloupec nově vytvořené tabulky posloupností čísel 0-120 s inkrementem 1.

※ Volba 120 je zvolena jako horní mez věku, protože v datech se vyskytuje nejvyšší hodnota 120.

Přes kontextové menu nad atributem nově vytvořené tabulky v pravé části Power BI rozhraní lze volbou New Group otevřít dialog pro nastavení způsobu seskupení numerických hodnot. Power BI nabízí dva způsoby seskupení — binování a vlastní seznam. Při volbě Group type: Bin lze dále nastavit, kolik rovnoměrných skupin se má vytvořit, případně jak velký má být interval čísel ve skupinách. V našem případě dává smysl vytvořit 12 skupin s intervaly 10 let.

Druhou možností je Group type: List, kdy lze ručně vybrat skupiny z výčtu všech možných hodnot. Bylo by tedy možné tímto způsobem například vytvořit skupiny 0-18, 19-25, 26-40, 41-60, 61-120.

Skupiny lze vytvořit i pomocí vlastní funkce při vytvoření nového sloupce.

ERA model

Výsledný model by mohl vypadat následovně. 04erapng

Státní svátky v kalendáři

Kromě informace o tom, zda je den všední nebo víkendový, by mohlo být zajímavé mít informaci o státních svátcích a jiných dnech volna. V tomto případě je potřeba využít externí zdroj informací a vytvořit tabulku, která bude obsahovat dny, které jsou nějakým způsobem výjimečné. Tabulka bude následně napojena na tabulku kalendáře.

※ Jelikož byla tabulka kalendáře vytvořena pomocí DAX příkazu, nelze s ní pracovat v Power Query editoru.

📝 Datový zdroj: https://www.kurzy.cz/kalendar/statni-svatky/2023/

Načtení dat

Načteme zdroj jako webovou stránku pomocí Home → Get Data → Web. V dialogu zadáme URL zdroje a potvrdíme. V dialogu ohledně přihlášení zvolíme Anonymous a potvrdíme. V náhledu jsou požadovaná data v Table 1.

04svatky1png Fig — Načtená tabulka, z které budou dále použity první dva sloupce.

Úprava dat

V prvním sloupci jsou data v textovém formátu 31.12.2023, neděle. Prvním krokem tedy bude převod do formátu datumu. Nejprve rozdělíme hodnotu na dvě části podle čárky.

V tomto kroku možná došlo k problému se zpracováním textu, když se Power Query editor snažil pochopit text jako datum ve formátu MM.DD.YYYY. V takovém případě je potřeba u datového typu zvolit poslední možnost Using locale... a v dialogu vybrat Date a Czech (Czech Republic).

Zobecnění pro více let

Jelikož jsou kalendářní data některých svátků různá v různých letech, je potřeba využít tento postup i pro ostatní roky. Jednou z cest by mohlo být query rozkopírovat a upravit URL, z které se data načítají. Následně všechny tyto query spojit dohromady. Všimněte si, že URL obsahuje přímo rok, pro který se mají data načíst.

https://www.kurzy.cz/kalendar/statni-svatky/{rok}/

V tomto případě je ale vhodnější vytvořit parametr, který bude obsahovat číslo roku, pro který se mají data načíst, a s ním pracovat v query pro usnadnění práce.

04svatky2png

Takto upravenou query převedeme na funkci, která bude přijímat parametr rok a vracet tabulku s daty pro daný rok.

※ Po této operaci bude založena nová složka v seznamu query obsahující původní tabulku, parametr a funkční volání.

Nyní je potřeba vytvořit v Power Query editoru tabulku, která bude obsahovat seznam roků, ke kterým se mají dohledat informace o svátcích. Nejjednodušší bude vytvořit seznam čísel, seznam následně převést na tabulku a pro každou položku zavolat nově vytvořenou funkci svatky.

V tento moment by měla tabulka obsahovat dva sloupce — rok a Custom. Ve sloupci Custom jsou získané tabulky s daty o svátcích pro jednotlivé roky.

Připojení k tabulce kalendáře

V model view v hlavním okně projektu lze propojit vytvořenou tabulku s tabulkou kalendáře. Jedná se o 1:1 vazbu mezi tabulkou vytvořenou v Power Query editoru a tabulkou kalendáře vytvořenou pomocí DAX příkazu.

Vizualizace nákaz v zahraničí do mapy států

Shape map vizuál

Power BI nabízí vizuál určený pro tvorbu choropleth map, který se nazývá Shape map. Vizuál potřebuje informaci o území, ke kterému má data přiřadit, a o hodnotě, které tam na barevné škále vykreslit. Ze základu je možné pracovat pouze s omezeným výběrem územních jednotek, nicméně lze dodat definici jiné mapy z externího zdroje.

※ Vizuál je dostupný jako preview feature a může být potřeba ho v instalaci PowerBi povolit v nastavení. File → Options and settings → Options → Preview features → Shape map visual.

Q7 Zkuste vykreslit Shape map za použití sloupce nakaza_zeme_csu_kod a počtu případů. Jaké území je vykresleno?

Vykreslené území lze změnit v nastavení vizuálu Format your visual → Visual → Map settings → Map type. Kromě předdefinovaných možností je možné vybrat i vlastní definici mapy (Custom map a příslušný soubor v Add a map type).

※ Dle GitHub Copilot LLM je "tento vizuál dostupný pouze v Power BI Desktop a není možné jej publikovat do Power BI Service." Tvrzení nebylo ověřeno, ale obecně mohou nastat problémy s vizuály, které využívají externí zdroje dat.

Vlastní definice mapy

Vizualizace vyžaduje podkladové mapy v GeoJSON formátu. Taková data lze najít například v projektu World Atlas TopoJSON. Pro naše účely postačí data v měřítku 1:110m.

📝 Datový zdroj: countries-110m.json

V readme projektu je zmínka o tom, že země jsou identifikované podle číselného kódu ISO 3166-1 numeric.

Připojení dat

Nyní je potřeba propojit stávající covid data s nově načtenými geodaty. V našem případě obsahuje sloupec nakaza_zeme_csu_kod kód příslušné země, ale je v nevhodném formátu ISO 3166-1 alpha-2 (např. IT místo 380). Je tedy potřeba vytvořit nový sloupec, který bude obsahovat kód země ve správném formátu, případně připojit převodní tabulku.

Nejbližší zdroj dat pro tento problém by mohl být oficiální číselník ISO 3166-1, který je dostupný na https://www.iso.org/obp/ui/#search/code/. Ten ale kvůli svému provedení nelze v PowerBI zpracovat.

Alternativou by mohlo být důvěřovat Wikipedii nebo projektu udržujícímu informace z číselníku.

📝 Datový zdroj: ISO-3166-Countries-with-Regional-Codes (all)

※ Alternativním řešením by bylo nová data připojit k tabulce osoby v Power Query Editoru pomocí Home → Merge Queries. V dialogu by se označily primární a cizí klíče a vybral vhodný outer join operátoru. Následně by se odstranily přebytečné sloupce.

Vizualizace

Nyní by již mělo být možné data vykreslit.

04mapapng Fig — Choropletová mapa zobrazující importované počty nákaz dle země původu. Vpravo jsou stejná data zobrazena v seřazené tabulce.

※ U kartogramů bývá zvykem přepočítávat hodnoty na počet obyvatel, rozlohu nebo jinou charakteristiku daného území. To v rámci tohoto cvičení nebude demonstrováno.

Q8: Proč je jako 12. nejčastější země původu nákazy ze zahraničí uváděno Česko?