DWH - Incorrect data gets loaded to dwh_fin_plan_period_facts
search cancel

DWH - Incorrect data gets loaded to dwh_fin_plan_period_facts


Article ID: 214899


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


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):
  round(slice.slice * extract(day from (slice.finish_date - slice.start_date))::integer, 2) forecast_cost
  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
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):

 round(fpd.plan_cost, 2) forecast_cost
 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
 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.


Versions tested against: 15.9.2, 15.9.1, 15.8.1


Reported as  DE60921.


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.