Question on Multiple Parent-Child Hierarchies

Hi all,

I have a business scenario which is shown in the below hierachy which has 4 levels. Typically the fact table will hold data at the Project manager Level i.e Level 4. I want to implement a solution in SSAS 2005 such that it can create multiple parent child hierarchy

Can you please provide me with a data model whcih can do this?

I am aware of Many to Many dimensions but not sure how it can be implemented in SSAS 2005 based on the person logging into the cube i.e it will have an Employee Table with LOGIN ID.

Scenario 1:

Mgr1 can execute projects under Business CIO1 and Business CIO2, i.e. cross hierarchy is also possible.

Scenario 2:

A CIO at level 2 i.e. CIO1 can have horizontal level access of CIO2 also even though members of CIO2 are not reporting to CIO1.

CEO

/ \

/ \

/ \

CIO1 CIO2

/ \

/ \

/ \

Business CIO1 Business CIO2

/ \

/ \

/ \

Mgr1 Mgr2

Please treat this as urgent as i need to implement this as soon as possible for the security design

Regards

Sai

I do not fully understand the diagram you present in your question. Are you saying you have a member who may have multiple parents?

Regarding parent-child hierarchies in general, you can have only 1 per dimension. This is enough for most users. If you have a fixed number of levels and data associated with just the leaf-level of the hierarchy, you can implement your solution as a standard hierarchy. If you truly have two parent-child hierarchies, my advice would be to implement two dimensions, each with a single parent-child hierarchy.

B.

|||

Hi Bryan,

Yes am saying about a member having multiple parents. Can you please let me know how this can be achieved and based on the login ID I need to filter the members and fact data. Do you have a solution or any data model which can support this.

Regards

Sai Krishna

|||

If a member is part of independent parent-child hiearchies, you would need to implement two dimensions. Each dimension would house one of the parent-child hierarchies.

If a member may have multiple parents and those parents may have multiple parents and there is no logical/topical separation between these relationships, I'm not sure of a way to proceed. This kind of relationship is referred to as a network relationship and I do not believe SSAS supports this.

B.