Parent Child Dimension Question

I have a simple parent child dimension set up where the root level records have a ParentID of <null>.

When I browse the hierarchy the root level appear correctly, but when I drill into one of the levels of the hierarchy, the root level element is duplicated at the next level even though there is no such relationship in the dimension.

Here is a simplified example of records in the dimension:
ID Name ParentID
1 Root1 <NULL>
2 Level1 1
3 Root2 <NULL>
4 Level2 3

The hierarchy looks like this when I browse:
All
- Root1
- Root1
- Level1
- Root2
- Root2
- Level2

How can I keep the root elements from showing up at the 2nd level?

Did you set the "MembersWithData" property of the Parent attribute to "NonLeafDataHidden"?

http://msdn2.microsoft.com/en-us/library/ms174919(SQL.90).aspx

>>

MembersWithData

Used by parent attributes to determine whether to display data members for non-leaf members in the parent attribute. This property value is only used when the value of the Usage property is set to Parent, meaning that a parent-child hierarchy has been defined.

>>

|||

I didnt think that was the problem, but I did change the "MembersWithData" property from "NonLeafDataVisible" to "NonLeafDataHidden" and I still have the same problem.

I also noticed that the problem is not isolated to the root level members either, but each level repeats it's parent.

So my hierarchy really looks like:

All
- Root1
+ Root1 (leaf)
- Level1
- Level1 (leaf)
- Root2
+ Root2 (leaf)
- Level2
- Level2 (leaf)

When it should look like

All
- Root1
- Level1
- Leaf1
- Root2
- Level2
- Leaf2

I also have the appropriate primary and foreign key relationships defined in this dimension table.

Any other ideas?

Thanks in advance.

|||

Well, in my case, changing the "MembersWithData" property to "NonLeafDataHidden" fixed this issue (it is "NonLeafDataVisible" by default, according to BOL). So, do you find that switching this property on the Parent attribute has no effect on the visible hierarchy at all - which would be surprising?

PS: you can confirm this in the Adventure Works Employee dimension:

- on the Employees attribute, change the "MembersWithData" property to "NonLeafDataVisible"

- deploy the Employee dimension change and then browse the Employees parent-child hierarchy:

- you should now see each non-leaf node (starting with the root) also listed as a leaf child of itself

- switch the property back to "NonLeafDataHidden", re-deploy the dimension, and check by browsing