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

Error Stack in BG Log on Oracle Database

 [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".

Error Stack in BG Log on PostgresSQL Database

ERROR 2025-01-06 01:19:26,970 [Dispatch 1. Datamart Rollup - Time Facts and Time Summary : bg@clarity (tenant=clarity)] niku.njs (clarity:xxxx:XXXX-XX-XXX-XXX-XXX:1. Datamart Rollup - Time Facts and Time Summary) () Error executing job: 5529078
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 - :Determine current fiscal period: query returned more than one row
  Where: PL/pgSQL function cmn_job_datamart_rollup_sp(bigint,bigint) line 72 at RAISE
Executed:
call CMN_JOB_DATAMART_ROLLUP_SP ( 21790547, 5001003) 

Environment

Applies to all supported clarity version 

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. Pause Datamart Extraction and Datamart Rollup - Time Facts and Time Summary jobs 
2. Take a backup of the NBI_DIM_FISCAL_TIME table 
3. Execute the command to Truncate the Datamart Fiscal Time Period table  
   1. TRUNCATE TABLE NBI_DIM_FISCAL_TIME;  
4. Login to the Clarity and run the 'Datamart Extraction' Job to repopulate the NBI_DIM_FISCAL_TIME table
5. Run the 'Datamart Rollup - Time Facts and Time Summary' job and it should now be successful