Full load Data Warehouse failed on trying to add a unique index on a table based on a custom object

Products

• Clarity PPM SaaS

Issue/Introduction

It is failing on this table "odf_ca_bc1", when trying to build a unique index.

However, there are rows for returned for French and English and the constraint is mistakenly seeing the id's as duplicates.

So, the UNIQUE index creation fails.

This happened after enabling 

2022/06/03 07:58:53 - User Defined Java Class.0 - *****************************************************************
2022/06/03 07:58:53 - User Defined Java Class.0 -  
2022/06/03 07:58:53 - User Defined Java Class.0 -  STARTING EXECUTION FOR THE DIMENSION TABLE - DWH_ODF_BC1
2022/06/03 07:58:53 - User Defined Java Class.0 -  
2022/06/03 07:58:53 - User Defined Java Class.0 - *****************************************************************
2022/06/03 07:58:54 - User Defined Java Class.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unexpected error
2022/06/03 07:58:54 - User Defined Java Class.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_ODF_BC1
2022/06/03 07:58:54 - User Defined Java Class.0 -  at Processor.processRow(Unknown Source)
2022/06/03 07:58:54 - User Defined Java Class.0 -  at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1019)2022/06/03 07:58:54 - User Defined Java Class.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2022/06/03 07:58:54 - User Defined Java Class.0 -  at java.base/java.lang.Thread.run(Thread.java:829)
2022/06/03 07:58:54 - dwh_etl_interface_dim_pg - Transformation detected one or more steps with errors.
2022/06/03 07:58:54 - dwh_etl_interface_dim_pg - Transformation is killing the other steps!
2022/06/03 07:58:54 - dwh_etl_interface_dim_pg - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 
2020-09-07 05.09.05 by buildguy) : Errors detected!null

Environment

Release : 16.0.1

Component : Clarity Data Warehouse

Resolution

We disabled French in System Options > Data Warehouse settings.

 

If we need to enable French for the DWH in the future, we will need to modify the lookups.

Here is an example:

 

Rules for Including Language Translations in the Data Warehouse
Not all query tables have language translations associated with their data. Hence, adding language translations is optional for using a query lookup with the Data Warehouse. Use the following rules to include translations for query lookups in the Data Warehouse:

• Always include a select column named LANGUAGE_CODE
• Always include a select column named LANGUAGE_ID
• Always include the following statement in the WHERE clause of the Data Warehouse to recognize the language translations available in the query lookup:

@WHERE:PARAM:LANGUAGE@

Example: The following dynamic query lookup applies the correct rules for defining the last updated date and the language translations:

SELECT @SELECT:a.name:country_name@,

@SELECT:c.currency_code:currency_code@,

@SELECT:c.currency_code || ' (' || c.currency_symbol || ')':currency_name@,

@SELECT:c.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,

@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,

@SELECT:LANG.ID:LANGUAGE_ID@

FROM cmn_countries b,

cmn_captions_nls a,

cmn_currencies c,

CMN_LANGUAGES LANG

WHERE a.table_name = 'CMN_COUNTRIES'

AND @FILTER@

AND a.pk_id = b.id

AND a.language_code = @WHERE:PARAM:LANGUAGE@

AND b.currency_id = c.id

AND LANG.LANGUAGE_CODE=a.LANGUAGE_CODE