삼성 SDS 멀티캠퍼스 최인규(dba7811@hotmail.com) 데이터웨어하우스, OLAP, 데이터 마이닝 삼성 SDS 멀티캠퍼스 최인규(dba7811@hotmail.com)

will determine whether you win or lose.” “How you gather, manage, and use information will determine whether you win or lose.” – Bill Gates Key Message: Setting the stage. Slide Builds: None Slide Script: When we talk about “turning data into smart decisions,” we’re talking about a vital technology that underlies this concept: business intelligence. Traditionally, business intelligence has been the domain of IT and marketing research specialists who extract data, process it, and deliver report to an executive-level decision maker. But today, organizations are retooling this approach as business intelligence is transforming into a strategic enterprise application. And as Bill Gates accurately points out, the methods by which companies gather, manage, and use information will separate the winners from the losers. Microsoft is making it possible for a company, its partners and suppliers to collect, integrate, and organize data through the computing platform. And knowledge workers now have the necessary tools and services on desktops (and other devices, including mobile phones and PDAs) which allow them to perform their own analysis and reporting, giving them the power to act on results in near real-time. These end users need access to enterprise data so that they can be empowered to make quick decisions. Slide Transition: Strips Right-Down

목차 1.데이터 웨어하우스 소개 데모:모델링과 점진적으로 변하는 차원 관리 2.DTS를 이용해 DW구축하기 3.분석서비스 (OLAP) 데모:큐브 만들기 및 엑셀 클라이언트 구현 분석 서비스(데이터 마이닝) 데모:마이닝 모델 만들기

1.데이터 웨어하우스 소개

OLTP와 데이터 웨어하우스 OLTP 데이터베이스 데이터 웨어하우스 데이터베이스 실시간의 업무 분석을 위해 설계 범주와 속성으로 평가되는 업무의 분석을 위해 설계(비휘발성.주제 중심) 데이터 수정을 위한 최적화 구조 복잡하고 예측할 수 없는 쿼리에 대한 최적화 구조 많은 수의 동시 사용자 지원 OLTP에 관련된 동시 사용자를 거의 지원하지 않음 일관적 유효한 데이터로 로드(실시간 검증도 필요치 않음) 트랜잭션 동안 들어오는 데이터의 유효성에 대한 최적화

데이터 웨어하우스 구성요소 사용자 데이터 원본 데이터 마트 Staging Area Data Input Data Access

Relational Data Mart와 OLAP Cubes Data Storage Relational Data Structure N-Dimensional Data Content Detailed and Summarized Data Data Sources Relational and Non- Relational Sources Data Retrieval Fast Performance for Data Extract Queries Faster Performance for

Search Efficiency. Personal information Agent Data Mining OLAP 쿼리 및 레포팅 툴

필요한 기술 관계형데이터베이스 데이터웨어하우스의 데이터를 관리한다. Staging으로 들어 오는 데이터를 정제 및 변환을 지원한다. OLAP 직관적이고 빠르게 데이터를 추출할수 있는 다차원 뷰를 생성한다. 추출, 변환, 로딩 도구 이질적인 시스템으로 부터 데이터를 추출한다. 목적지에 원본데이터를 변환.로딩한다.

SQL Server BI Platform Reporting Services Development Tools Management Tools Analysis Services OLAP & Data Mining Data Transformation Services ETL SQL Server Relational Engine

데이터 웨어하우스 디자인 차원적 모델 설계 Fact Table Fact Table: 비즈니스의 특정 이벤트를 설명하기 위한 숫자 데이터를 가진 중앙 테이블 Measures Fact Table의 숫자 컬럼으로 분석에 사용된다. Dimension Table 데이터의 의미를 설명한다. Fact Table Measures 차원적 모델 설계

데이터 웨어하우스 설계 OLTP 시스템과 간섭 없는 기능 일관성 있는 데이터의 중앙 관리 기능 복잡한 쿼리에 빠르게 응답 OLAP와 데이터 마이닝 같은 강력한 분석 툴 제공 차원적 모델에 근거를 둔다. 판매, 재고와 같은 주제에 초점을 둔다. 다양한 소스로 부터 데이터를 결합한다.

