Fakta v datovém skladu (DWH) - 7. díl

V tomto článku se věnuji faktovým tabulkám. Ukážu příklad na fakturách, dále se věnuji propojení faktových tabulek s dimenzemi, zmíním se o periodě loadu. K článku přikládám v odkazech i link na příklady v SQL, které si můžete při čtení zkoušet.

Star schéma v datovém skladu (DWH) – 1 faktová tabulka a okolní dimenze

Faktury

Na začátku zopakuji, že faktová tabulka obsahuje číselné metriky (agregovatelné) a odkazy (cizí klíče) do dimenzí, které obsahují atributy.

Stěžejními doklady jsou objednávky, faktury a platby, které na sebe mají určitou vazbu. Podoba může být v každé firmě trochu jiná.

Faktovou tabulku ukáži na příkladu a pro ukázku jsem si vybral faktury. Obvykle bývá faktura reprezentovaná hlavičkou a jednotlivými položkami ve faktuře – tedy 2 tabulky. Mohly by vypadat následovně.

INVOICE_HEADER

Sloupec	Typ	Vlastnosti	Popis
INVOICE_ID	VARCHAR(10)	PRIMARY KEY	Id faktury = číslo ve formátu doplněném o počáteční nuly do 10 znaků: ‚0001245784‘
ORDER_ID	VARCHAR(10)	NOT NULL	Vazba na objednávku, stejný formát jako id faktury
INVOICE_DATE	DATE	NOT NULL	Den vystavení faktury
PAYMENT_DATE	DATE	NOT NULL	Datum splatnosti
CUSTOMER_ID	VARCHAR(10)	NOT NULL	Id zákazníka ve stejném formátu jako id faktury

Tabulka hlavičky faktury

INVOICE_ITEM

Sloupec	Typ	Vlastnosti	Popis
INVOICE_ID	VARCHAR(10)	PRIMARY KEY	Id faktury = číslo ve formátu doplněném o počáteční nuly do 10 znaků: ‚0001245784‘
PRODUCT_ID	VARCHAR(10)	PRIMARY KEY	Id produktu, stejný formát jako id faktury
AMMOUNT	INT	NOT NULL	Počet kusů
PRICE	DECIMAL(25,5)	NOT NULL	Cena za všechny kusy bez DPH
PRICE_VAT	DECIMAL(25,5)	NOT NULL	Cena za všechny kusy včetně DPH

Tabulka položky faktury

FACT_INVOICE

Pro vznik faktové tabulky nám postačí jen obě původní tabulky pospojovat a máme hotovo. Faktová tabulka se skládá primárního klíče – id faktury a id produktu. Z odkazů na dimenze – id produktu, id zákazníka, den vystavení faktury. Zbyly nám metriky – počet kusů, cena a cena s DPH.

Sloupec	Typ	Vlastnosti	Popis
FACT_INVOICE_ID	VARCHAR(255)	PRIMARY KEY	Id záznamu – spojení INVOICE_ID a DIM_PRODUCT_ID (zbavení se složeného klíče – stále závislé na datech)
DIM_PERIOD_ID	VARCHAR(10)	NOT NULL	Id periody, formát ‚yyyyMM‘
INVOICE_ID	VARCHAR(10)	NOT NULL	Id faktury bez úvodních nul
ORDER_ID	VARCHAR(10)	NOT NULL	Id objednávky bez úvodních nul
DIM_CUSTOMER_ID	VARCHAR(10)	NOT NULL	Id zákazníka, bez úvodních nul
DIM_PRODUCT_ID	VARCHAR(10)	NOT NULL	Id produktu, bez úvodních nul
INVOICE_DATE	DATE	NOT NULL	Datum vystavení faktury
PAYMENT_DATE	DATE	NOT NULL	Datum splatnosti
AMMOUNT	INT	NOT NULL	Počet kusů
PRICE	DECIMAL(25,5)	NOT NULL	Cena za všechny kusy bez DPH
PRICE_VAT	DECIMAL(25,5)	NOT NULL	Cena za všechny kusy včetně DPH

Faktová tabulka FACT_INVOICE

Load co naplní FACT_INVOICE je jednoduchý SELECT s JOINem mezi tabulkami a drobnými úpravami na sloupcích. Faktová tabulka se typicky přepočítává pouze za aktuální periodu (šetří se výpočetní čas). Toto zajišťují části kódu poblíž ‚PARAM‘, který se může do SELECTu dostat několika způsoby: Nastaven ručně nebo je kód generován scriptem/frameworkem nebo je celý load je pojmutý jako procedura. Celý load se skládá ze 2 části – smazání a vložení dat.

/* Load data into table FACT_INVOICE for specified PERIOD by PARAM - manually/generated by script or framework/using procedure */
/* Delete data for specified period in PARAM */
DELETE
FROM l2.FACT_INVOICE src
WHERE src.DIM_PERIOD_ID = '202310' /* PARAM - param period for which load of fact table has been laoded */
/* Load new data for specified period in PARAM */
INSERT INTO l2.FACT_INVOICE (FACT_INVOICE_ID, DIM_PERIOD_ID, INVOICE_ID, ORDER_ID, DIM_CUSTOMER_ID, DIM_PRODUCT_ID, INVOICE_DATE, PAYMENT_DATE, AMMOUNT, PRICE, PRICE_VAT)
SELECT concat(removeLeadingZeroes(hdr.INVOICE_ID), '.', removeLeadingZeroes(itm.PRODUCT_ID)) AS FACT_INVOICE_ID,
	periodFromDate(hdr.INVOICE_DATE) AS DIM_PERIOD_ID,
	removeLeadingZeroes(hdr.INVOICE_ID) AS INVOICE_ID,
	removeLeadingZeroes(hdr.ORDER_ID) AS ORDER_ID,
	removeLeadingZeroes(hdr.CUSTOMER_ID) AS DIM_CUSTOMER_ID,
	removeLeadingZeroes(itm.PRODUCT_ID) AS DIM_PRODUCT_ID,
	hdr.INVOICE_DATE, hdr.PAYMENT_DATE,
	itm.AMMOUNT, itm.PRICE, itm.PRICE_VAT
FROM l0_erp.INVOICE_HEADER hdr
	LEFT JOIN l0_erp.INVOICE_ITEM itm ON itm.INVOICE_ID = hdr.INVOICE_ID
WHERE periodFromDate(hdr.INVOICE_DATE) = '202310' /* PARAM - param period for which load of fact table has been laoded */

Toto je čistá forma faktové tabulky, se kterou se dále pracuje na základě informací, které je třeba zjistit. Zručnější uživatelé mohou s tabulkou pracovat dál pomocí SQL. Pro neznalé uživatele SQL databáze (business owner, sales, …) je většinou ještě vhodné nad faktovou tabulkou vytvořit view, které dotáhne důležitá data z dimenzí, případně udělá agregaci na požadovanou úroveň. Při velkém množství dat se může stát, že view bude pomalé a je třeba jej optimalizovat, případně vytvořit view materializované.

Mám například za úkol připravit podklady z faktur. Zajímá mě kolik DPH firma zaplatila za jednotlivé produkty celkem v měsíci. Takový SELECT by mohl vypadat následovně.

SELECT
	src.DIM_PERIOD_ID, per.PERIOD_FIRST_DATE,	
	src.DIM_PRODUCT_ID, sku.PRODUCT_NAME, sku.BRAND_FAMILY, sku.BRAND_NAME,
	sum(src.AMMOUNT) as AMMOUNT,
	sum(src.PRICE) as PRICE,
	sum(src.PRICE_VAT) as PRICE_VAT,
	sum(src.PRICE_VAT) - sum(src.PRICE) as VAT
FROM l2.FACT_INVOICE src
	LEFT JOIN l2.DIM_PERIOD per ON per.DIM_PERIOD_ID = src.DIM_PERIOD_ID
	LEFT JOIN l2.DIM_PRODUCT sku on sku.DIM_PRODUCT_ID = src.DIM_PRODUCT_ID
GROUP BY src.DIM_PERIOD_ID, per.PERIOD_FIRST_DATE, src.DIM_PRODUCT_ID, sku.PRODUCT_NAME, sku.BRAND_FAMILY, sku.BRAND_NAME
ORDER BY src.DIM_PRODUCT_ID