IBM Business Intelligence Solution Seminar 2005 - IBM Business Consulting Service (cslee@kr.ibm.com)
I. - II. DW ETT Best Practice
(DW)., (EDW). Time 1980 ~1990 1995 2000 2005 * 1980 IBM Information Warehouse DW ( ) * 1992 W.H.(Bill) Inmon Subject oriented, Time-variant, Nonvolatile collection of data in support of management Decision support system * 1994 Kelly DBMarketing DW DW (Data Mart) CRM DW DW (EDW)
,.. (TB) 100 100TB / 10 <10TB 1 < 1TB >1TB Transaction DW -> DW
. support call ETT Data Mgmt Application use B/S Reside in HA H/W, S/W, N/W IT Infra
: EDW ( A) BMT, POC (Pilot), IBM. ERP ICIS DB / /MIF CDR 1 Feedback Loop /,, HUB EDW Administration (Security, Scheduling, Backup & Recovery) DB Meta Data Repository,, Set Set DQI ETT Tracking Report Reporting Analysis ABC SEM BIS IT-BSC - EDW, -, - PMIS BI CRM IBIS NETIS / Ad-Hoc Reporting ROLAP MOLAP Application RM/AM
DW DW.. Integrity, Extensibility, Flexibility (,,..) Supertype Subtype DW ( Circulation )
Best Practice : TDWM (Telecommunication Data Warehouse Model) DW TDWM,, IBM. Raw SoR, 1 Summary Area, Star Schema 2 Analysis Area, Feedback Area. TDWM SoR, &,,, Call ABC, Type.
: Framework DW A Layered. (Staging) (System Of Records) MIF (Mart Interface) (Summary) (Analysis) :, : / EDW : Summary, Aggregation : MIF : Modeling Layered Customer Analysis Financial Analysis Profitability Analysis Usage Analysis MIF 1 2 Star Schema -> ( ),MIF Distribution /DB 3NF (ER Model) Fact
: (SoR) DW TDWM Customizing SoR,. DW (PD) (CD) (LO) (IP) (AR) _ (EV_C) (CL) _ (EV_U) (BD) (RI) _ (EV_F) _ (EV_L) (LO) (IP) Super type / Sub type (PD) - Super type Subtype (AR) ER Modeling (RI) (EV-C) (EV-U). - (CD). - / (EV_F) (CL). Surrogate Key ( ) -
: ID DW AEDW / / ID Single View. ICIS,,,,,, I I D Appl. Appl. EDW CRM / CDR Single View Customer ID ID Application Application (IP) (IP) ID ID Single Single View View
ETT DW ETT DW 50%. ETT. Common modulization, Error Minimization, Performance Maximization ETT,,,, 1 file ETT DB Link, Full data file unload File handlingett, ETT DW DB handling, Loading ETT, DB,, SQL Static SQLDynamic SQL (,,..), ETT,, DW ETT (Extraction,Transfortation, Transformation) ETL.
: ETT DW ERP ICIS. Highway SQL*Net FTP Channel MQ DataStag e EAI SyncSort,, DB2 Load / Import,,, DB /2 DataStag e DB2 Load / Import E*SQL E*SQL QualityStag e,,, DB DB / Export E*SQL E*SQL E*SQL Reverse / DataStage Loader DataStag e CRM ABC SEM CRM. ETT ETT DB MDR ETT (MDR)
: ETT ( ) ETT ( ) ETT / TWS (Master) / FILE EDW ( / / ) FTP FTP SyncSort QualityStage WMQ Adapter FILE / / / FILE DataStage WMQ WMQ Adapter DataStage DB2 export WMQ DataStage DB2 load / import / FILE Embedded SQL DB 101 / / DB
:. /, Timestamp,, 4. EDW Highway SQL*Net DataStage DataStage SyncSort ( ) DB e-mail Excel(csv) Excel(csv) FTP File Full Copy Full Copy ( ) DB Loading File FTP PUT FTP File File FTP FTP GET File DataStage Job SAM File 22 33 ORACLE DUMP 22 33 DUMP ORACLE MERGE
: -> (SoR), (SoR) ETT. 7, CASE I. - CASE I ( ) file CASE I CASE I 3 4 Insert Update 5 Delete Key Map ( ) SyncSort 1 file 2 ( ) 6 7 8 Insert Update Delete file ( ) (I/U/D ) (, ) 1) / 2) file Table 3) record I Key Map TableInsert 4) record U Key Map TableUpdate( ) 5) record D Key Map TableUpdate(,IUD = D ) 6) record TableKey Map Join DBInsert 7) record TableKey Map Join DBUpdate 8) record TableKey Map Join DBUpdate(IUD = D ) -> /,MIF.
DW H/W,S/W,. Parallel processing, Extensibility, Stabilization H/W DBMS H/W,S/W Gigabit Eithernet ETL tool Data DB Partition CPU, Memory, Disk Storage DW (HACMP) RAID (,,, I/O, CPU, Database, Memory,)
: DW P690+ Cluster DB2 UDB,. DB.. DB / / / Sort/ /Sort/ / MDR/ MQ DB Link FTP Temp. Highway 101 Node 1 Temp. Node 2 Sort/ /Sort/ /Sort/ /Sort/ / (Layer 4,5) (Layer 3) SoR (Layer 2) 1 (Layer 1) Temp. Temp. Temp. Temp. Node Node 3 4 USB (1 Instance) Node 5 Node 6 DB DB2 UDB DB I/O,
DW. DW,. Right Time, Right Place, Right Person PMO QA,IT, DW DW 1) 2) 3) 4) (,,,..) (,,,)
Best Practice : DW. DW Executive Sponsor DW Business Sponsor DW Technical Sponsor Steering Committee PMO Project Manager DW Strategiest DW Information Architect Change Management Coordinator Business Team Technical Team Business Req. Analyst Source Data Analyst Back-End development Data Modeler Reporting and Analytics Reporting & Analytics Programmer Technical Support Tool Administrator Quality Assurance Analyst ETL Programmer Portal Development Analyst System Administrator Data Steward DB Administrator H/W Support Business Subject Matter Expert Portal Security Administrator DW Team roles Supporting roles IBM
: DW. / / / / PQM( ) PMO PQM( ) PMO * TLG TLG * *SoR * *MIF AP AP / (,,, ) TLG TLG AP / /.