SSAS Backup자동화 하기

   

-      Version : SQL Server 2005, 2008, 2008R2, 2012

   

SQL Server Analysis Services는 백업을 자동화할 수 있는 방법은 무엇이 있을까?

아쉽게도 SQL Server의 유지관리계획에는 Analysis Services에 대한 백업기능은 제공하지 않는다.

또한 분석 서비스의 백업 기능에서도 자동 백업 기능은 없다. 스크립트를이용하여 자동으로 백업 받는 트릭을 사용해 보자.

   

   

SSMS를 실행 하여 분석서버에 접속 한다. 백업하려는 데이터베이스를 선택하고 마우스 오른쪽을 클릭한다. [백업]을 선택 한다.

      

   

   

백업 속성이 나타나면 필요한 설정을 한다. 기존 파일을 덮어 쓸 것인지, 백업 파일을 암호화 할 것인지 등을 설정.

   

   

백업 창의 상단에 [스크립트]를클릭하여 [새 쿼리창 동작 스크립팅]을 선택 한다.

   

   

다음과 같이 SSMS의 쿼리창에SSAS의 백업 스크립트 구문이 출력되는 것을 확인 할 수 있다.

   

   

SSMS에서 [데이터베이스엔진]으로 접속하여 SQL Server Agent에서 작업을추가한다.

   

   

작업 단계에서 아래 그림처럼 유형을 [SQL Server AnalysisServices 명령]을 선택 한다.

명령에는 위에서 생성한 스크립트를 복사해서 붙여 넣는다.

   

   

나머지 일정과 오류 처리 등인 기존의 데이터베이스 백업과 동일 하다.

   

   

현업에서는 큐브의 크기가 매우 커서 실제 백업 및 유지 관리를 어떻게 하는지 모르지만 작은 규모의 경우에는 위의방법을 사용하면 주기적으로 백업을 할 수 있어 관리에 편리할 듯 하다.

   

2013-02-01 / 강성욱 / http://sqlmvp.kr

'SQL Server > SSAS 강좌' 카테고리의 다른 글

SSAS Backup자동화 하기  (0) 2015.07.22
27 - SSAS - MDX 스크립트  (0) 2015.07.22
26 - SSAS - MDX 쿼리  (0) 2015.07.15
25 - SSAS - MDX 활용  (0) 2015.07.15
24 - SSAS - 집계 최적화  (0) 2015.07.15
23 - SSAS - 사용자 계층 설계  (0) 2015.07.15

MDX 스크립트

   

MDX 스크립트는 큐브를 통하여 다양한 관점의 다양한 수준에서 데이터 분석하기 위하여 분석 요구 조건에 따라 복잡한 규칙을계산 멤버나 별도의 추가적인 계산을 위한 MDX 표현식이나 구문들의 모음 입니다.

큐브의집계 작업 지정, 계산 멤버, 명명된 집합의 정의 등이 포함됩니다. 또한 큐브의 특정 부분에 대한 값 할당 처리 스크립트를 통해 특정한 셀의 값을 덮어 쓰거나색상과 같은 셀의 속성을 변경 할 수도 있습니다.

   

[CALCUATE]

CALCUATE는 큐브를 처리하는 시점에서 하위 수준에서 상위 수준으로 집계 작업을 수행할지 여부를 결정 합니다. 큐브 처리 작업시 집계 설계에 따라 집계한 데이터를 큐브에 저장합니다. 그러나 CALCUATE를 생략하면 큐브처리를 하더라도 데이터를 상위 수준으로 집계하지 않고 데이터를 큐브에 초기 적재한상태로 남아 있습니다.

MDX 스크립트는 선언문적인 성격을 가져 MDX 스크립트 내에 정의된 모든 계산관련 로직이 큐브처리 시점에서 모두 계산되는 것이 아니라 조회를 하는 시점에 계산 됩니다. 즉, 계산 멤버나 값 할당 등의 작업이 모두 실시간으로 처리 됩니다.

   

[계산 순서 제어하기]

큐브에계산 멤버를 추가하면 계산 멤버의 순서대로 MDX 스크립트에 정의가 추가 됩니다. 이때 계산 멤버를 서로 다른 2개 이상의 차원에 정의하면 계산 멤버들이서로 교차하는 큐브 내의 셀에 대해서 어느 계산 멤버의 정의를 적용해야 하는가 하는 문제가 발생 합니다.

셀의계산 순서는 MDX 스크립트에 기술된 계산 멤버의 순서와 동일 합니다.계산순서를 바꾸려면 계산 멤버의 정의 위치를 바꾸면 됩니다.

