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

Modelování dat u analytických datových skladů

V této kapitole budou představeny koncepty a techniky, které lze využít při návrhu schématu analytického datového skladu.

🎓 V předchozích dílech jsme:

Pomalu se měnící dimenze (revisited)

Pomalu měnící se dimenze (SCD, slowly changing dimensions) jsou dimenze, které se mění v čase. V předchozích cvičeních se objevila v kontextu tabulky Players, kde se může měnit jméno libovolného hráče v nepravidelných intervalech. SDC se dělí na 4 (až 6) typy dle zvolené strategie:

V případě tabulky Players byl zvolen Type 1, kdy se původní záznam přepíše novým při volání INSERT OR REPLACE příkazu, což je pohodlné při naplňování. Nevýhodou je, že nelze činit analytiky ohledně toho, kdy ke změně došlo, a nelze dohledat původní hodnoty.

Vlastní dimenzní tabulky

Při modelování typicky dochází k přidání další informace o dimenzích, které nepocházejí z původního zdroje dat. Například u údaje o server by bylo možné k hodnotám jako Washington_DC, London nebo Tokyo přidat informace o regionu, kde se server nachází. Tato informace by mohla být v jiné tabulce, která by byla spojena s tabulkou Matches pomocí cizího klíče.

Jelikož je v datech jen omezený počet serverů bude nejefektivnější vytvořit tabulku Servers manuálně spíše než se snažit dohledat nějaký datový soubor obsahující mapování měst na regiony.


  

DROP TABLE IF EXISTS servers;

CREATE TABLE servers (
    name VARCHAR PRIMARY KEY,
    region VARCHAR
);


INSERT INTO servers (name, region) VALUES
    ('Chicago', 'North America'),
    ('Frankfurt', 'Europe'),
    ('London', 'Europe'),
    ('Los_Angeles', 'North America'),
    ('Mumbai', 'Asia'),
    ('Seattle', 'North America'),
    ('Singapore', 'Asia'),
    ('Tokyo', 'Asia'),
    ('Washington_DC', 'North America');

※ Některé SQL dialekty neumožňují vložit více záznamů najednou. V takovém případě je nutné použít více INSERT INTO příkazů.

※ Přehled o serverech by bylo možné získat pomocným dotazem SELECT DISTINCT server FROM matches;

Jelikož se využívá původní textový název jako primární klíč, není potřeba nic ve faktové tabulce měnit.

Ve faktové tabulce jsou další atributy, které by bylo možné rozvést jako dimenze. Atributy player1_race a player2_race by mohly být spojeny s tabulkou races, pokud bychom k nim měli nějakou další informaci využitelnou pro analýzu. Podobně atribut leaderboard by mohl být rozveden v dimenzní tabulce, kde by například bylo možné uchovat informaci o herním módu (1v1) případně evidovat dobu, ve které je leaderboard aktivní.

Odvozená fakta, denormalizace

V předchozím týdnu byla v analýze použita metrika rozdílu hodnot MMR a diskutováno, že tím lze vyjádřit kvalitu (vyrovnanost) zápasu. Tato metrika by mohla být zahrnuta do faktové tabulky, aby nemusela být vyhodnocována při každém dotazu.

Výpočty odvozených sloupců lze v DuckDB definovat při vytvoření tabulky tímto způsobem:


  

CREATE TABLE IF NOT EXISTS matches (
-- ...
  mmr_diff INTEGER AS (FLOOR(ABS(player1_mmr - player2_mmr)))
);

Podobně by bylo například možné předpočítat procentuální šanci na výhru prvního hráče, nebo změnu v MMR po zápase.


  

expected_win DOUBLE AS (ROUND(1 / (1 + 10^(-mmr_diff / 400)), 4)),
mmr_change DOUBLE AS (CASE 
             WHEN player1_result = 'win' THEN ROUND(20 * (1 - expected_win), 4) 
             WHEN player1_result = 'loss' THEN ROUND(-20 * expected_win, 4) 
             ELSE 0 END)

※ Výpočet je založen na 📚Elo systému pro parametry K = 20 a D = 400.

Dimenze pro rozsahy intervalů

Poměrně často se při analýzách pracuje s hraničními číselnými hodnotami, které rozdělují obor hodnot na významové intervaly. V řešeném případě lze takto chápat rozdíl v MMR, kde lze určit určité rozsahy zájmu (např. 0-50, 50-100, 100-125, ...).

Jedním z řešení může být vytvoření dimenzní tabulky obsahující definici intervalů, která bude v dotazech propojena s faktovou tabulkou.


  

DROP TABLE IF EXISTS mmr_ranges;

CREATE TABLE mmr_ranges (
    id INTEGER PRIMARY KEY,
    low INTEGER,
    high INTEGER,
    quality VARCHAR
);

INSERT INTO mmr_ranges (id, low, high, quality) VALUES
    (1, 0, 50, 'perfect'),
    (2, 50, 100, 'ok'),
    (3, 100, 200, 'acceptable'),
    (4, 200, 9999, 'poor')
;

Všimněte si hodnoty 9999 značící největší očekávanou možnou hodnotu. Pokud by byl použit numerický datový typ podporující hodnotu Infinity, bylo by lepší použít to. Dále si všimněte, že intervaly se teoreticky mohou překrývat.

Výhodou tohoto řešení je možnost upravovat rozsahy relativně jednoduše bez nutnosti zásahu do faktové tabulky (v porovnání s řešením přidat odvozený sloupec do faktové tabulky). Zároveň lze pomocí dotazů získat informaci o nastavených rozmezích intervalů.

Redundance dat pro efektivnější dotazování (revisited)

