Building Basic Dimensions and Cubes - Expert Cube Development with SSAS Multidimensional Models - page 54

You will also notice a change in how some user hierarchies are displayed once
you've optimized your attribute relationships in this way. User hierarchies that have
one-to-many relationships defined between the attributes that make up each level
are called natural user hierarchies. User hierarchies that don't are called unnatural
user hierarchies and display an amber warning triangle in their top left-hand corner.
This warning triangle does not signify that the hierarchy is broken or incorrectly
configured, just that you should check your attribute relationships to see whether
they can be optimized. You may actually want to build an unnatural user hierarchy,
and it may not be possible to set up the attribute relationships to make it into a natural
user hierarchy, and so long as you are aware that unnatural user hierarchies may not
perform as well as natural user hierarchies, you should not be put off by the warning.

It is very important to understand your data before you set up any attribute
relationships, because if you set them up incorrectly, then it could result in Analysis
Services returning incorrect data. The Dimension Health Check functionality
in BIDS Helper, available when you right-click on a dimension in the Solution
Explorer , checks whether the attribute relationships you've defined actually reflect
the data in your dimension table. Here's an example of a common mistake that is
made: your Year attribute has members 2001 , 2002 , 2003 , and 2004 ; your Quarter
attribute has four members from Quarter 1 to Quarter 4 . Is there a one-to-many
relationship between Year and Quarter ? The answer is in fact no, despite what
common sense tells you, because, for example, a quarter called Quarter 1 appears
in every Year . What you can and should do here is modify your data so that you can
build an attribute relationship: a quarter called Quarter 1 2001 only exists in the
year 2001 . There are two ways to do this:

• By modifying the data in your dimension table (the best option)

• By using a composite key in the KeyColumns property of the Quarter

attribute that is made up of both the keys for the quarter and the year