DWH - Incorrect data gets loaded to dwh_fin_plan_period_facts

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Cost Plan data in Jaspersoft reports and Ad Hoc views do not get populated correctly 

Following are the steps to reproduce this issue:

1. Navigate to Administration -> System Options. Set values as follows against the "Data Warehouse Options" section:
   • Language = "English"
   • Include Weekly Period Data = YES
   • Include Monthly Period Data = YES
   • Include Fiscal Period Data = YES
   • Consistent Fiscal Periods across Entities = YES
   • Include only financial Plan of Record = YES
2. Navigate to the Modern UX -> Projects -> Financials
3. Create a Cost Plan as follows:
   • Period Type = Annual
   • Start Period = 2021-01
   • Finish period = 2021-12
4. Navigate into the details of the Cost Plan. Click on View Options. Change Periods to "Months"
5. Populate 100 against each month
6. Let a Time Slicing job finish. Now query the PPM DB with the following query (by replacing the Project ID in the WHERE Clause) (Change the query appropriately to suit the DB. The query below is for PostgreSQL):

 select
  slice.start_date,
  round(slice.slice * extract(day from (slice.finish_date - slice.start_date))::integer, 2) forecast_cost
 from
  fin_plans fp
 join fin_cost_plan_details fcpd on
  fcpd.plan_id = fp.id
 join odf_ssl_cst_dtl_cost slice on
  slice.prj_object_id = fcpd.id
 join inv_investments ii on
  ii.id = fp.object_id
 where
  ii.code = 'PR2000';

    7. Run Load Data Warehouse job (Full Load)

    8. Check the values that have been transferred over to the DWH using the following query (replace Project ID):

select
 dcp.period_start_date,
 round(fpd.plan_cost, 2) forecast_cost
from
 dwh_fin_plan_period_facts fpd
join dwh_fin_plan fp on
 fp.plan_key = fpd.plan_key
join dwh_inv_investment ii on
 ii.investment_key = fp.investment_key
join dwh_cmn_period dcp on
 dcp.period_key = fpd.period_key
where
 ii.investment_id = 'PR2000';

Expected Results: The values returned in step 6 and step 8 are same

Actual Results: The values returned in step 8 do not match with that in step 6. DWH has reduced values against certain months.

Environment

Versions tested against: 15.9.2, 15.9.1, 15.8.1

Cause

Reported as  DE60921.

Resolution

The issue could not be validated by engineering therefor e the work around is as follows:

Untick the checkbox for "Consistent Fiscal Periods across Entities" in the Clarity => Administration => General Settings => System Options page.

- This option was added to improve processing time if an organization has more than one entity and uses different types of reporting periods. For example one entity uses Monthly and one entity uses 13 Period.

Unchecking the item will not cause other impacts.

Additional Information

This will cause Jaspersoft reports and Ad Hoc views to render incorrect Cost plan data

Reference also: Load DWH job frequently reported issues

 

Now issue was reported in 16.0.2 where if you create a monthly plan but add the values annually in the Per-Period metrics, only the first month of each year is shown.

This is reported as DE64336 which is under review.

Here is the related article:

Cost Plans created with different Per-Period Metrics than the cost plan period type do not show in DWH correctly.