SSAS: Ignore Unrelated Dimension or not

  • Article

Introduction

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.

The examples 1, 2 and 3 below are three MDX queries that are showing the consistent results regardless if the dimension has default member or not, root [All] member or not. They also showed that the result is the same as the VALIDMEASURE() function.

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

https://bisherryli.files.wordpress.com/2014/02/image_thumb.png

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

https://bisherryli.files.wordpress.com/2014/02/image_thumb1.png

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

https://bisherryli.files.wordpress.com/2014/02/image_thumb2.png

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. If you don’t like how the measure is repeating the same value for all the un-related dimensions, you might be tempted to change the default setting to False. The examples 4, 5, 6 and 7 below are four MDX queries that are showing some very inconsistent results, depending on if the dimension has default member or not, root [All] member or not. They also showed that the result can be different from the VALIDMEASURE() function.

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 

    https://bisherryli.files.wordpress.com/2014/02/image_thumb3.png

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 same value for all members

https://bisherryli.files.wordpress.com/2014/02/image_thumb4.png

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

https://bisherryli.files.wordpress.com/2014/02/image_thumb5.png

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.

https://bisherryli.files.wordpress.com/2014/02/image_thumb6.png

Reporting Driven Decision – Related at quarter and year level only

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. The requirement is to show a measure at a middle level and above (such as at the Calendar Quarter level and the Year level), and be able to aggregate properly. At the lower levels (such as the Month and Day level), the measure must show NULL.

To meet the new requirement, I had a few choices:

  1. Experimenting with setting IgnoreUnrelatedDimensions to False. 
  2. Creating a somehow complex MDX calculation to show the measure for the appropriate levels on the un-related dimension, and show NULL at lower levels on the hierarchy.
  3. Linking the fact table to the dimension table in the relational data warehouse, then in the cube, linking the measure group not to the leaf level members but to a middle level, similar to how in the example 4 where the Sales Target measure group is related to the Date dimension but only at Calendar Quarter and Year level.

Option 3 requires me to modify the fact table, and possibly the ETL process (fortunately I didn’t need to because the related level is fixed so I opted to hard-code the key in the fact table). It also requires me to modify the cube and dimension relationship. The changes are minimum and the solution worked for the reporting requirement.

To demonstrate the idea of linking a measure group to a higher granularity attribute, rather than the lowest level attribute key, I will use the Sales Target measure group in the Adventure Works cube as an example.

Let’s take a look at the relational database first. The screenshot below shows how the FactSalesQuota table is linked to the DimDate table. It is easy to see that the fact table has both the CalendarYear and CalendarQuarter column in addition to the DateKey column. This lays down the foundation for the relationship in the cube.

Now let’s took a look at the Adventure Works cube. The screenshot below shows that the Calendar Quarter is selected as the link between the Sales Targets measure group and the Date dimension, instead of the default Date attribute. A warning at the bottom reminds us of this, and also that we need to have related attributes defined correctly if any to get proper aggregation.

Are they truly unrelated

I have started with the setting IgnoreUnrelatedDimensions, but ended with relating the measure group with the dimension, not at the lowest granularity attribute level, but at a higher granularity attribute. So next time, when we leave many blank intercepts of measure groups and dimensions on Dimension Usage tab, we might want to ask the question, “are they truly unrelated?”.