Analysis Services DB 설계 가이드 권오주 대표시삽 www.olapforum.com

프론트-엔드 작업과 미래의 재설계 작업을 최소화하도록 데이터베이스에 가능한한 많은 스트럭처와 인텔리전스를 반영한다

설계 목적 관계형 설계와 분석 서비스 설계의 결합 일반적으로 관계형 설계는 분석 서비스 설계를 지원. 그러나, 분석 서비스의 특성을 파악해야 보다 완전

뷰의 사용 차원 테이블과 사실 테이블에 대한 사용자의 접근 제공 사용자로부터 물리적 데이터베이스의 격리 접근에 대한 영향을 주지 않고 물리적 데이터베이스의 변경 용이 사용자 친화적 커스터마이징

차원 테이블 칼럼들 정수형 대리키(Surrogate Key) 소스 시스템으로부터의 키(App Key) 설명 (하나 이상 가능) 정렬 단항 연산자(Unary operator) MDX 수식 칼럼

정수형 대리키의 사용 보다 빠른 조회(Lookup) 다중 데이터 소스로부터의 키 충돌 제거 지능적인 키의 사용으로 미래의 문제점 제거 Identity 속성으로 최상의 구현 정수형 키를 사용하여 사실 테이블 적재

사용자 지정 집합과 롤업 하나의 계층구조 내에 사용자 지정 집합과 롤업 포함 가능 MDX 수식과 단항 연산자를 사용하여 구현 차원 내에 포함되면 구성원들은 자동으로 해당 차원을 포함하는 임의의 큐브의 일부가 됨 Time 차원과 재무 응용프로그램의 경우 매우 중요 주의 – MDX 구문 체크 없음

Product Family 테이블

Product Item 테이블

Product 차원

Time 테이블 칼럼 현재일자, 현재월 등 DOW, DOM, DOQ, DOY, MOQ 등 자식 구성원의 수 – 주를 구성하는 일자수 근무일수, 영업일수 등 시작일자, 종료일자 주말, 휴일 기타 – 요일, 날씨 등등

현재 시간 결정 각 Time 테이블에 Current Time 칼럼 생성 Current Time 칼럼을 기반으로 Time 차원의 각 수준에 Current 속성 생성 SQL을 이용하여 Current Time 칼럼을 업데이트 증분 업데이트 옵션으로 Time 차원 처리

Time Calculations – MDX 현재 일자 Filter( [Time.Standard].[Day].Members, [Time.Standard].CurrentMember.Properties("Current") = "-1" ).Item(0) 이전 일자 ).Item(0).Lag(1) 참고 : StrToMember

Time Calculations – MDX 전년도 동일 기간 ParallelPeriod( [Time.Standard].[Year], 1, Filter( [Time.Standard].[Day].Members, [Time.Standard].CurrentMember.Properties("Current") = "-1" ).Item(0) )

