Google BigQuery a Reporty v Google Data Studio

Ako už viete z nášho minulého článku na našom blogu, Daktela ponúka celý rad reportovacích nástrojov. Zjednodušene možno reportovacie
a analytické nástroje rozdeliť na dve skupiny:

1. Daktela Štatistiky a Analytiky
2. Google riešenie

Daktela ako komplexná webová aplikácia taktiež poskytuje robustné analytické nástroje (Štatistiky, Analytiky, Reporting), ale pre nasledujúce prípady
je vhodnejšie použiť externé riešenie:

• Veľké množstvo dát
• Jeden zákazník používa viac ústrední
• Prepojovanie („join“) tabuliek
• Pokročilé filtrovanie reportovaných dát
• Potreba nezaťažovať ústredňu
• Čo najrýchlejšie načítanie dát

Preto využívame moderné a super-výkonné riešenie od Googlu, na ktoré sa tentokrát zameriame.

Všeobecne sa jedná o kombináciu úložiska (Google BigQuery), reportovacieho nástroja (Google DataStudio), pridružených služieb (Google Cloud Platform – zabezpečenie, správa, údržba, monitoring) a dátových tokov (Daktela), ktoré dohromady tvoria ETL (z angl. extract- transform-load)
a BI (z angl. Business Intelligence) riešenie.

Nespornou výhodou celého riešenia je prístupnosť kompletne cez webový prehliadač, nie je teda potrebný dedikovaný softvér a všetko je dostupné
z akéhokoľvek počítača cez Google účet s dostatočnými právami. Operabilita systému je závislá iba od rýchlosti pripojenia k internetu.

Daktela

Na serveri bigquery.daktela.com sú nastavené pravidelné úlohy zaisťujúce transformáciu a nahranie dát z ústrední do BigQuery (ďalej len BQ)
v pravidelných intervaloch. Tieto tzv. „run skripty“ sú napísané v programovacom jazyku java.

Interval synchronizácie je 120 minút a 24 hodín. Dáta sa teda pravidelne nahrávajú každú druhú hodinu a na konci dňa (o polnoci) je ešte nahraná konsolidačná dávka kvôli zaisteniu integrity. Dvojhodinový interval je možné u vybraných ústrední znížiť na jednohodinový.

V ojedinelých prípadoch môžu vzniknúť v BQ zdanlivo duplicitné záznamy – o ich odhalenie sa stará samostatný skript, ktorého výstup je sledovaný
a notifikovaný v monitorovacom softvéri. Duplicity sú automaticky opravované počas dennej synchronizácie.

Keďže na serveri bigquery.daktela.com beží „run skripty“ všetkých projektov napojených na BQ (k decembru 2021 je to 21 ústrední), existuje rozvrhnutie po minútach, vďaka ktorému si nahrávanie jednotlivých inštancií nekonkuruje a využíva sa tak maximum systémových prostriedkov.
Aj to prispieva k relatívne malej dobe spracovania, kedy jeden synchronizačný krok (nahranie hodinovej alebo dennej dávky pre všetky inštancie projektu) trvá aj pri najväčších ústredniach v rádoch minút, až desiatok minút, typicky 10-30 minút, v extrémnych prípadoch cca 60-90 minút. Synchronizáciu je možné taktiež spustiť okamžite manuálne pre prípad riešenia problémov.

Priebeh všetkých skriptov je logovaný do textového súboru.

Prístup k serveru bigquery.daktela.com je zabezpečený SSH kľúčom. Server je plne pod správou IT administrátorov Daktela, rovnako ako udeľovanie užívateľských prístupov.

Google BigQuery

BQ je plne riadené, bez serverové dátové úložisko (DWH – data warehouse), ktoré umožňuje škálovateľnú analýzu extrémne veľkého množstva dát
(v ráde petabytu, 1PB ≈ 1024 TB ≈ 1 milión GB). Jedná sa o platformu vo forme služby (dostupnej cez web), ktorej súčasťou je aj dotazovacie rozhranie (na báze ANSI SQL).

Ďalej Google poskytuje pridruženú funkcionalitu, ako je strojové učenie, geografické nástroje, analytické a štatistické nástroje a správu užívateľských práv a prístupov.

Architektúra je kompletne pod správou Googlu, užívatelia/konzumenti sa teda nemusia zaoberať otázkami, ako je škálovanie, distribúcia výpočtových prostriedkov, zálohovanie, údržba atď. petabyty dát v niekoľkých minútach.

Vďaka tomu je možné reporty načítať (a interaktívne filtrovať/triediť) v reálnom čase.

