Posted by 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):
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:
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.
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!
Jorg Klein's Blog 
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Can I do something like this?
ALTER CUBE CURRENTCUBE UPDATE DIMENSION.Dimension_Name Measures,
DEFAULT_MEMBER = [Measures].UseAsDefaultMeasure;
Thanks.
LikeLike
I guess with this trick you can brake a ssrs reporting system. The standard query for a parameter dataset does not use any measures, we have {} on columns so potentially all parameter sets get empty.
I mean we cannot develop cubes in agile way, you fix one thing and can brake another. Any change requires testing of the whole system.
LikeLike
Hi Maxim, it’s a long time ago since I worked on this solution, but I cant remember any SSRS reports parameters broke. On the other hand, I remember I usually manually edited SSRS parameter queries to filter out rows with no data.
LikeLike