Attribute in multiple hierarchies in Analysis Services 2008

Question

Hi!

I have designed a relatively simple data warehouse that uses the star schema. I have a fact table with just a primary key along with CompanyID and Amount (the actual measurement) columns. Of course I also have a dimension table to represent the companies which the fact table references.

Now I'm required to create a single level hierarchy (CompanyGroup) for companies. This seems like an easy task but the catch is that a single company should be allowed to exist within multiple CompanyGroups.

I experimented with this by creating a new dimension table called CompanyHierarchy that holds a primary key, GroupKey and CompanyKey. Defining a user defined hierarchy where GroupKey is the top level and CompanyKey is the second level yields A duplicate attribute key has been found error for the CompanyKey attribute while processing the dimension.

So, I'm not quite sure how to even start with this. How can I create a user defined hierarchy within a dimension where attributes can exist multiple times?

Screen shot of my current cube definition can be seen at:

img132.imageshack.us/img132/6729/ssasm2m.gif

Answer 1

You need to create a many-to-many relationship (one company can belong to many groups and one group can have many companies) There is an example of a many-to-many relationship in the Adventure Works cube around the sales reason dimension and there is an extensive white paper here that explains a number of different ways of using many-to-many relationships.

There is also a technique for supporting multiple members in the one hierarchy that I documented here