MDX 의 이해와 활용 권오주 올랩포럼 시삽 Microsoft Global MVP www.olapforum.com.

Presentation on theme: "MDX 의 이해와 활용 권오주 올랩포럼 시삽 Microsoft Global MVP www.olapforum.com."— Presentation transcript:

1 MDX 의 이해와 활용 권오주 올랩포럼 시삽 Microsoft Global MVP www.olapforum.com

2 소개 내용  MDX 의 의의  MDX 기본 이해  MDX 기본 활용  MDX 고급 활용

3 MDX 의 의의

4 MDX 의 이해  MDX = Multi Dimensional eXpressions  OLAP 데이터베이스를 모델링하고 쿼리하는 구문 다차원 오브젝트 이해 큐브, 차원, 수준, 구성원, 셀, … 다차원 공간 탐색 및 이동  OLAP Services 고급 구현 필수 복잡한 비즈니스 로직 구현 다양한 부가적 기능  OLE DB for OLAP 스펙의 부분  Provider (Analysis Services, TM1, SAS, WhiteLight, SAP, …)

5 OLE DB for OLAP 지원  지원 선언 업체 리스트 (2002.06 기준 ) Acuity Group, Andyne Computing Limited, Application Consulting Group, Inc. (ACG), Applix TM1 Software, Arbor Software, arcplan, Inc., AVOCA Systems Limited, BAAN, Brio Technology, Business Objects, Cognos, Comshare, Gentia Software, Hyperion Software, Information Advantage Inc., International Software Group, InterNetivity, Intersolv, Inc., IQ Software, Knosys, Microsoft, MIS AG, NCR,Panorama Software Systems Ltd., Pilot Software, Sagent Technology, Inc., SAS Institute, Seagate Software, Silvon Software, Simba Technologies, Speedware Corporation, WhiteLight Systems, X- Tension,...

6 MDX 쿼리가 가능한 데이터베이스  Analysis Services, TM1, SAS, SAP, Whitelight, Silvon, Harmony, NCR, Seagate, Essbase,...  제한된 범위 내에서 업체별로 MDX 를 확장해 기능 강화 (Superset, Subset)

7 Analysis Services MDX 활용  동작  계산된 구성원  계산된 셀  명명된 집합  사용자 롤업 수식  사용자 지정 구성원 수식  차원 보안의 사용자 지정 규칙  셀 보안의 사용자 지정 규칙  동적인 속성 설정

8 MDX 의 활용 유형  MDX Statement 브라우징용 쿼리 언어 (SQL 쿼리와 유사 ) 완전한 구문 (SELECT…FROM) 클라이언트 Apps 용 도구 완전한 보고서 형태 값들 반환 (Data Set)  MDX Expression 다차원 수식 ( 스프레드시트 수식과 유사 ) 비즈니스 모델링 계산된 구성원, 기본 구성원, 차원 / 큐브 속성 생성 및 설정 등에 활용 하나의 단일 값 반환

9 MDX 기본 이해

