Dimension Hierarchy does not allow duplicates

Dan Perlman

Hello,

I am very new to data warehousing.

I have created a hierarchy on "StoreID", "Make" and "Model".

The Make "Harley" should appear below all of our Harley-Davidson store ids. It looks like there are no duplicates of any make or model in the hierarchy when I browse the dimension. I am using BI dev studio and SQL 2005 analysis services.

I'm guessing it's just some property I have not found yet.

Any ideas

Thanks

Thomas Ivarsson

Hello! In the middle pane in the dimensions editor, where you create the the user- or natural hierarchies, you can expand the lower to levels(Make and Model) and create attribute relations for the lower levels.

Attribute relations are pointers to what is the next higher level in an user hierarchy.

Drag the attribute Make(from the left window) and make it an attribute relation under Model. Drag StoreID to Make also as an attribute relation.

Have a look at attribute relations in Books On Line or download the performance guide for SSAS2005, pointed to at the top of this news group.

HTH

Thomas Ivarsson

Dan Perlman

Hi,

Yes, that is the way I have it setup.

StoreID is on top.

Then Make with a relationship to StoreID.

Then Model with a relationship to Make and StoreID.

That is the correct way to do this isn't it

I get a list of makes under storeid's and models under makes but any given make or model only appears once in the hierarchy.

Thanks

Thomas Ivarsson

Model should only be related to Make.

And remove the redundant relationships under the dimension key after this change.

HTH

Thomas Ivarsson

Dan Perlman

Thomas,

I removed the storeid relationship from model.

Didn't see any redundant relationships under the dimension key.

I re-processed it and still have the same issue.

I must be doing something else wrong.

Thanks for the advice!

Martin Mason

You're going down the right path. However, I think to do what you want to do, you're going to have to create a composite key consisting of the StoreID and the Make for the Make attribute. Therefore, Harley will appear under every store that sells Harley. You're also going to have to create a composite key of StoreID and Model for the Model attribute.

Darren Gosbell

Probably the best way to deal with this is to create a "bridge" fact table that holds the relationship between models and stores, then use this table as the intermediate fact table in a many-to-many relationship.

When you put StoreID on a make/model and set the relationships up the way you have, you are implying that each make/model has only one "parent" storeID. When the dimension is being built the storeid attribute basically gets overwritten multiple times and the last one wins and the make/model only shows up under that last storeid.

Thomas Ivarsson

Hello again! This is how natural hierarchies works. You have a cardinality property on the attribute relation that only can be one-to-one or one-to-many .

Like others have told you you would have to rethink your design of the dimension.

Regards

Thomas Ivarsson