CA PPM: When to rebuild the Data Warehouse database

Document ID:  TEC1889227
Last Modified Date:  08/03/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 between versions of PPM and DWH

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 db vendor:
dwh_oracle_base.db and import via the Oracle datapump utility.
dwh_mssql_base.db and restore via MS SQL Server Management Studio.

b.Run the command on the PPM application server to have it match the version PPM:
admin db dwh-upgrade -Dupgrade.phase=maintenance








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 >