INV_FLAT_HIERARCHIES or INV_HIERARCHIES which one to use?

[Deactivated User]

INV_FLAT_HIERARCHIES or INV_HIERARCHIES which one to use?

 

INV_HIERARCHIES is described

This table stores hierarchical relationship information between investments.
It maps the parent to child relationships and also indicates
what percentage of a child investment is allocated.
INV_FLAT_HIERARCHIES is described
This denormalized table stores data based on INV_HIERARCHIES.
The flattened table contains parent_id and child_id entries
for all descendants of a given investment parent_id that has a hierarchy.
The link_source_id contains the ID of the immediate parent of the child.
This table enables rapid retrieval of all descendants within a hierarchy.
By examining the link_source_id, the original hierarchical order can also be retrieved.

Does parent to child relationships mean just the immediate parent child relationship
or does it mean also further generations? If so, is that explained somewhere?

Like with OBS I have not used the flat tables, but the real relationship tables in queries
and build whatever is needed in spite of the orphans. See
https://communities.ca.com/message/8883253?commentID=8883253#comment-8883253 

For which versions does
/*This query is a derived version of INV_FLAT_HIERARCHIES as the OOTB table 
has a bug with the column LINK_SOURCE_ID 
where it doesn't refer to immediate parent...

apply?

[Deactivated User]

From Skrikanth Gooni in APJ Office Hours  Feb 22

If you add child investments individually, the LINK_SOURCE_ID value is set correctly. However if you add an investment that has a Child to a hierarchy, the LINK_SOURCE_ID for this Child is pointing not to the parent investment, but to the grand parent investment.
Example:
  INVESTMENT A
    INVESTMENT B - points to INVESTMENT A
Separately, you have
      INVESTMENT C
        INVESTMENT D - points to INVESTMENT C
Add INVESTMENT C as a Child to INVESTMENT B to obtain the following hierarchy
   INVESTMENT A
      INVESTMENT B
         INVESTMENT C
            INVESTMENT D
It is expected that INVESTMENT D points to INVESTMENT C
In the table it shows the direct relationship correctly, but for flattening the hierarchy on the other levels, INVESTMENT D is pointing to INVESTMENT B
: REF_COUNT column stores the number of ways a parent-child relationship exists. When the same parent-child relationship is added directly or through some other hierarchy, this column will get incremented. So at any point we will come to know how many ways a parent-child relationship is existing. This will not reveal how the relationship is established.
Once a parent-child relationship is created, if same parent-child relationship is created in any manner then the existing record's ref_count will be updated by 1. The link_source_id can not be used to establish how the hierarchy created.
The mentioned bug was closed with the above explanation.

 

 

as long as you get what you need from inv_hierarchies,  we would recommend using that table.