ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with error : "DWH_INV_STATUS_REPORT is not existing in database"

book

Article ID: 46080

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) Job fails with an error message that  DWH_INV_STATUS_REPORT does not exist in the database.

Full error message below:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist ORA-06512: at "PPM_DWH.DWH_CFG_ADDIN_EXTRAS_SP", line 24 

DWH_INV_STATUS_REPORT is not existing in database 

EXECUTE IMMEDIATE 'CREATE VIEW DWH_INV_STATUS_REPORT_LATEST_V AS SELECT I.INVESTMENT_KEY, R.CLARITY_STATUS_REPORT_KEY STATUS_REPORT_KEY, ' || 
'ROW_NUMBER() OVER (PARTITION BY R.INVESTMENT_KEY ORDER BY R.REPORT_DATE DESC, R.CLARITY_STATUS_REPORT_KEY DESC) REPORT_ORDER ' || 
'FROM DWH_INV_INVESTMENT I INNER JOIN DWH_INV_STATUS_REPORT R ON I.INVESTMENT_KEY = R.INVESTMENT_KEY ' || 
'WHERE NVL(R.SR_REPORT_STATUS_KEY,''FINAL'') = ''FINAL''''FINAL''''FINAL'''

Cause

The Status Report object must be included in the Data Warehouse (at least during the first successful run of the Load Data Warehouse job in the environment). 

Environment

All supported environments for Clarity 14.2 and higher.

Resolution

Include the Status Report in the Data Warehouse: 

  1. In Clarity go to Administration->Objects->Status Report
  2. Check the option 'Include in the Data Warehouse' and click 'Save '
  3. Rerun the Load Data Warehouse job in full mode

Additional Information

See also Data Warehouse (DWH) known issues