Introduction Data Warehouse Based on IBM DB2 8 th Nov. 2005

1. Introduction Data WarehouseBased on IBM DB28thNov. 2005 Lee, JeonGeon (leejg@kr.ibm.com) Business Intelligence S/W Group, IBM Korea DB2 Information Management Software

2. Agenda • What is Data Warehouse? • Trends of Data Warehouse • Real-time Data Warehouse • DW Architecture by IBM • Parallel Database • Case Study : KT EDW

3. 1. What is Data Warehouse? Naver 지식 검색을 활용하세요! 데이터 웨어하우스는 1980년대 중반 IBM이 자신이 하드웨어를 판매하기 위해 처음으로 도입했던 개념으로 IBM은 인포메이션 웨어하우스(Informationn Warehouse)라는 용어를 사용하였다. 이후 이 개념은 많은 하드웨어, 소프트웨어 및 툴 공급 업체 들에 의해 이론적, 현실적으로 성장하였으며, 1980년대 후반 Inmon이 데이터 접근 전략으로 데이터 웨어하우스 개념을 사용함으로써 많은 관심과 집중을 받게 되었다. - From Naver 지식in

4. 트랜잭션 시스템 데이터웨어하우스 OLTP(On-line Transaction Processing) 반복되는 단위업무처리(거래처리) 신속한 거래처리 업무별 정보 현행정보(Current Values) 상세정보 1~2 초 내의 Response Time OLAP(On-line Analytical Processing) 의사결정지원(ad-hoc query) 대량의 정보관리 매우 빠른 속도로 데이터 량 증가 주제영역별 정보 이력정보(Historical Snapshots of Data) 상세정보 + 요약,집계,외부정보 Several seconds to minutes “Analyze your business” “Run your business” OLTP vs. Data Warehouse

5. Data Warehouse… • Query only, no updates, no transactions - Optimized for Retrieval • Data refresh at regular intervals • Separate from OLTP apps • OLTP: “Run your business” • DW: “Analyze your business” • Oriented toward a specific business function • Historical snapshots of data • Potentially rapid data growth - Scalability critical!

6. Data Mart… • Subset of an existing data warehousing • Narrower business function • Query only, no updates, no transactions - Optimized for Retrieval • Data refresh at regular intervals • Separate from OLTP apps • OLTP: “Run your business” • DW: “Analyze your business” • Oriented toward a specific business function • Highly summarized data • Historical data

7. DW, DM Query … Select product, market, customer… , sum(revenue), sum(margin) … from sales, product, market, customer ... where date between 01/01/2002 and 12/31/2002 and market.state in (‘서울’, ‘경기’) and customer_age in (18 to 34) group by product… order by customer… ; • “갑돌”이라는 고객의 주소는? • 주민등록번호 123456-1234567의 고객이 이번 달 보험료는 납부했는가? • 고객번호 101번의 고객이 가입하고 있는 모든 보험의 내역은? • 고객들이 정말로 선호하는 것은 무엇인가? • 지난 5년간 고객들의 구매 패턴은 어떻게 변해왔는가? • 작년에 각 지역별로 가장 많은 이익을 남겨준 제품은 무엇인가? • 각 지역별로 top 10에 드는 고객은 누구인가? • 분기별로 각 벤더들의 제품에 대하여 매출액, 비용, 순이익은 얼마인가?

8. Star Schema Customer Dimension Time Dimension customer code customer name age gender address company income level education … … (수백만 건) time code order date month quarter year … … (수백 건) Fact Table : Sales time code product code store code customer code revenue cost unit_sold net profit quantity … … (수십억 건) Product Dimension product code product name product type vendor name … … (수천 건) Store Dimension store code store name store type street state … … (수백 건) • Fact table : dimensional model의 중심, measures, • how much or how many • Dimension tables : what, where, when

