26 - SSAS - MDX 쿼리

MDX 쿼리

 

MDX 쿼리의 기본 문법 및 사용 법을 알아 보도록 하겠습니다.

 

[MDX 쿼리 기본]

MDX의 쿼리 기본 구조는 다음과 같습니다.

SELECT [<축지정>], [<축지정>].. FROM [<큐브지정>] WHERE [슬라이서지정]

 

<축 지정>은 같은 차원성을 가지는 튜플들의 모임인 집합으로 기술하여 128개 까지 지정이 가능 합니다. 축은 AXIS(0), AXIS(1)등과 같이 기술하며 COLUMNS, ROWS와 같은 별칭을 쓰기도 합니다. 이때 축을 지정하는 순서는 상관 없으나 높은 번호의 축을 지정하는 경우 그보다 낮은 번호의 축을 지정해야 합니다.

 

Sales 큐브에서 데이터를 읽어오는데 축정의를 하지 않아 이때에는 모든 차원의 기본 멤버들로 구성되는 튜플에 해당하는 값을 반환 합니다. 현재 실습중인 [Adventure Works]큐브의 Measures 차원의 기본 멤버는 [Reseller Sales Amount]입니다.

SELECT FROM [ADVENTURE WORKS]

 

 

 

Measure 차원은 [Sales Amount]에 해당하고 나머지 차원은 기본 멤버에 해당하는 튜플의 값을 반환 합니다.

SELECT     {[SALES AMOUNT]} ON COLUMNS FROM [ADVENTURE WORKS]

 

 

 

제품 카테고리를 구성하는 각각의 멤버들에 대하여 Measures 차원은 [Sales Amount]에 해당하고 나머지 차원은 기본 멤버에 해당하는 튜플의 값을 반환 합니다.

SELECT     {[SALES AMOUNT]} ON COLUMNS,     {[PRODUCT].[CATEGORY].[CATEGORY].MEMBERS} ON ROWS FROM [ADVENTURE WORKS]

 

 

 

제품 카테고리를 구성하는 각각의 멤버들에 대하여 Measures 차원은 [Sales Amount], Sales Channel 차원은 [Internet]에 해당하고 나머지 차원은 기본 멤버에 해당하는 튜플의 값을 반환 합니다. WHERE 절을 통하여 축에서 지정하지 않는 차원에 대하여 기본 멤버 대신 다른 멤버를 참조하도록 할 수 있습니다.

SELECT     {[SALES AMOUNT]} ON COLUMNS,     {[PRODUCT].[CATEGORY].[CATEGORY].MEMBERS} ON ROWS FROM [ADVENTURE WORKS] WHERE ([SALES CHANNEL].[SALES CHANNEL].&[INTERNET])

 

 

 

ROW라는 높은 번호의 축을 지정하려면 반드시 COLUMNS라고 하는 더 낮은 번호의 축을 지정해 주저야 합니다. 그렇지 않으면 오류가 발생 합니다.

SELECT     {[SALES AMOUNT]} ON ROWS FROM [ADVENTURE WORKS]

 

 

 

[MDX 쿼리에서의 계산 멤버 활용]

빈번하게 사용하는 계산 멤버는 서버의 큐브에 미리 정의해 놓을 수 있습니다. 그러나 필요한 경우에만 WITH 키워드를 사용하여 쿼리를 실행하는 시점에 런-타임으로 정의해서 활용할 수도 있습니다.

WITH MEMBER MEASURES.[%OFTOTAL] AS [SALES AMOUNT] / ([SALES AMOUNT], [ALL PRODUCTS]), FORMAT_STRING = 'PERCENT' SELECT {[SALES AMOUNT], [%OFTOTAL]} ON COLUMNS, {[PRODUCT].[CATEGORY].[CATEGORY].MEMBERS} ON ROWS FROM [ADVENTURE WORKS]

 

제품 카테고리의 각 멤버들에 대하여 전체 제품 대비 매출 기여도를 확인 할 수 있습니다.

 

 

[MDX 쿼리에서의 명명된 집합 활용]

제품 서브 카테고리의 각 멤버들을 대상으로 매출액 상위 10에 해당하는 멤버들의 집합을 런-타임으로 생성하여 처리 합니다.

WITH SET TOP10OFSUBCATEGORY     AS TOPCOUNT([PRODUCT].[SUBCATEGORY].[SUBCATEGORY].MEMBERS,         10, [SALES AMOUNT]) SELECT {[SALES AMOUNT]} ON COLUMNS,     TOP10OFSUBCATEGORY ON ROWS FROM [ADVENTURE WORKS]

 

 

SELECT 문에서 WITH 사용시 해당 쿼리를 실행하는 시점에서 런-타임으로 실행 후 바로 소멸 됩니다. 만약 현재 세션에서 반복적으로 사용하고자 한다면 CREATE MEMBER나 CREATE SET 으로 먼저 한번 정의해 주면 이어지는 추가 SELECT 쿼리문에서 반복 참조가 가능 합니다.

 

 

[WHERE 절에서의 집합 활용]

판매 채널의 멤버들에 대하여 Bikes와 Clothing의 합에 해당하는 매출액과 세금액을 반환 합니다.

SELECT {[SALES AMOUNT], [TAX AMOUNT]} ON COLUMNS, {[SALES CHANNEL].[SALES CHANNEL].MEMBERS} ON ROWS FROM [ADVENTURE WORKS] WHERE {[BIKES],[CLOTHING]}

 

 

-- SSAS 2000 방법. WITH MEMBER [PRODUCT].[CATEGORY].[BIKES+CLOTHING] AS AGGREGATE({[BIKES],[CLOTHING]}) SELECT {[SALES AMOUNT], [TAX AMOUNT]} ON COLUMNS, {[SALES CHANNEL].[SALES CHANNEL].MEMBERS} ON ROWS FROM [ADVENTURE WORKS] WHERE {[BIKES],[CLOTHING]}

 

 

[서브 쿼리 활용]

SELECT 쿼리의 FROM절에 큐브 뿐만 아니라 서브쿼리에 의해 생성되는 서브큐브를 사용할 수 있습니다. 서브쿼리를 먼저 실행하여 Bikes와 Clothing만을 포함하는 가상의 서브큐브를 만들어 처리 하므로 [Bikes]+[clothing]에 해당하는 결과를 얻을 수 있습니다.

SELECT {[SALES AMOUNT], [TAX AMOUNT]} ON COLUMNS, {[SALES CHANNEL].[SALES CHANNEL].MEMBERS} ON ROWS FROM (SELECT {[BIKES],[CLOTHING]}ON COLUMNS FROM [ADVENTURE WORKS])

 

 

 

[CrossJoin 활용]

하나의 축에 2개 이상의 차원을 중첩시키고자 하는 경우 유용합니다. 판매 채널별로 제품 카테고리별 매출액과 세금액을 반환 합니다.

SELECT {[SALES AMOUNT], [TAX AMOUNT]} ON COLUMNS,     CROSSJOIN ([SALES CHANNEL].[SALES CHANNEL].MEMBERS,         [PRODUCT].[CATEGORY].[CATEGORY].MEMBERS) ON ROWS FROM [ADVENTURE WORKS]

 

 

 

[Generate 활용]

멤버 집합에 대하여 반복 처리를 하는 경우에 유용합니다. 판매 채널의 각 채널별로 손익이 상위 3위 안에 드는 제품 서브 카테고리들에 대한 매출 금액과 세금 금액을 반환 합니다.

SELECT {[SALES AMOUNT], [TAX AMOUNT]} ON COLUMNS,     GENERATE ([SALES CHANNEL].[SALES CHANNEL].[SALES CHANNEL].MEMBERS,     CROSSJOIN ({[SALES CHANNEL].[SALES CHANNEL].CURRENTMEMBER},         TOPCOUNT([PRODUCT].[SUBCATEGORY].[SUBCATEGORY].MEMBERS, 3, [GROSS PROFIT]                 )              )              )ON ROWS FROM [ADVENTURE WORKS]

 

 

 

계산 멤버는 주로 측정값 차원에 정의하여 사용 합니다. 하지만 일반 차원에서도 자유롭게 정의하여 사용할 수 있습니다. 또한 차원 계층구조 내에서 어떤 멤버 아래에 위치할 지도 지정 할 수 있습니다.

측정값이 아닌 일반 차원에 계산 멤버를 지정하는 경우 합계, 평균, 최대, 최소 등과 같이 어떤 대상 집합에 대한 계산을 하는 경우가 많습니다.

합계나 평균을 구하는 경우 계산의 대상을 먼저 결정해야 합니다. MDX계산에서느 이러한 계산 대상을 서브 쿼리를 통해 집합을 생성한 후 해당 집합에 대하여 계산을 수행 합니다.

 

[평균 구하기]

 

AVG([PRODUCT].[CATOGORY].[CATEGORY].MEMBERS,[SALES AMOUNT])

 

 

[시계열 계산]

 

SUM(YTD([DATE].[CALENDAR].CURRENTMEMBER),[SALES AMOUNT])

 

 

2012-08-02 / 강성욱 / http://SQLMVP.KR