dimension with recursive parent child relationship

Question

It's been a while since I worked with SSAS so I need a refresher. I would appreciate any feed back on my understanding/questions as I've layed out below Thank you.

Say I have a geography dimension that can go several levels deep and data can be booked at various levels in the hierarchy. (Not just at the leaf level)

So, if my hierarhcy looks like

World
    NorthAm
        USA
            NY
            NJ
            CT
    SouthAm
        Brazil
        Argentina
    

Q1: This would mean that I would need a dimension key for every member at every level in the hierarchy so that I can use these keys in the fact table. Like

dimKey       member

1                World
2                NorthAm
3                USA
4                NY
5                NJ
6                CT
7                SouthAm
8                Brazil
9                Argentina

Q2: The dim table above alone doesn't describe the hierarchy so I would need another table like the following. I hope I have the key relationships consistent with the above hierarchy, but hopefully you'll see what I'm doing.

childKey    parentKey
1                1
2                1                
3                2
4                3
5                3
6                3
7                1
8                7
9                7

Q3: The recursive table like the above is the best way to model this kind of uneven hierarchy, as opposed to trying to have separate columns for each level. And in fact, the recursive table is the only way to go in this case because the data can be booked at various depths in the hierarchy.

Q4: Should I try to combine these two tables into one table? Or is that done in the SSAS designer... I think that was called a named view or something like that.

Q5: Within SSAS, there is a setting that controls the aggregation behavior at the upper levels... I think the choices are something like, (a) overwrite upper level values with the aggregate of the lower level values, (b) include the value booked at the upper level with the values at the lower level when showing the aggregate. Not sure if there was another option.

Answer 1

I assume you're now working with AS 2005 or later.

You do want a single (logical) table that contains the Key, the Parent and the Name columns. You can do this by using ETL to put them all in a single physical table, or you can create a query that joins them and put that into a View. (If you don't have the necessary permissions to create a view, you can create a Named Query in the DSV in BIDS, but a real View is probably better.) In the DSV, you will need to make sure there is a recursive join from the Parent column back to the Key column of the same table.

When you create the dimension, you'll have two attributes: One, with Usage = Key, will have the relational Key column as its Key Column binding and the relational Name column as its Name Column binding. (You'll want to hide this attribute from end users.) The other, with Usage = Parent, will have the relational Parent column as its Key Column binding. This is the one that organizes the hierarchy in the right order and allows rollups to work properly.

Since you do have data for non-leaf members in your fact table, for the Parent attribute, you need to set the MembersWithData attribute to NonLeafDataVisible. (The only other option is NonLeafDataHidden. In AS 2000, there was a third option--None--which would raise an error if there were any fact table rows that joined to a non-leaf member, but that option was removed in AS 2005.)

Good luck.

Cheers,
Reed

Answer 2

Thanks, It think then I am going in the correct general direction.

Answer 3

 Hi, I've red the solution that you proposed. In fact I've done the same thing in SSAS 2008 for creating a perent-child recursive relation. But when I want to create my report and to put up the parent-child relation on the reports with the signe "+", I can't. Because I can see only one dataset (column name)where I've met all the parent child attribut id. It means that I want to get all the parent id and I want to put up the children whan the user clicks on "+"

Answer 4

is one of you did apply that relation in the reports? I need advice?

Answer 5

Informative thread. Also found these links useful http://www.bimonkey.com/2009/09/handling-recursive-hierarchies-in-sql-server/

http://www.jenstirrup.com/2009/04/comparison-between-parent-child-and.html

---

Mpumelelo