Unrelated Dimensions

What’s wrong with this picture?

I’ve browsed the AdventureWorks cube, and sliced Internet Sales by the Employee Department hierarchy. The same dollar amount ($29,358,677.22) is showing for every employee, which is obviously wrong and the sort of thing that confuses everyone who uses the cube.

The issue here is that the Employee Department hierarchy is not related to the Internet Sales Amount measure, and by default, the measure’s ALL member (grand total) is shown for any unrelated dimensions.

As a developer, it’s easy to dismiss this as a non-issue; it doesn’t make any sense to slice internet sales by staff member, however, this is the sort of thing that makes business people nervous about the quality of the BI system, and whether or not they trust it.

There’s two ways around this type of problem; Perspectives and IgnoreUnrelatedDimensions.

Perspectives, an Enterprise Edition feature, allows grouping together measures and their related dimensions. In the above example, there’s a “Direct Sales” perspective which excludes the Employee dimension and includes the Internet Sales Amount measure, therefore avoiding this type of invalid cube usage.

The other option is IgnoreUnrelatedDimensions, a property of a measure group, as shown here;

 

The default value for this is True, leading to the behaviour in the first image. By setting this to False, as shown here, unrelated dimension slicing is prevented; if attempted, the measure value will be blank, and no dimension members will show.

More details on this property can be found in this blog post from Hilmar Buchta.

Cheers,
Rod.

Comments

[Zohar Svirsky]

Hello Rod !
Can you base dynamic dimension data based on unrelated dimesion ex: dimesion data is based on Empdim which is not related to Sales Cube but can be part of new hierarchy of org
thanks Zohar

[Rod Colledge]

Hi Zohar - Do you mean Dynamic Dimension *Security*? If so, then the typical implementation is to have the dimension related to another dimension via a bridging table, as described here http://www.rodcolledge.com/rod_colledge/2010/10/ssas-dynamic-security-kerberos-and-performancepoint.html - Let me know if i've misunderstood the question.