HATÉKONY ETL FOLYAMATOK WORKSHOP

HATÉKONY ETL FOLYAMATOK WORKSHOP Gollnhofer Gábor – JET-SOL Kft.

Nyilvántartási szám:

503/1256-1177

JET-SOL KFT. • Alapadatok • 2003-ban alakultunk • Több mint 120 magasan képzett munkatárs

• Ügyfélkör • Nagyvállalati szektor • Magyarországon és Közép-Kelet Európában

• Értékeink • Precizitás, professzionalitás, hatékonyság DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

2.

DW & BI SZAKTERÜLETEINK • Szakterületeink • • • •

Adattárház és üzleti intelligencia rendszerek Adatvagyon biztosítás (Data Governance) Metaadat kezelés Vezetői információs rendszerek

• Technológiai partnereink • EMC2, Oracle, Microsoft, SAP, Teradata, AnalytixDS

DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

3.

HATÉKONY ETL FOLYAMATOK KIALAKÍTÁSA • Félnapos workshop • DW adatintegráció és ETL • tervezési, fejlesztési és üzemeltetési témakörök

• Workshop • • • • • • •

ETL architektúra ETL eszközök mappingek tervezése/kezelése némi adatmodellezés metaadat kezelés DW automatizálás Menetközben bemutatok egy-két használható eszközt, megoldást DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

4.

HATÉKONY ETL FOLYAMATOK KIALAKÍTÁSA • Saját kérdések? • amiket megpróbálunk közösen megvitatni és akár megoldani is


2014.06.06.

5.

TARTALOM • • • • • • • • • •

Bemutatkozás  Alapelvek Extract – adatok kinyerése & összegyűjtése Transform – transzformációk Load – betöltés Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

6.

ALAPELVEK • Az ETL helye és szerepe az architektúrában • ETL/ELT • Három adatmozgatási modell


2014.06.06.

7.

ALAPELVEK AZ ETL HELYE ÉS SZEREPE AZ ARCHITEKTÚRÁBAN Adatforrások

Történeti adattárház

Staging terület Érkeztetés és transzformáció terület

Elemi történeti adattár (DW – Data Warehouse)

Felhasználói elérés

Témakör orientált adatpiacok (DM – Data Mart)

D7 D6

D8

Pénzügy Fix és paraméterezett riportok

SF 1 FACT 2

D2

Értékesítés D1

D3 D1

SD 2 Dokumentum kezelés

Ad-hoc riportok, elemzések

D3

FACT 1 D5 D1

ST 3 D4

CRM

Vezetői portál D6

Adminisztráció, monitoring

D5 FACT 3

Gyártás Audit Trail

D4

D2

Kötelező jelentések Analitikus CRM

Külső adatok

Interfaces

Mappings META 1 Meta adatok META 2

Kézi adatforrások (xls, csv)

Tulajdonosi riportok Data mining

Metaadat karbantartás

Törzsadat karbantartás (master data)

Mapping karbantartás


2014.06.06.

8.

ALAPELVEK ETL/ELT • ETL = Extract-Transform-Load • • • •

A „klasszikus” felépítés Dedikált ETL szerver Teljesen(?) átalakított adatok töltődnek az adatbázisba Pl. Data Stage

• ELT = Extract-Load-Transform

• Ha már van adatkezelésre optimalizált szerver, használjuk! • Az extraktált adatok töltődnek az adatbázisba, ott történik az átalakításuk • Pl. Oracle Data Integrator

• ETLT? és egyéb kombinációk


2014.06.06.

9.

ALAPELVEK ADATMOZGATÁSI MODELLEK


2014.06.06.

10.

TARTALOM • • • • • • • • • •

Bemutatkozás  Alapelvek  Extract – adatok kinyerése & összegyűjtése Transform – transzformációk Load – betöltés Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

11.

EXTRACT – AZ ADATOK KINYERÉSE • • • •

Push/pull Full/changed File/direct/messaging CDC (timestamp, trigger, log, replikáció, compare)


2014.06.06.

12.

ADATKINYERÉS PUSH / PULL • Push

• Az adatforrás küldi az adatokat • Amikor a forrásnak kényelmes • A DW oldalon érkeztető (landing) terület • Közösen definiált interfészek (fájl/DB/messaging) • Forrás oldali fejlesztést igényel

