Incremental Load Data Warehouse job is taking too much time in PPM

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Incremental Load  Data Warehouse (DWH) job is taking too long, more time then it takes to run the Full load in CA Project & Portfolio Management (PPM).

When we checked DWH_RUN_STATUS_V, we found one table(DWH_INV_TEAM_PERIOD_FACTS) is taking approximately 70 Minutes.

 

Environment

Release: CODCVU9900-14.3-PPM SAAS-View Only Userpack for-Canadian Government Entities
Component:

Resolution

There have been some performance improvements in 15.1, 15.2, and a defect fixed in 15.3 that have improved performance. Below is a list of the improvements included:

As per the CA PPM 15.1 Release Notes:

Performance Improvement for Incremental Updates. Performance has been improved for the Incremental Data Warehouse load specifically in the following areas:

• Resource availability
• Project teams
• Task assignments

We have reduced the incremental load time by adding some indexes and using temporary processing tables.

As per the CA PPM 15.2 Release Notes:

The following performance improvements are also available:

• Financial plans for incremental loads: The incremental load makes use of a new DWH_TMP_FIN_RECORD_KEYS table that contains only those plans that changed since the last incremental load. The database view joins to this table to reduce the amount of data processed. This can significantly improve performance on large data sets.
• Financial transaction facts: Similar to recent improvements for assignments and teams, the load performance of financial transaction facts data is improved. These tables often contain millions of rows of data. The tables include DWH_FIN_TRANSACTION, DWH_FIN_TRANSACTION_LN, and DWH_INV_TASK_PERIOD_FACTS.

Additionally, in 15.3:

The following defect has been fixed that can cause the job to run longer than expected in Incremental mode: KB000115923

Workarounds:

Some suggestions that may help with performance until upgrading the latest CA PPM version:

1. Run the Load DWH job in full mode only
2. Reduce the amount of custom attributes included in DWH
3. Reduce the amount of languages included
4. Ensure you are on latest fix pack (patch) for your release
5. Make sure that both Time slicing and Investment Allocation are incompatible with the Load DWH job (this can avoid Load DWH job failures in particular): KB000036882