Datový sklad je centrální informační systém, který slouží k uchování dat z jednoho nebo více podnikových operačních systémů, případně dalších externích zdrojů (excel, sharepoint, apod). Data jsou v datovém skladu organizována do schémat a před uložením projdou procesem čištění a úpravou struktury dat tak, aby byla pro uživatele srozumitelná a dale se jednoduše reportovat. Narozdíl od provozních systémů datový sklad obsahuje kompletní historii (jak se data mění v čase).

Faktové a dimenzní tabulky (fakta a dimenze)

V datovém skladu se setkáte prakticky se dvěma základnímy typy tabulek – fakta a dimenze. Členění na faktové a dimenzní tabulky vychází z nutnosti data strukturovat do schémat kvůli přehlednosti a orientaci na uživatele. Takto členěná data také v čase negenerují tolik historických záznamů. Často se v datovém skladu nachází stovky a tisíce tabulek, proto je struktura dat velmi důležitá. Typicky se setkáme se schématy star schema a snowflake.

Pro ukázku použiju volně dostupnou sample databázi od Microsoft – AdventureworksDW2016CTP3 ¹. Model jsem vytvořil v trial verzi dbForge studia ². Pohled do schématu ledasco napoví. Tabulky jsou uspořádány do star schématu (až na jednu výjimku – currencyKey nad DimOrganization) a obsahují data z účetnictví (účetní výkazy – rozvaha a výsledovka).

• Faktová tabulka FactFinance obsahuje účetní zůstatky a cizí klíče do dimenzí
• Dimenzní tabulky obsahují kontext k zůstatkům
  • DimAccount – účty a účetní hierarchie (aktiva, pasiva, dlouhodobý majetek, apod)
  • DimDate – datumová dimenze
  • DimDepartmentGroup – podnikové oddělení (marketing, sales, manufacturing)
  • DimOrganization – podnikové divize a jejich hierarchie (Severní Amerika, Francie, Česká republika, Evropa apod)
  • DimScenario – typ hodnoty ve faktové tabulce (Actual, Budget, Forecast)

Faktová tabulka

Ve faktových tabulkách jsou uložené metriky neboli měřitelná atomická data – např. tržby, náklady nebo nějaká transakční data (pokladna apod). Faktové tabulky tedy logicky obsahují největší množství záznamů a tvoří základ datového skladu. Dále v ní najdeme samozřejmě primární klíč a také cizí klíče do dimenzních tabulek. Všechny vazby jdou směrem od faktové tabulky ven (viz. Star/snowflake schema).

Naopak faktová tabulka nesmí obsahovat žádné atributy – textová pole, flagy, popisky apod. Tyto informace patří do dimenzí. Příklad faktové tabulky obsahující tržby: FactFinance:

Dimenzní tabulka v datovém skladu

Tyto tabulky narozdíl o faktových žádná měřitelná data neuchovávají. Slouží k tomu, aby faktům daly kontext. Máme – li ve faktové tabulce zůstatky z účetnictví, tak připojením dimenzní tabulky DimAccount (viz model) a DimScenario získáme tržby přes účty a typ zůstatku. Připojením dimenze DimDate zase získáme podrobný časový kontext (neboli k jakému časovému bodu je zůstatek validní), protože tato tabulka obsahuje časové hierarchie (den, týden, měsíc, kvartál apod).

Aktuální zůstatky přes jednotlivé účty za leden 2013:

SELECT
   dat.FullDateAlternateKey
   ,acc.AccountDescription
   ,SUM(fin.Amount) AS AccountBalance
FROM
   [AdventureworksDW2016CTP3].[dbo].[FactFinance] fin
   JOIN [AdventureworksDW2016CTP3].[dbo].[DimAccount] acc
      ON fin.AccountKey=acc.AccountKey
   JOIN [AdventureworksDW2016CTP3].[dbo].[DimScenario] sce
      ON fin.ScenarioKey=sce.ScenarioKey
   JOIN [AdventureworksDW2016CTP3].[dbo].[DimDate] dat
      ON fin.DateKey=dat.DateKey
WHERE
   sce.ScenarioName='Actual'
   AND Dat.CalendarYear=2013
   AND Dat.EnglishMonthName='January'
GROUP BY
   dat.FullDateAlternateKey,
   acc.AccountDescription;

Výsledek:

Další příklady vytvoření faktových a dimenzních tabulek

Na další ukázku se můžete podívat v mém veřejném projektu na GitHubu. Je to dbt projekt, který má za úkol ztransformovat vstupní data (ze zdrojových systémů) do star schématu faktové a dimenzních tabulek. SQL kódy naleznete v adresáří model/marts/sales. Celý GitHub projekt i s popisem najdete na stránce s repozitářem – Sample Data warehouse | Datamart (Data Engineering project)

1. Microsoft, AdventureWorks sample databases [on-line]. [cit. 2018-07-23]. Dostupné z WWW: https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms 
2. DevArt, Download dbForge Studio for SQL Server [on-line]. [cit. 2018-07-23]. Dostupné z WWW: https://www.devart.com/dbforge/sql/studio/download.html