Attribute in Multiple Hierarchies in Analysis Services 2008
I have developed a relatively simple datastore that uses a star schema. I have a fact table with a primary key along with the CompanyID and Amount columns (actual dimension). Of course, I also have a dimension table to represent the companies that reference the fact table.
Now I need to create a single level hierarchy (CompanyGroup) for the companies. This seems like an easy task, but the catch is that one company should be allowed to exist in multiple corporate groups.
I experimented with this by creating a new dimension table called CompanyHierarchy that contains the primary key, GroupKey, and CompanyKey. Define a user-defined hierarchy where GroupKey is the top level and CompanyKey is the second level. A duplicate attribute key for the CompanyKey attribute is obtained when processing a dimension.
So, I'm not really sure how to start with this. How can I create a user-defined hierarchy within a dimension where attributes can exist multiple times?
A screenshot of my current cube definition can be seen at:
img132.imageshack.us/img132/6729/ssasm2m.gif
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 sales, and there is an extensive white paper here that explains a number of different uses for many-to-many relationships.
There is also a method for supporting multiple items in one hierarchy, which I have documented here
a source to share