AnsweredAssumed Answered

Actuals not visible in ad-hoc view created from financial Management domain in Advanced Reporting

Question asked by Anant_Raj_89 on May 3, 2016
Latest reply on May 19, 2016 by FREDERIC FRANCIS

Hi All,

We have following type of clarity setup.

  • Separate charge code has been created for forecasting user in the cost plan
  • Actuals are migrated from SAP system which i.e. actuals are populated into different charge code ultimately actuals are filled with respect to different charge codes other than which has been used for forecasting.

Now an ad-hoc view is created using Financial Management domain in advanced reporting, below shown is the query which is getting generated.

 

select * from ( select "DWH_CMN_PERIOD_COSTPLAN_F_V"."PERIOD_NAME" as "DWH_CMN_PERIOD_COSTPLAN_F_V_PE",

"DWH_FIN_PLAN"."CHARGE_CODE" as "DWH_FIN_PLAN_CHARGE_CODE",

"DWH_FIN_PLAN"."DW_UPDATED_DATE" as "DWH_FIN_PLAN_DW_UPDATED_DATE",

"DWH_FIN_PLAN"."PLAN_NAME" as "DWH_FIN_PLAN_PLAN_NAME",

"DWH_INV_INVESTMENT"."BILLING_CURRENCY_CODE" as "DWH_INV_INVESTMENT_BILLING_CUR",

Sum("DWH_FIN_PLAN_SUMMARY_FACTS"."FCST_COST") as "Sum_DWH_FIN_PLAN_SUMMARY_FA6",

Sum("DWH_FIN_PLAN_SUMMARY_FACTS"."PLAN_COST") as "Sum_DWH_FIN_PLAN_SUMMARY_FA7",

Sum("DWH_FIN_PLAN_SUMMARY_FACTS"."ACTUAL_COST") as "Sum_DWH_FIN_PLAN_SUMMARY_FACTS"

from "PPM_DWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"

inner join "PPM_DWH"."DWH_FIN_PLAN" "DWH_FIN_PLAN" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_FIN_PLAN"."INVESTMENT_KEY")

inner join "PPM_DWH"."DWH_FIN_PLAN_SUMMARY_FACTS" "DWH_FIN_PLAN_SUMMARY_FACTS" on ("DWH_FIN_PLAN"."PLAN_DETAIL_KEY" = "DWH_FIN_PLAN_SUMMARY_FACTS"."PLAN_DETAIL_KEY")

inner join "PPM_DWH"."DWH_FIN_PLAN_PER_FACTS_F_V" "DWH_FIN_PLAN_PER_FACTS_F_V" on ("DWH_FIN_PLAN"."PLAN_DETAIL_KEY" = "DWH_FIN_PLAN_PER_FACTS_F_V"."PLAN_DETAIL_KEY")

inner join "PPM_DWH"."DWH_CMN_PERIOD_F_V" "DWH_CMN_PERIOD_COSTPLAN_F_V" on ("DWH_FIN_PLAN_PER_FACTS_F_V"."PERIOD_KEY" = "DWH_CMN_PERIOD_COSTPLAN_F_V"."PERIOD_KEY" and "DWH_CMN_PERIOD_COSTPLAN_F_V"."LANGUAGE_CODE" = 'en')

inner join "PPM_DWH"."DWH_INV_SECURITY_V" "DWH_INV_SECURITY_V" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_SECURITY_V"."INVESTMENT_KEY")

where ("DWH_INV_SECURITY_V"."USER_UID" = '******') and "DWH_FIN_PLAN"."IS_PLAN_OF_RECORD" = 1 and "DWH_INV_INVESTMENT"."INVESTMENT_ID" = 'some project id'

group by "DWH_FIN_PLAN"."PLAN_NAME", "DWH_CMN_PERIOD_COSTPLAN_F_V"."PERIOD_NAME", "DWH_FIN_PLAN"."CHARGE_CODE", "DWH_FIN_PLAN"."DW_UPDATED_DATE", "DWH_INV_INVESTMENT"."BILLING_CURRENCY_CODE"

order by "DWH_FIN_PLAN_PLAN_NAME", "DWH_CMN_PERIOD_COSTPLAN_F_V_PE", "DWH_FIN_PLAN_CHARGE_CODE", "DWH_FIN_PLAN_DW_UPDATED_DATE", "DWH_INV_INVESTMENT_BILLING_CUR"

) where ROWNUM <= 100001

 

The SAP charge codes i.e. the charge codes with respect to which the actuals have been filled actuals are coming '0'.

Now we tried to decompile the etl dataware house job using pentaho DI, below is the overall analysis.

 

 

DWH_FIN_PLAN_SUMMARY_FACTS     ---- main table which displays data

                         

DWH_FIN_PLAN_SUM_FACTS_LOAD   ----   SP responsible to populated data into above table

 

DWH_X_FIN_PLAN_SUM_FACTS_V     -----  view used by above SP

 

dwh_fin_plan_period_facts                       -----  above view uses this table

 

DWH_FIN_PLAN_PER_FACTS_LOAD   ----   SP used to populated above table

 

DWH_X_PLAN_PER_ACT_FACTS_MV  --- materialized view used by above SP

 

Above drilled down approach was used to check where the data is getting populated or not.

 

We found that data was not getting populated in DWH_X_PLAN_PER_ACT_FACTS_MV materialized view.

In below shown clause fpd is alias of dwh_fin_plan table present in DWH schema and t is of dwh_fin_transaction table

 

fpd.charge_code_key = CASE WHEN g.charge_code = 1 THEN t.charge_code_key ELSE 0 END

 

now in the above clause, it  checks whether the transaction charge code id (which is of actuals) is present in the cost plan or not and generates a plan_details_key with respect to which data comes, in our case it's coming 0 (because the charge codes for actuals are different then that for forecasting), so the actuals doesn't gets populated.

 

Query -

 

The set up which we are using is it ok.

Is it possible for the ad-hoc view to bring the actuals in case of the set up  which we have done i.e. different charge codes for forecasting and actuals in advanced reporting.

Outcomes