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

Document ID : KB000106147
Last Modified Date : 10/10/2018
Show Technical Document Details
Issue:
For MSSQL database, the error is:
[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.'

For Oracle database, the error is:
[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 "CLARITY151DWH.DWH_CMN_PERIOD_MAPPING_LOAD", line 43

 
Environment:
15.1 and 15.2
Cause:

Reported as defect DE34579, there is duplication/overlapping time periods that exists in the RPT_CALENDAR table

Resolved in 15.3

 

 

Resolution:

1. Have the DBA team truncate the RPT_CALENDAR table.
2. Run the Update Report Tables job.
3. Run the full Load DWH job