Dynamic Named Sets and the Formula Cache

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.

6 thoughts on “Dynamic Named Sets and the Formula Cache

  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.

    • 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.

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