점진적으로 변하는 차원들 Type1. 차원 레코드를 덮어쓰기 Type2. 새로운 차원 레코드 추가하기

데모 모델링과 점진적으로 변하는 차원 관리

2.DTS를 이용해 DW구축하기

데이터 추출, 변환 및 로딩기술 개 요 OLTP데이터베이스에서 추출(Extraction)되고 데이터웨어하우스 스키마에 맞게 변환(Transformation)되고 데이터웨어하우스에 로딩(Loading)된다. 일회성이 아니라 주기적으로 동작한다.

데이터 추출, 변환 및 로딩기술 ETL구성요소 추출:원본시스템에서 데이터를 추출하는 역할을 담당. 일반적으로 OLTP의 성능을 유지하기 위해 누적된 데이터를 데이터웨어하우스로 옮긴다. 변환:데이터를 검증하고 데이터의 타입을 바꾸며 비즈니스 룰을 적용하는 역할을 한다.(T-SQL,사용자 애플리케이션) 로딩:변환된 데이터를 데이터웨어하우스에 입력한다.(BULK INSERT,BCP,Bulk copy API) 메타데이터:데이터의 이동,변환,데이터웨어하우스 운영에 관한 정보를 유지하며 변환이 일어나는 동안 데이터 매핑에 관해서도 설명해 준다.

데이터 추출, 변환 및 로딩기술 SQL Server 2000 ETL 요소 DTS:다양한 원본 시스템으로 데이터를 추출,변환해서 목적지에 통합하는 기능을 제공하는 그래픽한 도구와 프로그램이 가능한 개체 SQL Server 에이전트:주기적인 작업에 대한 일정수립이 가능하도록 하고 오류에 대해 통보하는 기능 저장 프로시저와 뷰:여러 애플리케이션간에 로직을 통일되게 구현한다. T-SQL: 표준SQL 보다 확장된 프로그래밍 기능을 제공한다. OLE DB:모든 종류의 데이터를 접속하기 위해 설계 되었다. 메타 데이터 서비스:메타 데이터를 저장하고 관리하는 방법을 제공한다. 데이터나 요소 정의, 개발 모델, 재 사용 가능한 요소, 데이터 웨어하우스 설명등을 제공하는 허브로 사용된다.

데이터 로드 시나리오 정의 디자인 고려사항 디자인 결정 데이터로드가 기능적 단위로 분리될수 있는가? 모듈화된 패키지 디자인 데이터로드가 기능적 단위로 분리될수 있는가? 모듈화된 패키지 디자인 원본과 목적지가 어딘가? 연결 관리 데이터 로드 시 어떤 변환과 기능이 요구되는가? 작업과 변환 선택 데이터로드가 다른 데이터로그에 의존적인가? 패키지 워크플로 정의 메타데이터를 추적해야 하는가? 얼마나 자주 실행하는가? 패키지 저장 패키지 실행

모듈화된 패키지 디자인 모듈화된 패키지 만들기 복잡한 작업을 단순화 한다. 패키지 이해가 빠르다. 디버깅 하기 좋다. Outer Packages 만들기 여러 개의 패키지를 하나의 패키지로 실행 모듈화된 패키지를 워크프로에 맞게 실행 다른 곳에서 재사용 패키지 병렬처리

작업 선택 목적지에 Inserts 수행 대량 삽입 작업 원본이 콤마나 탭등으로 구분되는 파일이며 목적지가 반드시 SQL Server이고 어떤한 변환도 할 필요가 없다. 데이터 변환 작업 원본과 목적지가 다양한 데이터 소스며 데이터 변환. 패러미터 쿼리.다중 데이터 펌프를 이용해 다양한 로직 적용이 가능하다.

작업 선택 유연한 쿼리 수행 데이터 기반 쿼리 행단위의 유연한 쿼리 실행. SQL 실행 작업 SQL문 실행. 성능보다 다양한 융통성이 필요함. insert, update, delete 수행 SQL 실행 작업 SQL문 실행. 매개변수 쿼리 수행. 쿼리의 결과를 매개변수로 전역변수에 할당.

작업 선택 Objects 복사 도구 데이터 가져오기/내보내기 마법사 SQL Server개체 복사 작업 Table, View, indexes와 같은 개체 복사 원본과 목적지가 SQL Server 7.0 이상 일때만 가능 데이터베이스 전송 작업 로그인 전송 작업등

