20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata...
Transcript of 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata...
![Page 1: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/1.jpg)
Dátové skladyETL
Juraj Caňo
1.10.2010Pokrocile databazove technologie, FIIT STU
![Page 2: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/2.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 3: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/3.jpg)
Architektúra BI
Metadata
Back-end BI tools
ODS
DWH
Fro
nt
En
dB
Ito
ols
CRM
External
Technology
Billing
ERP
Operational Data Level
(OLTP, legacy systems)
Analytical level
(Query, reporting, dashboards, OLAP, data minning)
ETL
Source systems Metadata
DWH Metadata
Metadata
Access
Access
ETL
Access
ETL
ETL
ETL
StagingArea
ETL
ETL
![Page 4: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/4.jpg)
Architektúra BI
![Page 5: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/5.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 6: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/6.jpg)
ETL
Extract, Transform, Load- „pravidelné procesy plnenia DWH“- „dávkové spracovanie dát a príprava informácií“
� DAILY – každú noc� WEEKLY – každý víkend
� MONHLY – raz za mesiac
� MANUAL – na požiadanie
![Page 7: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/7.jpg)
ETL
Extract – Transform - LoadExtract – Clean – Conform – Deliver
� prenos dát zo zdrojových systémov do DWH
Prax ukazuje, že ETL tvorí až 65% prácnosti projektu !
![Page 8: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/8.jpg)
ETL - Extract
Získavanie údajov zo zdrojových systémov� typy HW a operačné systémy� formy uloženia dát (DB, csv súbory, ...)
� forma prístupu (DB native, ODBC, app, ...)
� znakové sady
� mechanizmus zmien� CDC – changed data capture
![Page 9: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/9.jpg)
ETL - Transformation
Transformácia dát počas spracovania� zmena formátov� zmena štruktúry� agregácia� validácia, zvyšovanie kvality dát� integrácia (z rôznych systémov)
![Page 10: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/10.jpg)
ETL - Transformation
Operational Environment Data Warehouse
Application A m,žApplication B 1,0Application C M,FApplication D muž, žena
m,ž
Coding
Attribute scale
Application A pipe length - mApplication B pipe length - cmApplication C pipe length - kmApplication D pipe length - yds
pipe length - m
![Page 11: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/11.jpg)
ETL - Load
Ukladanie dát� uprava dát do požadovanej štruktúry� príprava na plnenie cieľových tabuliek� update, insert, bulk load� historizácia
![Page 12: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/12.jpg)
Staging area
Metadata
Back-end BI tools
ODS
DWH
Fro
nt
En
dB
Ito
ols
CRM
External
Technology
Billing
ERP
Operational Data Level
(OLTP, legacy systems)
Analytical level
(Query, reporting, dashboards, OLAP, data minning)
ETL
Source systems Metadata
DWH Metadata
Metadata
Access
Access
ETL
Access
ETL
ETL
ETL
StagingArea
ETL
ETL
![Page 13: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/13.jpg)
Staging area
“dočasné úložisko dát”„obraz zdrojových systémov“ (v určitom čase)
Prínosy� znovuspustiteľnosť bez opätovného zaťaženia
zdrojových systémov� záloha dát� jednoduchšie odhalenie chýb v ETL
![Page 14: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/14.jpg)
ETL - realizácia
� hand-coded scripts vs ETL tool
� batch vs streaming data flow� scheduler� exception handling� quality handling� task recovery and restart� metadata� security
![Page 15: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/15.jpg)
ETL transformácie
![Page 16: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/16.jpg)
ETL transformácie
![Page 17: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/17.jpg)
ETL transformácie
![Page 18: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/18.jpg)
ETL transformácie
![Page 19: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/19.jpg)
ETL transformácie
![Page 20: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/20.jpg)
ETL joby
![Page 21: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/21.jpg)
ETL monitoring
![Page 22: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/22.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 23: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/23.jpg)
Proces tvorby ETL
![Page 24: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/24.jpg)
Logické mapovania
„návrh dátových tokov pre plnenie DWH“
Vstupy:� požiadavky používateľov
� dáta v zdrojových systémoch� cieľové štruktúry v DWH
Forma realizácie:
� „rich-textové“ dokumenty� case nástroje
![Page 25: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/25.jpg)
Typy ETL procesov
Full load� úplný prenos celej tabuľky
Incremental Load� prenos len nových a zmenených záznamov� vhodné pre veľké tabuľky - performance
� Problém pri identifikácii zmien na zdroji� Problém pri (logickom) odmazávaní záznamov z
DWH
![Page 26: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/26.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 27: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/27.jpg)
Náhradné k ľúče
Výhody:� šetrenie diskového priestoru:
� primárne kľúče zo zdrojových systémov� textové reťazce – varchar(15); t.j. 1-15 BYTE
� umelé kľúče� integer – 4 BYTE
� rýchlejšie dotazy na DWH� nevýznamový primárny kľuč
Nevýhody� počas procesu transformácie sa realizuje doplnenie /
výmena primárneho kľúča� existencia mapovacích tabuliek pôvodných a umelých kľúčov
![Page 28: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/28.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 29: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/29.jpg)
Historizácia dimenzií
� Koncept Slowly Changing Dimensions (SCD)
� Typy SCD� SCD1� SCD2� SCD3
![Page 30: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/30.jpg)
SCD1
� Prepísanie záznamu novou informáciou – história nie je udržiavaná
Primárny kľúč Prirodzený kľúč Názov produktu Kategória produktu
1123 CC332 Náramok Doplnky
Primárny kľúč Prirodzený kľúč Názov produktu Kategória produktu
1123 CC332 Náramok Šperky
Zmena kategórie produktu
![Page 31: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/31.jpg)
SCD2
� Vytvorenie nového záznamu v dimenzii� Využívanie indikátorov platnosti – dátumy,
flagy
![Page 32: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/32.jpg)
SCD3
� Odloženie záznamu do nového stĺpca� Alternate reality
![Page 33: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/33.jpg)
ETL v BI architektúre
Extract, Transform, Load
Proces tvorby ETL
Náhradné (umelé) k ľúče
Historizácia dimenzií
Grain faktových tabuliek
Obsah
![Page 34: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/34.jpg)
Grain faktových tabuliek
� Transaction grain� Záznam je do faktovej tabuľky vložený len v prípade
nastatia konkrétnej udalosti
� Periodic snapshot� Pravidelný obraz dát za konkrétne obdobie
� Accumulating snapshot� Popísanie procesu s jasným začiatkom a koncom� Jeden záznam reprezentuje konkrétny proces od jeho
začiatku po súčasnosť
![Page 35: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/35.jpg)
ETL nástroje
Informatica Power Center
Enterprise Data Integration Server
IBM InfoSphere DataStage
Data Transformation Services BusinessObjects
Data IntegratorOracle Warehouse Builder
![Page 36: 20101001 DWH ETL JCano - Profinit, profinit.eu · 2016. 10. 10. · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational](https://reader035.fdocument.pub/reader035/viewer/2022062507/5fd8b14dc89e2f70e549bbe3/html5/thumbnails/36.jpg)
Diskusia
� Komentáre� Otázky
� Pripomienky� Upresnenie
� Poznámky� …