Hoci sa nejedná čisto o databázu ako takú, možno o BQ hovoriť ako o nerelačnom databázovom OLAP systéme (z angl. Online Analytical Processing), optimalizovanom na čítanie a analýzu. Vkladanie (insert) je ale tiež možné, rovnako ako napojenie BQ na externé dátové zdroje.

Medzi kľúčové vlastnosti patrí škálovateľnosť, spoľahlivosť, dostupnosť, nízkonákladovosť, samoopraviteľnosť a decentralizovanosť. Je alternatívou
pre DWH systémy ako napríklad Amazon Redshift alebo Microsoft Azure SQL.

Dáta sú v BQ usporiadané do tzv. dátumu setov (obdoba databázy), ktoré sa skladajú z tabuliek, pohľadov a prípadne ďalších entít (napríklad modelov strojového učenia). Práva (prístup, úpravy, mazanie atď.) je možné nastavovať ako pre dataset, tak pre čiastkové tabuľky.

Riadenie používateľských účtov a práv je realizované prostredníctvom webu platformy Google Cloud. Jednotliví používatelia služby musia mať funkčný Google účet. Kontrola a udeľovanie prístupov je plne v kompetencii Daktela administrátorov.

Viac informácií nájdete na oficiálnych stránkach Google Cloud [anglicky].

Kvóty a limity

Google do určitej miery obmedzuje vyťažiteľnosť systému nastavením limitov a kvót za určité obdobie (napr. počet otázok, veľkosť prenesených dát atď.). BQ je určené pre naozaj veľké a dátovo náročné projekty, preto z pohľadu Big Data nie sú ani tie najväčšie a najzložitejšie Daktela ústredne ničím než malou a jednoduchou dátovou sadou.

Viac o kvótach a limitoch možno nájsť na oficiálnych stránkach Google [anglicky].

Google Data Studio

Google Data Studio (DS) je nástroj pre vizualizáciu a reportovanie dát podobne ako Microsoft Power BI alebo Tableau. Jedná sa o webovú aplikáciu, pomocou ktorej je možné v užívateľsky prívetivom prostredí vytvárať reporty skladaním tabuliek a grafov doplnených o textové a vizuálne prvky.

Je to teda súčasne nástroj pre:

• Vytváranie/úpravy jednotlivých reportov
• Správu viacerých reportov prístupných danému užívateľovi
• Zobrazovanie jednotlivých reportov
• Vytváranie/úpravy/správu dátových zdrojov (pozri ďalej)
• Správu prístupov k týmto reportom a dátovým zdrojom

Vlastný report sa vždy skladá z:

• Dátového zdroja
• Obsahu
• Nastavenie prístupov

Jeden dátový zdroj môže byť zdieľaný naprieč viacerými reportmi. Jeden report môže obsahovať viacero dátových zdrojov.

V rámci prostredia Google sa riadia prístupové práva v každej jednotlivej oblasti zvlášť::

• Big Query
  • Prístupy a práva k projektu (interné prístupy v rámci Daktela)
• Data Studio
  • Prístupy a práva k zdieľaným dátovým zdrojom

Pre všetky oblasti (BQ, DataStudio) platí princíp, že používateľ vidí iba entity, ku ktorým má udelený prístup. Nevie o existencii entít (datasety, tabuľky, dátové zdroje, reporty) ostatných užívateľov, pokiaľ sa k nim nepokúsi pristúpiť cez URL.

DataStudio – dátové zdroje

Termínom „dátový zdroj“ (data source) sa označuje súhrn:

• Nastavenie pripojenia k zdroju dát
• Pravidiel transformácie a formátovania získaných dát
• Frekvencia synchronizácie dát
• Prístupových práv

Tieto položky spoločne tvoria entitu v Google DS, s ktorou používateľ pracuje. Nejde teda priamo o zdroj dát, ale skôr o dátový model/schéma, alebo „konektor“ k zdroju dát, typicky k DWH.

Google DS sa pomocou „dátového zdroja“ vie pripojiť k mnohým druhom DWH, od vlastných (Google BQ, Google Sheets, Google Analytics, Google Ads, Google Surveys, Google Cloud atď.), cez bežné (Microsoft SQL Server, MySQL, PostgreSQL, súbory vo formáte CSV ad.), až ku konektorom tretích strán. Kompletný zoznam je možné nájsť na oficiálnych stránkach. Daktela má aj vlastný konektor.

Vytvorením „dátového zdroja“ vznikne entita, ktorá určuje:

