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.