SSAS - Many to many dimension with "one" table

Question

I have an organization table and an organization membership table

Org table: |OrgId|Name|...|

Org membership table: |MembershipId|Parent organization id|Child organization id|

This describes an org structure where one organization can be a child of several parent organizations, the parent organizations may in their turn be children of other parents.

There are no circular references like o1 is child of o2 that is child of o1.

I am struggling to model this as a dimension in SSAS. Is it possible?

Answer 1

You are effectively trying to model multiple hierarchies in a single parent child dimension. You need to create a normal dimension out of the Org table, a parent child dimension out of the Org membership table and a new intermediate measure group linking the two Org table to the Org membership table.

To save copying all Marco's copyrighted content here, go read The many-to-many revolution and lookup the "Multiple Hierarchies" section.