Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using Dimensions as Parameters to Calculations and Sets

with 3 comments

One of the advantages of using SSRS as a client for SSAS is the control you have over your MDX, and one of the advantages of control over your MDX is the ability to parameterise not only your queries but also any calculations defined in those queries. It would be great if we could parameterise calculations defined in the MDX Script (I have a Connect open on this, in fact – please vote!) but until we can, we can do something almost as good: we can use a dimension hierarchy to store a pre-defined range of parameter values and then use the CurrentMember on this hierarchy to pass one of these values to a calculation or dynamic named set. Here’s how.

First of all, we need to create a dimension to hold these parameter values, something like a Time Utility or Shell dimension. We can create the source data for this easily in the DSV using a named query and a SQL SELECT statement as follows:

SELECT        1 AS ParamID, ’5%’ AS ParamDesc, 1.05 AS ParamValue
UNION ALL
SELECT        2 AS ParamID, ’10%’ AS ParamDesc, 1.1 AS ParamValue
UNION ALL
SELECT        3 AS ParamID, ’15%’ AS ParamDesc, 1.15 AS ParamValue
UNION ALL
SELECT        4 AS ParamID, ’20%’ AS ParamDesc, 1.20 AS ParamValue

We can then build an SSAS dimension – I called it [Parameter Values] – with a single attribute, its KeyColumn property using the ParamID column above, its NameColumn property using the ParamDesc column, and its ValueColumn property using the ParamValue column. This means we have a simple hierarchy with four members on it. We then add the new dimension to the Adventure Works cube; it doesn’t need to have any relationship with any measure group.

Now let’s use it. Imagine we want to see what the value of Internet Sales Amount would be if it grew by 5%, 10%, 15% or 20%, how would we do it? Well, what we could do is say that when the All Member on the Parameter Values is selected we see the real value of Internet Sales Amount, but when one of the other members is selected we increase the value of Internet Sales Amount by the percentage associated with the selected member. The MDX required would look like this:

SCOPE([Measures].[Internet Sales Amount]);
    SCOPE([Parameter Values].[Parameter Values].[Parameter Values].MEMBERS);
        THIS =
            ([Measures].[Internet Sales Amount], [Parameter Values].[Parameter Values].[All])
            *
            [Parameter Values].[Parameter Values].CURRENTMEMBER.MEMBERVALUE;
        FORMAT_STRING(THIS) = ‘CURRENCY’;
    END SCOPE;
END SCOPE;

What I’m doing here is scoping on Internet Sales Amount and all of the members except the All Member on my new dimension, so that I’m only doing my calculation when a selection is made on the Parameter Values hierarchy. In this scope I’m then multiplying the value of Internet Sales Amount at the All Member with the value returned by the MemberValue function for the CurrentMember on [Parameter Values].[Parameter Values] – which is the value from the column I used in the ValueColumn property of the attribute. Even though I’m scoping on a real measure, the calculation doesn’t aggregate up to the All Member because this dimension has no relationship with the measure group that Internet Sales Amount is from (or indeed any other measure group).

Here’s a query that shows the results:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Parameter Values].[Parameter Values].MEMBERS ON 1
FROM [Adventure Works]

And here’s the output, showing Internet Sales Amount and underneath it the value increased by 5%, 10%, 15% and 20%:

image

So we’ve got a (sort of) parameterised calculation. We can also use a similar approach with dynamic named sets too – here’s an example dynamic named set definition that uses the same dimension to control the percentage passed into TOPPERCENT function:

CREATE DYNAMIC SET MYSET AS
    IIF([Parameter Values].[Parameter Values].CURRENTMEMBER IS
        [Parameter Values].[Parameter Values].[All],
            [Customer].[Customer].[Customer].MEMBERS,
            TOPPERCENT(
                [Customer].[Customer].[Customer].MEMBERS
                ,([Parameter Values].[Parameter Values].CURRENTMEMBER.MEMBERVALUE – 1) * 100
            , [Measures].[Internet Sales Amount])
        );

The set returns all Customers if no selection is made on Parameter Values. However if a selection is made on Parameter Values then the selection drives the number of Customers that the set returns. So the query:

SELECT [Measures].[Internet Sales Amount] ON 0,
MYSET ON 1
FROM [Adventure Works]

…returns all 18485 Customers, whereas:

SELECT [Measures].[Internet Sales Amount] ON 0,
MYSET ON 1
FROM [Adventure Works]
WHERE([Parameter Values].[Parameter Values].&[1])

…returns the top 5% of Customers (I’m using the MemberValue function minus 1, multiplied by 100, here so I can use the value 5 rather than the original MemberValue of 1.05) by Internet Sales Amount, which results in the top 164 Customers being returned. Slicing by the other members on Parameter Values will give me the top 10%, 15% and 20% of Customers by Internet Sales Amount.

Written by Chris Webb

October 9, 2009 at 10:14 pm

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. Excellent idea!Seems like there\’s no end in usefullness of Utility dimensions :-)

    Tomislav

    October 10, 2009 at 12:36 pm

  2. perfect for my case!!! Thanks chris!

    mark kim

    July 24, 2013 at 1:47 am

  3. nice idea!

    George Qiao

    July 26, 2013 at 2:06 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: