DWH töltési elvek alapok

1. Forrásrendszeri tábla-kapcsolatok feltérképezése

A forrásrendszer adatmodelljének megértése kulcsfontosságú a megfelelő DWH modell kialakításához. Ehhez az alábbi lépéseket javaslom:

1.1. Adatmodell dokumentáció és ERD (Entity Relationship Diagram) elemzése

A forrásrendszerekhez általában elérhető valamilyen ERD, amely bemutatja a táblák közötti kapcsolatokat (1:N, N:M, stb.).
Ha nem áll rendelkezésre dokumentáció, visszafejthető az adatmodell:
- Oracle Data Dictionary vizsgálata (USER_CONSTRAINTS, USER_TAB_COLUMNS, USER_INDEXES)
- Idegen kulcsok feltérképezése (USER_CONS_COLUMNS)
- Reverse Engineering ETL eszközzel (pl. Informatica, Talend, Oracle Data Integrator)

1.2. Tranzakciós és törzsadatok azonosítása

Törzsadatok (Master Data): ügyfél, partner, termék, szerződés
Tranzakciós adatok (Transaction Data): rendelés, vásárlás, számla, fizetés
Kapcsolattáblák (Bridge/Link): ha sok-sok kapcsolatokat kell modellezni (pl. ügyfél és több szerződés)

2. DWH ETL Lookup-alapú betöltés tervezése

A forrásrendszeri kapcsolatok DWH modellbe való beépítése többféleképpen történhet.

2.1. Lookup típusok az ETL-ben

Direct Lookup (Online, közvetlen keresés): közvetlen lekérdezés az adatbázisból minden egyes rekord betöltésekor.
- Előny: Nem kell külön tárolni az összerendelt kulcsokat.
- Hátrány: Nagy mennyiségű adatnál lassú, mert minden rekordhoz külön SQL fut.
Cached Lookup (Gyorsítótárazott keresés): az ETL folyamat elején egy teljes lookup-tábla betöltése a memóriába.
- Előny: Gyors, mert nem kell minden rekordhoz külön SQL.
- Hátrány: Nagy lookup táblák esetén sok memóriát igényel.
Persistent Staging Lookup (Persistens előtöltéses lookup): a lookup-adatokat előzetesen egy staging táblába töltjük.
- Előny: Nagy teljesítmény, mert egyedi indexelt táblából történik a keresés.
- Hátrány: Extra tárolási igény.

2.2. Lookup megvalósítása DWH betöltés során

2.2.1. Törzsadatok lookup betöltése

Pl. egy ügyfél azonosítót kell keresni a forrásrendszerből a DWH ügyfél táblában (dimenzióban).

SQL példa egy ügyfél lookup-hoz:

---sql

SELECT customer_id 
FROM dwh_customer_dim 
WHERE source_system_key = :input_customer_key;

Ha nincs találat:
- SCD Type 1: frissítjük az ügyfelet
- SCD Type 2: új verziót hozunk létre (új sor)

2.2.2. Tranzakciók lookup betöltése

A tranzakciós adatok lookup-jához több táblát kell vizsgálni (pl. szerződés lookup, termék lookup).

Egy tranzakciós rekord lookup SQL-lekérdezése:

---sql

SELECT contract_id 
FROM dwh_contract_dim 
WHERE source_contract_number = :input_contract_number;

2.2.3. Kapcsolattáblák (Link Table) lookup-ja

Ha a DWH modellben Data Vault megközelítést használunk, akkor a HUB és LINK táblák lookup-ját is el kell végezni:

---sql

SELECT link_id 
FROM dwh_customer_contract_link 
WHERE customer_id = :customer_id AND contract_id = :contract_id;

3. Legjobb megoldás kiválasztása

A lookup stratégia kiválasztása az adatmennyiségtől és az ETL teljesítményelvárásaitól függ.

Módszer	Előny	Hátrány	Mikor ajánlott?
Direct Lookup (SQL alapú keresés minden rekordnál)	Egyszerű implementálás	Lassú nagy adatmennyiségnél	Kis méretű adathalmaznál
Cached Lookup (Memóriában tárolt lookup tábla)	Gyors, mert nincs sok SQL hívás	Nagy memóriaigény	Közepes adatmennyiségnél
Persistent Staging Lookup (Előtöltött lookup tábla)	Nagy teljesítményű, indexelve keres	Extra tárolási igény	Nagy adathalmaz esetén

Ajánlott módszer nagy DWH környezetben:

Előtöltött lookup staging táblák használata (Persistent Lookup)
- Nagy mennyiségű lookup adatnál az előtöltés biztosítja a legjobb teljesítményt.
- Használj indexelt lookup táblát, amelyet minden ETL betöltés előtt frissítesz.
- ETL eszközökben (pl. Informatica, ODI) paraméterezhető lookup stratégiát használj.
SCD Type 2 használata a változó törzsadatokhoz
- Ha egy ügyfél vagy szerződés adatai változnak, biztosítsd az előző állapotok megőrzését.
Indexek és particionálás optimalizálása
- Lookup táblákhoz érdemes B-Tree indexeket létrehozni.
- Ha az adatmennyiség extrém nagy, particionált táblákat is használhatsz (pl. havi bontás).

Összegzés

Első lépésként térképezd fel az összes forrásrendszeri kapcsolatot ERD, adatdokumentáció vagy adatbázis-metaadatok alapján.
Dönts a lookup módszerről: Kisebb adatmennyiségnél Cached Lookup, nagy adatmennyiségnél Persistent Staging Lookup a legjobb.
Optimalizáld az ETL teljesítményt: Használj indexeket, particionálást, és előtöltött lookup táblákat.
DWH modell kialakítása: Használj SCD Type 2 verziózást, ha az adatok időbeni változásait is nyomon kell követni.

Adatmodell visszafejtés

Az Oracle adatbázisban a modell visszafejtése során meg kell értenünk a táblák közötti kapcsolatokat, különös tekintettel a szülő-gyermek (parent-child) viszonyokra, valamint a forrás-példány (source-instance) használatra. Ezek ismerete elengedhetetlen egy DWH modell kialakításához, különösen a Data Vault, Dimenziós Modell (Kimball) vagy Inmon alapú megközelítésekhez.

1. Modell visszafejtés alapjai

A tranzakciós adatbázisokat jellemzően 3. normál forma (3NF) szerint tervezik, így a tábla kapcsolatok feltérképezéséhez az alábbi szempontokat kell vizsgálni:

Idegen kulcsok (Foreign Key - FK)
Elsődleges kulcsok (Primary Key - PK)
Indexek és egyedi megszorítások
Önhivatkozások és hierarchikus struktúrák
Törzsadatok és tranzakciós adatok elkülönítése

A modell visszafejtéséhez az Oracle Data Dictionary tábláit használjuk.

2. Kapcsolatok azonosítása és elemzése

2.1. Idegen kulcsok feltérképezése

Minden táblakapcsolat alapja az idegen kulcs (FK), amely egy másik tábla elsődleges kulcsára hivatkozik.

🔹 Lekérdezés az Oracle Data Dictionary-ből:

---sql

SELECT 
    a.table_name AS child_table,
    a.column_name AS child_column,
    a.constraint_name AS fk_name,
    b.table_name AS parent_table,
    b.column_name AS parent_column
FROM 
    user_cons_columns a
JOIN 
    user_constraints c ON a.constraint_name = c.constraint_name
JOIN 
    user_cons_columns b ON c.r_constraint_name = b.constraint_name
WHERE 
    c.constraint_type = 'R';

🔹 Példa eredmény:

Child Table	Child Column	FK Name	Parent Table	Parent Column
ORDER	CUSTOMER_ID	FK_ORD_CUST	CUSTOMER	CUSTOMER_ID

🔹 Értelmezés:

Az ORDER (megrendelés) tábla CUSTOMER_ID oszlopa egy idegen kulcs a CUSTOMER tábla CUSTOMER_ID oszlopára.
Ez egy szülő-gyermek (parent-child) kapcsolat, ahol a CUSTOMER a szülő, az ORDER pedig a gyermek.

2.2. Szülő-gyermek (Parent-Child) kapcsolatok vizsgálata

A szülő-gyermek kapcsolatok olyan esetekben fordulnak elő, amikor egy tábla egy másik tábla elsődleges kulcsát használja kapcsolóként.

🔹 Tipikus példák:

Ügyfél és rendelés kapcsolata (CUSTOMER → ORDER)
Szerződés és tranzakció kapcsolata (CONTRACT → TRANSACTION)
Alkalmazott és beosztott kapcsolata (önhivatkozás) (EMPLOYEE → EMPLOYEE)

🔹 Lekérdezés egy adott szülő-gyermek kapcsolatra:

---sql

SELECT 
    uc.constraint_name, 
    ucc1.table_name AS parent_table, 
    ucc1.column_name AS parent_column,
    ucc2.table_name AS child_table, 
    ucc2.column_name AS child_column
FROM 
    user_constraints uc
JOIN 
    user_cons_columns ucc1 ON uc.r_constraint_name = ucc1.constraint_name
JOIN 
    user_cons_columns ucc2 ON uc.constraint_name = ucc2.constraint_name
WHERE 
    uc.constraint_type = 'R'
    AND ucc1.table_name = 'CUSTOMER';

🔹 Példa eredmény:

Constraint Name	Parent Table	Parent Column	Child Table	Child Column
FK_ORD_CUST	CUSTOMER	CUSTOMER_ID	ORDER	CUSTOMER_ID

🔹 Értelmezés:

Egy ügyfél (CUSTOMER) több megrendelést (ORDER) hozhat létre.
A CUSTOMER_ID az összekötő kulcs.

2.3. Forrás és példány kapcsolatok vizsgálata

Bizonyos esetekben egy entitás két táblában jelenik meg:

Forrás (Source): Az elsődleges adat, amely az entitás alapadatait tartalmazza.
Példány (Instance): Az adott entitás különböző állapotai vagy változatai.

🔹 Tipikus példák:

TERMÉK és TERMÉK_VÁLTOZAT (PRODUCT → PRODUCT_INSTANCE)
SZERZŐDÉS és SZERZŐDÉS_VÁLTOZAT (CONTRACT → CONTRACT_VERSION)

🔹 Lekérdezés példány kapcsolatok keresésére:

---sql

SELECT 
    a.table_name AS instance_table,
    a.column_name AS instance_column,
    b.table_name AS source_table,
    b.column_name AS source_column
FROM 
    user_cons_columns a
JOIN 
    user_constraints c ON a.constraint_name = c.constraint_name
JOIN 
    user_cons_columns b ON c.r_constraint_name = b.constraint_name
WHERE 
    c.constraint_type = 'R'
    AND a.table_name LIKE '%INSTANCE%';

🔹 Példa eredmény:

Instance Table	Instance Column	Source Table	Source Column
PRODUCT_INSTANCE	PRODUCT_ID	PRODUCT	PRODUCT_ID

🔹 Értelmezés:

A PRODUCT_INSTANCE tábla minden egyes termékről tárolhat különböző verziókat vagy egyedi példányokat.
A PRODUCT_ID az azonosító, amely összekapcsolja a forrás és példány táblákat.

3. Oracle Hierarchikus Adatok és Önhivatkozások

Ha egy tábla önmagára hivatkozik, akkor egy hierarchikus kapcsolatot kezelünk.

🔹 Példa: Alkalmazott és felettes kapcsolata (önhivatkozás)

----sql

SELECT employee_id, manager_id 
FROM employees;

Itt az employee_id az alkalmazott egyedi azonosítója.
A manager_id egy másik alkalmazott employee_id-jára hivatkozik.

🔹 Hierarchikus lekérdezés az Oracle-ben (CONNECT BY)

---sql

SELECT 
    level, 
    employee_id, 
    manager_id, 
    LPAD(' ', LEVEL * 2) || employee_name AS hierarchy
FROM 
    employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Ez a lekérdezés megmutatja a vállalati hierarchiát, ahol a felső szintű vezetők vannak legfelül.

4. Összegzés

Tábla kapcsolatok feltérképezése:
- FK-k és PK-k azonosítása user_constraints és user_cons_columns segítségével.
- Parent-child relációk vizsgálata.
- Forrás-példány kapcsolatok azonosítása.
Hierarchikus kapcsolatok és önhivatkozások:
- Hierarchikus adatok visszafejtése CONNECT BY segítségével.
Lookup ETL stratégiákhoz való felhasználás:
- Az azonosított kapcsolatok alapján lookup táblákat építhetünk DWH betöltéshez.

IT, BI, DWH, DM, AI

Keresés ebben a blogban