Clarity PPM: Datamart Rollup (DMR) job fails with constraint NBI_RT_FACTS_PK violated error

Products

• Clarity PPM On Premise

Issue/Introduction

The Datamart Rollup job fails with one of the following errors:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Executing NBI_EXTR_PCF_SP: ORA-20000: Error in NBI_EXTR_PCF_SP - executing NBI_PROJECT_CURRENT_FACTS_SP: ORA-20000: Error in NBI_PROJECT_CURRENT_FACTS_SP - Calling NBI_PRTF_FM_SP since FM is enabled: ORA-01403: no data found

Datamart Rollup - Time Facts and Time Summary) Error executing job: 5XXXXX error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in CMN_JOB_DATAMART_ROLLUP_SP - ORA-20000: Error in NBI_EXTRACT_TF_TS_SP - Rolling up NBI_RT_FACTS: ORA-20000: Error in NBI_RTF - Processing Rollup SQL and insert into summary table: ORA-00001: unique constraint (schema.NBI_RT_FACTS_PK) violated

Error executing job: 5XXXXX com.niku.union.persistence.PersistenceException: SQL error code: 0 Error message: ERROR: Error in CMN_JOB_DATAMART_ROLLUP_SP - Error in NBI_EXTRACT_TF_TS_SP - :Rolling up NBI_RT_FACTS: Error in NBI_RTF - during Inserting of records into NBI_RT_FACTS: duplicate key value violates unique constraint "nbi_rt_facts_pk"
 

Environment

Release: All Supported Releases

Cause

The job will fail if it cannot produce the data for the amount of monthly fiscal time periods created in
Administration > Finance > Setup > Entity > Fiscal time periods

Run the following queries to check

--a daily resource time slices setup
select 'TS SETUP', id, request_name, from_date, num_periods, to_date, request_completed_date, expiration_date
from PRJ_BLB_SLICEREQUESTS
where id in (1,2,3,10,11)
order by id

--b min/max from daily time slices
SELECT 'TS MIN/MAX', MIN(FROM_DATE), MAX(TO_DATE)
FROM PRJ_BLB_SLICEREQUESTS
where id in (1,2,3,10,11)

--c get fiscal time periods
SELECT * FROM BIZ_COM_PERIODS
WHERE PERIOD_TYPE = 'MONTHLY'
order by start_date

SELECT 'FISCAL MIN/MAX' MONTH, min(start_date), max(end_date)
FROM BIZ_COM_PERIODS
WHERE PERIOD_TYPE = 'MONTHLY'

 

Resolution

This is a configuration issue, therefore if the DAILY resource time slices do not cover the monthly fiscal time range end date, then perform the following:
 

1. Pause the Time Slicing job when it is in the WAITING or SCHEDULED state only.

2. Set the following configuration:

id = 1, set number of periods = 730
id= 2, set number of periods = 730
id= 3, set number of periods = 730
id = 10, set number of periods = 730
id = 11, set number of periods = 730

3. Resume/Unpause the Time Slicing job and let it complete.

4. Run the Datamart Extraction job.

5. Run the Datamart Rollup job.

 

Additional Information

https://communities.ca.com/thread/241700657
CA PPM Tech Tip: How to check the Datamart (Datamart Extraction and Datamart Rollup jobs)