When running the full load, one of the following error appears in the BG DWH log:
Error for table 'DWH_X_RES_AVAIL_PER_FACTS' - Error Number 1505
'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_X_RES_AVAIL_PER_FACTS' and the index name
'DWH_X_RES_AVAIL_PER_FACTS_U1'. The duplicate key value is (xxxxxx, xxxxxxx).'
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_RES_AVAIL_PER_FACTS. SQLERRM : ORA-00001: unique constraint (PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_U1) violated
ORA-06512: at "PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_LOAD", line 110
ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_PERIOD. duplicate key value violates unique constraint "dwh_cmn_period_u1"
Where: PL/pgSQL function inline_code_block line 23 at RAISE
Check if the weekly or monthly periods are not all starting on the same day
on the RPT_CALENDAR table.
Example: The following shows overlap/duplication:
If so, have the DBA truncate the table.
If you note the periods are set up wrong, you have to set up the DWH time slices by the Best practices on DWH Time Slices
1. Once you complete the DWH Time Slices setup run Time Slicing job to completion
2. Ensure Update Report Tables job is paused
3. Truncate table RPT_CALENDAR
4. Run Update Report Tables job with all options
5. Run Load Data Warehouse - Full Load