Denodo Platform a virtualizace dat v praxi

Naše zadání vykrystalizuje z následujícího příběhu odehrávajícího se jednoho čtvtečního odpoledne v Praze u společnosti realizující výstavbu dopravních komunikací. Je 15:50 a analytik BI Honza už odpočítává minuty do celé, aby mohl pádit domů. Bohužel k jeho smůle přichází v 15:51 e-mail od vedení, že do zítra 10:00 musí být hotový report sumarizující počty realizací silnic dle krajů. A to nejen v Čechách, ale také za Moravu.

Náš analytik moc dobře ví, že kdyby se týkal úkol jen Čech, vyjede data z Oracle databáze, ve které jsou vedeny realizace projektů za tuto geografickou oblast a má vyhráno. Bohužel tak jednoduché to není a musí kontaktovat Pavla z BI oddělení z Brna, kde realizace dopravních komunikací za Moravu vedou v PostgreSQL. 

Po hysterickém šoku :D, kdy už nikdo nepočítal s žádným úkolem do konce týdne, se oba shodnou, že není čas realizovat přelití dat z jednoho do druhého databázového systému (v podstatě ETL nějakou datovou pumpou) a vše vyřeší formou excelů, které po vygenerování výstupů „splácnou“ dohromady. Jelikož Pavel má domluvené rande 😊, které v žádném případě nemůže zrušit, domluví se, že finální výstup po obdržení dat za Moravu jde za Honzou. Když však Honza prochází oba dva excely, k úděsu zjistí, že místa realizace silnic nejsou vždy na úrovni krajů, ale dalších jednotek (obcí, měst, okresů atd.). Aby toho nebylo málo, v PosgreSQL evidují tyto jednotky pouze kódem (např. CZ052, což představuje Královéhradecký kraj) bez jmenného názvu. Aby Honza vůbec mohl uvažovat o tom, že je schopný docílit požadovaného výstupu, musí ještě doplnit kódy o jmenný popis, neboť takto není v jeho silách identifkovat, v jaké „horní dolní“ na Moravě se dopravní komunikace vůbec realizovaly.

Pro lepší názornost a ověření finálního výstupu zjednodušíme výstup pouze na čtyři zobrazené realizace dopravních cest. Jak je vidět dle barevného vyznačení, žlutě a zeleně podbarvené řádky představují data z databáze Oracle. Analogicky modře a oranžově vyznačené řádky pak PostgreSQL, kterých chybí ony názvy míst realizace ve sloupci L.

Protože právě dochází ve firmě k implementaci nástroje pro pokročilou analýzu dat na bázi AI a LLM, obrátí se Honza na DATERu v časově prekérní situaci (termín do zítra), zda nemají v portfoliu nástroj, který by pomohl v řešení takového úkolu. Dle prvních informací padla volba právě na Denodo Platform, které je přesně pro takováto zadání jak ušité. A kdyby tento nástroj používali na BI oddělení, měl by Honza za deset minut hotovo a dle očekávání v celou pádit domů 😊Až tak rychle je možné tento úkol vyřešit …

Ještě, než se dostaneme k ukázce samotné realizace našeho zadání, je dobré zmínit, v jakém nástroji (odkud jednotlivé screeny jsou) budeme realizovat řešení. Jedná se o Denodo Virtual DataPort Administration tool, což je desktopový nástroj pro obsluhu Denodo Platform. Stejného výstupu je možné dostáhnout přes Denodo Design Studio, což je webová aplikace, díky které se Denodo Platform stává dostupné odkudkoliv. V neposlední řadě ještě zmíním, že k provedení úkolů vždy dojdeme přes sql syntaxe. Opětovně stejného výstupu je možné docílit přes drag and drop funkcionalitu, kdy uchopením, přenesením a klikáním realizujeme jednotlivé dílčí kroky.

Nyní už však přejděme k realizaci našeho zadání. Víme, že výsledný výstup se skládá v podstatě ze tří zdrojů dat. Dva představují databáze (Oracle a PostgreSQL), ve kterých jsou vedeny informace o realizacích dopravních komunikací, jejich délek, míst jejich vybudování apod. (prostě strukturovaná data v normalizované formě). Jelikož informace o místě realizace jsou v PostrgeSQL uvedeny jen v podobě kódu (např. CZ052), je zapotřebí třetí zdroj, který bude představovat „překladač“ onoho kódu na název místa realizace (tedy k CZ052 jsme schopni doplnit Královéhradecký kraj). Relevantním zdrojem těchto dat je Český statistický úřad, z jehož stránek je možné stáhnout soubor struktura_uzemi_cr ve formátu .xlsx (konkrétně sekce Základní územní číselníky na území ČR a klasifikace CZ-NUTS).

Abychom se všemi těmito zdroji mohli pracovat, musíme je načíst do Denodo Platform. V sekci Administrace uživatel vytvoří v terminologii Denodo Platform novou databázi (osobně bych to spíše popsal jako založení nového projektu či virtuálního datového poolu). 

Do této nové databáze se pak provede import datových zdrojů. Jelikož dva jsou standardní databáze komunikující na bázi JDBC konektoru, uživatel vybere stejnojmennou možnost. Následně vyplní údaje z connection stringu dané databáze. Jako u jiných nástrojů pomocí testového tlačítka ověří napojení obou datových zdrojů.

Po úspěšném připojení obou datových zdrojů musí uživatel načíst tabulky, aby s nimi mohl dále pracovat. Pomocí volby Create base view vytvoří „otisk“ zdrojových tabulek. Při jejich vytváření může měnit datové typy podle potřeby a tím lépe ladit „podvozek“ pro výsledný výstup.

Jelikož s view mají pracovat zejména uživatelé z business úrovně, pomocí možnosti selection (uživatel s potřebnými právy nebo administrator) vytvoří tzv. derived view. Na rozdíl od base view atributy či názvy v derived view mohou být více uživatelsky přívětivé a tím napomáhat realizaci samotného datového modelu ze strany BI uživatelů.

Připojením datových zdrojů a vytvořením base a derived view se nám podařilo do pomyslného virtuálního data lake dostat dva datové zdroje (Oracle a PostgreSQL). Něco podobného provedeme pro číselník míst (konkrétně soubor struktura_uzemi_cr). Před samotným načtením .xlsx souboru do Denodo Platform nahrajeme soubor na file systém VM (další možnosti, jak připojit excel ukazuje rozbalovací menu). Následně přes rozklikávací menu najdeme onen soubor na file systému a provedeme jeho import (de facto vytváříme nový datový zdroj).

Stejně jako u dvou předešlých datových zdrojů, i zde musíme vytvořit z listu excelu base view. Po jeho uložení máme konečně všechny tři datové zdroje včetně všech potřebných informací ve virtuálním data lake a můžeme s nimi dále pracovat.

Po provedení této prerekvizity (mít virtuálně všechny tři zdroje na jednom místě) už nás čeká v podstatě jen analytická práce. Nejprve potřebujeme „slít“ obě tabulky realizací dopravních cest, mezivazební tabulky (obsahující id o realizacích a místech) a umístění vždy do jednoho odpovídajícího view. K tomu nám stačí prostý union all, kterým „spojíme“ ve VQL Shell konzoli patřičná derived view.

Dále musíme upravit derived view číselníku míst tak, aby odpovídalo zadaní. Tedy tak, že přestože geografická úroveň (obec, město, okres atd.) vyplnění místa realizace pozemní komunikace může být různá, do finálního výstupu se nám vždy dostanou transformované úrovně na kraje bez duplicit.

O vysvětlení poslední věty se pokusím na příkladu projektu s názvem „Cesta tří krajů“, u kterého je uvedeno místo realizace na úrovni Královehradecký kraj, Nový Bydžov, Pardubice a Středočeský kraj. Na první pohled máme dvě dopravní komunikace realizované na úrovni krajů (Královehradecký a Středočeský kraj) a další dvě na nižší úrovni (Nový Bydžov a Pardubice, které potřebujeme převést až na úroveň kraje). Z tohoto důvodu vytvoříme nové view ciselnik_uzemi_kraje. V rámci něho převedeme místa realizace, které nejsou na úrovni kraje, na tyto vyšší územní celky. Po transformaci Nového Bydžova na krajskou úroveň (Královéhradecký kraj) a Pardubic (Pardubický kraj) nám ale vychází databázově čtyři řádky, přičemž dva nesou informaci Královéhradecký kraj. Pokud bychom dle zadání provedli operaci (pomocí funkce count v SQL dotazu), která má za cíl spočítat počet projektů v krajích, u této jedné realizace nám vyjde u Královéhradeckého kraje číslo 2 (jinými slovy, výstup říká, že projekty byly realizovány dva). To ale víme, že není pravda (stále je to jeden projekt s názvem „Cesta tří krajů“… jen pro připomenutí, výstup má obsahovat počet projektů, nikoliv počet dopravních cest realizovaných v kraji). Proto musíme provést ještě deduplikaci, aby výstup odpovídal realitě (tedy jednou Královéhradecký kraj). Takto předpřipravené view pak můžeme připojit k view týkajících se realizací projektů bez obav o výsledné počty v krajích (výstupy budou odpovídat realitě).

Po provedení výše uvedeného máme data připravené pro výsledný datový model. Nyní stačí pouze buď ve VQL Shell konzoli napsat SQL skript spojující potřebná view a jejich atributy. Anebo pomocí volby New -> Join a formou drag and drop přetáhnout opět daná view a na nich vybrat potřebné atributy.

Suma sumárum, docílení finálního výstupu je skutečně práce na deset minut. V podstatě nejvíce času zabere uvědomění si onoho případného zkreslení výstupu (při transformacích nižších územních jednotek na úroveň kraje) a vyřešení tohoto úskalí. Ostatní činnosti (připojení zdrojů dat, vytvoření base či derived view) apod. jsou činnosti v řádu několika málo minut.