• Pull

• A cél (DW) olvassa ki az adatokat • Amikor a DW-nek kényelmes (és a forrás elérhető) • A DW oldalon érkeztető terület (nem „kötelező”) • Közvetlen elérés v. szabványos interfészek (pl.natív/ODBC/JDBC) • Forrás oldali jogosultságot igényel


2014.06.06.

13.

ADATKINYERÉS PUSH / PULL • Lehet vegyes megoldás is: • Az adatforrás: • leválogatja az adatokat egy dedikált extrakt területre (amikor neki kényelmes) • Jelöli, hogy készen van!

• A DW: • elveszi a dedikált területről a kész adatokat (amikor neki kényelmes) • Jelöli, hogy készen van!

• Nagyobb hibatűrés, de dupla helyigény DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

14.

ADATKINYERÉS FULL / CHANGED • Full – teljes leválogatás • Egyszerűbb(?) • Kicsi adatmennyiség • Nincsenek megbízható változás adatok

• Changed – csak a változott adatok • Bonyolultabb(?) • Megbízható változás adatok vannak • CDC (changed data capture) megoldások DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

15.

ADATKINYERÉS FILE / DIRECT / MESSAGING • Fájl alapú • Közvetlen elérésű • Üzenet alapú


2014.06.06.

16.

ADATKINYERÉS FÁJL ALAPÚ • Szabványos formátumok, előre definiált struktúra • CSV, XML, JSON • SAP printout? Log fájlok?

• Előnyök • • • •

Egyszerű Platform & technológia független (codepage? CRLF?) Újra tölthető Lazán csatolt rendszerek

• Hátrányok

• Forrás oldali fejlesztést igényel • Plusz tárolót igényel DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

17.

ADATKINYERÉS KÖZVETLEN ELÉRÉSŰ (DIRECT) • Szabványos formátumok, előre definiált struktúra • DB  DB (ELT) • Natív/ODBC/JDBC/gateway/webservice

• Előnyök • Gyorsan kialakítható, módosítható • Nem kell plusz tároló

• Hátrányok • Szorosan csatolt rendszerek • Jogosultságok • Teljesítmény hatások (a forrás rendszeren), konzisztencia? DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

18.

ADATKINYERÉS ÜZENET ALAPÚ (MESSAGING) • Szabványos formátumok(?), technológia • Messaging (pl. MQ), replikáció

• Előnyök • Lazán csatolt, aszinkron • Akár tranzakciónként frissíthetünk • Nem kell plusz tároló (queue?)

• Hátrányok • Plusz infrastruktúra • Összetett felépítés • Teljesítmény? DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

19.

ADATKINYERÉS TIPPEK & TRÜKKÖK • Fájl alapú • Trigger fájl  Az extraktum kész, vihető • Fájl név, dátum&idő, rekordszám, checksum

• Névkonvenció • Forrás_típuskód_ÉÉÉÉHHNNóóppss.txt (csv,xml,json,gzip)

• Adatforrásonként saját „postaláda”

• Közvetlen elérésű • Metaadat tábla/táblák a fentiekhez hasonló tartalommal

• Egyéb fontos dolgok! • Locale settings, codepage (unicode? UTF-8/UTF-16/UCS-2) DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

20.

ADATKINYERÉS CDC – MEGVÁLTOZOTT ADATOK KERESÉSE • Forrás adat változások • Új adat • Módosítás • Törlés • Fizikai  full extrakt(?) • Logikai == módosítás(?)


2014.06.06.

21.

ADATKINYERÉS CDC - TECHNOLÓGIÁK • • • • •

Timestamp Trigger Log Replikáció Full compare


2014.06.06.

22.

ADATKINYERÉS CDC – TIMESTAMP • A forrás adat tartalmazza az utolsó módosítás timestamp-jét • Batch jellegű ETL-hez • Megbízható? • Automatikus v. az alkalmazás állítja be • Ha megbízható  csak változások kinyerése • Különben  csak full extrakt

• Fizikai törlés lehetséges?

• Ha igen  csak full extrakt

• De két extrakt közötti változások elvesznek! DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

23.

