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%:

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.

You know, I have the nagging feeling I’ve already blogged about this before… but I can’t find the post (I have been at this for over four years now) so it’s worth mentioning again…

Anyway, a common question asked on the MSDN Forums is how to get the current date and then use it in an MDX calculation – see this thread for example. Usually this is because users want calculations that show the current day’s sales, or something similar. One answer is of course to use the Now() function, but what people don’t often realise is how this can impact the ability of Analysis Services to cache the values returned by calculated members, and therefore reduce overall query performance.

To understand why, let’s look at some examples in Adventure Works. First of all create a calculated measure on the AW cube as follows:

CREATE MEMBER CURRENTCUBE.[MEASURES].[NOWDEMO] AS NOW();

Then run the following query a few times:

SELECT {[Measures].[NOWDEMO]} ON 0
FROM [Adventure Works]

As you’d expect, every time you run this query you see the current date and time – and every time you run it, you see a different value. But, you may be thinking, doesn’t Analysis Services cache the results returned by calculated members? Well, yes it does in most cases, but for non-deterministic functions (functions that could return a different result every time they’re called) like Now() no caching takes place, because otherwise the value returned from the cache might be different from the one the function actually returns.

The next problem is that if you create any other calculated members that depend directly or indirectly on the value returned by a non-deterministic function, their values can’t be cached either. One problem I see sometimes in my consultancy work is poor query performance resulting from SSAS being unable to use the formula cache, because a large number of calculations have a dependency on a single calculation that uses the Now() function. Here’s a greatly simplified example of two calculated members, the first of which finds the current year and the second which returns a sales value for the year eight years before the current year:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Current Year]
AS YEAR(vba!format(now(),"MM/dd/yyyy")),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[SHOW SALES FOR A YEAR]
AS ([Measures].[Internet Sales Amount],
STRTOMEMBER("[Date].[Calendar Year].&[" + CSTR([MEASURES].[Current Year] – 8) + "]", CONSTRAINED));

If you run a query that references the second calculated measure on a cold cache, such as:

SELECT [MEASURES].[SHOW SALES FOR A YEAR] ON 0
FROM [Adventure Works]

The first time you run it you’ll see SSAS going to disk as you’d expect; the second time you run it though you’ll see SSAS is able to use the Storage Engine cache but not the Formula Engine cache, as this Profiler trace shows:

The highlighted Get Data From Cache event shows data being retrieved from the measure group cache. This is ok and can be beneficial for query performance, but if the calculation we’re doing is very expensive then it can still mean our query takes a long time to run. We’ll only get an instant response on a warm cache if we can work out how to use the formula cache somehow.

Luckily, in most cases where Now() is used, we don’t usually want the system date and time, we just want the date. That means that we only want to return a different value when the date changes, once every 24 hours. What we can do therefore is use a named set to somehow store the value returned by Now(), for example like this rewrite of the calculation above:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Current Year]
AS YEAR(vba!format(now(),"MM/dd/yyyy")),
VISIBLE = 1;

CREATE SET CURRENTCUBE.MYYEAR AS
{STRTOMEMBER("[Date].[Calendar Year].&[" + CSTR([MEASURES].[Current Year] – 8) + "]", CONSTRAINED)};

CREATE MEMBER CURRENTCUBE.[MEASURES].[SHOW SALES FOR A YEAR – CACHEABLE]
AS ([Measures].[Internet Sales Amount],
MYYEAR.ITEM(0));

What we’re doing here is finding the year we want, then finding the member for the year 8 years ago and storing that in a named set. Because a static named set is only evaluated once, when we do our final calculation we can reference the single member stored in the named set and therefore make use of the formula cache as the following Profiler trace shows:

We’re now getting data from the flat cache, which is one part of the formula cache (which isn’t ideal either as it indicates the calculation is being evaluated in cell-by-cell mode, I guess because we’re referencing a named set inside it) and so warm-cache performance will be better.

The next problem is that when the date does change, we need to clear the cache. This can be accomplished easily by running an XMLA ClearCache command, perhaps direct from SQL Server agent, every night at midnight or whenever necessary.

To be honest, though, I’m not sure using the Now() function at all is a good thing – apart from the issues described here there are a lot of other risks involved, such as the time or date on your server being wrong or confusions with time zones and date formats. I think a better approach to the problem is to have an extra attribute in your Time dimension which flags up a date as ‘today’, and which changes every day. Of course this means you need to do some extra ETL and processing on your Time dimension as a result, but I think it’s a much cleaner solution than Now() and leads to much more efficient MDX calculations.