CREATE MEMBER [ADVENTURE WORKS].[PRODUCT].[CATEGORY].[ALL PRODUCTS].[BIKES + CLOTHING]

AS [PRODUCT].[BIKES] + [PRODUCT].[CLOTHING];

   

CREATE MEMBER [ADVENTURE WORKS].[MEASURES].[AVG RESELLER SALES]

AS [MEASURES].[RESELLER SALES AMOUNT]/[MEASURES].[RESELLER ORDER QUANTITY],

FORMAT_STRING="$#,#";

   

   

SELECT

    {[RESELLER SALES AMOUNT], [RESELLER ORDER QUANTITY],

    [AVG RESELLER SALES]}ON COLUMNS,

    {[PRODUCT].[CATEGORY].MEMBERS} ON ROWS

FROM

[ADVENTURE WORKS]

  

 

 

   

위와같이 두 개의 계산 멤버가 정의되어 있으면 먼저 Product 차원의[Bikes + Clothing]를 계산한 후 측정값 차원의 [Avg Reseller Sales]를계산합니다. 따라서 두 계산 멤버의 정의가 중첩되는 위치의 셀들은 최종적으로 [Bikes]와 [Clothing]의 더한 값을 이용하여 평균을 구한 [Avg Reseller Sales]의 값을 가지게 됩니다.

만약 [Avg Seller Sales]를 먼저 계산한 후 [Bikes +Clothing]를 계산하도록 원하면 간단히 두 계산 멤버의 정의 순서를 변경해 주면 됩니다.

   

[값할당]

큐브의특정 부분의 값을 집계된 값이 아닌 다른 비즈니스 로직에 따라 덮어써야 하는 경우가 있을때(CALCULATE 명령을통해 큐브의 집계가 일어난 후에 다른 적절한 값으로 덮어써야 할 경우) 사용합니다.

예를들어 Bikes 제품 카테고리에 속하는 모든 멤버들의 모든 측정값을NULL로 덮어쓸 수 있습니다.

SCOPE([MEASURES].MEMBERS);

DESCENDANTS([PRODUCT].[PRODUCT CATEGORIES].[CATEGORY].[BIKES]) = NULL;

END SCOPE;

   

MDX 스크립트에 위의 구문을 추가하면 Bikes 관련 제품들의 값이 보이지 않습니다. 주의할 점은 SCOPE이 적용된 상위 레벨의 집계 값에서도 해당값들이 제외된다는 것입니다. 즉 [Bikes]의 부모 멤버인 [All Products]는 [Bikes]의 값이 제외된 집계값으로보입니다.

만약 [All Products]가 [Bikes]의 값도 포함된 원래의 집계값을보고자 한다면 다음과 같이 FREEZE를 활용할 수 있습니다.

SCOPE([MEASURES].MEMBERS);

FREEZE;

   

DESCENDANTS([PRODUCT].[PRODUCT CATEGORIES].[CATEGORY].[BIKES]) = NULL;

END SCOPE;

   

FREEZE에 의해서 [BIKES]의 상위 수준에 속하는 멤버의 값은 원래의 집계 값을반환 합니다.

   

2012-08-06/ 강성욱 / http://sqlmvp.kr

'SQL Server > SSAS 강좌' 카테고리의 다른 글

SSAS Backup자동화 하기  (0) 2015.07.22
27 - SSAS - MDX 스크립트  (0) 2015.07.22
26 - SSAS - MDX 쿼리  (0) 2015.07.15
25 - SSAS - MDX 활용  (0) 2015.07.15
24 - SSAS - 집계 최적화  (0) 2015.07.15
23 - SSAS - 사용자 계층 설계  (0) 2015.07.15

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

'SQL Server > SSAS 강좌' 카테고리의 다른 글

SSAS Backup자동화 하기  (0) 2015.07.22
27 - SSAS - MDX 스크립트  (0) 2015.07.22
26 - SSAS - MDX 쿼리  (0) 2015.07.15
25 - SSAS - MDX 활용  (0) 2015.07.15
24 - SSAS - 집계 최적화  (0) 2015.07.15
23 - SSAS - 사용자 계층 설계  (0) 2015.07.15

MDX 활용

 

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://sqlmvp.kr)

이번 시간에는 MDX 활용에 대해서 알아 보도록 하겠습니다.

 

MDX – Multidimensional Expressions는 관계형 데이터베이스의 SQL에 해당.

 

다차원 큐브 공간은 해당 큐브를 구성하는 차원들에 의하여 구조가 결정됩니다. 큐브를 구성하는 차원들의 교차점에는 셀이라고 하는 단위 공간이 위치하며 분석의 대상이 되는 측정값이 들어 있습니다.

