Clarity

Just wondering about the orphan records in INV_HIERARCHIES ...

The Tech ref says
INV_HIERARCHIES
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.

Column
Description

CHILD_ID
This column provides a reference to the child investment. The value corresponds to the ID found in INV_INVESTMENTS.

CREATED_BY
This column refers to the resource (CMN_SEC_USERS.ID) that created this row.

CREATED_DATE
This column refers to the date and time this row was created.

DEFAULT_ALLOC_PCT
This column indicates the default percentage allocated from the child investment to the parent investment.

LAST_UPDATED_BY
This column indicates the user who last updated the record.

LAST_UPDATED_DATE
This column provides the date when the record was last updated.

PARENT_ID
This column provides a reference to the parent investment. The value corresponds to the ID found in INV_INVESTMENTS.

When I created a new program a record is inserted in INV_HIERARCHIES where the program ID is the child_id. The table description does not give any indication of that. When I create a new project that does not happen. Why might that be? A program is likely to have projects or other under it and thus more likely to be a parent than a child.

Martti K.

Martti K.

Hello

1. Yes, you are right - ' A program is likely to have projects or other under it and thus more likely to be a parent than a child.'
However, we have seen instances where one or more programs are added as a child to one program.

2. 'When I create a new project that does not happen' - You need to add a child / a parent program/project under the hierarchy of that project in order for that record to be visible under the 'INV_HIERARCHIES' table.

Hope this helps ... :-)

Regards
NJ

Thank you for your input.

I am not trying to build the hierarchies.

For one I am trying to query all the descendants for an investments and those orphans are just obsolete in that respect.

Then the OOTB Purge temporary hierarchy job is scheduled and running. It is schedule to run every ten minuts The system is an upgrade so the required parameter purge option does not have a value. The job takes several hours.

If I set the parameter to purge only outdated data it takes a couple of hours and if I purge all temporary data it takes just several minutes.

Updating the hierarchy takes also a couple of hours.

So to get the run times down I would like to get rid of the orphans them being some 20 % or more of the table record count.
The concept of an orphan in hierarchy sounds like a corruption.

Martti K.

Hi.

Recently, we've noticed the number of orphans in INV_HIERARCHIES seems to continually increase. By orphan in this case I mean ANY entry in which PARENT_ID is null. We're experiencing this also for regular projects, not just programs.

It appears that Clarity inserts initial entries into the INV_HIERARCHIES when creating a project, which it later doesn't clear away.
We're suspecting the XOG interface (i.e. when creating new projects over XOG), but so far we haven't really bothered to investigate further.

We just discovered this when a couple of our queries failed when for a given CHILD_ID there were multiple rows in INV_HIERARCHIES, with a "null" PARENT_ID in one of them.

Daniel.

Thanks.

So creating orphans for projects is as the product is designed to work (again not desired), but creating orphans for projects only happens through XOG interface. In the system I am looking at about half of the orphans are programs.

The percentage of the orphans is about 25 % from inv_projects ie 7500
How many is that for you?

The update hierarchy job takes 20 - 30 minutes or more.
How long does it take for you.

The same with purge temporary hierarchy with the option delete outdated only.
With purge all temporary data it is just several minutes.
Apparently verifying what is outdated takes too long.
How long does it take for your?

Martti K.

We have experienced that there is different behavior of the system between the sub-project functionality and the Add Child button in the Hierarchy functionality.

Maybe this will help in yout further investigations.

Michiel Meijler

Martti,

Hope CA had addressed this issue in 12.0.6_generic_010.


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

Thank you Sundar.
That makes the problem even worse.
The original problem was when creating these tens of thousands of programs the orphans were created.
Then when creating the myriad of hierarchies all of them are not until the end of time.
In fact one use case is to add items into the middle of the hierarchy which effectively removes a child.
However, they are not necessarily as subprojects just in the hierarchy.
Looking at the release notes I am getting more and more concerned whether or not a fix creates more (undocumented) problems that it solves. Like Pete Foley put it it is preferable for somebody else to try it first.

Martti K.

Martti,

Next weekend we are going for this Patch 010 (Not because of this but for other 2 critical issues for us.(CLRT-54278 ).
we need to test it and share it.Iam also afraid of any new bugs -_-in that patch.

cheers,
sundar