Excel OLAP Reporting / OWC를 이용한 의사결정지원 시스템 구축 Hwan-Tae Kim(htkim@bisolution.net) Senior Consultant BI Solution, Inc / OLAP Forum

Session Structure Part I. - 개발이 필요 없는 조회 도구 Excel OLAP Reporting Part II. - 직접 만들어 봅시다 ! Office Web Component Part III. - 먼저 이해하셔야 할 내용 DW /OLAP Architecture

Part I. Excel Pivot Table Service Excel 2002/2003 Add-in for SQL Server Analysis Services

Microsoft BI Architecture Microsoft Business Solutions BI Applications Office System Windows Client Solution Accelerators Business Scorecards Visual Studio .Net Dev Tools Excel OLAP Reporting Excel Visio Project OWC SharePoint Portal Server Project Server Data Analyzer SQL Server Relational Engine Management Tools Reporting Services Analysis Services OLAP & Data Mining Windows Server Source : Microsoft

Excel OLAP Reporting 신속한 적용 - 기존 인프라 재활용 저렴한 비용 - 별도 제품 구매 불필요 효율적 운영 - 친밀한 인터페이스 (최소 교육) 풍부한 자료 - ex) MSDN, News Group, …

Excel Pivot Table Service 엑셀의 가장 강력한 분석 기능 중 하나 Analysis Service 의 전통적 Client Tool Filter / Pivot / Drill Up / Drill Down / Sort … OLAP Cube 데이터를 원본으로 지정할 수 있다 Local Cube 생성- Desktop OLAP의 구현

Excel Pivot Table Service Analysis Service 연결 Filter / Pivot / Drill Up / Drill Down / Sort… Chart / 자동서식 웹으로 게시 예측 값 생성 Local Cube 생성

Excel 2002/2003 Add-in for SQL Server Analysis Services 다양한 OLAP Cube에 손쉽게 접근 가능 엑셀을 사용한 상세한 데이터 분석 유연한 리포트 양식 (Free form/Structured) OWC와 HTML을 이용한 WEB 게시 다운로드 Link http://www.microsoft.com/downloads/details.aspx?FamilyID=dae82128-9f21-475d-88a4-4b6e6c069ff0&DisplayLang=en

for SQL Server Analysis Services Excel 2002/2003 Add-in for SQL Server Analysis Services Download 및 설치 연결관리 Semi-Structured Report Free Form Report Action / Drill Through …

Part II. Office Web Component VB Example

Office Web Component Office에 포함된 Active-X Control Chart / Pivot Table / Spreadsheet 대부분의 엑셀 기능을 웹 브라우져에서 구현 [웹 페이지로 저장] 시에 생성되는 그것 Office Web Component Toolpack http://www.microsoft.com/downloads/details.aspx?FamilyID=beb5d477-2100-4586-a13c-50e56f101720&displaylang=en

Office Web Component Office Web Component Tool Pack 소개 Chart Pivot Table Spread Sheet …

VB Example Pivot Table / Chart Component 사용 화면 저장 / 불러오기 기능 차원 검색 기능 OWC / ADOMD

VB Example OWC OLAP Client 구현을 단계별로 시연 Analysis Service 연결 화면조회 조회 화면 저장 및 불러오기 ADOMD를 활용한 차원 검색 …

Part III. 왜 이런 시스템을 구현해야 합니까? 과연 기존의 방식으로는 안 되는 것일까요? 먼저 분석 시스템 전반에 대한 이해가 필요합니다. 가볍게 OLAP 시스템의 배경에 관하여 살펴보겠습니다.

OLTP OLTP (On-Line Transaction Processing)는 기업의 기본적인 업무를 수행하는 Application 업무를 처리하는 과정에서 데이터를 발생시킴 개개의 레코드에 대하여 조회와 수정이 효율적으로 이루어지도록 최적화된 포맷으로 데이터를 저장 발생한 이벤트들에 대하여 제한된 숫자의 요약화 된 보고서를 생성

OLAP OLAP(On-Line Analytical Processing)는 기업의 데이터를 분석하고자 하는 목적 필요한 데이터를 OLTP 시스템과 기업 외부의 데이터를 포함한 다른 데이터 소스로부터 추출 데이터는 분석적 질의(Analytical queries)에 적합한 형태로 포맷으로 저장 분석적 질의 들은 흔히 수많은 레코드와 테이블 정보의 요약화를 요구함 OLAP 시스템의 목적은 분석가들에게 모든 유용한 관점에서의 데이터에 대한 분석을 가능하게 하는 브라우징 도구를 제공하는 것

