Full Load DWH job fails with error on DWH_INV_TASK

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Time slices were adjusted/reconfigured. When the full run run, it fails with the error in the BG-DWH logs:

ERROR 2020-08-17 06:26:58,174 [dwh_etl_master_pg UUID: xxxxxx] dwh.event set variables - oracle - An error occurred executing this job entry :
Couldn't execute SQL: DO $$
BEGIN
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_CMN_PERIOD');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_BENEFIT_PLAN');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_PLAN');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_ASSIGNMENT');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_INVESTMENT');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TASK');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TEAM');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_RES_RESOURCE');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_SHEET');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_ENTRY');
CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_PERIOD');

UPDATE DWH_CFG_AUDIT SET DW_LOAD_START_DATE = (SELECT LOCALTIMESTAMP FROM DUAL) WHERE TABLE_NAME = 'DWH_INV_TASK_HIERARCHY';
CALL DWH_INV_TASK_HIERARCHY_SP();
UPDATE DWH_CFG_AUDIT SET DW_UPDATED_DATE = (SELECT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS),
DW_LOAD_END_DATE = (SELECT LOCALTIMESTAMP FROM DUAL) WHERE TABLE_NAME = 'DWH_INV_TASK_HIERARCHY';

UPDATE DWH_CFG_AUDIT SET DW_LOAD_START_DATE = (SELECT LOCALTIMESTAMP FROM DUAL) WHERE TABLE_NAME = 'DWH_CMN_PERIOD_MAPPING';
CALL DWH_CMN_PERIOD_MAPPING_LOAD();
UPDATE DWH_CFG_AUDIT SET DW_UPDATED_DATE = (SELECT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS),
DW_LOAD_END_DATE = (SELECT LOCALTIMESTAMP FROM DUAL) WHERE TABLE_NAME = 'DWH_CMN_PERIOD_MAPPING';

IF 'Y' = 'N' THEN
CALL DWH_INV_MISC_PPM_UPDATES_SP();
END IF;
END $$;

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_PERIOD_MAPPING. more than one row returned by a subquery used as an expression
Where: PL/pgSQL function dwh_cmn_period_mapping_load() line 36 at RAISE
SQL statement "CALL DWH_CMN_PERIOD_MAPPING_LOAD()"
PL/pgSQL function inline_code_block line 21 at CALL

Cause

There are time slice IDs that do not start on a uniform date.
There are overlapping time periods generated in the time period views.

Resolution

Solution 1

Run queries to find out the time slice IDs having issues

Query 1

select
sr.id, sr.request_name, TO_CHAR(SR.FROM_DATE, 'MM-DD-YYYY HH24:MI:SS AM Dy')
from PRJ_BLB_SLICEREQUESTS SR
where SR.IS_DWH_REQUEST = 1
and SR.PERIOD = 1 --weekly
order by SR.id

Query 2:

select * from rpt_calendar where period_type = 'WEEKLY' order by start_date

Have the slice IDs uniformly start on the same day/date

Go to Administration > Time Slices page to change the from dates of the identified time slice ids.
Run the following jobs:
- Time Slicing
- Update Report Tables with all parameters selected.
- Full Load DWH

Solution 2:

Run queries to find which periods are affected per the error in the BG-DWH log

select * from dwh_cmn_period_v
WHERE period_type_key = 'WEEKLY'
ORDER BY period_start_date