The Datamart Rollup job is failing with error '...Building string of weeks that need to be rolled up again'

Document ID : KB000025841
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:  

The 'Datamart Rollup - Time Facts and Time Summary' job is failing with following error (shown on the job log or shown in the background log file - bg-niku.log):

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
Error in
Caused by: java.sql.SQLException: ORA-20000: CMN_JOB_DATAMART_ROLLUP_SP - ORA-20000: Error in NBI_EXTRACT_TF_TS_SP -
Rolling up NBI_RESOURCE_TIME_SUMMARY: ORA-20000: Error in NBI_RESOURCE_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

Note: these are Oracle-specific error messages, however, this issue can occur in an Microsoft SQL database too with similar error messages)

Cause:  

The NBI_DIM_CALENDAR time table has too many years worth of data in the table; the dates in the table are too old. The minimum date in the table should be equal to or greater than 2000.

Resolution:  

CLRT-56120 Resolved in CA Clarity PPM 13.2

Although this was resolved as a 'fix', the existing data must be removed and some customers may still experience this issue in later releases.  In the event of experiencing this issue, please use the steps below to resolve the issue.

 

The SQL statements below can be used for either Oracle or Microsoft SQL databases.
Be sure to backup your data and first test in a development environment.  
Services need to be stopped to ensure that the table is not being accessed during this operation.
  1. Stop Application and Background Services   
  2. Check the records in NBI_DIM_CALENDAR_TIME table 
    Using your preferred query analyzing tool, connect to the database and run the following query :  

    SELECT min(year) FROM NBI_DIM_CALENDAR_TIME ;

    If the date is less than 2000, then the solution is to delete all the rows for any year less than the year 2000 from that table as stated in step 2, below 

  3. Run the SQL  

    DELETE FROM NBI_DIM_CALENDAR_TIME WHERE YEAR < 2000 ;   

    Commit ;  

  4. Restart Application and Background Services
  5. Run the 'Datamart Extraction' job 
  6. Run the 'Datamart Rollup Time Facts and Time Summary' job