Clarity PPM: Load DWH job fails due to duplication in DWH_TME_SHEET

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The full Load DWH job started to fail with no known STR.

Error is similar to: 

ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_TME_SHEET. duplicate key value violates unique constraint "dwh_tme_sheet_pkey"

Environment

Clarity 16.3.2 Data Warehouse 

Cause

There are duplicates in the PRCALENDAR table at the time of the job run.

Run query to confirm:
select * from prcalendar where prid in (
select r.prid from prcalendar r, prj_resources p where r.PRRESOURCEID in( select PRRESOURCEID
from prcalendar
group by PRRESOURCEID
having count(PRRESOURCEID) > 1 )
and r.prresourceid =p.prid
and p.prcalendarid <> r.prid);

Resolution

Run this query on the ppm schema to verify duplicates:

  select count(timesheet_key),timesheet_key
  from dwh_timesheet_v
  group by timesheet_key
  having count(timesheet_key) > 1

Choose one of the records returned to see more details and use the timesheet_key in this query

select  * from
prtimesheet prtimesheet
left join srm_resources srmresourcesub on
 prtimesheet.prsubmittedby = srmresourcesub.user_id
left join srm_resources srmresourceapp on
 prtimesheet.prapprovedby = srmresourceapp.user_id
left join prcalendar pr_cal on
 prtimesheet.prresourceid = pr_cal.prresourceid,
 dwh_timesheet_timeperiod_v dwh_timesheet_timeperiod_v,
 srm_resources srm_resources
left join srm_resources manager on
 manager.user_id = srm_resources.manager_id,
 cmn_languages dataw_context
where
 1 = 1
 and prtimesheet.prtimeperiodid = dwh_timesheet_timeperiod_v.prid
 and prtimesheet.prresourceid = srm_resources.id
 and dataw_context.is_dw_enabled = 1
 and prtimesheet.prid = <Timesheet_key>

 

Run this select statement to find the duplicates in the calendar:

select count(prresourceid), prresourceid from prcalendar
    group by prresourceid 
    having count(prresourceid) > 1

If records are returned the duplicate entries will need to be removed.

Contact support for steps to remove.