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

Document ID : KB000004413
Last Modified Date : 04/08/2018
Show Technical Document Details
Issue:

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.

--PPM
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
from DWH_META_COLUMNS a
order by 1,2

--DWH
select
b.dwh_table, b.dwh_column, b.attribute_type, b.attribute_data_type
from DWH_META_COLUMNS b
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.

Environment:
CA PPM 14.2+
Cause:

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.

 

Resolution:

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. Rebuild the dblink if required

d. 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?
https://docops.ca.com/ca-ppm/15-3/en/installing-and-upgrading/install-ca-ppm/install-and-configure-the-data-warehouse

The Restore Domains Job
https://docops.ca.com/ca-ppm/15-3/en/reference/ca-ppm-jobs-reference#CAPPMJobsReference-RestoreDomainsJob