Data Warehouse Trend jobs cleanup and synchronization in case of errors such as tables or records missing etc.

Products

• Clarity PPM On Premise

Issue/Introduction

In some cases the Trending records may get out of sync, and records on PPM not match DWH records. This causes problems with any subsequent jobs to run. Error can be such as:
 
> invalid identifier
> view or table does not exist
> Cannot find the object "X" because it does not exist or you do not have permissions.
> ORA-01430: column being added already exists in table
etc.

Cause

Trending tables out of sync

Resolution

1. Backup your trending tables if you need to.
2. Now run the Stored Procedure by connecting to the Data Warehouse database and executing:

Note you have to replace the DBLINK and database name/schema with the appropriate values.

For Oracle:
/*
BEGIN
DWH_TRD_CLEANUP_TRENDS_SP ('PPMDBLINK');
END;
*/
For MSSQL:
/*
BEGIN
EXEC DWH_TRD_CLEANUP_TRENDS_SP @P_DBLINK='PPMDBLINK.niku.niku'
END;
*/

This should fix the synchronization issue.

Additional Information

More information on what this job does:
 
Enhanced Trending Data Synchronization
  DWH_TRD_CLEANUP_TRENDS_SP (DBLINK)
 
1. Drops all trending tables that were dynamically created.
2. Resets the PPM and data warehouse trending metadata tables (sets the processing flag to 0 as though trending has never been processed).
3. Truncates the core trending tables and removes only their data.
As a reminder, make a backup of any trending data that you want to preserve and restore later.

For Clarity versions below 15.4.1 - the Stored Procedure is not there

1. Download the Stored Procedure attached for 15.4 and lower (it's already included with PPM 15.4.1 and above).
2. Create the Stored Procedure by running the scripts for versions below 15.4.1 on the Data Warehouse database
3. Run the steps indicated in the resolution above