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

Document ID : KB000124968
Last Modified Date : 18/01/2019
Show Technical Document Details
Issue:
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.
etc.
 
Cause:
Trending tables out of sync
Resolution:
To resolve the issue, please do the following:

0.    Download the Stored Procedure attached for PPM version below 15.4.1 (it's already included with PPM 15.4.1 and above).

1.    Backup your trending tables if you need to.

2.    Create the Stored Procedure by running the scripts for versions below 15.4.1 on the Data Warehouse database

3.    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.
 
File Attachments:
dwh_trd_cleanup_trends_sp.zip