CA PPM Load Data warehouse rights job fail with error DWH_SEC_LOAD_DATE": invalid identifier

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

CA PPM Load Data warehouse rights job fail with error DWH_SEC_LOAD_DATE": invalid identifier

Now look at the BG-DWH.log and you will find below 

 

ERROR 2016-12-09 09:19:37,574 [Thread-15516] dwh.event dwh_sec_load_exec_oracle - Errors detected!

ERROR 2016-12-09 09:19:37,578 [Thread-15516] dwh.event dwh_sec_load_exec_oracle - Errors detected!

ERROR 2016-12-09 09:19:37,611 [Dispatch Load Data Warehouse Access Rights 0912 : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below: 

2016/12/09 09:19:37 - Update Security Load Date.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error

2016/12/09 09:19:37 - Update Security Load Date.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : org.pentaho.di.core.exception.KettleStepException: 

2016/12/09 09:19:37 - Update Security Load Date.0 - Error while running this step!

2016/12/09 09:19:37 - Update Security Load Date.0 - 

2016/12/09 09:19:37 - Update Security Load Date.0 - Couldn't execute SQL: UPDATE dwh_cfg_settings

2016/12/09 09:19:37 - Update Security Load Date.0 - SET    dwh_sec_load_date = TO_DATE('2016/12/09 09:19:37', 'yyyy/mm/dd HH24:MI:SS')

2016/12/09 09:19:37 - Update Security Load Date.0 - WHERE  1=1

2016/12/09 09:19:37 - Update Security Load Date.0 - 

2016/12/09 09:19:37 - Update Security Load Date.0 - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "DWH_SEC_LOAD_DATE": invalid identifier

Environment

CA PPM 14.2 and above

Cause

The reason for this error is DWH schema under table dwh_cfg_settings a column DWH_SEC_LOAD_DATE is missing and this can happen due to incorrect upgrade when DWH schema was not upgraded properly

Resolution

1. Drop the DWH Schema 
2. Import the DWH Schema dump provided with CA PPM ISO 
3. Run the command admin db dwh-upgrade -Dupgrade.phase=maintenance 

The command will upgrade the DWH schema to the PPM Patch level.

 

Once you do the above run the Load Data warehouse rights Job and also the Load Data warehouse Job as blank DWH will not have the data in DWH Schema.