10 MDX 쿼리 SELECT ON COLUMNS, ON ROWS, … FROM Cube WHERE SELECT ON COLUMNS, ON ROWS, … FROM Cube WHERE  128 개의 축 ( 하나의 축에 차원 중첩 가능 )  쿼리 실행 축 생성 셀의 데이터 값 획득  예 ) 97, 98 년 CA 의 판매수량, 판매액 SELECT { [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS, { [Time].[1997], [Time].[1998] } ON ROWS FROM Sales WHERE ( [Store].[USA].[CA] )

11 MDX 쿼리 실행 GroceriesClothing Appliances SalesCostUnits 97 98 99 9695 20 10

12 MDX 구조  구성원 (Member) [ 홍두깨 ], [1991].[Q1].[1 월 ]  튜플 (Tuple) ( [ 홍두깨 ], [ 서울 ] ), ( [ 컴퓨터 ], [1991] )  집합 (Set) { [ 홍두깨 ], [ 홍길동 ] } {( [ 컴퓨터 ],[ 서울 ] ), ( [ 프린터 ],[ 경기 ] )} [1991].Children TopCount( [ 영업부 ].Members, 10, [ 판매액 ] )

13 다차원 공간  구성원 (Member)  수준 (Level)  차원 (Dimension)  큐브 (Cube)/ 파티션 (Partition)  셀 셀 주소 (Cell Address) 상대 주소 (Relative Address) 현재 셀 (Current Cell)

14 셀 주소 97 98 99 Non-Cons Food Drink SalesCostUnits (Products.Food, Measures.Units,Time. 98) (Products.Food, Measures.Sales,Time. 97) (Products.[Non-Cons], Measures.Cost,Time.95) 96 95

15 98 Food Units ? ? (Products.Food, Measures.Units, Time.98.PrevMember) (Products.Food, Measures.Units, Time.98.NextMember) 상대 주소

16 98 Food Units ? (Products.Food, Measures.Units, Time.98.PrevMember) ? (Products.Food, Measures.Units, Time.98.NextMember) ? (Products.Food, Measures.Units.PrevMember, Time.98.Lag(3)) (Products.Food, Measures.Units.PrevMember, Time.98.Lead(-3)) 또는 상대 주소

17 현재 셀 97 98 99 Non-Cons. Food Drink 96 95 Current Cell : ( Product.CurrentMmeber, Time.CurrentMember, Measures.CurrentMember ) Sales Cost Units Growth

18 97 98 99 Non-Cons Food Drink SalesCostUnits 96 95 WITH MEMBER Measures.Growth AS ‘(Time.CurrentMember,Measures.Sales) - (Time.CurrentMember.PrevMember,Measures.Sales)’ SELECT Time.Year.Members ON ROWS, AddCalculatedMembers(Measures.Members) ON COLUMNS FROM Sales 15135 17 13 6 22 11 7 29 9 9 30 10 8 GrowthTime.95Empty 15 Time.CurrentMember Time.CurrentMember.PrevMember 판매액의 전기 대비 성장률 ?

19 Time.96 Time.95 15 2 13 5 17 13 6 22 11 7 29 9 9 30 10 8 97 98 99 Non-Cons Food Drink SalesCostUnits 96 95 Growth WITH MEMBER Measures.Growth AS ‘(Time.CurrentMember,Measures.Sales) - (Time.CurrentMember.PrevMember,Measures.Sales)’ SELECT Time.Year.Members ON ROWS, AddCalculatedMembers(Measures.Members) ON COLUMNS FROM Sales Time.CurrentMember Time.CurrentMember.PrevMember

20 Time.97 Time.96 15 2 5 13 5 17 13 6 22 11 7 29 9 9 30 10 8 97 98 99 Non-Cons Food Drink SalesCostUnits 96 95 Growth WITH MEMBER Measures.Growth AS ‘(Time.CurrentMember,Measures.Sales) - (Time.CurrentMember.PrevMember,Measures.Sales)’ SELECT Time.Year.Members ON ROWS, AddCalculatedMembers(Measures.Members) ON COLUMNS FROM Sales Time.CurrentMember Time.CurrentMember.PrevMember

21 Time.98 Time.97 15 2 5 7 13 5 17 13 6 22 11 7 29 9 9 30 10 8 WITH MEMBER Measures.Growth AS ‘(Time.CurrentMember,Measures.Sales) - (Time.CurrentMember.PrevMember,Measures.Sales)’ SELECT Time.Year.Members ON ROWS, AddCalculatedMembers(Measures.Members) ON COLUMNS FROM Sales Time.CurrentMember Time.CurrentMember.PrevMember 97 98 99 Non-Cons Food Drink SalesCostUnits 96 95 Growth

22 Time.99 Time.98 15 2 5 7 1 13 5 17 13 6 22 11 7 29 9 9 30 10 8 WITH MEMBER Measures.Growth AS ‘(Time.CurrentMember,Measures.Sales) - (Time.CurrentMember.PrevMember,Measures.Sales)’ SELECT Time.Year.Members ON ROWS, AddCalculatedMembers(Measures.Members) ON COLUMNS FROM Sales Time.CurrentMember Time.CurrentMember.PrevMember 97 98 99 Non-Cons Food Drink SalesCostUnits 96 95 Growth

23 MDX 기본활용

24 기간 분석 요구 사항 …  판매액의 전기 대비 성장률 ?  판매액의 전년 대비 성장률 ?  판매액의 기간 누적 ?  재고량 기간별 재고량 ? 기간별 평균 재고량 ? 최근의 평균 재고량 ? 기간 중 최대 / 최소 재고량

25 판매액의 전기 대비 성장률 ? YearQuarterMonthSalesResult1997790 Q1120 Jan30 Feb40 Mar50 Q2200 Apr65 May45 Jun90 Q3185 Jul55 Aug60 Sep70 Q4285 Oct80 Nov100 Dec105 10 100 10 20 5 -20 45 -15 -45 5 10 80 15

26 Time.CurrentMember, Measures.CurrentMember Time.CurrentMember,Measures.Sales Time.CurrentMember.PrevMember, Measures.Sales YearQuarterMonthSalesResult Q2200 Apr65 May45 Jun90 -20 45 80 15

27 판매액의 전년 대비 성장률 ? YearQuarterMonthSalesResult 1997790 Q1120 Jan30 Feb40 Mar50 : : : : : : Q4285 Oct80 Nov100 Dec105 1998 Q1170 Jan50 Feb55 Mar65 Q4275 Oct90 Nov100 Dec85 20 10 50 -10

28 (Measures.Sales,Time.CurrentMember) - (Measures.Sales,ParallelPeriod(Year,1,Time.CurrentMember)) YearQuarterMonthSalesResult 1997790 Q1120 Jan30 Feb40 Mar50 : : : : : : Q4285 Oct80 Nov100 Dec105 1998 Q1170 Jan50 Feb55 Mar65 Q4275 Oct90 Nov100 Dec85 20

29 판매액의 기간 누적 ? YearQuarterMonthSalesYTD 1997790 Q1120120 Jan3030 Feb4070 Mar50120 Q2200320 Apr65185 May45230 Jun90320 Q3185505 Jul55375 Aug60435 Sep70505 Q4285790 Oct80585 Nov100685 Dec105790

30 Sum(YTD(Time.CurrentMember),Sales) Time.Mar,Measures.Sales Time.Feb,Measures.Sales Time.Jan,Measures.Sales + + (Time.CurrentMember,Measures.Sales) + (Measures.YTD,Time.CurrentMember.Prevmember) YearQuarterMonthSalesYTD 1997790 Q1120120 Jan3030 Feb4070 Mar50120

31 재고량 분석 요구사항...  일정 시간 시점의 재고량 (Snapshot)  비즈니스 분석요구사항 각 기간의 시작과 종결시점의 재고량 각 기간의 평균수량과 재고가치 한 기간에서 최저와 최고일 때의 재고량 전체 재고가치에 대한 각 아이템의 상대적 기여도  문제점 : 재고량 측정값은 시간에 대해 누적되지 않는 값

32 재고량 ……. 문제점 : 재고량은 누적되는 값이 아니다.. … YearQuarterMonthInventory1997790 Q1120 Jan30 Feb40 Mar50 Q2200 Apr65 May45 Jun90 Q3185 Jul55 Aug60 Sep70 Q4285 Oct80 Nov100 Dec105

33 마감 재고 해당기간의 마지막 월의 수량 다음 중 마지막 월의 수량 (Descendants([Time].CurrentMember, [Month]) (Measures.Quantity, Tail(Descendants([Time].CurrentMember, [Month]),1).Item(0)) (Measures.Quantity, ClosingPeriod([Month]))

34 (Inventory, ClosingPeriod(Month)) ClosingPeriod(Month)) 50 90 70 105 YearQuarterMonthInventoryResult1997790 Q1120 Jan3030 Feb4040 Mar5050 Q2200 Apr6565 May4545 Jun9090 Q3185 Jul5555 Aug6060 Sep7070 Q4285 Oct8080 Nov100100 Dec105105

35 Avg( Descendants([Time].CurrentMember,[Month]), Quantity) 기간의 모든 월별 수량의 합계를 월수로 나눈다 Sum ( 기간의 월들, Quantity) / Count( 기간의 월들 ) Sum (Descendants([Time].CurrentMember,[Month]), Quantity) / Count(Descendants([Time].CurrentMember,[Month])) 기간에 대한 평균

36 Avg( Descendants([Time].CurrentMember,[Month]), Inventory) 40.0 66.7 61.7 95.0 65.8 YearQuarterMonthInventoryAvg 1997790 Q1120 Jan3030 Feb4040 Mar5050 Q2200 Apr6565 May4545 Jun9090 Q3185 Jul5555 Aug6060 Sep7070 Q4285 Oct8080 Nov100100 Dec105105

37 최근의 평균 재고량 IIF(Time.CurrentMember.Level.Name = “Month”, AVG(LastPeriods(5, Time.CurrentMember), AVG(LastPeriods(5, Time.CurrentMember), Inventory), Inventory), NULL) NULL) Measures.[Avg Inv Of Last5 Mon]

38 기간 중 최대 재고량 Measures.[Maximum Inventory] MAX( Descendants(Time.CurrentMember,Time.Month), Descendants(Time.CurrentMember,Time.Month), Measures.[Value] Measures.[Value] )

39 기간 중 최소 재고량 Measures.[Minimum Inventory MIN( Descendants(Time.CurrentMember,Time.Month), Descendants(Time.CurrentMember,Time.Month), Measures.[Value] Measures.[Value] )

40 가장 최근의 값  재귀적 참조 이용 WITH MEMBER Measures.[Last Sales] AS 'IIF(IsEmpty((Measures.[Store Sales], Time.CurrentMember)), 'IIF(IsEmpty((Measures.[Store Sales], Time.CurrentMember)), (Measures. [Last Sales], Time.PrevMember), (Measures. [Last Sales], Time.PrevMember), (Measures.[Store Sales], Time.CurrentMember) (Measures.[Store Sales], Time.CurrentMember) )' )' SELECT {[Store Sales], [Last Sales]} ON COLUMNS, {Time.Members} ON ROWS {Time.Members} ON ROWS FROM Sales Store Sale Last Sales Jan100100 Feb200200 Mar Apr :: 200

41 기여도 분석요구 사항...  순위 소속별 순위 전체 순위  기여도 점유율 배부

42 제품의 소속 그룹내 판매액 순위 제품의 그룹별로 각 제품의 판매액 순위 Rank( 현재제품, 판매액으로 정렬된 현재제품이 속한 그룹의 제품들 ) Rank(Product.CurrentMember, Order( 해당 그룹의 제품들, [Store Sales])) Rank(Product.CurrentMember, Order(Product.CurrentMember.Siblins, [Store Sales], DESC) )

43 제품에 속한 레벨내 판매액 순위 제품의 레벨내에서 각 제품의 판매액 순위 Rank( 현재제품, 판매액으로 정렬된 현재제품이 속한 레벨의 제품들 ) Rank(Product.CurrentMember, Order( 해당 레벨의 제품들, [Store Sales])) Rank(Product.CurrentMember, Order(Product.CurrentMember.Level.Members, [Store Sales], BDESC) )

44 점유율 현재 제품 또는 그룹이 전체에서 차지하는 판매 기여도 현재 제품 또는 그룹의 판매 금액 / 전체 제품의 판매금액 [Store Sales] / ( 전체 제품, [Store Sales]) [Store Sales] / (Product.[All Products], [Store Sales])

45 판매실적 기준 예산 배분 SalesCost Allot Budget All Products 800040004000 Drink Drink33001650 Alcoholic Alcoholic20001000 Beverages Beverages800400 Dairy Dairy500250 Food Food47002350 Baked Baked1500750 Canned Canned25001250 Eggs Eggs700350

46 ([All Products], [Allot Budget]) * ([Store Sales] / ([All Products], [Store Sales])) 제품 전체 예산 * 현재 제품 또는 그룹이 전체에서 차지하는 판매 기여도 ([All Products], [Allot Budget]) * 현재 제품 또는 그룹이 전체에서 차지하는 판매 기여도

47 MDX 고급활용

48 Current Cell Context  Default Member All Member 최상위 레벨의 첫번째 Member 별도 지정된 Member (MDX 활용 가능 )  CurrentMember FROM WHERE Closest Context 축과의 교차점 서브 집합

49 반복 처리  반복처리 관련 주요 함수 Generate CurrentMember Current Item  상점별 판매금액 기준 Top 5 제품들  판매이익 Top5 고객 중에 판매금액이 Top5 인 고객  SCD Type II 에서의 사원별 판매금액 실적

50 Generate()  Set Generate(Set1, Set2[, ALL]) Set1 을 구성하는 각 튜플에 대하여 Set2 복제 결합  문자열 Generate(Set, String Expression[, Delimiter]) Set 을 구성하는 각 튜플에 대하여 문자열 생성 결합

51 Current  집합내의 튜플들에 대한 반복 처리에서 현재 튜플 반환  Set.Current  Set ↔ Tuple ↔ Member Set.Item(n) → 집합내에서 n + 1 번째 튜플 반환 Tupe.Item(n) → 튜플내에서 n + 1 번째 구성원 반환 Ex) Set.Item(0).Item(0) → 집합내의 첫번째 튜플의 첫번째 구성원

52 상점별 판매금액 기준 Top 5 제품들 상점 반복 처리 ( 현재 처리 중인 상점에 대한 판매금액 Top5 제품들 ) Generate(Store.[Store Name].Members, 현재 처리 중인 상점에 대한 판매금액 Top5 제품들 ) Generate(Store.[Store Name].Members, TopCount( 제품들, 5, [Store Sales]) Generate(Store.[Store Name].Members, TopCount([Product].[Product Name].Members, 5, [Store Sales])

53 판매이익 Top5 고객 중 판매금액 Top5 고객 ProfitTop5: TopCount([Customers].[Name].Members, 5, Profit) SalesTop5: TopCount([Customers].[Name].Members, 5, [Unit Sales]) Generate(ProfitTop5, 현재 처리 중인 고객이 판매금액 Top5 에 속하는가 ?) Generate(ProfitTop5, Filter(SalesTop5, ProfitTop5.Current IS SalesTop5.Current)

54 SCD Type II 에서의 사원별 판매금액 실적 판매 실적 조직 전체 70 A 부서 50 김기훈 20 홍길동 30 B 부서 20 정일수 20 판매 실적 조직 전체 70 A 부서 50 김기훈 20 홍길동 30 B 부서 20 정일수 20 홍길동 30 변경전 변경후

55 SUM({[A 부서 ].[ 홍길동 ], [B 부서 ].[ 홍길동 ]}, [Sales]) SUM(Filter([ 조직 ].[ 사원 ].Members, [ 조직 ].CurrentMember.Name = “ 홍길동 ”), [Sales]) SUM(Filter([ 조직 ].[ 사원 ].Members, [ 조직 ].CurrentMember.Properties(“ 사원 ID”) = “100”), [Sales]) SUM(Generate({[ 조직 ].CurrentMember} AS Set1, Filter([ 조직 ].[ 사원 ].Members, Set1.Current.Item(0).Properties(“ 사원 ID”) = [ 조직 ].CurrentMember.Properties(“ 사원 ID”) ) ), [Sales])

56 동적 축 처리  동일한 로직을 가지는 분석 대상 전체에서 차지하는 현재 기준의 점유율 소속 그룹에서 차지하는 현재 기준의 점유율 현재 기준은 사원, 고객, 제품,... 등 다양 → 기준별로 계산된 구성원 정의 ?  Rows 축에 상관없이 동일한 로직을 가지는 계산된 구성원 Rows 축에 위치하는 차원 정보를 동적으로 얻을 수 있는가 ? 얻을 수 있다면, 하나의 계산된 구성원으로 대체 가능

57 동적 축 처리  Axis(n) 쿼리 결과로부터 Axis(n) 의 집합 참조 멤버 쿼리가 발생해야 AXIS 컨텍스트가 결정 예 ) Axis(0).Item(0).Item(0).Dimension. CurrentMember  StrToSet( "Axis(0)" ) 문자열 식으로부터 집합 생성 큐브 내에 AXIS() 존속 가능 예 ) StrToSet(“Axis(0)”).Item(0).Item(0).Dimension. CurrentMember

58 계산된 구성원으로 이루어진 차원  수식 정의를 위한 특별한 차원 설계 Measure 하나당 여러 개의 수식 정의 요구 → Measures 차원에 Measure 별로 복수개의 계산된 구성원 정의 ?  별도의 차원으로 분리 계산된 구성원 사용자 지정 구성원 수식  Measures 를 비롯한 모든 차원과 교차  부모 - 자식 차원은 최고의 유연성 제공

59 팩트 테이블 설계  팩트 테이블용 뷰 활용 SELECT Time_ID, Product_ID, Customer_ID, Store_ID, 1 AS Calc_ID, ← Sales_Amt Time_ID, Product_ID, Customer_ID, Store_ID, 1 AS Calc_ID, ← Sales_Amt FROM Sales_Fact

60 차원 테이블 설계  Dummy 테이블 팩트 테이블과 조인되는 하나의 데이터만 관리 계산된 구성원 정의 및 변경을 큐브 편집기에서 관리  별도의 일반 테이블 팩트 테이블과 조인되는 하나의 데이터 + 계산된 구성원 계산된 구성원 정의 및 변경을 테이블에서 관리

61 Thank you !