CA PPM: Load DWH job fails with error in DWH_CMN_PERIOD_MAPPING table

Document ID : KB000106147
Last Modified Date : 04/02/2019
Show Technical Document Details
Issue:
Load Data Warehouse job fails with error:
Oracle:
[Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_PERIOD_MAPPING. SQLERRM : ORA-01427: single-row subquery returns more than one row
ORA-06512: at "DWH_CMN_PERIOD_MAPPING_LOAD", line 43

MSSQL:
[dwh_mssql_error_handling UUID: 7071a63f-2e06-4e91-8050-4b0b6e9e6d62] dwh.event - Error for table 'DWH_CMN_PERIOD_MAPPING' - Error Number 512 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'



 
Cause:

Duplicate entries in the periods in RPT_CALENDAR table.

They may be caused by many different issues:
1. BG restart when Update Reports Table job is still running
2. Cancelled the Update Reports Table job, ran another one again / or Ran two jobs very close to each other
3. First day of the week does not match correctly the day in reports (Administration - Project Settings - First day of the week and Administration - Time Slices - Enabled for DWH weekly slices should start on the same first day) I.e. Monday and Monday.
4. DE34579 - defect resolved in 15.3 causing duplication in RPT_CALENDAR due to overlap in Fiscal Periods on the entity
5. Others, such as the start date on the Monthly Time Slice Requests for Data Warehouse (Should be the same on all DWH requests and should be set to the 1st of the month)

 

Resolution:

1. Ask the DBA team truncate the RPT_CALENDAR table. SQL query:
truncate table RPT_CALENDAR
2. Run the Update Report Tables job upon completion. This will fully repopulate the RPT_CALENDAR table from scratch
3. Run the Load Data Warehouse - Full Load