Shawn_Moore

CA Clarity Tuesday Tip: Inv_Flat_Hierarchies

Discussion created by Shawn_Moore Employee on Jul 26, 2011
Latest reply on Aug 8, 2011 by sundar
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

Outcomes