9. DW 개발 단계 • Think big • Build small • Verify success against success criteria Analysis • 포괄적으로 interview를 한다 • executive, manager, business analyst, end user • 기본 report • DW에 기대하는 요구사항 • Scope 결정, requirement 정의 • 필요한 data가 모두 사용 가능한가? 제시간 안에 data를 얻을 수 있는가? • 필요한 H/W, S/W 사양은? Design • 주제 영역을 정한다 • measure/dimension 정의 • fact table의 구성요소 정의 • Dimensional model 생성 • 사용자들과 함께 model 확정 • Physical model 생성 Data gathering • 데이터 추출 • 데이터 cleansing • 데이터 변환 • 데이터 로드 Implement • Query prototype 실행 및 tuning • BI 툴을 사용한 개발 • 필요한 application 개발 시연 & feedback • 일정 사용자들에게 DW와 개발된 프로그램 소개 • Feedback • Tuning • Recovery plan

10. 기업 경영상의 효과 • 기업 경쟁력 향상 • 하나의 일관성 있는 정보제공 • 사용자에게 직접 정보제공 • 정보자산의 효율적 이용 • 새로운 시장기회 발견 • 의사결정정보에 관한 전산실 업무 감소 • 양질의 정보제공 • Business Process Reengineering • 과거 정보시스템 리엔지니어링 최종사용자 부문의 효과 • 전산부서 의존도 감소 • 사용의 편리성 • 원하는 정보에 신속하게 접속 • 다양한 분석 수행 • 워크그룹 생산성 증대(업무 프로세스 개선) • 환경변화에 신속하게 대응 Data Warehouse 효과 정보기술 부문의 효과 • OLTP 시스템 작업량 감소 • SQL 프로그래밍 작업량 감소 • 보고서 생성 작업량 감소 • 유지보수 비용 절감 • 기 투자된 시스템 가치 상승 DW의 구축 효과

11. 2. Trends of Data Warehouse

12. Top 5 trends …. BI Becomes: Pervasive Mission Critical & Essential Just in time Maximize Business Insight The driver of action Forward looking

13. Trend #1 – BI Becomes “Pervasive” • 조직이 보다 많은 사용자들의 다양한 업무수행을 위해 BI가 활용될 수 있기를 기대 • 비즈니스 일선에 좀 더 자세하고 수행 가능한 정보를 제공 • 아주 전문적인 분석가나 BI 전문가가 아니더라도 쉽게 사용될 수 있는 insight를 요구 • BI가 이젠 더 이상 별도의 영역이 아니라 업무 수행의 모든 영역에 스며들어 별도로 분리할 수 없게 됨. • Eclipsys Health Systems – 환자 침대 옆의 컴퓨터들이 a) 병원의 데이터베이스와 연결되어 환자의 상세정보 조회가능 b) 여러 병원의 데이터베이스와도 연결되어 치료 정보와 결과를 제공 • Monaco Cardio Thoracic Centre – 아주 상세한 환자의 레코드들과 예정된 절차, 치료과정 등을 조회 • Staples – 재고 관리,상품간의 제휴, Cross Sell과 Self Service 채널 등

14. Trend #2 – BI Becomes “Just-in-time” • 가장 최근의 정확하고 개인화된 데이터를 기반으로 비즈니스 insight 제공. • 의미 있는 정보가 어떤 device를 통해서나 어느 곳으로나 바로 전달될 수 있게 됨으로써 정보의 가치를 극대화하도록 기대 • Pepsi – “on the fly” 배달 트럭의 경로를 결정함에 있어 가장 수익성있는 배달 경로를 적시에 발견

15. Trend #3 – BI Becomes the Driver of Actions • BI와 analytics가 비즈니스 프로세스와 작업의 흐름에 바로 결합되어짐 - 조직의 DNA와 같은 일부가 됨. • Analytics를 operational 시스템과 연계하여 insight와 비즈니스 수행 사이의 Closed loop cycle을 형성. • 이 insight를 활용하여 비즈니스 전략과 수행을 가이드 • Bank One – 지점의 수익성 시스템이 핵심적인 관리 도구가 되어 직원들의 업무수행과 보너스를 결정 • Nieman Marcus – 여러 상점들의 재고 관리 시스템이 요구에 따라 각 지역 branch 들 사이의 상품의 흐름을 제어. • Home Deport – 고객,재고,수익 그리고 employee들의 관리를 도울 수 있는 광범위한 enterprise system • Credit Union Of Texas – “CEO Flight Deck” 고객, cross-sell/up-sell,캠페인의 효과를 추적하고, spatial dimensions도 분석 가능

