Creating Dimension Hierarchy

  • dean gross

    SSCrazy

    Points: 2632

    As an SSAS newbie, i'm confused about how dimension hierarchies get created in 2005 and I'm hoping someone can help me out. I am importing an Employees table that has Employee Number, Emp Name, Company, Division, Department, Country, State, City fields. I thought that I would be able to easily create an Organization Hierarch with Compay-Division-Department attributes and a Geography hierarchy with Country, State, City attributes, but when I do this in the HIerarchies and Levels pane, I get errors about attribute relationships not existing. What am i doing wrong?

    Thanks

    Dean

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Yeah, they didn't make this very obvious in AS2K5.

    When you look at the dimension in the dimension editor, by default, all of the attributes will have their relationships set to being directly off the key. So, let's say Employee Number is you key attribute, if you hit the plus next to it (to expand the attribute relationships below) you'll see that City, State, Country etc are under the key. This isn't what you want. You want to move the relationship for (say) Country and drag it to be under State. The reltionship for State should be moved from under the key and place under City. The basic idea is, where items are a 1:1 with the key, then their relaionships should remain there under the key attribute. Where there are natural hierarchies in the data (*whether you create "User Hierarchies" or not!*) the attribute relationships should reflect these natural relationships.

    An image from another post here shows (expanded in the user hierarchy rather than the attributes pane, but it's holds true) how when the relationships are set correctly, the usr hierarchy *won't* have the warnings. The pic is here.

    HTH,

    Steve.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply