Report Portal

About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

Search

blank

SSAS 2008 Katmai – MDX Changes

August 26th, 2007 by Vidas Matelis

Mosha Pasumansky just posted blog entry about dynamic named sets in SSAS 2008. In his post Mosha listed 4 SSAS changes that were included in June CTP release:

  • Dynamic named sets
  • CREATE MEMBER statement extension to allow specify display folder and associated measure group
  • New CREATE KPI statement
  • Ability to change calculated member “on the fly”

As Mosha focused on changes with Dynamic named sets, I decided to do a quick test on other 3 changes. To be honest, just from Mosha’s blog entry I realised that these MDX changes are already in CTP. I knew that some of these changes were planned, but from webcasts and chat sessions I was under impression that these changes will be available just in the future releases. Is it possible that these changes were included just in July CTP?

So here are my tests on other changes. 

CREATE MEMBER now allows to specify Display folder (property DISPLAY_FOLDER) and associated measures group (property ASSOCIATED_MEASURE_GROUP). It is very simple but very welcome change. All of my calculated measures were always associated with measure group and most of calculated measures were placed in the folders. In SSAS 2005 I was doing assignments manually and any calculated measure named changes required manual reassignment. In SSAS 2008 folder and measure group assignment is now part of the create code and it is much easier to maintain. Here is a example of how to use new properties:

CREATE MEMBER CurrentCube.[Measures].[Calculated Internet Sales Amount] AS
[Measures].[Internet Sales Amount]
,
NON_EMPTY_BEHAVIOR = {[Internet Sales Amount]}
,
DISPLAY_FOLDER = ‘NET Amounts’
, ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

Note: if you specify non existing measure group name for ASSOCIATED_MEASURE_GROUP property, you will get error during deployment: “The Internet Sales 2 measure group was not found”.

There is new CREATE KPI statement that allows to move KPI definitions into common calculation tab area. I do like having my all calculations in one single place, as to me KPI is just another calculated measure that has more properties (value, goal, status, trend, etc). This statement is well documented in BOL, and here is example:

CREATE KPI [Adventure Works].[Internet Sales KPI]
AS ([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild)
,
GOAL = ([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild.PrevMember) * 1.10 — 3% goal is 3% over previous year
, STATUS = IIF(([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild)
   >= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].
LastChild.PrevMember) * 1.10
     , 1
     ,
IIF( ([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild)
          >= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].
LastChild.PrevMember) * 1.10 * 0.9
           , 0, -1 ) )
,
TREND = IIF(([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild)
     >= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].
LastChild.PrevMember) * 1.10
    , 1
   
, IIF( ([Measures].[Internet Sales Amount], [Date].[Calendar Year].LastChild)
         >= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].
LastChild.PrevMember) * 1.10 * 0.9
        , 0, -1))
,
STATUS_GRAPHIC = ‘Traffic Light’ — Shapes (3), Traffic Light (3), Road Signs (3), Gauge (3), Reversed Gauge (5), Thermometer (3), Cylinder (3), Faces (3), Variance arrow (3)
, TREND_GRAPHIC = ‘Standard Arrow’ — Standard Arrow (3), Status Arrow (3), Reversed Arrow (3), Reversed status arrow (5), Faces (3)
// , WEIGHT = 1
, CURRENT_TIME_MEMBER = [Date].[Date].LastChild
, PARENT_KPI =
, CAPTION = ‘Internet Sales KPI’
, DISPLAY_FOLDER = ‘KPIs’
, ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’
;

There is also new DROP KPI statement that allows to delete KPI.

UPDATE MEMBER is a new statement that you can use to change calculated member value. From BOL: “The UPDATE MEMBER statement updates an existing calculated member while preserving the relative precedence of this member with respect to other  calculations. Therefore, you cannot use the UPDATE MEMBER statement to change SOLVEORDER. An UPDATE MEMBER statement cannot be specified in the MDX script for a cube.”

For some reasons my tests to use UPDATE MEMBER statement kept failing with error “The member ‘MyCalcMeasure’ was not found in the cube when the string, [Measures].[MyCalcMeasure], was parsed.”

Update: Mosha pointed out why my UPDATE MEMBER statement kept failing in comments below: “That’s because you probably tried to change calculated member defined inside MDX Script. You can only change the calculated member defined in the session scope by using the session UPDATE MEMBER command.” So I tried this new statement on session calculated member and now this worked:

CREATE CALCULATED MEMBER [Adventure Works].[Measures].[Calculated Internet Sales Amount] AS [Measures].[Internet Sales Amount];
SELECT [Measures].[Calculated Internet Sales Amount] ON 0
  FROM [Adventure Works];
— Result: $29,358,677.22
UPDATE MEMBER [Adventure Works].[Measures].[Calculated Internet Sales Amount] AS 10;
SELECT [Measures].[Calculated Internet Sales Amount] ON 0
  FROM [Adventure Works];
–Result: 10
DROP CALCULATED MEMBER [Adventure Works].[Measures].[Calculated Internet Sales Amount];

While I was testing these new statements, I realized how much I miss “Deploy MDX” option that BIDS Helper utility gives to SSAS 2005 environment. I added suggestion to include “Deploy MDX” functionality in SSAS 2008, please vote it you believe this is important to you too:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295178

I compiled a list of known to me SSAS 2008 changes here.

Posted in SSAS, SSAS 2008 - Katmai | 5 Comments »

5 Responses

  1. Mosha Says:

    > For some reasons my tests to use UPDATE MEMBER statement kept failing with error “The member ‘MyCalcMeasure’ was not found in the cube when the string, [Measures].[MyCalcMeasure], was parsed.”

    That’s because you probably tried to change calculated member defined inside MDX Script. You can only change the calculated member defined in the session scope by using the session UPDATE MEMBER command.

  2. Vidas Matelis Says:

    Mosha, thanks for explaining this. I tried UPDATE MEMBER statement on session calculated measure and it worked.
    Do you know reason why UPDATE MEMBER statement was introduced? I cannot think of example where I would want it to use.

    Thanks again for your help.

  3. Cristian Lefter, SQL Server MVP Says:

    Just a small observation: CREATE MEMBER supports also the CAPTION property.

    CAPTION
    A string that the client application uses as the caption for the member.

  4. Personalized Cubes - Prologika (Teo Lachev's Weblog) Says:

    […] to calculated members and KPIs, which Chris Webb has already written about. Vidas Matelis has provided great examples of using the Caption property. Thanks to this enhancement, you can keep the names of […]

  5. mauro marchi Says:

    Hi my name is Mauro and i use SSAS 2008 STANDARD EDITION.
    I have an OLAP CUBE that contains SELLOUT Data about STORES(SHOPS)..

    I have a dimension called STORES
    This dimension is a regular dimension and it has 2 levels
    First Level MAIN STORE
    Second level SUBMAIN STORE
    I have ALSO a measure called SOLD with the usual aggregate function = SUM

    I have this problem with THIS SCENARIO to solve

    MAIN STORE SUBSTORE SOLD

    A AA 100
    BB 200
    CC 300
    DD 400
    =========
    700 ???????????? and not 1000

    i Mean i do not want to summarize levels AA and BB but i want to view them, BT NOT TO SUM

    HOW CAN I DO THAT ????

    I did tests with UNARY OERATORS and CUSTOM ROLLUP FORMULA but there was no solution.
    I read about SCOPE MDX and perhaps it is really a solution, but my MDX was nt able to solve wih a good expression this problem…

    Can anyone to help me ???

    Regards in advance.

    Ciao Mauro.