Time Calculations - MDX Year-To-Date Sum( PeriodsToDate( [Time.Standard].[Year], Filter( [Time.Standard].[Day].Members, [Time.Standard].CurrentMember.Properties("Current") = "-1" ).Item(0) ) 참고 : YTD

Time Calculations - MDX 전년도 Year-To-Date Sum( PeriodsToDate( [Time.Standard].[Year], ParallelPeriod( 1, Filter( [Time.Standard].[Day].Members, [Time.Standard].CurrentMember.Properties("Current") = "-1" ).Item(0) )

Time Calculations - MDX 성장 (Change) [Time.Standard].[Current] - [Time.Standard].[Previous] %성장 (Change%) iif( CoalesceEmpty( [Time.Standard].[Previous], ) = 0, NULL, [Time.Standard].[Change] / [Time.Standard].[Previous] ) 참고 : iif([Time.Standard].[Day].[Previous]=0, NULL, ...)

장점 SQL과 AS2K 모두 현재 시간을 결정하기 위하여 동일한 소스를 사용 DSO 또는 분석관리자를 통해 기본 구성원을 변경할 필요가 없음

계층 구조 기본적으로 각 차원을 하나의 명시적인 계층 구조를 갖도록 생성 및 관리 Std 또는 Standard로 생성 나중에 데이터베이스를 재구축 (차원 재생성) 하지 않고도 계층 구조 추가 가능 차원이 다중 계층구조를 가지면 항상 All 수준을 가진다

Unknown 구성원의 사용 모든 수준에 unknown 구성원 포함 총계 일치 데이터의 완전한 적재 가능 미지의 레코드에 대한 Granularity 제공을 위한 별도의 사실 테이블과 파티션 생성 가능

Property (Attribute) 타입 Unconstrained – Member Property? 차원 구성원의 어트리뷰트는 임의의 값이 될 수 있다 Constrained – (Virtual?) Dimension 차원의 어트리뷰트는 가능한 값들의 집합으로 제한된다 어트리뷰트는 차원간 관계(Relationship)가 된다 ADAPT( Application Design for Analytical Processing Technologies ) : OLAP용 ERD

Attribute 예 Unconstrained Constrained ADAPT( Application Design for Analytical Processing Technologies ) : OLAP용 ERD Constrained

속성(Properties) 데이터 필터링에 탁월 대부분의 프론트 엔드가 제공하는 보완 정보 제공 가상 차원에 사용되는 대부분의 속성들에 대하여 제약 차원(Constrained Dimensions) 사용 데이터 무결성 강제 제약 조건을 정의할 필요성 제거 제약 차원 : 사실 테이블을 경유하여 해당 리스트를 쉽게 유지관리

Type 속성 : Caption 차원의 증분 업데이트로 이름 변경 반영 Name 칼럼을 소스 키로 설정하고 MDX에서 사용 가능 차원의 각 수준에 대하여 설정

Type 속성 : Caption 차원 테이블에 변경 이름을 가질 칼럼 생성 해당 칼럼을 구성원 속성으로 사용 차원 테이블의 칼럼 값 변경 증분 업데이트 옵션으로 차원 처리

Type 속성 : Caption

Member Name Column CAST(”Relational_Table_Name”.” Member_Code” AS VARCHAR(10)) + ' - ' + isNull(”Relational_Table_Name”.”Member_Label”, isNull(”Relational_Table_Name”.” Member_Name”, ‘**미정의**’)) 특히, Name이나 Label 필드가 NULL을 포함할 수 있을 때 레코드 식별을 보다 쉽게 한다

사실 테이블 설계 datetimestamp 또는 batch id로써 함께 처리되는 레코드들을 식별

사실 테이블 데이터 수정 재적재 오프셋과 새로운 트랜잭션 사용 유일한 이슈는 삭제할 레코드를 식별하는 것 별도의 사실 테이블과 파티션 생성 트랜잭션이 적재인지 수정인지 식별하는 정수 칼럼 추가 측정값으로서 데이터베이스에 추가하여, 분석에서 카운트를 사용시 올바른 카운트를 얻을 수 있도록 한다

Unknown 구성원의 처리 _Idx, _Code, 측정값 필드들로 구성되는 별도의 사실 테이블 생성 별도의 파티션 생성 해당 큐브와 Unknown 파티션에 대하여 드릴스루 옵션 설정

Unknown 사실 테이블 설계

Unknown 사실 테이블

장점 unknown 구성원에 대한 노출 제공 (데이터 품질) 양질 데이터와 불량 데이터 사이의 물리적인 분리 제공 불량 데이터의 효과를 쉽게 평가 정상적인 사실 테이블에 대한 수정 트랜잭션 처리 가능

파티션 - 개념

파티션 - 적용

파티션 전략 일반적으로 파티션 전략은 관계형과 다차원에 대하여 동일해야 함 비즈니스 또는 처리 판단에 따른 데이터베이스의 부분집합 처리 시간 감소 서로 다른 집계 전략 가능 쿼리 성능 개선

파티션 전략 파티션에 대한 데이터 슬라이스 설정 – 설정하지 않으면 성능향상 없음 처리해야 할 메타데이터 증가로 관리 작업은 느려질 수 있음

기간별 파티션 일정 기간 단위로 파티션 오래된 데이터 제거 용이 오래된 데이터에 대한 집계 전략을 다르게 가능 다른 차원들로부터의 레코드 유지관리는 더 어려움

기간별 파티션 전략 예 Month 1 Month 2 이전 2년 데이터 : ROLAP Month 24 Month 25 최근 3년 데이터 : MOLAP Month 60 새로운 월에 대한 새 파티션 Month 61

DSO 선언 Public dsoSrv As DSO.Server Public dsoDtb As DSO.MDStore Public dataDtb As ADODB.Connection Dim dsoDB As DSO.MDStore

DSO OLAP 연결 Public Function OpenOLAPSrv(sSrv As String) As Boolean '글로벌 오브젝트의 초기화 Set dsoSrv = Nothing Set dsoDB = Nothing '서버와 연결 인스턴스 생성 Set dsoSrv = New DSO.Server dsoSrv.Connect (sSrv) OpenOLAPSrv = True End Function

DSO 파티션 처리 Public Sub ProcessCubes() Dim sCube As Variant Dim dsoCube As DSO.MDStore Dim dsoPart As DSO.MDStore Dim dsoSrcPart As DSO.MDStore Dim dsoDim as DSO.Dimension Dim dsoLev as DSO.Level Dim rstRun As ADODB.Recordset Dim sName As String ‘다음 슬라이드의 코드 End Sub

DSO 파티션 처리 For Each sCube In oCubes Set dsoCube = dsoDtb.MDStores(sCube.Name) Set dsoSrcPart = dsoCube.MDStores(dsoCube.Name) UT_OpenRecordSet rstRun, dataDtb, "Get_Months" Do While rstRun.EOF <> True sName = "Fact_Sales_" + rstRun!Month_Name If Not dsoCube.MDStores.Find(sName) Then Set dsoPart = dsoCube.MDStores.AddNew(sName) dsoSrcPart.Clone dsoPart, cloneMajorChildren dsoPart.SourceTable = Replace(dsoPart.SourceTable, dsoCube.Name, sName, 1) dsoPart.FromClause = Replace(dsoPart.FromClause, dsoCube.Name, sName, 1) dsoPart.JoinClause = Replace(dsoPart.JoinClause, dsoCube.Name, sName, 1) ‘슬라이스 설정 ... Set dsoDim = dsoSrcPart.Dimensions(“Time.Standard”) Set dsoLev = dsoDim.Levels("Month") dsoLev.SliceValue = rstRun!Month_Name End If Set dsoPart = dsoCube.MDStores("Fact_Sales_" + rstRun!Month_Name) dsoPart.Process processFull rstRun.MoveNext Loop Next BOL : 참고 부분 Major and Minor Objects In DSO, most child objects cannot commit their own changes to the Analysis server, but instead must rely on their parent object to commit the changes of their child objects. Any object that can commit itself and its children is referred to in DSO terminology as a major object. Any object that cannot commit itself, but must rely on a major object to perform such an action, is referred to as a minor object. Objects with the following ClassType property values are considered major objects: clsCube clsDatabase clsDatabaseCommand clsDatabaseDimension clsDatabaseRole clsDataSource clsMiningModel clsPartition clsServer All objects not included in the previous list are considered minor objects. To commit changes to major and minor objects, all major objects in DSO support the Update method. Any change to a DSO minor object must be committed through the parent DSO major object in order to be committed. For example, a change to a clsCubeRole object is committed only when the Update method of its parent clsCube object is executed. Although most interfaces in the DSO hierarchy have an Update method, attempting to use the Update method on a minor object in DSO will result in an error.

현재 파티션 현재 파티션과 이력 파티션 생성 다음 적재 이전에 현재를 이력에 병합하거나 덮어쓴다 다른 전략과 조합 가능 잘못되었을 때 현재 데이터 제거 용이

2개 차원 파티션 하나의 차원은 Time이어야 함 데이터 가용성이 시간에 따라 변하는 다른 차원 선택 – 조직 또는 지역 SQL과 DSO는 더 복잡해짐 평균 쿼리 시간은 감소

빈 파티션(Empty Partition) 데이터베이스에 영향을 주지 않고 처리 테스트 할 때 사용 새로운 파티션을 생성하기 위한 기초로서 사용

가상 큐브 사용자로부터 물리적 큐브의 격리 분석을 위해 서로 다른 차원성을 가지는 별도의 큐브들을 결합

DSO 가상 큐브 Public Sub ProcessVirtualCubes() Dim cubeCnt As Integer For cubeCnt = 1 To dsoDtb.MDStores.Count Set dsoCube = dsoDtb.MDStores(cubeCnt) If dsoCube.SubClassType = sbclsVirtual Then dsoCube.Process processFull End If Next cubeCnt End Sub

기타 고려사항 큐브와 차원에 대한 원본으로서 뷰 사용 스노우플레이크 사용 테이블과 큐브 사이의 추상계층 역할 큐브 파티션보다 효과적인 WHERE절 사용 뷰 안의 로직 : 구성원 이름, 속성 스노우플레이크 사용 정규화된 설계의 장점 각 수준별 고유한 키, 임의의 수준의 Granularity 제공 But... Trade-off

기타 고려사항 클라이언트 S/W 선택 신중 수준 이름 Count 실제 예측값 입력 Custom MDX 지원 ? Analysis Services 전용 ? 수준 이름 사용자 친화적인 수준 이름 수준 이름 변경 최소화 : 차원, 큐브, MDX 참조 Count 실제 예측값 입력 차원 구축시 Member Count 자동 저장 실제 추정치 입력 -> 저장소 설계 마법사 작업 개선

기타 고려사항 그룹핑 수준 수작업 구성원 속성 사용 신중 64,000미만의 자식 구성원 그룹핑 자동화 ? 수준의 추가 및 직접 조작 LEFT("CustomerDimTable"." CustomerName", 1) 구성원 속성 사용 신중 서버 기동시 차원 구성원 키, 이름, 속성의 메모리 로딩 3GB RAM 제한

기타 고려사항 MDX의 역할 이해 주의 사항 데이터 원본 이름 MDX를 사용하지 않는 OLAP Solution ? 대용량 차원 -> 별도의 메모리 공간 사용 (VLDM) -> Buggy ! 피하라! HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion\VLDMThreshold : 4,000,000 기본값 ROLAP 차원 -> ROLAP 큐브 -> 성능 손해 데이터 원본 이름

Mitigation/Contingence Plan 위험 관리 관리요소 심각성 확률 영향 Mitigation/Contingence Plan ERP와의 연계에 따른 충분한 운영 Test의 미비 H M ERP 실 운영환경에서의 Test 부재 향후 관리위험 현상태의 ERP Test Data와 DW Data의 동기화 7월이후 Production Mode 로서 DW 운영 및 보완