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

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• Clarity PPM On Premise

Issue/Introduction

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)

 

Environment

Release: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment
Component:

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