When to rebuild the Data Warehouse (DWH) database

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job - Full Load fails - when do you rebuild the DWH, and what are the steps to rebuild?

Environment

Release: All Supported Releases
Component: Clarity Data Warehouse

Cause

There is a mismatch between the Clarity and DWH database meta data which causes the Load Data Warehouse job to fail upon the first mismatch.
After running the Full Load, if it does not work, the proposed solution is to rebuild the DWH.
To avoid having to rebuild the DWH due to a missing view or table, see KB: Load DWH Full - When to run to prevent failures that lists changes that require the Load DWH Full job be run first before running the incremental

Resolution

When to rebuild the DWH

Clarity and DWH database versions do not match

Run the following queries to find out if the database versions match:

Clarity: select * from cmn_install_history order by installed_date desc
DWH: select * from cmn_dwh_install_history order by installed_date desc

The Load DWH job fails due to missing views/tables from the Clarity database

This is a mismatch between the Clarity and DWH meta data.

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

This is due to missing objects on the DWH database.

Examples:

--
EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';
DWH_CFG_PRE_CONFIG_SP(P_DBLINK => 'DWHdblink_Clarity', P_FULL_RELOAD => 'Y');
DWH_CFG_RECOMPILE_SP;
END;

--
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
ORA-06512: at "PPM_DWH.DWH_CFG_PRE_CONFIG_SP", line 43
ORA-06512: at line 3 -- 2022/10/27 10:36:02 - META_LOAD_TABS_SQL - Invalid column name 'USE_CREATED_DATE'. 2022/10/27 10:36:02 - META_LOAD_TABS_SQL - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry : 2022/10/27 10:36:02 - META_LOAD_TABS_SQL - Couldn't execute SQL: MERGE INTO DWH_META_TABLES G

After a refresh from one environment to another, the Clarity / DWH DB pair do not match

This is a mismatch between the Clarity and DWH meta data.

The above are indications that a DWH database rebuild may be necessary:

ATTENTION: If you drop the current DWH schema, and you have been using Trending, you may lose the entire Trending historical data. You have to back up all Trending tables before proceeding!

How to rebuild the DWH schema

If using trending data, backup the trend DB tables
Locate the out-of-the-box database file which can be found in the CLARITY_INSTALL\database\backups folder. Ask your DBA to restore the corresponding version.

a. DBA will need to be given the out-of-the-box database file, depending on the database vendor:

Oracle: dwh_oracle_base.db
MSSQL: dwh_mssql_base.db
PostgreSQL: dwh_postgres_base.db

b. The DBA will drop the DWH schema and recreate it.

c. The DBA will import/restore the database file
ONLY if you have a patch applied in Clarity (else skip to step 4): After restoring the DWH database, run the command on the Clarity application server to have the patch from Clarity apply to Data Warehouse:

admin db dwh-upgrade -Dupgrade.phase=maintenanceIf you are on a base version and do not have patches DO NOT run this command
Recreate the Data Warehouse database link to ensure the connection from the Data Warehouse to the Clarity database is successful. Check Recreating the DWH DBLINK for more information
If you have trend data, replace the backed up trend tabled in the newly imported schema

Notes:

The following jobs need to be run and completed to repopulate the DWH database tables after recreating them:
- Load Data Warehouse - Access Rights
- Load Data Warehouse - Full Load
If when rebuilding the Data Warehouse database you modified the database schema and connection details, and the DWH connection is no longer the same, then run the Jaspersoft commands to update the DWH connection information in Jaspersoft:
(ONLY if you change the DWH schema name and connection details, run the following commands):

admin content-jaspersoft csk restoreDomains -userName superuser -password superuser

admin jaspersoft syncPPMContext -userName superuser -password superuserIf the Data Warehouse schema name and connection details are the same and you are just replacing the existing schema you do not have to run this.

Additional Information

If the above doesn't help, see: