Best practice to deactivate or delete custom datawarehouse enabled attributes

book

Article ID: 196536

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

In clarity PPM once a Datawarehouse enabled attribute is deleted from PPM studio the subsequent partial and full load jobs fail.

Bg-DWH logs:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 7, column 1188:PL/SQL: ORA-00904: "xxxx": invalid identifier ORA-06550: line 6, column 1:PL/SQL: SQL Statement ignored

Cause

The metadata for attributes included for Datawarehouse is stored in both PPM and DWH schema. When the attribute metadata goes out of sync the load Datawarehouse job tends to fail with the invalid identifier indication in the bg-dwh logs.

Environment

Clarity PPM 15.8.1 and supported releases

 

Resolution

To recover from the load datawarehouse failure situation the action required would be to add deleted attribute back to PPM studio the exact way it was before it was deleted. 
  1. PPM Studio - Administration - Objects- Open the respective object - Go to Attributes
  2. Create attribute "xxxx" with the same id that it was existing prior to it being deleted.
  3. From the attribute properties check the box "include in the data warehouse"
  4. Run a FULL load Datawarehouse job
 
The ideal steps to delete an attribute already checked for Datawarehouse job processing is as follows:
 
    1. From PPM studio bring up the Object> Attribute > Attribute properties
    2. Un-check the "include in the data warehouse" box for removing the attribute for Datawarehouse processing
    3. Run a FULL load datawarehouse job
    4. After the job is successful then you can choose to deactivate or delete the attribute from the system.

 

 

Additional Information

Documentation reference for the full load vs incremental load Datawarehouse job Techdocs