Pages

Tuesday, April 19, 2011

Performance problems with Dynamic Named Sets

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 Winking smile), 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.

Dynamic Set Performance

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.

1 Query before set creation

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

2 Query after set creation

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

3 Query for SetString measure

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.

4 Reason for performance degradation

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.

5 Solution

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.

Thursday, April 14, 2011

SQLBits 8 - Hangover

Yups, 10 days have passed since I attended the SQLBits by the Seaside, and I am yet to recover from the hangover. And nopes, I am not talking of the hangover from the beer I drank (though I must admit I did guzzle down a few too many, thanks to all the wonderful sponsors who were way too generous handing out free coupons).

SQLBitsLogo

I had become a big fan of the SQLBits conferences, ever since I attended the last one in York. But this time, I was even more excited because it was going to be held in Brighton. Sunny forecasts and the prospect of lunch at the beach just made me count down the days till April 3. In a sadistic sort of way, I was even more pleased with myself on registering pretty early because many people could not get it done on account of the registration being fully booked. In fact, I had to use some native Indian yoga tricks to restrain myself from flaunting it off in front of my poor colleagues who couldn’t get a reservation. Due to work reasons, I had only gone for the free community day sessions on Saturday.

I reached Brighton by 8.55am and straightaway jumped into Marco Russo’s (blog | twitter) session on Analysis Services Advanced Best Practices.

IMG_0043IMG_0044

Even though it was a level 400 session, Marco explained the concepts in a very simple way so that everyone could understand and the session was pretty captivating. In the end, I took away some very nice design related tips for SSAS dimensions for scenarios like SCD type 2, parent-child, etc.

Next I went for John Stevens-Taylor’s (blog | twitter) session on Data Modelling for a Flexible Cube. The room was so jam packed with people that I had to stand at the back and even then, people were pouring in. Since I had sprained my ankle the other day, I couldn’t stand for long and I came out rueing on not coming early to the room. This feeling was compounded when I came to hear the amazing feedback on the session.

After that, I was double minded on whether to go for the SQL Product Team Panel session by the SQL CAT Team or for Vincent Rainardi’s (blog) session on Advanced Dimensional Modelling. I decided to go for Vincent’s session and had a very enriching time there learning about  Fact Table Primary Key, Vertical Fact Tables, Aggregate Fact Tables, SCD Type 6, Snapshotting Transaction Fact Tables, Dealing with Currency Rates, When to Snowflake, etc.

IMG_0049IMG_0048

Soon after the session, I was able to materialize my dream of having lunch at the Brighton beach side, soaking up the warm sun and watching the surfs getting washed up the shore (ok, I admit, and some hot chicks in bikinis Flirt male)

The next one I attended was Jamie Thomson’s (blog | twitter) session on SSIS Dataflow Performance Tuning. Though I am more of a SSAS/SSRS person than SSIS, I just couldn’t stop myself from attending Jamie’s session, I had attended one of his sessions in a Local User Group meeting, and have remained a big fan of his presentations after that.

IMG_0050IMG_0051

His session was heavily based on demos which made it very interesting and I had some great takeaways from his session on performance tuning and execution trees.

Next on my list was Alex Whittles’ (blog | twitter) session on Automating SSAS cube documentation using SSRS, DMV and Spatial Data. Now if you are one of those who follow the SQL world in twitter, you would be already aware of the ‘Spatial Art’ tweets being passed around by Alex, Alastair (blog | twitter) and Simon (blog | twitter). This got me hooked and I was waiting for the session to actually see it live.

IMG_0053IMG_0054

Alex’s slides were so beautifully done and I was so much in awe of his presentation skills. The topic was like a breath of fresh air and to top it all, I won a book on Microsoft SQL Azure during his session.

Soon we had the customary post event party and lots of prizes were announced from 50” plasma TV to Xboxes. Everyone I spoke to had lots of fun and a big thanks to all the sponsors without whom it would not have been possible.

Sponsors

A special mention for Fusion-IO for successfully conducting the Crappy Code Games in Manchester, London and Brighton, was really an awesome event. Totally loved it! (Click here to read my London experience).

Now this post would not be complete without thanking the people behind it - Simon Sabin, Christian Bolton, James Rowland-Jones, Martin Bell, Darren Green, Chris Webb, Allan Mitchell, Tim Kent and Chris Testa-O’Neill. A big, BIG, BIG thanks to all of you guys.

Now for those who missed SQLBits 8 (and those who are still in the hangover like me), what better way to relive the experience than Jamie’s videos. Catch them right here - Reflections on SQLBits 8

Tuesday, April 5, 2011

MDX Basics

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.
 
Download MDX Basics ppt by clicking here
 
Feel free to comment and let me know if there are any additional areas you would like to see being included here.

Sunday, April 3, 2011

Using TYPED for member properties in SSRS MDX queries

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.

Sort by member property report

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
FROM [Adventure Works];

MDX Query original

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

SSRS report table with SubCategory and Mkey

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

Table sort by member property SSRS report

4) Now preview the report.

Incorrect sort order

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
FROM [Adventure Works];

To quote MSDN – “The Properties function returns the value of the specified member for the specified member property. By default, the value is coerced to be a string. If TYPED is specified, the return value is strongly typed. If the property type is intrinsic, the function returns the original type of the member. If the property type is user defined, the type of the return value is the same as the type of the return value of the MemberValue function.”

2) Now preview the report and the sorting would be done in the expected way.

Correct sorting order