IgnoreUnrelatedDimensions

IgnoreUnrelatedDimensions is a property of Measure Groups. By default its value is TRUE. So what does that mean? A Measure Group need not be linked to all available Dimensions in the cube. In other words there are some Dimensions that are unrelated to a Measure Group. When IgnoreUnrelatedDimensions property is TRUE, the Measure Group ignores any dimension not linked to it by showing the current value for all members in the unrelated dimension. This is confusing to the Information Consumer when the cube is browsed.

Take for example, the AdventureWorks cube. The Sales Quotas measure group is related to Employee Dimension and unrelated to Product Dimension as shown in Figure 1

Figure 1 - Related & Unrelated Dimensions
Figure 1 - Related & Unrelated Dimensions

With the default value IgnoreUnrelatedDimensions=TRUE, the cube when sliced by Employee (Related Dimension) and Product (Unrelated Dimension), the result is confusing to the Information Consumer as shown in Figure 2

Figure 2  IgnoreUnrelatedDimensions=TRUE
Figure 2 IgnoreUnrelatedDimensions=TRUE

To avoid this confusion, set IgnoreUnrelatedDimensions=FALSE.  From BIDS, Open the cube. Go to Dimension Linkage tab, right click on the Measure Group and set this property as shown below in Figure 3.

Figure 3 - IgnoreUnrelatedDimensions
Figure 3 - IgnoreUnrelatedDimensions

By doing so, Analysis Services return a blank value for Dimensions that are unrelated, as shown below in Figure 4.

Figure 4 IgnoreUnrelatedDimensions=FALSE
Figure 4 IgnoreUnrelatedDimensions=FALSE

Personally, I would prefer the default value of IgnoreUnrelatedDimenisons to be FALSE. I haven’t found any compelling reason for this property to have a default value of TRUE. At least there is a workaround!

Benny Austin

15 thoughts on “IgnoreUnrelatedDimensions

  1. Thanks for the post.
    Is there any way so that I can set the same effect to “All” member (Grand Total line) just like you showed for other members

      1. Hi Benny, I don’t want to set IsAggreagatable to false as I’d need that dimension for other measure group which are relevant to that. Anyway I have tried to find the dimension name using axis(), if I find any irrelevant dimension then I have made the irrelevant measuregroup null. Thanks for your reply.

  2. I have an issue with IgnoreUnrelatedDimensions, I have a hierarchical product dimension, let say Product Category, Product Sub Category and Product. I have 2 different facts with different granularity (lowest level), let say one is FactSales with Product level as granularity (lowest level), and the other is FactSalesTarget with Product Sub Category as granularity (lowest level). When I set it up into TRUE, and then I browse FactSales both FactSalesTarget together with Product Level, it shows wrong values, then I set it up into FALSE, it doesn’t show the values as I want, solved, but…. it creates new issue, when I browse FactSalesTarget with Product Category Level, the values aren’t shown up, it seems it’s only showed up when I use Product Sub Category only !! Product Category is counted as UnrelatedDimension !! Any suggestions ?

    1. Hi there, from your explanation what you are observing is the expected behaviour of Analysis Service. Setting IgnoreUnrelatedDimension=FALSE means both your measure groups can be sliced by Product Subcategory only since this is the common grain.

  3. Its a nice article for the reference. I do have a question.

    In the above example (Fig #4), the Product Category –> All Products has been selected. The selections, Accessories, Bikes, etc individual items may not make sense. Does my understanding is correct?

    1. I mentioned this here because in my environment, there are so many ProClarity views that use few unrelated dimension attributes in the background filter with few members have been selected. Set IgnoreUnrelatedDimensions=False in the cube makes more number of ProClarity views stop work and we have updated all the views to start work.

  4. I agree with you, returning value sets users on the wrong foot.

    I always remember it’s working by thinking about the MDX query.

    Ignore = True, the dimension is left out the MDX query and the tuple is based on all other dimensions used in the selection.

    Ignore = False, the dimension is in the MDX query and the tuple will be null.

  5. This was a good write-up. The MS BI Dev and Maint study guide for 70-448 doesn’t explain IgnoreUnrelatedDimensions well at all. You made it perfectly clear though. Thx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s