Clarity PPM: Full Load DWH Job Fails With Error on DWH_INV_TASK_HIERARCHY

Products

• Clarity PPM SaaS

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

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

Environment

Release : 15.8.1

Component : CA PPM SAAS APPLICATION

Resolution

Solution 1

A. Run queries to find out the time slice IDs having issues:

--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

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

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

1. Go to Administration > Time Slices page to change the from dates of the identified time slice ids.

2. Run the following jobs:
a. Time Slicing
b. Update Report Tables with all parameters selected.
c. full Load DWH

Solution 2:

1. 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 

2. Review the results for overlapping periods to truncate the RPT_CALENDAR table