# Month: April 2011

## Performance problems with Dynamic Named Sets

April 19, 2011

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

Posted by SQLJason, 1 comment

## SQLBits 8 – Hangover

April 14, 2011

Posted by SQLJason, 1 comment

## MDX Basics

April 5, 2011
One of my colleagues in MindTree Ltd, Bragdishwaran U, had recently taken a session on MDX Basics. I found the session well structured and specially asked his permission to publish the slides from his deck for the benefit of my readers who are completely new to MDX.

Feel free to comment and let me know if there are any additional areas you would like to see being included here.

## Using TYPED for member properties in SSRS MDX queries

April 3, 2011

Recently I got a very interesting query from one of my colleagues. He was trying to sort a table in a SSRS report based on an integer member property, but the sort results were coming wrong according to him. He said that the report was treating the property field as a character and claimed that this was a problem reported by many people all over the MSDN forums. That is when I thought of putting forth this post.

To illustrate the problem as well as the solution, I will be using the Adventure Works cube.

1) The requirement is to sort the Sub Categories based on the key values in the report. For this, a new dataset is made with the following MDX query

WITH
MEMBER [Measures].[Mkey] AS
[Product].[SubCategory].CurrentMember.Properties(“key”)
SELECT
NON EMPTY
{[Measures].[Mkey]} ON COLUMNS
,NON EMPTY
{
[Product].[SubCategory].Children
} ON ROWS

2) Drag and drop the SubCategory and MKey fields into a table.

3) Right click on the Subcategory row group for the properties and then sort it by Mkey field.

4) Now preview the report.

We can see that the key has been considered as a string for sorting, because of which 10 comes after 1. For the proper numerical sorting, we will have to follow the steps below.

Solution

1) Modify the MDX query to include the TYPED keyword.

WITH
MEMBER [Measures].[Mkey] AS
[Product].[SubCategory].CurrentMember.Properties(“key”,TYPED)
SELECT
NON EMPTY
{[Measures].[Mkey]} ON COLUMNS
,NON EMPTY
{
[Product].[SubCategory].Children
} ON ROWS