ADATKINYERÉS CDC – TRIGGER • A forrás rendszer (db) változás esetén automatikusan eltárolja a megváltozott adatokat (egy másik táblában?) • Batch jellegű ETL-hez • Megbízható? • Igen (elvileg – kivéve, ha hibás a kód) • Két extrakt közötti változások is megvannak

• De

• Fejlesztést igényel • Plusz erőforrást igényel • Nem szeretik  DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

24.

ADATKINYERÉS CDC – LOG • A forrás adatbázis tranzakció naplója • Batch jellegű ETL-hez • Megbízható?

• Igen, rendszer szintű • Ha be van kapcsolva és nem végeznek nem logolt műveleteket • Két extrakt közötti változások is megvannak

• De

• Nem mindenhol/mindig van bekapcsolva • A forrás tükre kell, hogy használni tudjuk • Némi erőforrásigény, de „nagy” diszk igény DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

25.

ADATKINYERÉS CDC – REPLIKÁCIÓ • Forrás db log alapján automatikus • Akár tranzakció szintű • Aszinkron (publish-subscribe; ~messaging)

• Megbízható? • Mint a Log • Két extrakt közötti változások is megvannak

• De • Plusz szoftver igény, plusz erőforrás igény DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

26.

ADATKINYERÉS CDC – TELJES HASONLÍTÁS (FULL COMPARE) • Teljes forrás állományt veszünk át és DW oldalon különbség képzés (a megelőző teljes állomány alapján) • Megbízható? • Igen (elvileg – kivéve, ha hibás a kód) • Két extrakt közötti változások elvesznek

• De • Feleslegesen sok adatot mozgatunk • Plusz diszk és feldolgozás igény DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

27.

ADATKINYERÉS CDC – TIPPEK & TRÜKKÖK • Bármelyik megoldást választjuk • Mindig kellhet teljes összehasonlítás, mert • Kiesünk a szinkronból  Teljes feltöltés

• Teljes összehasonlítás • HASH kód (pl. MD5) alapú • Táblák minden sorára HASH kód, majd Select * from extr_tab full outer join dw_tab on dw_tab.pk = extr_tab.pk where dw_tab.hash <> extr_tab.hash DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

28.

ADATKINYERÉS CDC – TIPPEK & TRÜKKÖK • Teljes összehasonlítás • Gyors oszlop alapú:


2014.06.06.

29.

TARTALOM • • • • • • • • • •

Bemutatkozás  Alapelvek  Extract – adatok kinyerése & összegyűjtése  Transform – transzformációk Load – betöltés Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

30.

TRANSFORM – ADATOK ÁTALAKÍTÁSA • Érkeztető terület (landing) • A forrásadatok az extraktált formában itt várják a feldolgozást (akár fájl, akár db táblák&sorok) • A feldolgozott adatokat archiváljuk

• Feldolgozó terület (staging) • A tényleges feldolgozásokat, transzformációkat itt végezzük el • Általában az adott ETL futáshoz szükséges adatokat tartjuk benne

• Persistent Staging? DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

31.

TARTALOM • • • • • • • • • •

Bemutatkozás  Alapelvek  Extract – adatok kinyerése & összegyűjtése  Transform – transzformációk  Load – betöltés Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

32.

LOAD - BETÖLTÉS • „Bárhogy”, csak gyors legyen • Bulk load • Logged/non-logged • Partitioning, partition-swapping

• Betöltési hibák kezelése • Adathibák • Programhibák • Paraméterezési hibák DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

33.

LOAD - BETÖLTÉS HIBÁK KEZELÉSE • Adathibák • • • •

Hiba tábla/állomány  Mit fogunk kezdeni vele? Részleges betöltés  Konzisztencia? Auditálhatóság? Kihagyás  Konzisztencia? Auditálhatóság? DataQuality jel/dimenzió

• Program/paraméterezési hibák • Észrevettük? • Nem  minden OK  ?  javítás/újra töltés • Igen  megállunk/folytatjuk v. javítás után & újra/folytatjuk

• Adjon lehetőséget az újra futtatásra! DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

34.

NAGY MENNYISÉGŰ ADATOK ÉS ÉRZÉKENY ADATOK KEZELÉSE • Nagy mennyiségű adatok • Csak a feltétlenül szükséges adatokat mozgassuk • Bulk műveletek (minimális v. nolog) • Tömörítés és tömörítve mozgatás a hálózaton

