Dimension Hierarchy Relationships

We all (should) know that it is really important to set up relationships between our dimension attributes.  This enables AS to build aggregations at levels other than the top and the bottom.  For a large cube this could make a huge difference in query speed.  I haven’t found a really clear example of how to set these up so I thought I’d take a stab at it.

When you are editing a dimension hierarchy, you need to add a parent relation ship to all levels.

If you have Year, Month and Day (and a Key) you need to add a releationship to Year in the Month level and a relationship to Month in the Day level and then over on the left, add a relationship to the Key from the Day level.

Every level must have its parent defined as a relationship.

In addition to that, don’t forget to make sure that the relationship type is set up properly.  In this example, we have a rigid hierarchy since we know that no members will be moving around like people in an organization.  March of 2003 will always be a 2003.  Each relationship need to defind whether it is a many-to-one or a one-to-one relationship.  In this example, they are all many-to-one except for the relationship of the key to the Day level.  This would be a one-to-one.

Hope this helps.

Mark

Advertisements

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

%d bloggers like this: