Datamart Rollup job is failing with 'exact fetch returns more than requested number of rows' error.

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Datamart Rollup - Time Facts and Time Summary job failed with the following error message:

"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 - Determine current fiscal period: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at ".CMN_JOB_DATAMART_ROLLUP_SP", line 11 ORA-06512: at line 1".

Environment

Applies to all supported PAS environments

Cause

Manual alteration of Fiscal time periods (retrospectively) had resulted in an overlapping timeframe where a single "current period" could not be determined by the rollup stored procedure.

The information in the NBI_DIM_FISCAL_TIME table is not getting updated properly. The Fiscal Time Periods entered by the user through the application are correct, however, the NBI_DIM_FISCAL_TIME table contains records with overlapping start and end period dates so when the Datamart executes the query in the stored procedure it is returning more than one record; which is not expected.

Upon reviewing the data between the source table BIZ_COM_PERIODS and the Datamart destination table NBI_DIM_FISCAL_TIME, we see extra rows in the NBI table that are no longer in the source table, thereby considered orphaned and hence are causing the error message because the correct record and the orphaned record is overlapping or has the same start or end dates causing the error message that there are more rows returned than expected.

Resolution

The NBI_DIM_FISCAL_TIME table needs to be truncated and the Datamart Job re-executed which will re-populate this table with the correct data as seen by the user in the application.

1. Make sure the Datamart Extraction and Datamart Rollup jobs are not in a 'Processing' state.  Pause both job schedules so they do not start.
2. Make a backup of the NBI_DIM_FISCAL_TIME table or perform a full database backup
3. Truncate the Datamart Fiscal Time Period table  
4. From a SQL Tool execute the following statement:  TRUNCATE TABLE NBI_DIM_FISCAL_TIME;  
5. Be sure the update is committed
6. Login to the application and execute the 'Datamart Extraction' Job to repopulate the NBI_DIM_FISCAL_TIME table
7. Execute the 'Datamart Rollup' job and it should now be successful
8. Resume the previous job schedules that were paused