Hierachy drill down stops at point

I have the following scenario

I have a Staff Parent child hierarchy and that links down to a division. A staf member is in charge of a division but we want the managers to drill down into the children and see which divisions they are in charge of etc.

So here is what the data looks like

Staff Lev1 | Staff Lev2 | Staff Lev3 | Staff Lev4 | Division | Value

------------|--------------|--------------|--------------|-----------|---------

A | B | C | D | DivA | 1000

A | E | | DivB | 2000

the problem is, with drilling down though the staff hierarchy I can never see DivB, I can see the value associated to the Staff member E but when I try drill down it just comes up with total and I never get to see the Divisions he is in charge of. I have tried making leaf level data visible and hidden and everything. Does analysis services allow you to drill down though none existent members Is there a work around to get the data to look like what I have in the diagram

Answer this question

Hierachy drill down stops at point

Hossam Abdel Wahab
The thing is the staff hierarchy is a parent child hierarchy and flattening it is not an option. I need a reverse ragged option. Where instead of skipping levels I fill levels. I don't know if there is that option in SSAS or how to do it. Your first option is what I have tried to do but I can never get to see the division.
xRuntime

I think there are 3 different approaches that might get you the results that you are after.

Assuming that you are using SSAS 2005, without making any changes to your dimension you should be able to drag the Division attribute next to your staff hierarchy and see the associated divisions for each staff member.

A second possibility would be to add some code to your ETL process to insert records for the divisions into the bottom of the staff parent child hierarchy.

Thirdly, if your staff hierarchy is fixed at a finite number of levels as per the sample data that you posted, you could "flatten" out the hierarchy during the ETL process so that the actual dimension table looked like the sample data (without the value of course, that would be stored in the fact table) with columns for Level1, Level2, etc.
nares

The thing is that you cannot have a parent-child hierarchy that drills down onto a different attribute. If you cannot flatten out the hierarchy then I think your only choice is to insert the divisions as members underneath the lowest employee member. The concept of a reverse "ragged" hierarchy really only makes sense if your are dealing with a "leveled" hierarchy as you would need to have the concept of a fixed number of levels in order to know how many levels needed "filling in".
Hierachy drill down stops at point

Answer this question