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