Clarity

Title: CA Clarity Tuesday Tip: Inv_Flat_Hierarchies

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 07/26/2011

I recently learned some interesting things about our inv_hierarchies and inv_flat_hierarchies table and wanted to share them with you all.

The inv_hierarchies and the inv_flat_hierarchies tables store master-sub relationships for investments in Clarity.

e.g.

program1 (id: 5000001)
+---project2  (id: 5000002)
+---project3  (id: 5000003)
+---project4  (id: 5000004)        

In this case, program1 has a sub-project, called project2, which in turn has a sub-project called project3, which also has a sub-project, called project4.

In the inv_hierarchies table we would have records for each immediate relationship:

i.e.

parent_id child_id
--------------------------------------
5000001(program1) 5000002 (project2)
5000002(project2) 5000003 (project3)
5000003(project3) 5000004 (project4)

In order to find all the children, recursively for program1 (5000001), we would have to traverse the tree, which programmatically is a lot of work.

To simplify logic, development built a flat table (inv_flat_hierarchies) which simplifies record access.

The above relationships are represented in the inv_flat_hierarchies as:

parent_id child_id hierarchy_path
----------------------------------------------------------------------
5000001
5000002
:5000001::5000002:
5000001
5000003
:5000001::5000002::5000003:
5000001
5000004
:5000001::5000002::5000003::5000004:
5000002
5000003
:5000002::5000003:
5000002
5000004
:5000002::5000003::5000004:
5000003
5000004
:5000003::5000004:

The power that this provides is now you can run a simple select to determine who are the descendants (sub-projects, sub-sub-projects, etc.) of a particular investment.

i.e.

select child_id from inv_flat_hierarchies where parent_id = 5000001
-- This query shows all the sub-investments under program1 in this hierarchy.

Hope you enjoyed this tidbit!

-shawn

Hey Shawn,

Welcome back and thanks for the great tip!

Chris

Hi Shawn,

Welcome back, not sure if this is the place to post this, but I was wondering if you or one of the Engineers can give us pointers on what items to have in place in the tool before rolling out functionality. One Module in particular I would like to see a road map for is Resource Management even Project Management. An example of what I mean is something like this, prior to using Resource Requisition, make sure you have your templates in place and project plans built with roles, assign them to tasks and then allocate from estimates once this is done then you can do a role/replace. This flow would be great to see so that a PMO team can create a road map based on this and begin to work on their objectives based on the business needs. I think a nice view of this will allow a nice flow for rolling out functionality and getting the most of the tool. Any thoughts on someone would do this.

Thanks
Tammi

Great tip, thanks,

Martti K.

Well folks, I wanted to bring up a small addendum, with a word of caution, since I displayed the hierarchy_path in my example for this post:

It turns out that the hierarchy_path field is not a good field to base logic on. If you are doing any sort of hierarchy logic, it is best to actually use the parent_id and child_id relationships in the inv_flat_hierarchies table. Don't use the hierarchy_path field as it may not be intuitive and is not guaranteed to be present in future versions, due to the fact that it technically is not exposed for public use.


-shawn

shawn,

Thanks for your tip.

Can we use the table Inv_Flat_Hierarchies to fetch child records as until in 12.0.6 we have the below orphan record issue.Is it fixed in 12.1?.

I have tested in clarity v12.0.6 010 but still the issues exists.

CLRT-48499: Removing subproject (or child project in hierarchy) leaves orphaned hierarchy records
Steps to Reproduce
1. In Clarity, create 4 projects as follows: "Master", "sub1", "sub2" and "sub3"
2. Add "sub3" as a subproject/child of "sub2"
3. Add "sub2" as a subproject/child of "sub1"
4. Add "sub1" as a subproject/child of "master"
5. Remove "sub1" as a subproject/child of "master"
Expected Result: No references from any sub to the master in inv_flat_hierarchies or inv_hierarchies table
Actual Result: Orphaned references exist

cheers,
sundar