Load DWH incremental failure

Products

• Clarity PPM SaaS

Issue/Introduction

All of a sudden the scheduled Load DWH incremental jobs are failing with errors like:

2024/05/13 17:51:34 - Misc Updates PG - ERROR: ENCOUNTERED EXCEPTION WHILE UPDATING DWH_INV_TEAM - 2. malformed array literal: "0"
2024/05/13 17:51:34 - Misc Updates PG -   Where: PL/pgSQL function dwh_inv_misc_ppm_updates_sp() line 59 at RAISE
2024/05/13 17:51:34 - Misc Updates PG - SQL statement "CALL DWH_INV_MISC_PPM_UPDATES_SP()"
2024/05/13 17:51:34 - Misc Updates PG - PL/pgSQL function inline_code_block line 26 at CALL
2024/05/13 17:51:34 - Misc Updates PG - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : An error occurred executing this job entry : 
2024/05/13 17:51:34 - Misc Updates PG - Couldn't execute SQL: DO $$
2024/05/13 17:51:34 - Misc Updates PG - BEGIN
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_CMN_PERIOD');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_BENEFIT_PLAN');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_PLAN');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_ASSIGNMENT');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_INVESTMENT');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TASK');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TEAM');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_RES_RESOURCE');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_SHEET');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_ENTRY');
2024/05/13 17:51:34 - Misc Updates PG -     CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_PERIOD');

Resolution

Please proceed as follows:

1. Delete the scheduled Load DWH job instance

2. Run a full Load DWH job instance manually

3. Run an incremental Load DWH job instance manually

4. Reset the deleted incremental Load DWH job instance

If the incremental still fails, please rebuild the DWH schema:

When to rebuild the Data Warehouse (DWH) database