16. Trend #4 – BI Becomes Forward Looking • 단순히 레포팅과 분석의 범위를 넘어서 BI는 predictive modeling으로 발전되고 있음. • 향후 데이터 마이닝이나 패턴인식과 같은 차원 높은 분석기법에 대한 관심과 기대가 증대될 것임. • 의사결정 프로세스가 실제로 데이터를 기반으로 한 expert 시스템과 통합. • Ford – 콜센터에서 수집된 voice 데이터의 마이닝을 통해 안전에 대한 이슈와 recall등을 예측 • Boots The Chemist (UK) – 스마트 카드 로열티 프로그램이 수익성있는 고객을 확보할 수 있게 함

17. Trend #5 – BI Becomes Mission Critical & Essential • BI 솔루션이 비즈니스 수행과 성공 그리고 경쟁우위를 점할 수 있는 비즈니스의 핵심이 되고 있음. • BI 아키텍처가 보다 강력하고 확장성이 보장될 수 있도록 기대되고 있음. • 수 terabytes의 데이터를 다루고 수천 명의 사용자들이 사용하며, 24*365의 가용성이 요구되고 있음. • 진취적인 조직은 필요한 정보를 비즈니스 사용자에게 “push”함으로써 신속함과 융통성 그리고 결단력을 확보 • Sprint PCS – 100TB 데이터 웨어하우스 운영; 전사 데이터 웨어하우스가 단순한 분석 시스템이 아니라 비즈니스 수행과 고객 billing을 위한 핵심 시스템. 만일 DW가 down되면 기업으로서는 ‘money’를 잃게 되는 심각한 상황을 초래. 기본 애플리케이션과 비즈니스 인텔리전스 정보의 사용이 더 이상 경쟁우위의 요소는 아님. 고객,투자자, regulators,임원과 emploees들이 이미 자연스러운 것으로 인식되고 있음

18. What’s on the minds of 450 of the world’s leading CEOs? CEO 요구사항 CIO 고민들 • IT 와 비즈니스의 결합으로 매출증대와 비용절감의 목표달성 • IT를 통하여 조직의 역할을 지원 • 어떻게 IT가 사람들과 팀 이 좀 더 효과적으로 일할 수 있도록 도와줄 것인가? • 비용절감으로 인한 매출성장 • Key competency: responsiveness • Critical success factor:effectiveness 확보 - people and processes Source: CEO Study of 456 WW CEOs IBM Corporation - Feb 2004 Source: Operating Environment Market Drivers Study, IBM Corp. 2004

19. Yesterday ….. Going back as much as 15 years • Batch로 Data Warehousing updates • Data marts, Warehouse, Transaction Systems과의 분리 • Point in time BI • Stand alone 웨어하우스; 불완전한 EDWs Business Intelligence is evolving… BI used to employ the following fundamentals: 원인: 기술적인 제약, 기능적인 제약, best practices의 부족 Focus:완벽한 data warehouse 구축

20. 보다 빠른 데이터 제공과 성능 향상 Yesterday ….. Going back as much as 15 years Today • Batch로 Data Warehousing updates • Data marts; Warehouse , Transaction Systems과의 분리 • Point in time BI • Stand alone 웨어하우스; 불완전한 EDWs Business Intelligence is evolving… Today’s cutting edge warehouses raise the bar: • 통합,데이터의 중복 감소 • Real-time 가용성을 통한 이벤트 모니터링 • Unstructured 데이터를 포함하여 보다 많은 데이터 소스의 데이터를 통합, 원인 : 기술적인 제약, 기능적인 제약, best practices의 부족 Focus:완벽한 data warehouse 구축 Focus: true Business Intelligence

