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
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.
Release : 15.8.1
Component : CA PPM SAAS APPLICATION
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