20101001 dwh etl jcano€¦ · architektúra bi metadata back-end bi tools ods dwh front end b i...
TRANSCRIPT
![Page 1: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/1.jpg)
Dátové skladyETL
Juraj Caňo
1.10.2010Pokrocile databazove technologie, FIIT STU
![Page 2: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/4.jpg)
Architektúra BI
![Page 5: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/15.jpg)
ETL transformácie
![Page 16: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/16.jpg)
ETL transformácie
![Page 17: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/17.jpg)
ETL transformácie
![Page 18: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/18.jpg)
ETL transformácie
![Page 19: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/19.jpg)
ETL transformácie
![Page 20: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/20.jpg)
ETL joby
![Page 21: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/21.jpg)
ETL monitoring
![Page 22: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/23.jpg)
Proces tvorby ETL
![Page 24: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/29.jpg)
Historizácia dimenzií
� Koncept Slowly Changing Dimensions (SCD)
� Typy SCD� SCD1� SCD2� SCD3
![Page 30: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/32.jpg)
SCD3
� Odloženie záznamu do nového stĺpca� Alternate reality
![Page 33: 20101001 DWH ETL JCano€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/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€¦ · Architektúra BI Metadata Back-end BI tools ODS DWH Front End B I too ls CRM External Technology Billing ERP Operational Data Level (OLTP, legacy systems)](https://reader036.vdocuments.us/reader036/viewer/2022063017/5fd8b14ec89e2f70e549bbe8/html5/thumbnails/36.jpg)
Diskusia
� Komentáre� Otázky
� Pripomienky� Upresnenie
� Poznámky� …