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

book

Article ID: 124968

calendar_today

Updated On:

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

To resolve the issue, please do the following:

For Clarity version below 15.4.1 (steps 0-3)

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


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

For Clarity version above 15.4.1 (steps 2-3)

2.    Backup your trending tables if you need to.


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.
 

Attachments

1558537016756dwh_trd_cleanup_trends_sp.zip get_app