PFM_HIERARCHIES table holds orphaned child parent hierarchies

Products

• Clarity PPM On Premise

Issue/Introduction

PFM_HIERARCHIES is a table in clarity for capturing portfolio hierarchies, and PFM_FLAT_HIERARCHIES is the equivalent where it flattens the hierarchy data such that is can be queried directly with SQL. It is noticed that these PFM tables are leaving orphaned child parent references long after the porfolios are removed from the system. This is causing some confusion when reporting from these PFM tables. 

 

STEPS TO REPRODUCE:

1. Create three portfolios, e.g. Portfolio1, Portfolio2 and Portfolio3
   
   
2. Bring up Portfolio 3, click "Contents Editor" button
   
   
3. Click Add button in "Child Portfolios" section and select Portfolio1 and Portfolio2 
   
   
4. Query PFM_HIERARCHIES and PFM_FLAT_HIERARCHIES tables
   
   The hierarchy is now defined as:
   
   Portfolio 3 (parent)
   
   
   • Portfolio 1 (child of Portfolio 3)
     
     
   • Portfolio 2 (child of Portfolio 3)
     
     
     
5. Remove Portfolio 1 from the portfolio list
   
   
6. Query PFM_HIERARCHIES table and PFM_FLAT_HIERARCHIES tables

Expected Result: Table returns rows that show Portfolio 1 as a child of Portfolio 3 ; we expected to see Portfolio1 removed from the hierarchy.
Actual Result: Tables show child parent references for Portfolio 1

Environment

Clarity 15.9.3 

Cause

This is caused by clarity product defect DE62777.

Resolution

Defect currently being investigated by engineering. 

As per the current code only direct children are deleted from the PFM_FLAT_HIERARCHIES or PFM_HIERARCHIES  table after a child relationship is removed between two portfolios on the clarity UI. As a best practice the child portfolio association should be removed on the UI, and only then then the child portfolio should be deleted. 

Workaround:

The following SQL query would help identify the orphaned child portfolio hierarchy id references from the Clarity schema:

SELECT * FROM PFM_HIERARCHIES WHERE CHILD_ID NOT IN (SELECT ID FROM PFM_PORTFOLIOS)

The IDs can be safely removed from the PFM_HIERARCHIES or PFM_FLAT_HIERARCHIES tables.