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

Document ID:  TEC1889227
Last Modified Date:  11/09/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA PPM

Releases

  • CA PPM:Release:15.1
  • CA PPM:Release:14.4
  • CA PPM:Release:14.3

Components

  • CLARITY PPM INTEGRATIONS & INSTALLATIONS:PPMENV
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.

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:

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







Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >