Clarity PPM: When to Rebuild the Data Warehouse (DWH) database

Document ID : KB000004413
Last Modified Date : 19/10/2018
Show Technical Document Details
Issue:

One or more of the following issues/symptoms are attributed to the Load DWH job failing.
If the following conditions are present, a data warehouse database rebuild is necessary.

 

ISSUE 
Clarity PPM and DWH database versions do not matchRun the following queries to find out if the database versions match:
PPM:  select * from cmn_install_history order by installed_date desc
DWH: select * from cmn_dwh_install_history order by installed_date desc
If the Load DWH job fails due to missing views/tables from the Clarity database.
 
This is a mismatch between the PPM and DWH meta data.
 
If re-compiling the invalid objects on the DWH database does not work.This is due to missing objects on the DWH database.
If a refresh from one environment to another and the PPM/DWH database pair does not match.This is mismatch between the PPM and DWH meta data.


 

Cause:

There is a mismatch between the PPM and DWH database meta data which causes the Load Data Warehouse job to fail upon the first mismatch.
The resolution is to resynchronize the databases either by running the full Load DWH or rebuilding the DWH.
 

Resolution:

Solution 1: Run the full Load DWH job

When any of the following have taken place
-an incremental Load DWH job fails
-a new language is added in the data warehouse
-an entity for fiscal time periods is changed in the data warehouse.
-time reporting periods have been added or made active/inactive
-a data warehouse time slice is changed to include a larger time frame.
-an attribute is deleted or unselected from the data warehouse via Administration > Studio.
-an attribute data type is changed

Solution 2: Rebuild the DWH database

To rebuild the DWH schema, schedule for the following steps:

A. Locate the out-of-the-box database file for the corresponding database vendor, which can be found in the PPM_INSTALL\database\backups folder.

The Oracle version of the file: dwh_oracle_base.db can be imported via the Oracle datapump utility.
The MSSQL version of the file: dwh_mssql_base.db can be restored via the MS SQL Server Management Studio.

Note: For SaaS customers, a blank DWH schema matching the PPM schema will be imported

B. After restoring the DWH database, run the command on the Clarity application server to have the PPM and DWH versions match.
admin db dwh-upgrade -Dupgrade.phase=maintenance

C. Run the following (3) commands:

admin update jasperParameters

admin content-jaspersoft csk restoreDomains -userName superuser -password superuser

admin jaspersoft syncPPMContext -userName superuser -password superuser

D. Rebuild the database link from the DWH database to ensure the connection from the DWH to Clarity PPM Database is successful.
 

Example of when the dblink needs to be reviewed by the DBA team:

2018/12/13 20:45:02 - Slices - Couldn't execute SQL: BEGIN 
2018/12/13 20:45:02 - Slices - DWH_SEC_RIGHT_LOAD(P_DBLINK => 'aaaaaDWH_aaaaa', 
2018/12/13 20:45:02 - Slices - P_ARRAY_SIZE => 50000); 
2018/12/13 20:45:02 - Slices - END; 
2018/12/13 20:45:02 - Slices - 
2018/12/13 20:45:02 - Slices - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_SEC_RIGHT. SQLERRM : ORA-02019: connection description for remote database not found 
2018/12/13 20:45:02 - Slices - ORA-06512: at "aaaaaDWH.DWH_SEC_RIGHT_LOAD", line 42

E. The following jobs need to be ran and completed in the following order to repopulate the DWH database tables.

-Create and Update Jaspersoft Users
-Load DWH Access Rights
-Update Report Tables
-Load DWH full