큐브 공간 임의의 위치에 있는 측정값들을 참조하려면 우선 해당 셀들의 주소를 알아야 합니다. 튜블(Tuple)은 다차원 큐브 공간의 주소를 나타내는 좌표를 의합니다.

 

기하학의 경우 2차원 공간에서는 (X, Y)와 같은 형태로 3차원의 공간은 (X, Y, Z)와 같은 형태로 좌표를 나타냅니다. N차원의 큐브로 확장이 가능하며 기하학에서의 좌표와 다른점은 기하학에서는 X, Y의 순서에 따라 서로 다른 위치를 참조 하지만 튜블의 경우에는 순서에 상관없이 모두 동일한 좌표를 가리킵니다. 이는 어떠한 멤버를 지정할 때 그 멤버가 속한 차원까지 함께 결정이 되기 때문입니다.

 

[특정 멤버의 전체 대비 비율 계산]

가장 많이 사용하는 계산중 하나가 전체 대비 비율계산입니다. 제품 전체 판매액 대비 특정 제품이 차지하는 판매 기여도를 보고 싶을 때 튜플을 이용하여면 이러한 요구를 쉽게 해결할 수 있습니다.

 

예를들어 양주(Alcohol)가 제품 전체에서 차지하는 판매 기여도를 구해 보겠습니다. 요구사항은 (양주 판매액) / (제품 전체 판매액)이 됩니다. 이제 큐브내에서 분자, 분모에 해당하는 좌표를 찾아서 참조하면 됩니다. 구체적으로 표현하면 (판매액, 양주) / (판매액, 제품 전체)가 됩니다. 튜플들을 각 차원 멤버들을 이용하여 기술하면 다음과 같이 정의 됩니다.

([Sales Amount], [Alcohol]) / ([Sales Amount], [AllProducts])

 

큐브 또는 차원에서 멤버 자신만으로 유일성을 보장하지 못하면 최소한 유일성을 보장하는 조상 멤버들을 포함하여 기술해야 하며 필요한 경우 차원(또는 계층 구조)까지 포함하여 기술 하기도 합니다.

([Measures].[Sales Amount], [Product].[[ProductCategories].[Alcohol]) / ([Sales Amount], [AllProducts])

 

 

[임의 멤버의 전체 대비 비율 계산]

전체 대비 비율 계산은 항상 제품 전체에 대한 품목 제품군의 매출기여도를 계산합니다. 왜냐하면 분자와 분모 모두 특정 셀만을 참조하도록 명시되었기 때문입니다. 그렇지만 많은 경우 제품 전체에 대하여 현재 참조하고 있는 제품 멤버들의 매출 기여도를 보고 싶어 합니다. 이런 경우 분자가 자동으로 현재 제품 멤버를 참조하도록 변경되어야 합니다.

(판매액, 현재 제품 멤버) / (판매액, 제품 전체의 판매액)

([Sales Amount], [Product].[Product Categories], CurrentMember) / (Sales Amount], [All Products])

 

튜플에서 기술하지 않는 차원은 해당 차원의 현재 멤버를 자동으로 참조 합니다. 따라서 다음과 같이 단순화 시킬 수 있습니다.

([Sales Amount]) / ([Sales Amount], [All Products])

 

두개 이상의 차원으로 확장하여 쿼리를 작성할 수도 있습니다.

([Sales Amount]) / ([Sales Amount], [All Products], [All Customers])

 

 

[부모 대비 비율 계산]

현재 보고 있는 제품 멤버의 부모 멤버에 대한 매출 기여도 입니다. (매출액, 현재 제품 멤버) / (매출액, 현재 제품 멤버의 부모 멤버)가 됩니다.

([Sales Amount], [Product].[Product Categories].CurrentMember) /

([Sales Amount], [Product].[Product Categories].CurrentMember.Parent)

위의 쿼리를 단순화 시킬 수 있습니다.

([Sales Amount]) / ([Sales Amount], [Product].[Product Categories].Parent)

 

현재 멤버가 [All Product]인 경우는 부모 멤버가 존재하지 않기 때문에 분모가 NULL이 됩니다. 이 때 NULL은 0으로 치환 됨으로 [1.#NF]와 같은 오류 값이 반환됩니다. 따라서 필요에 따라 다음처럼 조건부 처리를 해야 합니다.

IIF(IsEmpty(([Sales Amount], [Product].[Product Categories].Parent)), 1, ([Sales Amount]) /

([Sales Amount], [Product].[Product Categories].Parent))

 

+ Recent posts