THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft-only BI consultant from the Netherlands

SSAS - Speed up dimensions using a NULL default cube measure

Recently I faced some problems with the performance of SSAS dimensions. The cube users were using a large dimension with more than 100.000 members that didn't perform well.
They tried to add a leaf dimension member on an Excel 2007 pivot table. When dragging this dimension member onto the rows they had to wait very long before the members returned from SSAS and showed on the screen.

After some mailing with Chris Webb he thought this could have something to do with the default cube measure. It seems that when you query dimension members without picking a measure, SSAS takes the first measure from the first measure group as its default measure. So even when you only query a dimension, SSAS is still using a measure!

You can find out which measure SSAS will take with the following query:

SELECT [Measures].DefaultMember ON 0 FROM [YourCube]

In this case the default measure that SSAS picked was from a measure group that was not connected to the dimension that was giving the performance problems. This, plus the fact that returning 100.000 times NULL is faster then returning some big float number, explained the performance issue.

Chris advised me to use a NULL calculation as default measure, as he explains on his blog. The only problem here is that you can't select a calculation as default measure in the cube properties in BIDS (only normal measures are allowed):

Default measure cube properties

 

 

 

 


Fortunately pasting this MDX statement in the calculations script (use the script view on the calculations tab) did the trick. Just paste it right under the CALCULATE command (or somewhere else):

CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure
 AS NULL,
VISIBLE = 1;

ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures,
DEFAULT_MEMBER = [Measures].UseAsDefaultMeasure;

When you return to the form view it should look like this:

Calculations Script with default measure

 

 

 

 

The next time you deploy the cube, SSAS will use the [UseAsDefaultMeasure] calculation as its default measure.


Test results:

I now had a nice opportunity to test the cube performance with and without the NULL calculation as default cube measure. I cleared the cache before each query to get a good comparison.

In SSMS I used the following query:

SELECT [Measures].DefaultMember ON 0,
[Dimension].[AttributeHierarchy].Members ON 1
FROM [Cube]

The results are shown in the bar-chart below:

  • The query with the NULL default measure took 13 seconds.
  • The query without the NULL default measure took 4 minutes and 35 seconds.
Chart

 

 

 

 

 

 

 

 

 

 

 

Conclusion:

Using a NULL default measure can be much faster when querying dimension members without selecting a measure. The result was shown more than 20 times faster in this particular case.

Thanks to Chris Webb for helping me out on this issue!

Published Friday, February 06, 2009 11:52 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Reed Jacobson said:

Hi Jorg,

This was cool. I've been doing a bunch of ROLAP (against Teradata) lately, and the "default" query is even slower because they're SQL.

I did find that using 0 for Null as the value of the default measure made Excel work better: With Null as the value, Excel would hide all the axis members (unless you explicitly convert the PT to show empty cells). But with 0 as the value, Excel shows the members (even though it doesn't show the measure itself if it's not explicitly selected).

Cheers,

Reed

February 25, 2010 1:23 PM
 

jorg said:

Hi Reed,

You are absolutely right, using NULL as the value makes all the dimension members hidden when no measures are selected (they are not queried!). When you use 0, SSAS will return all dimension members which is a operation that requires more resources.

In some cases its OK to use 0 but sometimes with really large dimensions this takes to much resources. So it really depends on the situation.

Thanks for the useful reply!

-Jorg

February 26, 2010 4:45 AM
 

Richard Madders said:

Hi Jorg, interesting idea.

I don't quite understand why you would ever run a query without a measure?

Presumably, as long as you have one measure, the defaultmember measure is never used?

Thanks,

R

June 13, 2010 5:29 AM
 

jorg said:

Hi Richard,

The default cube measure is used every time you drag and drop a dimension member in your browser/excel without using a measure. For example when somebody wants to see sales by customer, in this case it is likely the person drags and drops the customers on the rows before selecting the sales amount measure. When you have large customer dimension this can cause a serious delay.

So what you say is right, as long as you have one measure, the default cube measure is never used!

-Jorg

June 16, 2010 4:19 AM
 

SQLServerGeeks.com – SQL Server – Assign a Default Measure in SSAS Tabular Model Solution said:

July 6, 2014 3:38 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement