Load Data Warehouse job - Full Load fails, with one or more of the following symptoms.
Indications that data warehouse database rebuild may be necessary:
|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.
EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';
DWH_CFG_PRE_CONFIG_SP(P_DBLINK => 'DWHdblink_Clarity', P_FULL_RELOAD => 'Y');
[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.
Release: All Supported Releases
Rebuild the DWH database
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, schedule for the following steps:
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:
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
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
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.