Chris Webb's BI Blog

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

Dynamic Named Sets and the Formula Cache

with 6 comments

Here’s another scenario where the Formula Engine is unable to cache the results of calculations longer than the lifetime of a query (thanks to Akshai Mirchandani yet again for pointing this one out): when you use dynamic named sets inside calculations. To see this happening add the following calculations to the MDX Script of the Adventure Works cube:

CREATE SET NONDYNAMICSET AS [Date].[Day Name].MEMBERS;

CREATE DYNAMIC SET DYNAMICSET AS [Date].[Day Name].MEMBERS;

CREATE MEMBER CURRENTCUBE.MEASURES.NONDYNAMICCALC AS COUNT(NONDYNAMICSET);

CREATE MEMBER CURRENTCUBE.MEASURES.DYNAMICCALC AS COUNT(DYNAMICSET);

Now, clear the cache and run the following query twice:

SELECT MEASURES.NONDYNAMICCALC ON 0
FROM [Adventure Works]

Looking in Profiler, you can see that on the second run the query was answered from the formula engine’s flat cache:

image

When you run the following query using the calculation that references the dynamic named set:

SELECT MEASURES.DYNAMICCALC ON 0
FROM [Adventure Works]

…you can see that the cache does not get hit:

image

So be careful if you ever need to use them inside your calculations! Personally I’ve never done much with dynamic named sets at all, but Mosha has of course blogged extensively about several ways they can be useful.

Written by Chris Webb

March 31, 2011 at 9:50 pm

Posted in Analysis Services, MDX

6 Responses

Subscribe to comments with RSS.

  1. I’ve used Dynamic sets to use to calculate the values for a Shell Dimension. The sets create user based on certain conditions (eg Purchased on Internet or Puchased at Shop) then using those sets and except and intersect, we create the shell dimension to show “Internet Only”, “Shop Only” and Internet and Shop” users.

    While this works very well, it makes browsing the whole cube slow. When you try to do anything with the Date Dimension (even just browsing it) the engine creates sub-cubes to retrieve the dimension values. This makes opening and browsing the cube very slow, but the queries run at acceptable speeds.

    I haven’t found a way around this problem at all and so we just have to live with it.

    Michael Schreuder

    April 7, 2011 at 3:04 pm

    • I have the same problem here..any idea??? the
      performace is really terrible with this dynamic sets.

      Leonardo Barbosa

      November 8, 2013 at 7:25 pm

      • The only solution is not to use dynamic named sets, if possible…

        Chris Webb

        November 8, 2013 at 7:33 pm

      • But In my case te rank has to be dynamic, because is the rank:
        (Customers , YTD Y).. Rank of customers that sale in the prior year. based on the date that is in the report.
        Its not fixed.

        Leonardo Barbosa

        November 8, 2013 at 8:34 pm

  2. Is There a way to make Rank with good performance without dynamic named set? How are you doing Rank Chris?

    Leonardo Barbosa

    November 8, 2013 at 7:53 pm

    • Rank calculations always perform relatively badly in MDX, unfortunately. I find that I get the best performance by using the third parameter in the Rank() function to sort the set and handle tied ranks.

      Chris Webb

      November 9, 2013 at 10:12 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 3,189 other followers

%d bloggers like this: