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

Microsoft Data Platform MVP from the Netherlands

SSAS - Speed up dimensions using a NULL default cube measure

You can read this blog post at this link: https://jorgklein.com/2009/02/14/ssas-speed-up-dimensions-using-a-null-default-cube-measure

This blog has moved to www.jorgklein.com There will be no further posts on sqlblog.com. Please update your feeds accordingly.

You can follow me on twitter: http://twitter.com/jorg__klein

Published Friday, February 6, 2009 11:52 PM by jorg

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
 

Tonia said:

What if I set default in the cube properties by selecting any measure. will that work. Using 0 as the value allows the dimension display values but it does not apply the filter to the selected dimemsion member

March 13, 2015 12:50 AM
 

Tonia said:

Can I do something like this?

ALTER CUBE CURRENTCUBE UPDATE DIMENSION.Dimension_Name Measures,

DEFAULT_MEMBER = [Measures].UseAsDefaultMeasure;

Thanks.

March 13, 2015 1:20 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement