Clarity PPM: Datamart Rollup job is fails with error - Building string of weeks that need to be rolled up again'

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The job: Datamart Rollup - Time Facts and Time Summary
fails with one or more of the following errors:

Caused by: java.sql.SQLException: ORA-20000: Error in CMN_JOB_DATAMART_ROLLUP_SP - ORA-20000: Error in NBI_EXTRACT_TF_TS_SP - Rolling up NBI_PM_PROJECT_TIME_SUMMARY: ORA-20000: Error in NBI_PM_PROJECT_TIME_SUMMARY - Building string of weeks that need to be rolled up again: ORA-20000: Error in NBI_GET_ROLLUP_TIME_INFO_SP - Building string of weeks that need to be rolled up again: ORA-06502: PL/SQL: numeric or value error

or

Stored Procedure application 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 - Building string of weeks that need to be rolled up again: ORA-20000: Error in NBI_GET_ROLLUP_TIME_INFO_SP - Flip P_TIME_TAB upside down because it was loaded in reverse order: ORA-06502: PL/SQL: numeric or value error 
ORA-06512: at "schema.CMN_JOB_DATAMART_ROLLUP_SP", line 11 
ORA-06512: at line 1 

Environment

Release: All supported Clarity releases

Cause

There is an issue with the NBI_DIM_FISCAL_TIME table.

Resolution

Workaround 1: 

Run Datamart Extraction job to completion, this is meant to rebuild NBI_DIM_FISCAL_TIME table with any updated daily resource time slice ID configurations.

If the Datamart Extraction completes, rerun the Datamart Rollup job. This will typically fix the out of range/bounds situation, which can be confiirmed by running the statement:
select min(period_start_date) , max(period_end_date) from NBI_DIM_FISCAL_TIME

Workaround 2:

1. Make a backup of the NBI_DIM_FISCAL_TIME table.
2. Truncate this table.
3. Run the Datamart Extraction job.
4. Run the Datamart Rolllup job.