OLTP vs OLAP OLTP OLAP 업무 프로세스 중심 사용자의 분석 주제 중심 트랜잭션 처리 (입력, 조회, 삭제, 수정) 운영자 계층 시스템 보고서, 분석, 계획 (조회, 제한적 입력/수정) 분석가 및 의사결정자 계층 시스템 2차원, 정규화 다차원, 계층구조 상세 데이터, 중복 배제 요약 정보, 중복 허용 소량의 데이터 처리 활용 패턴 단순, 고른 시간대 분포 시스템 자원 사용량 예측 용이 대량의 데이터 처리 활용 패턴 다양, 시간대 불규칙 분포 시스템 자원 사용량 예측 어려움 구축 후 데이터 축적 중심 전통적 개발 주기 시스템 구축 후 유지보수 단순 구축 후 데이터 축적 및 스키마 변경 반복 확장 개발 주기 시스템 구축 후 유지보수 전략 필요 사용자 중심 응용프로그램 (4GL) Customizing 용이 정형화된 보고서/변경 어려움 단순한 화면 조작 전용 도구 (Off-the-Shelf, Out-of-Box) Customizing 제한적 동적인 비정형 분석/변경 용이 EUC(End User Computing) 활성화 필요

OLTP Data Model 운영계 시스템 데이터 무결성 유지(정규화) 2차원적 ER 모델링 Example - 판매관리

아래와 정보요구사항은 어떻게 해결하여야 할까? 년도 분기 월 일자 상품분류 상품 판매금액 판매수량 조회기간을 임의로 조정 상품분류나 상품을 임의로 선택하여 필터링 특정한 금액 구간 조건에 일치하는 상품을 조회

View를 사용하여 구현 하면? 상위 수준의 분석 쿼리 수행시 성능저하 낮은 응답속도 적시 개발이 어려움 (복잡한 쿼리…) CREATE VIEW V_기간_상품 AS SELECT DATEPART(YYYY, B.발주일자) AS 년 , DATEPART(QQ, B.발주일자) AS 분기 , DATEPART(MM, B.발주일자) AS 월 , DATEPART(DD, B.발주일자) AS 일 , D.상품분류명 , C.제품명 , SUM(A.수량) AS 판매수량 , SUM(A.단위가격*A.수량) AS 판매금액 FROM 주문내역 AS A LEFT OUTER JOIN 주문 AS B ON B.주문ID = A.주문ID LEFT OUTER JOIN 상품 AS C ON C.제품ID = A.제품ID LEFT OUTER JOIN 상품분류 AS D ON D.상품분류ID = C.상품분류ID GROUP BY DATEPART(YYYY, B.발주일자), DATEPART(QQ, B.발주일자) , DATEPART(MM, B.발주일자), DATEPART(DD, B.발주일자) , D.상품분류명, C.제품명 ORDER BY DATEPART(YYYY, B.발주일자), DATEPART(QQ, B.발주일자) , D.상품분류명, C.제품명

그렇다면 집계 테이블을 생성하면? 관리 작업의 어려움(전산실부하) 융통성이 결여된 데이터 전사 차원의 통합성 결여 … CREATE TABLE [dbo].[S_기간상품] ( [년도] [varchar] (4) COLLATE Korean_Wansung_CI_AS NULL , [분기] [varchar] (2) COLLATE Korean_Wansung_CI_AS NULL , [월] [varchar] (2) COLLATE Korean_Wansung_CI_AS NULL , [일] [varchar] (2) COLLATE Korean_Wansung_CI_AS NULL , [상품분류] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL , [상품] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL , [판매수량] [int] NULL , [판매금액] [numeric](18, 0) NULL ) ON [PRIMARY] GO INSERT INTO S_기간상품 ([년도], [분기], [월], [일], [상품분류], [상품], [판매수량], [판매금액]) SELECT * FROM V_기간_상품 /* INDEX 등의 생성은 생략함 */

주제중심적(Subject-oriented) 통합성(Integrated) 시계열적(Time-variant) 그러므로 의사결정에 중요한 영향을 미치는 데이터에 대해서는, 정보를 통합하고 조회하기에 최적화된 형태로 관리하여야 합니다. DW Model 주제중심적(Subject-oriented) 통합성(Integrated) 시계열적(Time-variant) 비휘발성(Nonvolatile) OLAP System 발생 가능한 경우의 수를 모두 사전 집계 분석 쿼리에 대한 응답속도를 극적으로 향상 DW Model을 원천으로 할 때 효과 극대화

OLAP을 위한 DW Data Model 분석용, EIS, DSS 조회 기능성 및 속도 다차원 모델링 Example - 판매분석

마지막으로 성공적인 분석 시스템 구축을 위해서는 정확하고 안정적인 데이터의 공급이 반드시 필요하다는 것을 반드시 강조 드리고 싶습니다. Example – 판매분석 적재

DW/BI Architecture Elements of the process Data Marts and cubes Source Systems Data Warehouse Clients Query Tools Reporting Analysis Data Mining 1 2 3 4 Design the Populate Create Query Data Warehouse Data Warehouse OLAP Cubes Data Source : Microsoft

성공을 위한 조건 BI Strategy Data Warehouse Design ETL CUBE Design Client Tools 성공 요소의 중요도 : 전략 > 설계 > 데이터이행 > Client Tool

요약 오늘 소개해 드린 내용은 Excel OLAP Reporting Office Web Component DW / OLAP 시스템 전반

Q & A http://www.olapforum/com htkim@bisolution.net