CA PPM: When to rebuild the Data Warehouse (DWH) database

Document ID : KB000004413
Last Modified Date : 14/02/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.

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.


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.

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. 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