SSAS - Speed up dimensions using a NULL default cube measure

re: SSAS - Speed up dimensions using a NULL default cube measure

Reed Jacobson — Thu, 25 Feb 2010 18:23:20 GMT

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

re: SSAS - Speed up dimensions using a NULL default cube measure

jorg — Fri, 26 Feb 2010 09:45:06 GMT

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

re: SSAS - Speed up dimensions using a NULL default cube measure

Richard Madders — Sun, 13 Jun 2010 09:29:48 GMT

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,

re: SSAS - Speed up dimensions using a NULL default cube measure

jorg — Wed, 16 Jun 2010 08:19:05 GMT

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