21. 보다 빠른 데이터 제공과 성능 향상 통합,데이터의 중복 감소 Real-time 가용성을 통한 이벤트 모니터링 Unstructured 데이터를 포함하여 보다 많은 데이터 소스의 데이터를 통합, Business Intelligence is evolving… Unveiling opportunities to significantly change the way you do business Today What this means Yesterday • 가치있는 가공되지 않은 데이터의 사용 증가 • 현재 필요한 데이터에 바로 분석하여 비즈니스 수행 • 배치 시간의 감소 • 복잡성의 감소 • 비용의 감소 • data inconsistencies 감소 • 즉석에서 당일의 정보를 획득하여 즉시 조치 • “Intelligent”의 변화(주기적인 특성 등)를 알 필요가 있음 • 분산된 데이터에 접근 • 낮은 리스크와 EDW와 관련된 비용 • 통합된 마이그레이션 경로 개발 • Batch로 Data Warehousing updates • Data marts; Warehouse , Transaction Systems과의 분리 • Point in time BI • Stand alone 웨어하우스; 불완전한 EDW

22. Information On Demand - 데이터를 Business Insight로 Insight proactive • 많은 데이터 소스로부터 데이터와 컨텐트를 통합 • 데이터를 유용한 정보로 전환 • 이 정보를 실시간 분석으로 intelligent하면서도 신속한 의사결정을 지원 Information Business flexibility & responsiveness active Data passive Business value

23. 3. Real-time Data Warehouse

24. 실시간(Real time) & 준 실시간(Near Real time) • 절대적인 시간이라기 보다는 고객의 환경에 따라 새로이 정의되어야 함 • 데이터가 발생한 후 정보로서 처리되는데 걸리는 시간으로 구분 • 실시간 : 현재의 비즈니스 상태를 반영한 새로운 데이터를 기업 정보로 처리 • 준 실시간 : 현재 발생하는 비즈니스 데이터는 아니지만, 그 기업에 “충분히 신선한(fresh enough)”데이터를 기업의 정보로 처리 source: META, Applied Analytics for Extreme Business Performance Management, 25 September 2002

25. 분석을 위한 데이터의 latency? < 1 second Not sure 6% <1 minute 6% 16% 16% 16% 16% 1시간 미만이 절반이상 <1 day 21% 21% 30% 30% 11% 11% < 1 hour < 12 hours N=419 Source: Colin White & The Data Warehousing Institute, Building the Real-Time Enterprise, Oct 2003

26. 실시간 BI 를 개발하거나 개발 계획이 있는 분야? Source: Cutter Consortium: Corporate Use of Real-Time Data WarehousingBy DMReview.com Web Editorial , July 29,2003

27. 리얼타임 BI 어플리케이션 • 고객 접점 분석 • 온라인 콜센터 고객 스코어링 • 상점 내 ‘즉시 발행’ 쿠폰 • ATM 사용시 개인에 맞는 광고 • 고객 개인에 맞는 웹 사이트 광고 및 관심 분야 표시 • 최적화되고, 예측 가능한 공급망 유지 • 적시 재고 관리 및 모니터링 • 부정행위 적발 • ATM, 휴대 전화, POS • 항공기 티켓의 적정가격 결정 혹은 결항 비행편에 대한 차선 스케줄 결정 • 온라인 대출 신용정보 조회 • 통신/에너지 분야의 망 관련 정보 관리 • 열차, 트럭, 선박 화물의 적재 및 운송 관리

28. 병렬 ETL Engines ODS MQSeries queues DB2 Warehouse 복제 Information Integration 웹 서비스 실시간 BI 동시 사용자 조회 개인화 , 데이터 마이닝, 비즈니스 룰, 캠페인 고객 끊임 없는 적재 Corporate Dashboards 경보, 트리거, KPIs, 분석

29. RouteToLabel MQOut MQIn Formatting & Mapping 비정형 데이터 EII “뷰”를 통한 단일한 가상의 데이터 저장소 구성 App2 App1 ETL, EII, EAI App3 EAI 인터페이스를 이용한 응용 프로그램 간의 호출 방식 응용 프로그램 Data Mart ETL Data Warehouse Data Mart … Data Warehouse Data Mart 데이터 복제 원시 데이터 소스

30. ETL – 적재를 사용한 실시간 환경 • 지속적인 loading 과 사용자 쿼리를 동시에 수행 • 고속 적재를 지원 동시 쿼리 짧은 주기의 지속적인 ETL

31. DB2 II Classic Federation Data Warehouse IBM Extended Search Biological Data And Algorithms EII를 사용한 실시간 환경 DB2 Family 실시간 접근 DB2/390 Sybase VSAM Informix IMS DB2 Information Integrator Software AGAdabas SQL Server CA-Datacom Oracle CA-IDMS Teradata … … Text XML Excel WebSphere MQ WWW, email,… ODBC

32. Data Warehouse EAI를 사용한 실시간 환경 • Queue를 사용하여 데이터의 실시간 전송 • IBM Websphere MQ 연동 function을 DB2에서 제공 Source ETL DB2 Oracle DB2 MQ function MQSI ETL Informix VSAM DB2 MQ Listener User Stored Procedure IMS

33. ETL vs. EII vs. EAI – Strengths and Challenges

34. ETL tool Fulfillment e-Commerce Marketing 비즈니스 응용 프로그램 Integrated Information ETL vs. EII vs. EAI – Strengths and Challenges

35. ETL vs. EII vs. EAI – 기술적 관점에서의 비교

36. ETL tool ETL Best Practices • ETL은 일반적으로 대용량 I/O bound 작업 • 불필요한 staging step을 줄여주어야 함 • 속도가 빠른 storage 사용 • 뜻하지 않은 I/O를 피하라 • “lookup” 프로세스에 주의 • data file의 위치에 유의 • ETL 툴 사용시 생산성 및 데이터 일관성 향상 • data mart에서 data mart로 가는 작업은 피하는 것이 좋음 • 과도한 locking을 피해야 함 • 많은 수의 프로세스들을 병렬로 처리하는 것이 키 • Key to running many concurrent processes in parallel • Query, Load, Backup이 동시에 허용되어야 함

37. Fulfillment e-Commerce Marketing 비즈니스 응용 프로그램 Integrated Information EII Best Practices • 일반적으로 규칙적이지 않은 ad-hoc 액세스에는 적합하지 않음 • 최상의 성능을 위해서 WII는 자주 사용되는 데이터를 캐쉬함으로써 query 실행 비용을 관리하도록 계획을 세움 • WII는 query의 종류와 비용을 관리 • DB2 Query Patroller • remote source 사이에 많은 데이터의 이동이 발생하는 operation의 경우 WII는 꽤 많은 시간이 소요될 것이다 • “permanent basis”에는 WII를 이용하여 “virtual warehouse” 구축을 시도하지 말라, 특히 ad-hoc 액세스가 일어날 것으로 예상되어진다면 더욱 그렇다 • remote 소스에 대한 federated query의 영향을 항상 염두에 두어야 한다 • Remote data에 대해서는 target access • 데이터의 흐름은 remote 소스에서 federated server로 • 두 개 이상의 remote 소스에서 사이즈가 큰 테이블을 조인하는 것은 피하라

38. EAI Best Practices • Point-to-point 통합은 피하라 • 좀 더 나은 재사용성을 Hub와 broker 사용 • 어플리케이션 개발시 주의사항 • 선행되어야 할 내용에 대한 준비 및 계획이 필요함 • 연관되는 시스템에 미치는 영향도를 이해해야 함 • 데이터 흐름에 대한 시나리오 및 일어날 수도 있는 현상에 대한 이해가 필요 • 성능에 대한 모니터링 • workflow에서 데이터의일관성과 성능에 대한 병목현상을 trace할 수 있도록 준비가 필요하다

39. 병렬 ETL Engines ODS MQSeries queues DB2 Warehouse 복제 Information Integration 웹 서비스 실시간 BI 동시 사용자 조회 개인화 , 데이터 마이닝, 비즈니스 룰, 캠페인 고객 끊임 없는 적재 Corporate Dashboards 경보, 트리거, KPIs, 분석

40. 4. DW Architecture by IBM

41. Application Application Application BI Apps Application Application Mart Mart Mart Data Marts ETL / Replication EDW Data Warehouse ETL / Replication ODS Layer ODS ODS ETL Operational systems 전통적인 EDW framework Issues: - 실시간 데이터 처리 난해 - 애플리케이션이 여러 레이어로 접속 - 처리 시간의 지연 - 전체 TCO 증가 - 변화가 어려움 - Network 부하 발생

