Datamart Extraction Job or Datamart Rollup Job Fails - No fiscal periods have been set up

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Datamart jobs are essential in populating the time scaled periods in the stock portlets such as the Resource Management portlets.
If there is no data being populated, check if the datamart jobs are failing.

The Datamart job can fail with the following BG log error:

1. Microsoft SQL Server: 
NBI_CFG_NO_FISCAL_PERIOD - No fiscal periods have been set up SQL error code: 50000

2. Oracle: 
DMT-0005: No fiscal periods have been setup

3. PostgreSQL/SaaS/GCP
Generic error: NJS-0401: Execution of job failed.

--Datamart Extraction
ERROR 2020-08-12 08:37:38,485 [Dispatch bcDatamart Extraction : bg@server(tenant=clarity)] niku.njs (clarity:broadcomadmin:xxx__xxxxxxxx:bcDatamart Extraction) Error executing job: 5060159
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: Error in NBI_EXTRACT_SP: <NULL>
  Where: PL/pgSQL function nbi_extract_sp() line 61 at RAISE

--Datamart Rollup
Error in CMN_JOB_DATAMART_ROLLUP_SP -  Error in NBI_EXTRACT_TF_TS_SP - :Calling NBI_VALIDATE_SETTINGS_SP: NBI_CFG_NO_FISCAL_PERIOD - No fiscal periods have been set up
  Where: PL/pgSQL function cmn_job_datamart_rollup_sp(bigint,bigint) line 72 at RAISE

 

Environment

All Clarity Versions

Cause

The fiscal time periods have to cover the maximum date of the daily resource time slice  IDs (1,2,3,10,11).
 

Resolution

Check the Administration > Data Administration > Datamart Settings > Datamart Entity being used.

1. Run query:

SELECT 'TS MIN/MAX', MIN(FROM_DATE), MAX(TO_DATE)
FROM PRJ_BLB_SLICEREQUESTS
where id in (1,2,3,10,11)

Review the min and max dates.

2. Go to Administration > Finance > Setup > Entities > 
Select Entity > Fiscal Time Periods tab

1. Create monthly time periods that cover the time slice date range above..
2. Checkmark the created time periods.
3. At the top right-hand side of the page, click Action > Activate
4. Run the Datamart Rollup job to completion.
5. Run the Datamart Extraction job to completion.

Additional Information

This issue also impacts the successful execution of the Datamart Rollup job and by configuring the Fiscal Periods, you will avoid the following errors.

Reference KB000022068 : Datamart Rollup Job Failed - Determine current fiscal period no data found

Reference KB000027098: Datamart Rollup Job Failed - Cannot insert the value NULL into column 'FISCAL_TIME_KEY' table

Reference KB000027098 : Why don't weekly fiscal periods appear in NBI_DIM_FISCAL_TIME table?