효과적인 컬럼 매핑 컬럼 매핑 수를 최소화 한다. 가급적이면 다 대 다 매핑을 사용한다. 일반적인 변환작업을 묶어서 한다. 가급적이면 시스템이 제공하는 변환을 이용한다. 성능이 중요하다면 ActiveX Script변환을 최소화 한다.(2배이상의 속도 저하)

워크플로 정의 병렬 워크플로를 위해 여러 개의 분리된 연결을 사용한다. 각 단계마다 트랜잭션 처리를 어떻게 할지를 결정한다.

패키지 저장

패키지 실행 및 트러블 슈팅 여러 개의 패키지를 하나의 Outer 패키지로 묶어 실행한다. DTS Run 유틸리티를 사용할때 매개변수를 생성하는 명령을 만든다. 패키지 스케쥴링을 할때 소유자 퍼미션을 확인한다. 패키지 실행 로깅 SQL Server Error files Windows event log 작업 오류 파일 생성 데이터 변환 작업 데이터 기반 쿼리 작업

패키지 관리 자주 변경되는 연결과 작업을 인식한다. 전역변수 관리 Outer 패키지에서 값을 할당한다. 실행시 유틸리티를 이용해 값을 할당한다. 스크립트로 할당한다. 패키지 연결 관리 Microsoft Data Link파일을 사용한다. 동적 속성을 이용하여 유연성을 증가 시킨다. 연결이 끊긴 편집을 사용하여 오프라인 변경을 한다.

DTS 데모 ETL도구로서 DTS의 기능을 100%로 활용한다면….. DW 구축

3.분석서비스 데이터 안에는 황금이 묻혀 있지만 답사 전에는 얼마나 많은 금이 묻혀 있는지 알 수 없다. -Don Haderle

분석 서비스 아키텍처 DSO PivotTable Service Application Analysis Server ADO MD Analysis Manager OLEDB for OLAP OLEDB for DM SQL Server Data Warehouse Other OLE DB Providers DSO Application PivotTable Service Analysis Server OLAP Engine DM ADO MD MOLAP Store

OLAP 표준 MD-API OLAP카운실이 1996년 9월에 발표된 최초의 API. 대부분의 OLAP벤더에 외면 된 API. 1998년에 JAVA라이브러리와 COM오브젝트로 구성된 MD-API2.0를 발표 OLE DB for OLAP 마이크로소프트에서 1998년 2월에 제안 대부분의 벤더에서 적극적으로 지원되는 실질적인 OLAP API표준 MDX(다차원질의언어)제공

분석 관리자 데이터베이스를 만들고 데이터 원본을 지정한다. 큐브를 작성하고 처리한다. 데이터 마이닝 모델을 만들고 처리한다. 저장소 옵션을 지정하고 쿼리 성능을 최적화한다. 보안을 관리한다. 데이터 원본, 공유 차원, 보안 역할, 그 밖의 개체 등을 찾아본다. 분석 관리자 자습서를 실행한다. Decision Support Objects (DSO) is a library of Component Object Model (COM) classes and interfaces that provide access to the Analysis server.

PivotTable Service 마이크로소프트 오피스 2000의 구성요소 이다. Desktop OLAP 요소 지능적인 쿼리 캐싱을 지원한다. 로컬 큐브를 만든다. MDX formula 엔진

오피스 2000 OLAP 구성요소 PivotTable Service Excel 2000 PivotTable Office Pivot Web Control Local Cubes

다차원 데이터베이스 큐브

차원 큐브 측정값의 의미를 다양한 관점에서 설명한다. 디자인 이슈 별모양 스키마와 눈송이 스키마 공유차원과 개인차원 차원을 개체 인터페이스로 접근해야 한다. 다양한 차원 가상차원.부모-자식 차원. 변경차원

차원 만들기 데이터 원본 스키마를 이해한다.

차원 속성 차원 수준 구성원 차원을 개체 인터페이스로 접근해야 한다.

