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