search cancel

DWH - Incorrect data gets loaded to dwh_fin_plan_period_facts

book

Article ID: 214899

calendar_today

Updated On:

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.

Cause

Due to defect DE60921.

Environment

Versions tested against: 15.9.2, 15.9.1, 15.8.1

Resolution

The defect is under review by Product Management.

As a workaround, untick the checkbox for "Consistent Fiscal Periods across Entities" in the Clarity => Administration => General Settings => System Options page.

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