• Pripojenie k BiqQuery alebo zdroju dát („data origin“)
• Určenie dotazovanej tabuľky alebo definície SQL dotazu
• Formátovanie „dimenzií“ (pozri ďalej)
• Agregované „metriky“ (pozri ďalej)
• Parametre – obvykle časové obdobie pre dáta
• Prístup – ktorý užívateľ môže dátový zdroj upravovať a/alebo zobrazovať jeho definíciu
• Frekvencia synchronizácie dát („data freshness“) – obvykle 1 hodina, môže byť ale až 1 minúta či naopak 4 alebo 12 hodín
• Zdieľanie dátového zdroja – buď obmedzené na aktuálny report, alebo umožnené zdieľanie zdroja cez niekoľko reportov

Na zúženie výberu dát v reporte sa okrem dátových zdrojov používajú aj „ovládacie prvky“ a „skryté filtre“ popísané ďalej. Jednotlivé dátové zdroje
je možné tiež v reportoch prepájať medzi sebou (tzv. blend), čím je možné docieliť komplexnú agregáciu dát, ktorá by bez dobrej znalosti jazyka SQL bola pre niektorých užívateľov nedosiahnuteľná.

Za každou požiadavkou na dáta z reportu do BQ stojí SQL dotaz, ktorého výsledok BQ dočasne ukladá (tzv. cash) pre ďalšie použitie (v rámci časového intervalu „data freshness“), a tým je možné docieliť okamžité odozvy a nízkych nákladov na dotaz.

Vlastník reportu sa automaticky stáva vlastníkom dátového zdroja, ktorý vytvára v danom reporte. Zdieľané dátové zdroje z vlastných reportov
je možné pridávať do akéhokoľvek ďalšieho vlastného reportu. Pre používanie a editáciu dátových zdrojov iných užívateľov je nutné požiadať o prístup k nim a ich vlastník musí prístup potvrdiť.

Odobratím „vstavaného“ (embedded) dátového zdroja z reportu dôjde k jeho odstráneniu. Odstránením zdieľaného dátového zdroja z reportu k jeho odstráneniu nedôjde.

DataStudio – reporty

Po nastavení dátového zdroja je možné vytvoriť report skladaním grafov, v ktorých užívateľ vyberá dimenzie a/alebo metriky z dátového zdroja.
Pre lepšiu orientáciu je možné report rozdeľovať do stránok a pridávať ďalšie sprievodné prvky ako obyčajný text a grafiku.

Pole

Polom sa rozumie dátový stĺpec. Principiálne existujú dva druhy – dimenzie a metriky. Dimenzie obsahujú entity, ktoré chceme merať, alebo kategórie. Metriky sú veličiny, ktoré číselne opisujú dimenzie.

Okrem polí automaticky načítaných z dátového zdroja je možné vytvoriť vlastné pole:

• Kombinovaním dimenzií vytvoríme novú dimenziu
• Kombinovaním metrík medzi sebou, alebo s dimenziami, vytvoríme novú metriku

Dimenzia

Dimenziou sa rozumie kategória informácií/dát, respektíve dátového radu. Napríklad pri meraní návštevnosti webovej stránky môžu byť dimenziami Prehliadač, Webová stránka, Jazyk atď.

V DS potom je možné zmeniť názov, dátový typ (text, číslo, dátum atp.) a popis dimenzií.

Dimenzia je obdobou stĺpca v tabuľke (ak používame databázový alebo tabuľkový dátový zdroj).

Metriky

Metrikou sa tu rozumie agregovaný výpočet/operácia, aplikovaný na sadu dát, napríklad počet záznamov, suma, maximum, minimum, priemerná hodnota atď. Je teda obdobou agregovanej funkcie v SQL dotaze.

V grafoch a tabuľkách je možné dimenzie a metriky kombinovať.

Grafy

K dispozícii sú:

• Tabuľky (bežné a pivotné)
• Grafy
• Tzv. skórkarty (prvok s jednou metrikou)

Filtre

Filtre sú reálne na pozadí DS interpretované ako úprava SQL dátového zdroja, čiže nezáleží na tom, či tvorca reportu dáta filtruje už na vstupe do DS (definícia SQL dotazu), na úrovni elementu reportu (skrytý filter grafu alebo tabuľky), alebo až pri interakcii návštevníka reportu (užívateľský filter = kontrolný prvok), vo výsledku sa vykonáva SQL dotazy do BQ.

Filtrovaním je možné tiež dosiahnuť rýchlejšie načítanie dát (načíta sa filtrovaná sada), preto je vhodnejšie u dátových zdrojov presne formulovať konkrétny SQL dotaz skôr než požadovať celú tabuľku (a filtrovať až na úrovni reportu).

Ďalšie prvky