V předchozím týdnu byl proveden trik s duplikováním řádek v tabulce matches pro získání pohledu na výsledek z pohledu druhého hráče. Tento přístup zjednodušuje dotazy v tom ohledu, že hráč zájmu je vždy na pozici _1 a jeho protivník jako _2. Nevýhoda, resp. cena za toto zjednodušení, je zvýšení objemu dat. V klasické produkční databázi by to bylo nežádoucí, ale v analytickém datovém skladu je zavádění redundance a s tím související denormalizace modelu poměrně častá praxe.

Takové řešení vždy vycházejí z dobrého pochopení modelované domény a zvážení, jaké dotazy budou na data kladeny.

Práce s časem a datem

V datových skladech se většinou vyskytuje nějaká forma časové dimenze, která umožní sledovat vývoj v čase, detekovat trendy a cykly, či analyzovat špičky ve vytížení služby. V kontextu multiplayer her je například populární měřit velikost populace ve smyslu kolik různých hráčů se přihlásilo během dne, týdne, měsíce, ...

Prvním krokem k takovým analýzám je samotné zpracování informace o čase, což se na první pohled může zdát triviální. Uvažujte v tomto případě situaci, kdy hráči jsou z různých regionů a časových pásem a je potřeba nějakým způsobem říci, jestli daný zápas spadá do dne N, nebo N+1. Uvědomte si, že je možné, že ve stejný moment je pro jednoho hráče kalendářní den jiný než pro druhého.

Q1: Jakým způsobem byste v tomto případě určili, do jakého dne záznam spadá? Jaké jsou alternativní řešení?

Zde zvoleným řešením bude to nejpřímočařejší - přidání sloupce refdate jako datové složky z údaje obsahujícím datum i čas. Neboli necháme na poskytovateli dat, aby určil referenční časovou zónu.


  

CREATE TABLE IF NOT EXISTS matches (
-- ...
refdate DATE AS (strptime(created_at, '%Y-%m-%dT%H:%M:%S')),
);

※ V řešení je provede převod z textového řetězce dle šablony a následné přetypování na typ DATE nesoucí pouze informaci o dnu.

Agregované tabulky faktů

Další formou zavedení redundance za účelem urychlení dotazování je vytvoření agregovaných tabulek faktů. Tyto tabulky obsahují agregované hodnoty z faktové tabulky, které jsou předpočítány, aby stačilo výpočet proběhnout pouze při načtení/aktualizaci dat.

V tomto případě by mohlo být efektivní propočítat počty zápasů v jednotlivých dnech pro jednotlivé hráče. Případně seskupit zápasy podle týdnů nebo serverů, na kterých se odehrály.

Dle možnosti databázového systému lze využít tzv. materializovaný pohled (materialized view), což je vlastně tabulkou, která je automaticky aktualizována při změně zdrojových dat. V DuckDB to ale není podporováno a je tedy potřeba přistoupit k tvorbě tabulky z výsledku dotazu.


  

DROP TABLE IF EXISTS activity_day;

CREATE TABLE activity_day AS
SELECT
    refdate, player1_id, player1_race,
    COUNT(*) AS played,
    COUNT(*) FILTER (player1_result = 'win') AS wins,
        COUNT(*) FILTER (player1_result = 'loss') AS wins,
        AVG(mmr_diff) AS mmr_diff_avg,
FROM matches
GROUP BY refdate, player1_id, player1_race
;

Toto řešení je v podstatě také ukázkou konceptu granularity dat - tedy jak detailně jsou data zaznamenána. V řešeném případě je původní granularita dat na úrovni jednotlivých zápasů, ale výsledná tabulka agreguje data na úroveň jednotlivých hráčů a dnů.

Zároveň pokud budou obě faktové tabulky existovat současně, lze hovořit o konformních dimenzích, kdy dimenze jsou znovupoužity napříč více faktovými tabulkami. Konkrétně dimenze players je využita v obou tabulkách. Bylo by se možné tedy například dotazovat, jak vypadaly hry (faktová t. matches) konkrétního hráče (dimenzní t. players) ve dnech, kdy měl pozitivní skore (faktová t. activity_day).

Extra

Celý příkaz na vytvoření tabulky matches:


  

CREATE TABLE IF NOT EXISTS matches (
  duration INTEGER, 
  server VARCHAR,
  leaderboard VARCHAR,
  match_id VARCHAR PRIMARY KEY, 
  created_at VARCHAR, 
  player1_id VARCHAR, 
  player1_result VARCHAR, 
  player1_race VARCHAR, 
  player1_mmr DOUBLE, 
  player1_ping INTEGER, 
  player2_id VARCHAR, 
  player2_result VARCHAR, 
  player2_race VARCHAR, 
  player2_mmr DOUBLE, 
  player2_ping INTEGER,
  refdate DATE AS (strptime(created_at, '%Y-%m-%dT%H:%M:%S')),
  mmr_diff INTEGER AS (FLOOR(ABS(player1_mmr - player2_mmr))),
  expected_win DOUBLE AS (ROUND(1 / (1 + 10^(-mmr_diff / 400)), 4)),
  mmr_change DOUBLE AS (CASE
    WHEN player1_result = 'win' THEN ROUND(20 * (1 - expected_win), 4)
    WHEN player1_result = 'loss' THEN ROUND(-20 * expected_win, 4)
    ELSE 0 END)
);

Způsob použití kvalitativních rozsahů pro rozdíl mmr:


  

SELECT
match_id, player1_mmr, player2_mmr, quality
FROM matches m
JOIN mmr_ranges mmr ON m.mmr_diff >= mmr.low 
                   AND m.mmr_diff < mmr.high
;