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 Data Warehouse Incremental and Full Failing After Upgrade to 16.0.1

book

Article ID: 236853

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

The error in the BG DWH log is:

ERROR 2022-03-14 08:00:31,633 [dwh_dim_has_records - Get Record Counts] dwh.event Get Record Counts - org.pentaho.di.core.exception.KettleDatabaseException: 
An error occurred executing SQL: 
select 
 (case when (src_record_count = dwh_record_count) then 0 else 1 end) as HAS_DELETED_RECORDS
from
 (
  select
   (
    SELECT COUNT(1) FROM
    (
     select distinct
      INVESTMENT_KEY as key_col, 1 as lang_col
     from 
      DWH_DW_INVESTMENT_V
    ) srctab
   ) as src_record_count,
   
   (
    SELECT COUNT(1) FROM
    (
     select distinct
      INVESTMENT_KEY as key_col, 1 as lang_col
     from 
      DWH_INV_INVESTMENT
    ) dwhtab
   ) as dwh_record_count
  from
   dwh_cfg_settings --just as a dummy table, because it always has one record
 ) t
 
ERROR: function obs_unit_full_formatted_path(bigint, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: remote SQL command: SELECT investment_key FROM schema.dwh_dw_investment_v
PL/pgSQL function schema.obs_unit_full_path(bigint) line 4 at RETURN

 at org.pentaho.di.core.database.Database.openQuery(Database.java:1772)
 at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:236)
 at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
 at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
 at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: ERROR: function obs_unit_full_formatted_path(bigint, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: remote SQL command: SELECT investment_key FROM schema.dwh_dw_investment_v
PL/pgSQL function sc52132p.obs_unit_full_path(bigint) line 4 at RETURN

Environment

Release : 16.0.1

 

Resolution

In a case study, the investment and roadmap item objects have a lookup attribute causing the issue.

The attribute is included in the DWH, which breaks the Load DWH incremental and full run.

It lookup uses NSQL that calls a Clarity function: OBS_UNIT_FULL_PATH
The NSQL eventually breaks the Load DWH job as the function calls another function(obs_unit_full_formatted_path)  that does not exist in the DWH database.

The only way to fix it is to NOT include the attribute in both objects.
Once excluded from the DWH, the job will run fine, a full run followed by incremental run cycle.