• Érzékeny adatok kezelése • Önálló területet biztosítsunk nekik • A hálózaton is kódolva mozgassuk • Anonimizálás, egy irányú kódolás DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

35.

TARTALOM • • • • • • • • • •

Bemutatkozás  Alapelvek  Extract – adatok kinyerése & összegyűjtése  Transform – transzformációk  Load – betöltés  Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

36.

METAADATOK • Mit, mikor – tervezési metaadatok • Monitorozás – működési metaadatok


2014.06.06.

37.

TERVEZÉSI METAADATOK MIT, MIKOR • • • • • •

Adatforrások Adatstruktúrák Mapping ETL folyamatok Függőségek Ütemezés


2014.06.06.

38.

TERVEZÉSI METAADATOK METAADAT MODELL • „Házi feladat”  • vagy lásd. később


2014.06.06.

39.

MŰKÖDÉSI METAADATOK MONITOROZÁS • Futási statisztikák • Feldolgozási idők (folyamat kezdete, vége, időtartama) • Adatmennyiségek (megkapott, feldolgozott fájl/message/rekord/bájt/stb.) • Hibák (száma, típusa) • Folyamatok (státusz) • Adatminőség (szélső értékek, darabszámok, eloszlások, hibás adatok száma, hibatípusok, stb.)


2014.06.06.

40.

MŰKÖDÉSI METAADATOK METAADAT MODELL • Tervezési és működési metaadat modell: • • • • • • • • DW fórum 2014 - Hatékony ETL workshop

Fogalmak Szereplők, szerepkörök Jogosultságok Forrásrendszerek Objektumok Transzformációk Folyamatok Vezérlés, napló 2014.06.06.

41.

TARTALOM • • • • • • • • • •

Bemutatkozás Alapelvek Extract – adatok kinyerése & összegyűjtése Transform – transzformációk Load – betöltés Nagy mennyiségű adatok és érzékeny adatok kezelése Metaadatok ETL tervezés, fejlesztés ETL automatizálás Kérdések & válaszok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

      

42.

ETL TERVEZÉS, FEJLESZTÉS • Forrás felmérés (elérhetőség, technológia, struktúrák, profiling) • Folyamatok (csoportosítás, függőségek, ütemezés) • Mapping definíciók • ETL változás kezelés


2014.06.06.

43.

ETL TERVEZÉS, FEJLESZTÉS ADATFORRÁS FELMÉRÉS • Elérhetőség, technológia, struktúrák • Profiling


2014.06.06.

44.

ETL TERVEZÉS, FEJLESZTÉS FOLYAMATOK - FÜGGŐSÉGEK, ÜTEMEZÉS • Kód vezérelt • Előre definiált végrehajtási sorrend • A végrehajtási sorrend „behuzalozva” • Optimalizált(?) • Új folyamatok beillesztése bonyolult (újra optimalizálás?) • Az ETL eszközökben ez az „alapértelmezett” futtatási mód

• Adat vezérelt • Csak az adat függőségek definiáltak (és prioritások) • A végrehajtási sorrend a tényleges adatfeldolgozástól függ • Új folyamatok beillesztése egyszerű (csak a függőségeket kell definiálni) • Az ETL eszközökben keretfolyamatokat kell készíteni


2014.06.06.

45.

ETL TERVEZÉS, FEJLESZTÉS MAPPING DEFINÍCIÓK • Excel tábla • demo

• Modellező eszköz • demo: PowerDesigner

• Spec. mapping kezelő és ETL generáló eszköz • demo: Analytix Mapping Manager

• ETL eszközök • Mindenkinek a kedvenc ETL eszköze (és SQL eljárásai) DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

46.

ADATFORRÁS ELEMZÉS EXCEL TÁBLA


2014.06.06.

47.

INTERFÉSZ DEFINÍCIÓ EXCEL TÁBLA


2014.06.06.

48.

MAPPING DEFINÍCIÓ EXCEL TÁBLA


2014.06.06.

49.

ETL TERVEZÉS, FEJLESZTÉS MAPPING DEFINÍCIÓK • Excel tábla



• demo

• Modellező eszköz • demo: PowerDesigner