Okrem vyššie uvedeného je možné tiež do reportov vkladať:

• Hypertextové odkazy
• Obrázky
• Externý obsah („embedded URL“)
• Parametre (časové obdobie, e-mailová adresa návštevníka alebo vlastné parametre)

Tým, že sa report nachádza na konkrétnej URL, je naopak možné celý interaktívny obsah vkladať inam (do online dokumentov, webových stránok
a pod.).

DataStudio – prístupy

Všeobecne je možné prístup rozdeliť na dve úrovne:

1. Zobrazenie
2. Úpravy

Užívatelia s prístupom (1) nemôžu vykonávať žiadne úpravy rozloženia reportu ani jeho nastavenia, môžu iba s reportom interagovať a obvykle jeho obsah exportovať do PDF, tlačiť, či vytvoriť kópiu (u tej je potom možné vstúpiť do režimu úprav a de facto zistiť, ako bol report konštruovaný).

Používatelia s prístupom (2) môžu upravovať obsah reportu a môžu meniť všetky nastavenia. Vlastník (autor) reportu môže riadiť ďalšie čiastkové vlastnosti, napríklad ostatným používateľom zrušiť možnosť pridávať ďalších užívateľov.

Reporty je tiež možné sprístupniť pomocou odkazu, vďaka čomu je možné upraviť vyššie uvedené na:

1. Zdieľanie cez odkaz vypnuté = iba uvedení užívatelia majú prístup
2. Zdieľanie cez odkaz zapnuté = prístup majú uvedení užívatelia plus:

• 2.1. ktokoľvek s odkazom v rámci spoločnosti (napr. Daktela s.r.o.) môže report zobraziť
• 2.2. ktokoľvek s odkazom v rámci spoločnosti môže report upravovať
• 2.3. ktokoľvek s odkazom môže report zobraziť
• 2.4. ktokoľvek s odkazom môže report upraviť
• 2.5. ktokoľvek na internete môže report nájsť a zobraziť (aj bez odkazu)
• 2.6. ktokoľvek na internete môže report nájsť a upraviť (aj bez odkazu)

Je teda možné vyhnúť sa problematike riadenia a obsluhovania prístupov k reportu tým, že vlastník zapne zdieľanie cez odkaz s úrovňou (2.3) alebo (2.5), hrozí ale nebezpečenstvo spreneverenia reportu/dát. Sledovať návštevnosť reportu na úrovni konkrétneho užívateľa nikde nemožno (cez Google Analytics je možné sledovať iba anonymizované informácie).

Prístupy k dátovým zdrojom sú popísané vyššie.

Pre získanie prístupu k interaktívnej a aktuálnej podobe reportu je potrebné poznať buď:

1. URL, na ktorej sa report nachádza alebo
2. Presný názov reportu a Google účet jeho vlastníka. PDF verzia reportu môže obsahovať odkaz na online alebo vnorenú verziu reportu.

V prípade (1) sa na webovej adrese buď zobrazí report (ak k nemu užívateľ má práva), alebo informácie o chýbajúcich právach a formulár
pre kontaktovanie vlastníka reportu. Pri chýbajúcom prístupe tak stačí nastaviť požadovanú úroveň oprávnenia a odoslať formulár – vlastníkovi príde emailové upozornenie. Po udelení prístupu k reportu potom žiadateľovi opäť príde emailové upozornenie (pokiaľ toto upozornenie nie je vlastníkom vypnuté). V prípade (2) je potrebné kontaktovať vlastníka inou cestou a ten potom musí daný report nájsť a manuálne pridať žiadateľovi oprávnenie.

Reporty vo formáte PDF môžu byť zabezpečené pomocou hesla, možno ich ale zdieľať ako akékoľvek iné súbory. Vlastník reportu môže pri online reporte zrušiť možnosť exportu do PDF (spoločne sa zruší export do PDF, možnosť Tlač a možnosť Kopírovať report).

Nakoniec existuje ešte distribuovaná forma zdieľania reportu – pravidelné zasielanie reportu na email. V tomto prípade je pravidelne vygenerovaná
e-mailová správa obsahujúca:

• Odkaz na aktuálnu podobu reportu
• Náhľad reportu (spravidla prvých niekoľko stránok)
• PDF súbor reportu k danému dátumu v prílohe

Tento email je možné ďalej preposielať. Pokiaľ ale nový príjemca nemá k reportu prístup, na odkazovanej stránke sa mu zobrazí formulár so žiadosťou o prístup, ale stále bude schopný otvoriť priložený PDF súbor.

Takto je možné umožniť distribúciu na mnoho emailových adries a medzi externých užívateľov.