Load DWH job fails - error in DWH_CMN_PERIOD_MAPPING table

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) 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.'

Postgres:

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

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)

 

Environment

Release: All Supported
Component: Clarity Load Data Warehouse

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

Additional Information

Check also to make sure the First day of the week matches for the below

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