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:
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:
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.
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.
I have the same problem here..any idea??? the
performace is really terrible with this dynamic sets.
The only solution is not to use dynamic named sets, if possible…
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.
Is There a way to make Rank with good performance without dynamic named set? How are you doing Rank Chris?
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.