다양한 차원 가상차원 다른 차원의 구성원 속성을 이용하여 만든다. 장점:큐브의 크기나 처리속도에 영향을 주지 않는다. 단점:집계가 되어 있지 않기 때문에 쿼리 속도가 표준 차원보다 떨어진다. 부모-자식 차원 장점:비대칭(UnBalanced)구조를 허용한다. 넌-리프 데이터를 보여줄 수 있다. 변경차원 차원의 구성원을 추가 및 재 정렬시 큐브를 재 처리할 필요가 없다.

Members with Data 표준 차원 리프 구성원(Leaf Members)의 데이터만 Fact Table에 존재한다. 부모자식 차원 Leaf and Upper Level Members Correspond to Fact Table Data 다음 값을 가진다. Leaf Members Only Non-leaf Data Hidden Non-leaf Data Visible

비정형 계층구조 처리 표준차원 Hide member if속성 부모자식차원 Skipped Levels Column

큐브 차원과 측정값을 묶어 빠르게 답변 하기 위해 설계된 다차원 데이터베이스 데이터웨어하우스의 일부분이다. 상세 데이터와 빠르게 답변하기 위해 집계데이터를 저장하고 있다(?) 다양한 큐브 가상큐브.연결된 큐브. 실시간 큐브. 분산된 큐브.오프라인 큐브

저장소 모드 큐브의 데이터를 물리적으로 어느곳에 저장하는가?

저장소 최적화 및 파티션 저장소 최적화 저장소 디자인 마법사 사용빈도 기반 최적화 분석 서비스 집계 파티션 큐브 처리와 쿼리 속도를 향상시키기 위해 큐브 데이터를 여러 저장소 모드로 물리적으로 저장

동작.드릴스루.쓰기 되돌림 동작 큐브가 주로 수치 데이터만을 보여주는 단점을 극복하기 위해 나온 방법으로 특정 작업을 추가할수 있다. 판매처별 실적을 검색하다 판매처 관리자 정보를 보거나 관련 웹페이지로 이동하는 등의 작업을 수행한다. 드릴스루 OLAP시스템에서 DW에 존재하는 상세 데이터에 접근할 수 있는 기능이다. MOLAP의 단점을 극복할 수 있는 개념이다. 쓰기 되돌림 클라이언트에서 큐브를 업데이트 한다. 예를 들어 작년도 실적을 기반으로 예산을 배분하는 경우를 생각해 보자. UPDATE CUBE [Budget Cube] SET ([2004], [Marketing], [Budget], [All Departments]) = 1000 USE_WEIGHTED_ALLOCATION BY ([1998], [Sales], [Actual])/ ([2003], [Sales], [Actual], [All Departments])

관리 큐브 처리 큐브에 쿼리를 보내기 전에 처리되어야 한다. 차원 처리는 차원데이터를 로드한다. 큐브 처리 시 큐브데이터와 집계가 만들어 진다. 차원과 큐브 데이터를 만들기 위해 SQL문이 사용된다. DTS와 SQL Agent로 자동화 할 수 있다. 보안 역할을 통해 분석서버에 접근할 수 있는 계정설정 데이터베이스, 큐브, 마이닝 모델등에 접근 권한을 역할 단위로 설정 셀 단위까지 보안이 지원

데모 공유 차원 및 큐브 만들기 엑셀 클라이언트 사용 기본적인 관리 작업 수행

데이터 마이닝 기저귀와 맥주 간단히 말하자면 데이터 마이닝은 패턴과 규칙의 형태에서 데이터에 대한 의미 있는 정보를 발견하기 위해 많은 양의 데이터를 검색하는 처리이다.

알고리즘 분석서비스 제공 알고리즘 Clustering Decision trees 기타 알고리즘 Marker Basket Analysis Memory-Based Reasoning Neural Networks

Decision trees

Clustering

마이닝 모델 훈련 Data To Predict Training Data Mining Model DM Engine DM Predicted Data

마이닝 모델 보기

종속 관계 네트워크

차기버전 획기적인 변화가 기대된다. 새로운 알고리즘 추가 Association rules, time series, regression trees, sequence clustering, neural nets, Naïve Bayes

데모 마이닝 모델 만들기 데이터마이닝 예측 작업 수행

요약 SQL Server는 BI platform이다. Reporting Services Development Tools Management Tools Analysis Services OLAP & Data Mining Data Transformation Services ETL SQL Server Relational Engine