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

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

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

1. A mismatch/discrepancy between the versions of the PPM and DWH database.

Run the following queries to find out if the PPM and DWH 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

2. If the Load DWH job fails due to missing views/tables, it causes a mismatch between the PPM and DWH meta data.

select a.dwh_table_name, a.dwh_column_name,
a.attr_type, a.attr_data_type, a.attr_data_size, a.is_deleted, a.js_processed
order by 1,2

b.dwh_table, b.dwh_column, b.attribute_type, b.attribute_data_type
order by 1,2

3. If re-compiling the invalid objects on the DWH database does not work.

4. If a refresh from one environment to another and the PPM/DWH database pair does not match.

5. If an upgrade install was performed, the PPM version is upgraded, but not the DWH version.

CA PPM 14.2+

There is a mismatch in the meta data during the ETL Load Data Warehouse job which causes a mismatch between the PPM and DWH schemas.



Solution 1:

When any of the following have taken place, always run the full Load DWH job:
Run the full Load DWH with 'full' option if
-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:
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 PPM 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 the Clarity Database and make sure the connection 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


Additional Information:

When to run the full Load DWH job?

The Restore Domains Job