Performance Full Load DWH Job Runtime Increasing

Products

• Clarity PPM SaaS

Issue/Introduction

The duration of the full Load DWH job has increased by more than double.
The table with most time spent on is: DWH_FIN_PLAN_PERIOD_FACTS 

The longer the job takes, the more prone other jobs wait and job can fail during maintenance/upgrade window.

The following recommendations were suggested/taken:

1. Run full load only because since changes to attributes are so frequent 

2. There are no conflicting ODATA queries nor database queries.

3. Uncheck value in
Administration > System Options > Data Warehouse Options
Include only financial Plan of Record

As this is the only workaround, the job does reduce significantly by 75%, however this option cannot be set because
all the plans are needed as users want to occasionally compare with past approved plans.

4. Reducing number of project/cost plans does not help and only improved by less than 10 minutes.

 

Resolution

This is reported as DE116147 and is reported as not a defect.

There issue is attributed to two areas, which will increase the runtime of the job.

1. Review the 13 DWH fiscal time slices.
If the range is greater than the best practices/recommendation/OOTB value of 72 months, then this is be expected.

Refer to: Configuring the Data Warehouse Time Slices


2. For additional improvement, use only the Plan of Record.
This is set in Administration > System Options > Data Warehouse Options

The cost plan load from multiple tables are too high and the volume of data can easily rise into the double-digit millions of records.