Load Data Warehouse Incremental slow performance on dwh_x_plan_act_per_facts (PG)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse Incremental stuck on dwh_x_plan_act_per_facts (PostgreSQL) and creating high bloat and temp usage on GCP

1. Connect PostgreSQL with large amount of financial plans
2. Ensure in Data Warehouse Settings " Consistent Fiscal Periods across Entities" is checked and "Include only financial Plan of Record" is unchecked
3. Run Load Data Warehouse - Full, note it completes successfully
4. Now edit a large amount of financial plans (by users or run a query to update the last_updated_date)
5. Run Load Data Warehouse - Incremental job

Expected Results: The job to run successfully and without issues

Actual Results: The job is hanging, runs much slower than usual, and creates excessive bloat, taking too much database temp space

Environment

Release : 16.0.1 with PostgreSQL

Component : Clarity Data Warehouse

Resolution

Workaround: Select "Include only financial Plan of Record" in System Settings if possible / or just run Full Load of Data Warehouse

This is DE64425, reviewed by Engineering - suggested solution: Load DWH Enable Actuals Plan Toggle for Financial Period Plan Facts