DWH job fails after including a custom investment with "relation "dwh_odf_test_track_02" does not exist"
search cancel

DWH job fails after including a custom investment with "relation "dwh_odf_test_track_02" does not exist"

book

Article ID: 215760

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Multiple Data Warehouse (DWH) tables created for a single object, DWH job fails. After including a custom investment type object in DWH, the job (Full and Incremental) is failing with error "relation "dwh_odf_test_track_02" does not exist". 

STEPS TO REPRODUCE:

  1. Create a Custom Investment (Investment Extension) with object code = test_track
  2. Include the object in DWH
  3. Add an attribute with Lookup String as Data Type. Pick Work Status as Lookup and Save
  4. Run Load Data Warehouse - Full Load or Incremental Load

Expected Results: The job to run correctly

Actual Results: The Load Data Warehouse fails with the below error: 

org.pentaho.di.core.exception.KettleStepException: Error while running this step!Couldn't execute SQL: ALTER TABLE DWH_ODF_TEST_TRACK_02 ADD TEST_KEY VARCHAR(255)ERROR: relation "dwh_odf_test_track_02" does not exist    at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)              at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)              at java.base/java.lang.Thread.run(Thread.java:834)Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Couldn't execute SQL: ALTER TABLE DWH_ODF_TEST_TRACK_02 ADD TEST_KEY VARCHAR(255)ERROR: relation "dwh_odf_test_track_02" does not exist    

Checking, you notice that object TEST_TRACK is requiring a few different tables in DWH:

  • dwh_odf_test_track_00
  • dwh_odf_test_track_01
  • dwh_odf_test_track_02
  • dwh_odf_test_track_02_ln

Additionally, you also notice dwh_odf_test_track_02 is missing in DWH_META_TABLES

Unchecking the object DOES NOT WORK to remove it and DOES NOT flip the is_deleted flag in DWH_META_TABLES and DWH_META_COLUMNS

Environment

Release : 15.9.1+, 16.0

Component : CLARITY DATA WAREHOUSE

Resolution

This is DE61093, fixed in 16.0.1

Workaround:

  1. Uncheck the object from DWH, this will not work to remove the columns/fields.
  2. Now manually delete all the fields for it in DWH_META_TABLES and DWH_META_COLUMNS
  3. Run a Full Load of DWH job

Additional Information

Reference also:

Powered by Wolken Software