Load Data Warehouse job fails with ERROR on DWH_FIN_PLAN_DETAIL.

Products

• Clarity PPM SaaS

Issue/Introduction

The load data warehouse job failed with 

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_FIN_PLAN_DETAIL. null value in column "detailed_record_description" of relation "dwh_fin_plan_detail" violates not-null constraint

This can happen on the full load or the incremental load.

Once the job fails two indexes got dropped and will need to be recreated.

Environment

16.3.1 on

Cause

With the introduction of child rows in the cost plan details the 'description' field is required for all child rows.

If, for some reason, the field is null, the job will fail as that field is required in the data warehouse.

At that point the job fails, two indexes have already been dropped on the DWH Schema

Those are

• DWH_FIN_PLAN_VIRTUAL_MV_U1
• DWH_FIN_PLAN_VIRTUAL_MV_N1

Resolution

On the PPM schema run this query to find the null record:

                  select * from DWH_FIN_PLAN_DET_DESC_V where detailed_record_description is null

This will show you the investment key and plan key so you can find the investment and plan.

Check the plan for a child with a null description field and populate it.

Then because the two indexes were dropped they will need to be recreated.

On the DWH Schema have the DBA recreate these indexes:

• DWH_FIN_PLAN_VIRTUAL_MV_U1
• DWH_FIN_PLAN_VIRTUAL_MV_N1