OLTP 에서대용량실시간 다차원모델링구현사례 (Cube 를 OLTP 영역에서활용하기 ) 인브레인우철웅
강사소개 경력 [ 현 ] 인브레인 BI 사업부기술이사 다우데이터강사 이천일아울렛전산실 주요영역 : DW/BI Modeling & Architecture 저서 ADO & MTS 공저 SQL Server 2000 Programming 실무를고려한 SQL Server 프로그래머그들만의이야기공저 취미 : 목공
Session Overview Lesson1 OLTP vs. OLAP Lesson2 Case Study Lesson3 Large Volume Key Point Lesson4 Write-back method Demo Write-back Summary
Lesson 1 OLTP vs OLAP OLAP, Cubes and Multidimensional Analysis OLTP vs. OLAP Why using OLAP at OLTP
OLAP, Cubes & Multidimensional Analysis OLTP vs. OLAP Basically OLAP is an awful name, author of the OLAP report calls the same thing FASMI. Fast - 90% of queries back in under 10 secs and no query takes longer than 30 secs. Analysis - Drill down, multiple aggregation techniques, sophisticated graphics, trends all form part of this Shareable - good security at the back end and available to a wide community of users. also multi currency, multi lingual to cope with the global economy. Multi-Dimensional - Excel pivot tables but more so. The ability to have any multiple dimensions of information on each axis of a cross-tab with other dimensions being used to further filter the results returned. Information - Real world KPI's rather than raw numbers. Fast Analysis of Shared Multidimensional Information (FASMI) is an alternative term for OLAP. The term was coined by Nigel Pendse of The OLAP Report (now known as The BI Verdict), because...
OLTP vs. OLAP OLTP vs. OLAP OLTP Online Transaction Processing (Operational System) OLAP Online Analytical Processing (Data Warehouse) Source of data Operational data Consolidation data Purpose of data To control business tasks source: www.rainmakerworks.com To help with decision support, problem solving, planning What the data Ongoing business processes Multi-dimensional views Inserts & Updates Short & fast inserts/updates Periodic long-running batch Queries Simple queries, few records Complex queries, aggregations Processing Speed Very fast Many hours Space Requirements Small if historical data is archived Larger Database Design Normalized, many tables De-normalized, fewer tables, Star or snowflake schemas
Why using OLAP at OLTP OLTP vs. OLAP 대량데이터조회성능 Matrix Mass data inquiry Multi relation measure group Multi dimension cross 간단하게대량데이터일괄 Write-back Intermediate Level에서 Write-back 가능 Update Cube ~ 구문으로하위모든 Leaf Cell 갱신 4개의 Disaggregation Method 제공 배분구성비에대해참조 measure 선택가능
Why using OLAP at OLTP OLTP vs. OLAP 대량데이터조회성능 Matrix Mass data inquiry Multi relation measure group 실매출, 재고, 생산 / 출고 /Shipping/ 입고계획, 다양한매출목표등 Multi Dimension cross Account(5,000) * Product(10,000) * 24 Week * 5 Measures 60 억 Cell Account(5) * Product(1,000) * 24 Week * 5 Measures 60 만 Cell RDB 에서어려움 Big Table vs Big Table Join Performance Index Key 큼 : PlanWeekID(int), AccountID(int), ProductID(varchar 30), WeekID(int) 42 Byte Index create & update & join Resource 많이듬 Matrix Format DB 작업가능하나어려움, Middle 이나 Front 에서수행시비용많이듬
Why using OLAP at OLTP OLTP vs. OLAP 간단하게대량데이터일괄 Write-back Intermediate Level 에서 Write-back 가능 Intermediate Level Product * Week Product * Month Account * Product Group * Month Leaf Level Allocation Method 및할당비율설정 Account * Product * Week UPDATE [CUBE] <Cube_Name> SET <tuple>.value = <value> [,<tuple>.value = <value>...] [ USE_EQUAL_ALLOCATION USE_EQUAL_INCREMENT USE_WEIGHTED_ALLOCATION [BY <weight value_expression>] USE_WEIGHTED_INCREMENT [BY <weight value_expression>] ] UPDATE CUBE [SalePlans] SET ([Measures].[SalesPlanQty], [Date].[Month].&[2012-12], [Product].[Product Family].[Product Family].&[Drink]) = 2560, ~~ = 4300, USE_WEIGHTED_ALLOCATION BY [Measures].[Sales3MonthAvg_Ratio]
Lesson 2 Case Study Project Overview Customer Requirement System Architecture Issue & Solution Optimize Result Interactive Simulation User friendly UI
Project Overview Case Study Biz 컨설팅 6 개월, 개발과 Roll-out 21 개월 User 60 여법인 5,000 Account 5,000 User 10,000 Product Item Time line Biz 설계컨설팅 : 6 개월 1 차 Roll-out 시작 : 7 개월 2 차 Roll-out & upgrade : 7 개월 3 차 Roll-out & upgrade : 3 개월 CPFR : 6 개월 : Overlap 4 개월 Resource System Resource 컨설팅 : 3 명 Roll-out : 12~15 명 개발 : 15~25 명 DB Server : 2 Ea Cube Server : 7 Ea Web Server : 4 Ea Storage : 6Tera byte
Customer Requirement Case Study Requirement Description Simulation Rule based Data Aggregation & Disaggregation Formula Simulation data can be saved Performance Data query : less than 10 sec. (30 sec.) Mass data : over 3 백만 cell Average 20 만 Cell Data simulation : real time Data save : less than 1 min. (3 min.) Convenience User friendly Operation Function Display Optimization
System Architecture Case Study Hardware 구성도 OLAP Server Partition Public 망 Internet Intranet 거래선사용자 [YY DMZ] L4 스위치 L4 스위치 L2 스위치 내부사용자 MSCS MSCS MSCS MSCS MSCS ABCD WEB #1 ABCD WEB #2 ABCD OLAP#1 ABCD OLAP#2 WEB #1 WEB #2 OLAP #1 OLAP #2 OLAP #3 OLAP #4 OLAP #5 ODS DW SAN Switch OLAP#1 (300GB) OLAP#2 (300GB) 스토리지 (USP#2) ODS (2TB) DW (4TB) OLAP#1 OLAP#2 (372GB) (310GB) OLAP#3 OLAP#4 (557GB) (310GB) 스토리지 (USP#6) OLAP#5 (420GB)
System Architecture Cube 구성도 Measure Group Partition DW / DM Cube UI Table_1_D Table for Measure Group 1 Delta 4) Save Table_1 Table for Measure Group 1 Data Partition 1) Data Partition for Delta 2) Measure Group 1_2011 Measure Group 1 Delta A B C Table_10 A B C OLAP 1 Measure Group 1_2012 Measure Group 10_2012 Write-back Partition 3) Measure Group 10 Write-back UI Server Partition Dimension Members A ~ H D E F OLAP 2 OLAP 3 G H 1) Data Partition is - used for basic unit with OLAP process and possible to reduce conflicts through simultaneous access - Partitioned by Period(Year) and Subsidiary 2) Data Partition for Delta is - ROLAP Delta Partition for UI Disaggregation and increment data insert 3) Write-back Partition is - Possible to write-back for Cube Disaggregation 4) Tables for Measure Group Delta - match up with data partition for Dalta 2) with cubes included other OLAP servers D E F G H
System Architecture Case Study Software 구성도 Smart client Smart client Client Smart Client : All user operation (data querying, inputting, saving) be executed in smart client environment based on.net 2Tier 3Tier Cellsets Tag 축소압축 Connection pooling Web Web Server : Optimize Data Cellsets Tag, zip and Connection pooling for lower network and oversea user SSAS s Cube OLAP Server OLAP Server : Load balance by separate SSAS Cube considering of system performance Legacy ODS Server DW Server DW, ODS Server : SQL Server Enterprise System SSIS MS-SQL SSIS MS-SQL Data I/F (ETL) : SQL Integration Services
Issue & Solution Case Study Issue Inquiry Performance Save 시단수차이발생 Simulation 시 Leaf Level 재계산필요 Cube Lock Solution DB : SQL Server Analysis Service 사용 Server Partition, Cube Partition Cube Model 최적화, MDX Generate 최적화 UI : Data CellSet XML Tag 축약, 통신압축 / 풀기 Grid Binding 시 Lazy Biding 처리 Calculation Engine 탑재 거시적 Planning : Cube Write-back 사용 ex) Statistical Forecast Adj, Marketing Plan, Top-down 등 미시적 Planning : Table Write-back 사용 (UI 구현 ) ex) Bottom-up, Weekly Consensus 등 Table Write-back 사용 (UI 구현 : Calculation Engine 사용 ) ex) 총금액수정 : 하위금액배분 단가로수량계산 단위금액재계산 총금액재계산 동시사용자가많은경우, Write-back partition 에대해 ROLAP Zero base Aggregation 설정
Optimize Result Case Study Data Loading Time Key Features Average data loading time ( 20,000 ~250,000 cell) SQL Server Multi DB Engine 사용 해외사용자를위한다양한접속방식지원 Matrix 조회에대해 RDB 대비 5~20 배빠름 대량데이터에대해 Partition 을통해최적화 실시간처리위한 ROLAP 이용 Hybrid 방식으로적용 2 Tier : 네트웍이좋은국내사용자 3 Tier : 네트웍이열악한해외국가사용자최적화를위한압축 / 풀기방식적용 Mass Option : 컴퓨터사양이낮은경우메모리풀기가아닌하드디스크에서풀기방식적용 Old System New System Data CellSets 의불필요 Attribute 제거와 Tag 최적화 Client 에서사용치않는불필요 Entity, Attribute 제거 XML 의 Tag 최소화로 Size 최소화, 30% 이하로줄임 Sec New Old 대량데이터 Grid Loading 최적화를위한 Lazy Binding 적용 Data Set 과 Grid Viewer Set 분리 Override 기법을이용하여보이는부분에한해먼저 Binding 과 Event 재적용 계산로직향상을위한 Calculation Engine 사용 2~3 단배분이참조값배분을위한최적화알고리즘적용 Finding 이나 Filtering 최적화를위한 Data Indexing Data Size (No of Sell)
Interactive simulation Case Study 다양한 Dis/Aggregation Key Features Grid 1 Rule1 : Forecast Rule2 : Sales Jan-11 Feb-11 Mar-11 Apr-11 May-11 Multi Level 배분 / 집계 Summary 와 Detail Grid 간배분 / 집계처리 Grid 내의 All 값에서배분 / 집계처리 Summary Forecast 180 100 Sales 200 Aggregation Disaggregation Grid 2 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Item G1 Forecast 60 Sales 30 Item G2 Forecast 40 Sales 20 Aggregation Disaggregation Grid 3 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Item1 Forecast 30 Sales 20 Item2 Forecast 30 Sales 20 배분참조 Measure 선택 Qty & Amt 자동전환단수처리최적화각종배분 Ratio 관리 수정한 Measure 기준으로배분 선택한참조 Measure 기준배분가능 Qty 수정시 Price 참조하여 Amt 자동계산 Amt 수정시 Price 참조하여 Qty 자동계산 수량단수나금액단수처리최적화지원 사사오입후총량맞추는것값큰단위 Item부터가감처리 Background 배분필요 Ratio 관리적용 UI 배분필요 Ratio 관리적용
User friendly UI Case Study 다양한 UI Function Key Features Menu & My Menu Docking 패널로넓은사용가능 잦은수행에대한빠른수행도구모음지원 All Group Qty Price Amt 6 6 1 통화전환처리 Local 통화입력후 USD, KWD 로전환가능 Measure 선택 Filter & Find 조회하고싶은 Measure 에대해사용자가직접선택하여사용가능 조회된데이터안에서관심있는항목에대해 Find 하거나 Filter 기능제공 Item 5 5 4 2 Grouping Level 입력화면에서 Grouping Level 선택으로 Grouping 된항목기준으로 FCST 입력기능 3 Summary Tab Collapse & Expand Cell Coloring & Blocking Excel Down & Upload Chart/Graph 세부데이터 Pop-up 조회 사용자선택적 Summary Grouping 제공 Row 와 Col 에대해 +,- 버튼을통해 Collapse & Expand 가능 Blocking 종류별로인지가능한컬러링 NPI/EOL, Un Assigned Item, Frozen Period 등의 Blocking Excel Down 시산식그대로적용 Upload 로 DB 저장지원 기본적인 Chart 와 Graph 지원 OLAP 에서사용자선택에따른지원 Link 활성화를통해세부데이터조회지원
Lesson 3 Large Volume Key Point Cube Design Hierarchies & Relationship RelationshipType은 'Rigid' 로 Measure Group Merge Measure dimension 사용 Write-back과 Locking
Cube Design Large Volume Key Point 계층구성시가능한특성관계를정의하자. 대량멤버 1) 차원의경우에 Numeric key 컬럼을사용하자. 특히 Distinct Count 에서 Character 와 Numeric 은많은성능차이발생. RelationType 은가능한 Rigid 옵션을설정하자. 동일차원과세분성 (Granularity) 를가진측정값그룹들의통합을고려하자. 큰행을가진측정값그룹에대해 Cube 파티션을만들자. 파티션당 200 만행이하또는 50Mb 이하가될수있도록 큰행 2) 을가진다대다차원관계또는측정값간에다대다관계를피하자. 제공되는참조수치는서버환경이나 Cube 설계, 데이터내용에따라다를수있음 1) 50 만이상 2) 100 만행
Hierarchies & Relationship Large Volume Key Point 계층구성시특성관계연결 종속관계가명확한경우에반드시특성관계연결 Case1 : 올바른특성관계연결 Case2 : 미흡한특성관계연결 미흡한특성관계연결시성능이떨어지는이유 집계과정에서불필요한단위집계생성 대신주요한집계가대상에서제외 Inquiry 시많은성능차이발생 조회튜플집계존재에따라 조회튜플근접한하위튜플레벨집계존재에따라 자식멤버리스트조회 자식멤버들의그룹핑 부모멤버참조해야하는경우 Fact Products Count Group1 10 5배수 Group2 50 Product 20배수 1,000
Hierarchies & Relationship Large Volume Key Point 계층구성시특성관계에따른차이. Case1 : Natural hierarchy ( 계층에따른특성관계설정 ) Case2 : Unnatural hierarchy ( 특성관계무시나미설정 ) 단위 millisec 구분 1 구분 2 Cell 수 Case1 Case2 Case2/1 비율 Duration CPUTime Duration CPUTime Duration CPUTime 최상위 1 0 0 0 0 - - Select 특정 PL2 모든 Product 272 0 32 16 62 % 194% 1개 Cell 1 0 0 0 0 - - 최상위 3,127 266 0 266 78 100% - Update 특정 PL2 UPDATE 272 251 0 204 16 81% - 1개 Cell 1 219 32 220 32 100% 100% 결과 조회튜플이나하위튜플의집계존재유무따라많은성능차이발생 가능한 Natural hierarchy가되도록유도필요 주의 : 제공되는수치는 Cube 설계나집계, 수행환경에따라비율차이가있을수있음
Numeric key 컬럼 Large Volume Key Point 대량멤버차원에대해 DataID 를위한 internal unique identifier 작업 & Lookup 최소화 Distinct 연산최적화 Data Retrieval Dimension Data Attribute Store Hierarchy Store Dim Data Retrieval Key Hash Table Storage Engine Measure Group Data Aggregations Fact data Storage Engine Cache Attribute lookups Key Hash Name Hash Bitmap Indexes Member name Hash Table : : Ex) Color Bitmap Index DataID Black Blue Red 1 1 0 0 2 0 1 0 3 1 0 0 : : Distinct Count 가큰 Att 에는 AttributeHierarchyOptimizedState 를 Not Optimized 로 Bitmap Index 생성않도록설정필요 Data ID Key Store Property Store Relationship Store DataID Key member values DataID, Att Property 들 Translations Data IDs Of Related Attributes DataID Att s relationships Ex) Relation Ship Store DataID Color Size.. : 567 2 3 : DataID 567 : Touring BK-T44U-60 Color 2 : Black Size 3 : 46
RelationshipType 은 'Rigid' 로 Large Volume Key Point 특성관계가시간에따라변경될지유무에따라설정 특성관계에대한정의기준 Rigid( 고정관계 ) : 멤버간의관계가시간에따라변경되지않는경우 Flexible( 유연한관계 ) : 멤버간의관계가시간에따라변경되는경우 설정않을시기본값은 Flexible 성능적영향 Flexible 로정의하면증분업데이트의일부로서집계가삭제되고다시계산됨 고정된관계로정의할경우차원이증분업데이트되면 Analysis Services 가집계를보유함. 문제의예 1 A 상품 Group2 변경 Group2_H Group2_Q Products Dim Group1 Group2 Products 2010 년 Partition 2011 년 Partition 2012 년 Partition 2 3 Daily Dim Incremental Processing Batch 2010년 Partition Agg1 2010년 Partition Agg2 2011년 Partition Agg1 2011년 Partition Agg2 Group1 Agg Group2 Agg 2012 년 Partition Agg1 Inquiry 2012 년 Partition Agg2 대응가능영역 최근 Partition 만 Full Processing Batch
Measure Group Merge Large Volume Key Point 동일차원과세분성의측정값그룹 Merge 유무차이. Case1 : 6개측정값을 1개측정값그룹에포함 Case2 : 6개측정값을 6개측정값그룹각각생성 단위 millisec 구분 1 구분 2 Cell 수 Case1 Case2 Case2/1 비율 Duration CPUTime Duration CPUTime Duration CPUTime 최상위 1 16 32 31 217 194% 678% Select 특정 PL2 모든 Product 272 16 32 31 186 194% 581% 1 개 Cell 1 0 32 16 112-350% 결과 Duration 약 2 배, CPU 는측정값그룹조합수만큼차이발생 따라서 Write-back 을수행해야하는 Forecast 테이블은분리 Reading 만하는테이블중세분성이같은경우는가능한 View 를통해통합
Measure Dimension 사용 Large Volume Key Point Pair Measure 들에대해 MDX Scope 사용 Case1 : 구분자를가지고한컬럼에여러 Measure 저장 Case2 : 각컬럼에저장하고 MDX Scope 구문사용 ex) Measure Dimension 에해당하는 Qty, Price, Amt 구현에대한방법 구분 1 구분 2 Cell 수 Case1 Case2 Case2/1 비율 Duration CPUTime Duration CPUTime Duration CPUTime Select Update 최상위 1 16 80 16 32 100% 40% 특정 PL2 모든 Product 272 48 142 31 48 65% 34% 1 개 Cell 1 16 0 0 0 0% - 최상위 3,127 12281 21407 14250 15172 116% 71% 특정 PL2 UPDATE 272 1905 3213 1689 1531 89% 48% 1 개 Cell 1 250 110 251 62 100% 56% 결과 : 전반적으로각컬럼에저장하고 MDX Scope 사용 50% 저렴 Select 경우 : Duration 30%, CPU 사용도 60% 정도적게사용 Update 경우 : Duration 은비슷하나 CPU 사용도 40% 적게사용함 데이터추출적재작업도 Case2 가더편리함
Write-back 과 Locking Large Volume Key Point Write-back Commit 동시수행에대한병목테스트 Case1 : 파티션별순차적으로개별 Write-back Commit 실행 Case2 : 모든파티션을동시 Write-back Commit 실행 구분 1 Account Partition Cell 수 Case1 Case2 Case2/1 비율 Duration CPUTime Duration CPUTime Duration CPUTime A Account 3,128 2,937 3,414 Update B Account 3,128 14,188 9,047 C Account 3,128 13,861 14,687 합계 9,384 30,986 27,148 60,251 32,747 194% 121% 결과 : 순차적 Commit 보다동시 Commit 이시간이더걸림 Update Execute 각세션에서처리되므로병목에관계없으므로 모든 Update Execute 를수행후일괄 Commit 수행으로최소화필요 Commit 에대한 Locking 메커니즘은 RDB 와다르며리소스도많이들어감 다수의동시 Write-back 수행을위해서는 Cube 의분리나 Server 분리를고려할필요있음
Lesson 4 Write back method Write-back Architecture Write-back Method Cube Write-back Table Write-back MS Intelligence planning
PivotTable Service Write-back Architecture Write-back method Custom add-in Microsoft Management Console(MMC) Update Cube~ Custom application Analysis Add-in Manager 1 Update Cube~ Update Cube~ Disk storage : 1 or more Meta data repository Analysis Manager Enterprise Manager Object model (Analysis Management Object) Cube Write-back 2 Commit Session cache Update Session cache Client Data source Data source Cube Cube Mining model Mining model Analysis Server Cube Server cache 3 PivotTable Service Write-back Partition processing Local cubes Local cubes Local data Local data Mining Mining model model Data source for local cubes Data source Data source for local data mining models Data source Excel 의가상분석 ADOMD.NET Management Studio MDX 창
Write-back method Write-back method Cube Write-back Direct Cube Write-back Cube 에 Write-back Partition 설정필요 Table Write-back Table 에 Increment data Insert 해당 Table 에연결된 Cube Partition 필요 구분장점단점 Cube Write-back Table Write-back Intermediate Level Update 가능 관련 Tuple 하위의모든 Members 에대해일괄갱신가능 복잡한 Disaggregation 요구에대응가능 ROLAP Zero Aggregation 인경우 Partition Process 불필요 최종데이터추출은 Query Commit 을통한 Partition Process 필요, 즉 Locking 비용필요 최종데이터추출은 MDX 로해야함 Leaf Tuple 에대해서만 Update 가능 개발적난이도있음 Data Cellsets 에대한이해 Front 에서증분값계산필요 Fact 테이블구조에맞게 Insert 필요 Locking 과정이없으로중복값검증및보정작업필요
Update Cube ~ Cube Write-back Write-back method Direct Cube Write-back Cube 에 Write-back Partition 설정필요 최종데이터추출 MDX 사용 Measure Group Fact Table Partition 2011 ADOMD.NET Partition 2012 Management Studio MDX 창 최종데이터추출 Use MDX Database Write-back Partition 3 2 Commit Commit 에의한 Partition Processing 1 Excel 의가상분석
Table Write-back Write-back method Table에 Increment data Insert 해당 Table에연결된 Cube Partition 필요 ROLAP Zero Aggregation 인경우 Partition Process 불필요 Front에서 Increment Value 계산필요 최종데이터추출 Query 사용 Fact Table Measure Group S-Phone I Sales 80 3 월 4 월 5 월 Partition 2011 Forecast 85 90 80 S-Phone II Sales 105 Forecast 110 115 120 130 최종데이터추출 Use Query 120 115 120 Fact Table -5 Partition 2012 General Partition For Write-back Database Insert ~ Values(S-Phone II, 4 월, Forecast, -5)
Cube Write-back vs. Table Write-back method Cube Write-back 과 Table Inert & Processing 비교 Case1 : Cube Write-back Partition에 Write-back 수행 Case2 : Table에 Insert 후파티션 Processing (Forecast UI에서증감분계산후 Insert 하는경우 ) Case 1 Case 2 Case2/Case1 비율 비고 Duration 4,273 883 20% CPUTime 9,243 1,489 16% 결과 Forecast UI 에서증감분을만들어 Insert 하는 Table Write-back 이 5 배정도리소스와시간을적게사용함을알수있음 따라서동시 Write-back 이많은시스템에대한병목을해결할수있으나, Locking 과정이없으므로중복값검증및보정작업필요
Locking 과중복값보정 Write-back method Cube Write-back 일반적인 Locking 메커니즘과동일 1 Session 1 : Read 60 2 Session 2 : Read 60 3 4 Session 1 : Write 66 Locking Interval Session 2 : Write 75 60 75 66 Table Write-back Write 대상 Partition Table 에직접저장, 즉 Delta Table Cube Lock 을사용치않기때문에별도보정작업필요 60 75 81 66 Tuple Read Write Delta 값 값 A null 60 12 A 60 75 15 75 3 A 60 66 6 81 4 A -15 66 Latest 값으로보정
Allocation Method Write-back method 4 개의 Allocation Method 를제공하며주로 USE_WEIGHTED_ALLOCATION 와 USE_WEIGHTED_INCREMENT 를사용함 Allocation method USE_EQUAL_ALLOCATION Description 해당 Tuple 의모든리프셀에대해 Count 로신규값을균등배분하여갱신 <leaf cell value> = <New Value> / Count(leaf cells that are contained in <tuple>) USE_EQUAL_INCREMENT USE_WEIGHTED_ALLOCATION USE_WEIGHTED_INCREMENT 신규값과기존값의차이분에대해 Count 로나누어균등배분 <leaf cell value> = <leaf cell value> + (<New Value > - <existing value>) / Count(leaf cells contained in <tuple>) 신규값을 Weight Expression 에비율로배분 Ex) A 제품군 Forecast 입력값에대해과거 3 개월평균매출로비율 <leaf cell value> = < New Value> * Weight_Expression 신규값과기존값의차이분에대해 Weight Expression 에비율로배분 Ex) 기존입력값들을부분존중하여증감분에대해서만 Weight Expression 에비율로배분 <leaf cell value> = <leaf cell value> + (<New Value> - <existing value>) * Weight_Expression 주의 : 정수가포함된측정값에서사용되는경우, 가중치적용한 USE_WEIGHTED_ALLOCATION 메서드는증분적인반올림변화로인한일부부정확한결과를반환할수있음. -. Weight_Expression 는 0 과 1 사이의값에해당하는값이나산식이여야함 -. Allocation method 지정않은경우 Weight_Expression = <leaf cell value> / <existing value> 으로처리됨
Allocation Method Write-back method 정수 Measure 에가중치를사용한경우데이터정합성 Case1 : USE_WEIGHTED_ALLOCATION로가중치지정한경우 Case2 : 그외 (USE_EQUAL_ALLOCATION, USE_EQUAL_INCREMENT USE_WEIGHTED_INCREMENT) 구분 Case 1 Case 2 입력값유지입력값의근사값처리됨 처리방법 0 또는 NULL 값 각멤버에해당하는가중치로적용하고각값을반올림하여적용함 Initial 이 NULL 이나 0 이더라도별도가중치가주어진경우값이할당됨 입력값유지됨 Method 에따라몇가지방법으로보정함 기존값이 0 이거나 NULL 인경우 0 이됨첫번째멤버일경우 0 이아닐수도있음 결과 유사값처리가허용되는경우가아니라면가중치설정한 USE_WEIGHTED_ALLOCATION 외의 Method 사용고려 Leaf Level 의값이작거나정확해야한다면가중치적용한 USE_WEIGHTED_ALLOCATION 외의 method 사용고려
Allocation Method 가중치사용치않고 Allocation : 18 37 38 Step1. 입력멤버에대한총증가배수를계산 : 54/25 = 2.16 Step2. 하위멤버에반올림한증가배수곱한수반올림 Step3. 전체합계와의차이를차이큰멤버부터순차적으로가감반영 기존값 신규값 증감비값 반올림값 차이분 차이분가감 최종값 Group 1 18 38 38 37 1 38 Member1 1 2.11111111 2 0.11111111 2 Member2 2 4.22222222 4 0.22222222 4 Member3 3 6.33333333 6 0.33333333 6 Member4 4 8.44444444 8 0.44444444 1 9 Member5 5 10.5555556 11-0.4444444 11 Member6 3 6.33333333 6 0.33333333 6 가중치사용 Allocation : 30 61 60 Step1. 신규값을 Weight Expression 에비율로배분 Step2. 배분값을반올림처리 Current Weight Measure Weight Ratio 증감비값반올림값 Group 1 30 200 1 61 60 Member1 3 14 0.07 4.27 4 Member2 7 50 0.25 15.25 15 Member3 9 62 0.31 18.91 19 Member4 11 54 0.27 16.47 16 Member5 (null) 20 0.10 6.10 6
MS Intelligence planning Centralized data model with Analysis Services. Dimensional data modeling with PowerPivot for Excel. Form and report authoring through Excel 2010 PivotTables. Data entry and What-If analysis through Excel PivotTables. Online document storage and collaboration with security and workflow for forms and reports through SharePoint Server. (solutions and scenarios) OLTP vs. OLAP http://technet.microsoft.com/en-us/library/gg558556.aspx
Excel 가상분석 (What-if) 사용 Write-back 활성화 Write-back 수행 Allocation Method 설정 1 2 3 4 선택조합 1 3 2 3 1 4 2 4 Allocation method USE_EQUAL_ALLOCATION USE_EQUAL_INCREMENT USE_WEIGHTED_ALLOCATION USE_WEIGHTED_INCREMENT
Demo Cube Write-back
Summary 다차원 DB 인 Analysis Services 활용 RDB 상에서속도가느리거나사용에불편함이있는경우에 Background 변경검토 Forecast 와같은 Matrix 형태의데이터조회는 Analysis Services 를사용하는것이효과적 특히 DW 의데이터용량이크거나화면표현데이터량이많은경우필수적이라할수있음. Write-back 선택 Forecast 요건이복잡하지않은경우라면간단하게 Excel 의가상분석 (Whit-if) 를사용하여구현. 복잡한배분이필요한 Biz 요건이있다면별도구현고려할수있음
r