SSAS 2005 - Organistion Hierarchy - Parent Child Relationship

Question

Post reply

SSAS 2005 - Organistion Hierarchy - Parent Child Relationship

jerstep

Old Hand

Points: 372
More actions
October 22, 2010 at 4:43 am

#388073

I am trying to create a dimension to show a company's organisational hierarchy which has the following structure.
Division
Department
Manager
Person
My underlying table is structure as follows:-
Division_Id
Division_Name
Department_Id
Department_Name
Person_Id
Person_Name
Manager_Id (this is a person_id)
The manager_id/person_id have a parent/child relationship.
My question is how do I build this in SSAS? My SSAS knowledge is limited. Using the dimension wizard I define person_id as my key column with the person_name as the member name. I then select parrent/child relationship with the manager_id.
This almost produces what I want but I need to have additional levels before the manager/person relationship.
If I then create a new hierarchy and drag accross division name, department name and then the manager_id hiearchy, I get a error stated that the source level of the attribute is marked as 'parent'.
Can anyone help?
Regards,
J

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

There is an article I just read on BIDN about this.

http://www.bidn.com/articles/miscellaneous/184/disecting-the-self-referencing-hierarchy

Registration is required.

I have also seen articles (also on BIDN) that suggest that a self referencing hierarchy will have very poor performance with more than a fairly small number of records, and it goes on to suggest that using strategies to flatten these self referencing hierarchies is generally a good idea.

jerstep Old Hand Points: 372 More actions · Answer 2

Thanks Daniel but that article concerns parent/child SQL queries not the creation of a SSAS dimension to reflect that relationship.

As I said, I already create a dimension with the parent/child relationship defined but it is only defined between 2 attributes i.e. manager_id and person_id. I need to be able to display the higher levels too i.e. division/department/manager/person.

Any further suggestions much appreciated.

Regards,

James

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

James,

Short answer, AFAIK, you can't do it the way you want to do it. What you can do, is add additional rows to the P-C table (ie Department will get an employee id and Managers may reference that emp_id as their 'manager').

Alternatively, you 'unroll' the P-C nature of the table into a wider dimension table. If you want to do this, am pretty sure that the BIDS Helper now has this utility added into it.

HTH,

Steve.

jerstep Old Hand Points: 372 More actions · Answer 4

Thanks for that Steve.

Would you be able to expand on those methods a little? I've be looking at it since you replied and can't quite work it out.

Regards,

J

gtschech Ten Centuries Points: 1351 More actions · Answer 5

Jerstep,

I think you will have to detail your plans to browse the cube a bit further.

From your posting I see two different approaches to browse the data:

* find the manager of a person (or all persons a manager is assigned to)

* browse the company organisational structure

You try to mix these, and that could be the problem.

I would suggest a 'Organisation' Dimension that has two hierarchies:

* the Division/Department/person hierarchy. This would be a standard hierarchy that you design in the middle or right pane of the dimension editor (depending on your settings)

* the parent/child-relation of manager and employee. This is a parent-child hierarchy that is defined in the properties view of the dimension attribute. However, you can define only one parent-child-relation per dimension.

You should define the Division and department attributes as relations in the key attribute (which is the person ID, I suppose) in the left pane. This allows you to query these values while browsing through the parent-child-relation.

I hope this gives you a starting point for youer design...

Guenter from Frankfurt/Germany

jerstep Old Hand Points: 372 More actions · Answer 6

Guenter

Thanks very much for your ideas/suggestions. I think they have helped me understand the problem I have and I can now move forward.

Noch mal vielen Dank!

Regards,

J