In Adventure Works cube 2008 or 2012, in the Dimension Usage tab, you will see that many intercepts of measure groups and dimensions are blank. To a specific measure group, the dimensions that have blank intercepts are un-related to the measure group. As a matter of fact you will see blank intercepts more often than not.

As the term un-related dimension suggests, these measure groups have no relationship to these dimensions. SSAS, however, gives us a measure group property IgnoreUnrelatedDimensions (true or false) to control how we want these measures to show for those un-related dimension members.

IgnoreUnrelatedDimensions is a boolean property, so how hard it can be. It turned out that this property is not as straightforward as it seems, and it might not be as effective as you would think either.

When IgnoreUnrelatedDimensions = True, the measure repeats for all members and all levels on the un-related dimensions

IgnoreUnrelatedDimensions = True is a default setting for measure groups. The measure behavior is not the best (actually very confusing to users), but the behavior is consistent and easy to remember.

When IgnoreUnrelatedDimensions = True,

• the measure will repeat with the same value for all member for all level for any unrelated dimension,
• regardless if the dimension has default member or not, root [All] member or not
• this behavior is the same as VALIDMEASURE() function which also repeats the measure with the same value for all member for all level for any unrelated dimension
  

Example 1:

• Exchange Rates measure group is not related to the Promotion dimension
• Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
• Promotion has no default member

Example 2:
• Exchange Rates measure group is not related to the Scenario dimension
• Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
• Scenario has a default member of Actual

Example 3:
• Exchange Rates measure group is not related to the Sales Channel dimension
• Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
• Sales Channel IsAggregatable is set to False; no root [All] member

When IgnoreUnrelatedDimensions = False, the measures behave differently depending on if  the un-related dimension ….

…has default member or not, has root level [All] member or not.

Example 4:

• Sales Target measure group is related to the Date dimension but only at Calendar Quarter level
• (I’ve disable the calculation in the MDX Script "Sales Quota Allocation" which allocates values down to the Month level)
• The measure shows at the Quarter level and rolls up to all the ancestor levels.
• VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all months and days 

Example 5:

• Sales Target measure group is not related to the Promotion dimension
• Promotion dimension has a [All Promotions] root level member
• The measure shows for the [All Promotions] member only.
• VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all members

Both Example 4 and 5 produce an easy to remember result, that is, the setting forces the measure to the top root member and the member that the measure is linked to and al the ancestor members from that point on.

However, Example 6 and 7 is a bit confusing, because the setting of IgnoreUnrelatedDimensions = False is totally ignored, just because the un-related dimension

• has a default member, or
• has no root [All] member

Example 6:

• Sales Target measure group is not related to the Scenario dimension
• Scenario dimension has a default member Actual
• The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
• VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

Example 7:

• Sales Target measure group is not related to the Sales Channel dimension
• Sales Channel IsAggregatable is set to False; no root [All] member
• The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
• VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

Reporting Driven Decision – relate or un-relate

I recently worked on a request to pull some measures on a dimension that is unrelated to the measure group.

Although the original cube design didn’t link the measure to the dimension, I can see that the design might have missed the reporting requirement. I had a few choices to meet the new reporting requirement:

1. IgnoreUnrelatedDimensions = False
2. Creating a MDX calculation to show the measure for the appropriate members on the un-related dimension.
3. Linking the measure to the dimension in the relational data warehouse, not to the leaf level members but to a middle level, similar to how the example 4 where the Sales Target measure group is related to the Date dimension but only at Calendar Quarter level.

After some experiment, I’ve abandoned both option 1 and 2, opted for option 3.

This blog explains why I didn’t choose option 1. The IgnoreUnrelatedDimensions setting for measure groups is not a very reliable way to meet my reporting requirement with so many moving parts.

Option 2 seemed very attractive to me at first. The reason that I didn’t go for it will be topic for another blog.

When SSAS is not enough

After running the above queries with different IgnoreUnrelatedDimensions setting for different scenarios, I recalled a Chapter in our book MDX Cookbook “When MDX Is Not Enough”. Here I’d like to say that sometimes settings in SSAS can seem to provide simple solutions but are actually not enough.