42. BI Apps Application Application Application Application Application EDW Logical and orPhysical Mart layer Data Marts (LOB Apps) Atomic Data (normalized) ODS / Staging Layer ETL Operational systems 통합 데이터 웨어하우스 아키텍처 Benefit: - 실시간 데이터 처리 - 애플리케이션이 단일 레이어로 접속 • 필요에 따라 여러 레이어의 데이터 접근 - 처리 시간의 감소 - 전체 TCO 감소 - 변화에 대한 대처 용이 - Network 부하 감소 - 동적인 자원 관리

43. 의사결정 데이터 2,3차 집계 데이터 마트 (요건 중심) 데이터 상세 수준 및 보관 기간 1차 집계 요약 데이터 데이터 량 주제영역별 사용자테이블 ( SoR -3rd normal form) 원천 Raw 데이터, Staging & 사용자테이블 Rolling data 계층별 Data 아키텍처 L5 L4 상위 Layer로 진행 될수록 데이터는 요약되고 보관 기간이 길어짐 상위 Layer로 진행될 수로 데이터 량은 줄어듬 L3 L2 L1 EDW is an architecture, Not a database!!

44. 의사결정 데이터 다차원 모델 일별/월별 데이터 다차원 OLAP 분석 (정형/비정형) 단계적 Recursive 정제 가공 수행 필요한 단계에 필요한 가공 2,3차 집계 데이터 다차원 모델 일별/월별 데이터 요약영역 L5 Executive Area 정형 조회 사용자 Ad-hoc Query 정제된 전사 통합 모델 데이터 1차 집계 요약 데이터 일별/월별 데이터 L4 Require Specific Area L3 통합영역 주제영역별로 정리된 상세 수준의 데이터 3차 정규화 모델 정제 완료된 데이터 준실시간/일별/월별 L2 Summary Area L1 정형조회/비정형/실시간/준 실시간 화면 조회 수집영역 • 원천 시스템 데이터와 동일한 상세 수준 데이터 • 1차 정제 수준 • 준실시간/일별/월별 Subject Area 수집영역 데이터 근접 실시간 제공 최소의 데이터 가공, 신속한 데이터 제공 Raw Data Area 쿼리 성능의 저하 없는 동시 적재, 쿼리 수행, 유지보수 제공

45. Access current customer records from a call centre Access current stock levels from a supply chain data mart Business activity monitoring – linking events to trends Application ODS Mart Mart Federation: Join real-time data to the warehouse DB2 Information Integrator Enterprise Data Warehouse DBMS DB2 Operational systems

46. Access to customer documentation (e.g. letters, media) from a call centre Linking photos or documents to analysis of customer claims in insurance Application Mart Mart DBMS Federation: Access XML & Unstructured Content DB2 Information Integrator Enterprise Data Warehouse XML Content Systems Operational systems

47. Access to marts developed by different departments for specific data Access from mart to warehouse level for detailed data BI Tool Enterprise Data Warehouse DB2 Information Integrator Mart Mart Second EDW Federation: Joining Marts & Warehouses

48. Intelligent Queuing and Governance • Mixed workload requires workload balancing • Evaluation and workload management prior to resource consumption • Define a data path for executives and emergency work • Define a data path for “as long as it gets done” work • Define rules or priority for the rest (80%+) • Evaluation and adjustment during resource consumption • Identify priority and “just get it done” work, allotting appropriate resources • Adjust the rest up and down based on priority, current workload and time period

49. Data Archiving and Retrieval • More important where regulatory & compliance reporting needs more history than Decision Support and BI • Set threshold for active vs aged data • Different partitions and storage groups • Use Hierarchical Storage Management (HSM) to leave stubs in aged tables, move data to tape • Aged data (ie candidate for archive) still available to SQL – HSM retrieves on demand • Generally over stressed, aged data will not usually influence or change aggregation and summaries • Be fast enough to keep up Hierarchical Storage Manager Offline Storage Aged data Active data EDW

50. 5. Parallel Database