Performance problems with Dynamic Named Sets

Reposted from Jason Thomas blog with the author's permission.

The last couple of days, I have been fiddling around with an interesting mdx query passed on to me by Hrvoje Piasevoli (blog | twitter | MSDN). Though I wouldn’t be explaining about the query as Hrvoje himself would blog about it soon (once he gets off the SSAS forums and twitter, which could be never ), I was trying to recreate the scenario in the cube and was involving a lot of dynamic sets. That is when I hit upon the problem – queries which were running in split seconds started to drag. And I am not even speaking of the measures which involved those dynamic named sets. If you are a beginner to named sets, I would suggest having a quick glance through the post – Static Named Sets v/s Dynamic Named Sets before reading further.

The steps to demonstrate the issues are given below.

Problem

1) Before illustrating the problem, let us take the average times of a simple query in cold cache. For the demo, the query shown below is used

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2001]}

The average time in cold cache was around 63ms in my laptop.

2) Now a simple dynamic set is created in the cube calculated member script as shown below

CREATE DYNAMIC SET CURRENTCUBE.[ExSet]
AS (EXISTING [Date].[Calendar].[Calendar Year]);

The average time of the previous query is again checked in cold cache and is found out to be around 63ms still. All well and good so far.

3) Now create a measure which will refer the dynamic set, like shown below

CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(ExSet),
VISIBLE = 1  ;

The average time of the previous query is again checked and suddenly, it has come up to 1.1s which is an increase of 75%. – Issue 1

4) Let us also note down the time taken to display the SetString measure which was created.

Even this takes around 1.1s on cold cache which is a lot for such a simple operation. - Issue 2

Reason

On closely checking the traces from Profiler, it is found that when the SetString measure referring the dynamic set is not created, only the above query (in step 1) gets executed (after the calculated member script is loaded into memory). However, after the SetString measure is created, in addition to the above events, a series of Query dimension events and Calculate Non Empty Current events are generated which indicates that the dynamic set is being evaluated, even though it is not being used in the query.

Workaround

A workaround for this issue is to trick the engine into thinking that the dynamic set is not being referred. This can be done by replacing the set with StrToSet. Eg – the dynamic set ExSet will be replaced by StrToSet(‘ExSet’). Hence, the calculated member definition in the script will be modified as shown below

CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(StrToSet(‘ExSet’)), VISIBLE = 1 ;

This will solve the issue and bring the performance back to normal.

Note

This issue had been raised in Connect almost 20 months ago and Microsoft had responded saying that they will investigate this in the future release. As of now (SQL Server 2008 R2 – 10.50.1600.1), there has been no improvements and part of it could be because of the very low number of votes. Considering that the performance could get a lot more worse in the case of complex dynamic sets, it is a serious issue for me and needs to be fixed. If you also feel the same, please vote for this Connect issue – Calculated member with a reference to dynamic named set Kills the cube's performance.

 

---

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com

---