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.