2014.06.06.

50.

MODELLEZŐ ESZKÖZ POWERDESIGNER MAPPING EDITOR •

•

•

Tervezés: adatbázis, folyamatok, drag & drop ETL mapping Generálás: adatbázis, ETL SQL query dokumentáció Egyedi bővítmények DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

51.

POWERDESIGNER BŐVÍTMÉNYBŐL GENERÁLT ETL SQL KÓD


2014.06.06.

52.

POWERDESIGNER MODELLEZŐ ESZKÖZ • demo: SAP PowerDesigner • How to Use the Mapping Editor in PowerDesigner http://youtu.be/9HzmHEKHxyo


2014.06.06.

53.




• demo

• Modellező eszköz



• demo: PowerDesigner



2014.06.06.

54.

MAPPING KEZELŐ ÉS ETL GENERÁLÓ ESZKÖZ ANALYTIX MAPPING MANAGER • • • • • • • •

Drag & drop mapping Szabályok Repository ETL import Excel import ETL export Excel export Bővíthető metaadatok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

55.

ANALYTIX MAPPING MANAGER ETL GENERÁLÁS • • • • • • •

DataStage Informatica SSIS XML egyéb egyedi bővítmények ETL konverzió


2014.06.06.

56.

MAPPING KEZELŐ ÉS ETL GENERÁLÓ ESZKÖZ • demo: Analytix Mapping Manager • http://www.youtube.com/watch?v=GitbN65BzFM


2014.06.06.

57.




• demo

• Modellező eszköz



• demo: PowerDesigner

• Spec. mapping kezelő és ETL generáló eszköz  • demo: Analytix Mapping Manager


2014.06.06.

58.

ETL TERVEZÉS, FEJLESZTÉS VÁLTOZÁS KEZELÉS • A DW-ben tárolt adatokról tudjuk megmondani, hogy mikor kerültbe, melyik ETL eljárással  audit_trail dimenzió • Fontos, hogy megőrizzük a régi ETL kódokat is! • ETL eszközök – általában támogatják (repository) • Egyedi fejlesztés – verziókezelő DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

59.

TARTALOM • • • • • • • • • •


2014.06.06.

       

60.

ETL AUTOMATIZÁLÁS FELADATOK • Hogyan kezeljük, ha: • 3 adatforrásunk van <> 30 adatforrás • 50 táblát kell tölteni <> 5000 táblát

• Automatizáljuk az ismétlődő feladatokat • Adatstruktúrák, formátum leírók • Mapping • Feldolgozási eljárások (SQL, map-reduce, scriptek, ETL eszköz kódok) • Tervezési metaadatok DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

61.

ETL AUTOMATIZÁLÁS ESZKÖZÖK • ETL eszközök • Könnyen(?) tanulható, átlátható, grafikus felület • Scriptelhető?

• Tervezőeszközök(?) • Adatmodellezés & mapping definíciók • ETL kódgenerálás (staging táblák, ETL kód) • Scriptelhető?

• Saját fejlesztés • Kód generálás DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

62.

MIVEL AUTOMATIZÁLJUNK? DW TERVEZÉS & FEJLESZTÉS • „Klasszikus” ETL eszközök • Scriptelhetők: „saját” script nyelv és/vagy repositoryba írás(?) • DataStage, Informatica, ODI, OWB, Pentaho, Talend, stb. • BASIC, Java, TCL, Groovy, SQL, saját, stb.; XML/XMI

• „ETL/BI automation” eszközök, pl. • • • • •

Analytix Mapping Manager BIReady Consensus Quipu Wherescape RED

• Saját, egyedi fejlesztés DW fórum 2014 - Hatékony ETL workshop

2014.06.06.

63.

TARTALOM • • • • • • • • • •


2014.06.06.

         64.

KÉRDÉSEK & VÁLASZOK [email protected] www.jet-sol.hu


2014.06.06.

65.

KAPCSOLAT 1143 Budapest, Ilka utca 2-4. Telefon: +36 1 453 0432 Fax: +36 1 430 1976 E-mail: [email protected]

www.jet-sol.hu


2014.06.06.

66. Nyilvántartási szám:

503/1256-1177

HATÉKONY ETL FOLYAMATOK WORKSHOP

Recommend Documents