Vidas Matelis Analysis Services Blog

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.