When to Rebuild the Data Warehouse DWH database

book

Article ID: 4413

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

  • Load Data Warehouse job - Full Load fails, with one or more of the following symptoms.
  • Indications that data warehouse database rebuild may be necessary:
     
ISSUE  
Clarity PPM and DWH database versions do not match Run the following queries to find out if the 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
If the Load DWH job fails due to missing views/tables from the Clarity database.
 

This is a mismatch between the PPM 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.

Example:
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
If a refresh from one environment to another and the PPM/DWH database pair does not match.

This is mismatch between the PPM and DWH meta data.

 

Cause

  • There is a mismatch between the PPM 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 the following KB that lists changes that require the Load DWH Full job be run first before running the incremental: KB: 129460 

 

 

Environment

Release: All Supported Releases
Component: PPMDWH

Resolution

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!

To rebuild the DWH schema:

  1. If using trending data, backup the trend DB tables
  2. Locate the out-of-the-box database file which can be found in the $clarity\database\backups folder.
    • Ask your DBA to restore the corresponding version.
  3. ONLY if you have a patch applied in Clarity:
    • 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=maintenance
    • If you are on base version and do not have patches DO NOT run this command
  4. Recreate the Data Warehouse database link to ensure the connection from the Data Warehouse to Clarity PPM transactional database is successful.
    • Example of when the dblink needs to be reviewed and recreated by the DBA team:

--Oracle
2018/12/13 20:45:02 - Slices - Couldn't execute SQL: BEGIN 
2018/12/13 20:45:02 - Slices - DWH_SEC_RIGHT_LOAD(P_DBLINK => 'aaaaaDWH_aaaaa', 
2018/12/13 20:45:02 - Slices - P_ARRAY_SIZE => 50000); 
2018/12/13 20:45:02 - Slices - END; 
2018/12/13 20:45:02 - Slices - 
2018/12/13 20:45:02 - Slices - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_SEC_RIGHT. SQLERRM : ORA-02019: connection description for remote database not found 
2018/12/13 20:45:02 - Slices - ORA-06512: at "PPM_DWH.DWH_SEC_RIGHT_LOAD", line 42

--PostgreSQL
2020/04/29 19:12:52 - postgres? -   Where: SQL statement "IMPORT FOREIGN SCHEMA gpus110017t EXCEPT (dual, cmn_db_history, dwh_cfg_object_type, prcounter, crv_sql_curves) FROM SERVER ppmdblink INTO serverxxxxxxdwh"
2020/04/29 19:12:52 - postgres? - PL/pgSQL function dwh_cfg_foreign_tables_sp() line 27 at EXECUTE

     5. If you have trend data, replace the backed up trend tabled in the newly imported schema 

Notes:

  1. The following jobs need to be ran and completed to repopulate the DWH database tables after recreating it:
    • 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 superuser

    • If the Data Warehouse schema name and connection details are the same and you are just replacing the existing schema you don't have to run this.

Additional Information

If the above doesn't help, see 196962 - How to troubleshoot Load DWH Job Failures

Powered by Wolken Software