I had Customers dimension in my cube, linked with Fact table by CustomerId. Now I got new table that describes parent-child relation for these Customers. New table has just to columns: CustomerId and ParentId. I need to include this hierarchy into the cube.
It can be easily done by including ParentId as new column to Customer dimension table, BUT in my case one child record can have multiple Parent records at same level, e.g.
A is child of B
A is child of C
A is child of D
I tried to make many-to-many dimension, it seems to work for that case but only if there only one level of parents, however in my case there can be very many levels, e.g.
A is child of B, then B is child of E, then E is child of F, ...
So, is there any way to make parent-child dimension with multiple parents and unlimited hierarchy levels?
Yes, it is possible to create parent-child dimension with multiple parents in many hierarchies.
Please check this link. You will explore here many different uses of many-to-many relationship in Multidimensional model and also Tabular. The PDF will explain you how. You can also find many BI tips inside.
[1]: http://www.sqlbi.com/articles/many2many/