How do you build a recursive hierarchy on top of an explicit hierarchy/derived hierarchy?

Question

Hi,

This is a requirement from SQL Server 2008 R2 MDS.

I have a requirement from my client to build a hierarchy following this logic:

1. Parent Product Hierarchy (Recursive for Product Hierarchy)
2. Product Hierarchy (Recursive for Product Hierarchy)
3. Product Family (Could be a consolidated member of Product in an explicit hierarchy)
4. Product (Could be a leaf item in the Product explicit hierarchy)

Example:

• Level 0 - Parent Hierarchy item
• Level 1|1 - Child Hierarchy item
• Clover Blue Cheese - Product Family
• Clover Blue Cheese|50 gr|Singles - Product

How would I accomplish this? I tryed building this hierachy any which way but the moment you pull in your Product Hierachy it will not allow you to pull the Parent Product Hiearchy in. It just states that it is not compatible with that level.

Please assist.

Thanks and Regards,

Paul

Answer 1

Paul,

I am pretty sure you can only have one recursive hierarchy in a derived hierarchy.  It looks like in your example, you have two (parent product hierarchy and product hierarchy).  I copied the below statement from here. 

• A derived hierarchy cannot contain more than one recursive relationship. It can, however, have other derived relationships (for example, a derived hierarchy that contains a recursive Manager to Employee relationship can also have Country to Manager and Employee to Store relationships).

Answer 2

I managed to solve my issue.

My problem was the approach I was using to solve the issue.  All I had to do is create an Explicit Hierarchy on top of the Product Family and use the domain based attribute between Product and Product Family. This allowed me to have an Explicit capped Derived Hierarchy to show my model.

This all means that I created the member items I thought could only be leaf items as consolidated items.

Regards,

Paul

---

Paul Burger