My Calculated Member is slow

Have you written a calculated member that is performing very slowly, while the underlying physical measures are fast? This could be a common cube query performance issue that has a very quick remedy.

Essentially, AS want to know when to treat this as an empty cell. If you don't specify the non_empty_behaviour, AS will need to physically calculate all potential cells to determine which ones are actually emtpy. With non_empty_behaviour specified, AS can avoid many calculations, since many blocks of cells are known to be empty. The non_empty_behaviour attribute is appropriate for both SQL Server 2005 and SQL Server 2008.

To remedy, all you need to do is specify the non_emtpy_behaviour for the calculated member.

Note, there are logic implications with non_empty_behaviour. With the example in the picture (hits per web session) it is logical that I would want to return null when the sessions measure is null. This would not be the case if I were calculating a YTD measure, since the measure might be null on the current day, but the calculation needs to add many other days that might not be null.

For live OLAP and data mining demonstrations with real data see http://RichardLees.com.au/Sites/Demonstrations