Lance England

Data | Analysis | Integration | Automation

SSAS Default NULL Measure

Today’s post is another of the “place to put a code snippet so I can easily find it in the future” variety.

When browsing a cube, end users will often choose their dimensions first before selecting a measure. In cases with a very large dimension or a very large default measure, this can often cause a long delay. One way of addressing this is by defining a default NULL measure. This eliminates the delay in those cases. The only trade-off is that initially (before a measure is explicitly picked) the dimension members won’t actually display since the result for every tuple will be NULL. The code snippet below goes in the SSAS script section.

// default null member
CREATE MEMBER CURRENTCUBE.[Measures].[DefaultNullMeasure] AS NULL,
DEFAULT_MEMBER = [Measures].[DefaultNullMeasure];

Credit goes to Chris Webb via Jorg Klein for posting about this five years ago. My post is merely a quicker way for me to reference the code.