Clarity PPM

Expand all | Collapse all

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

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

    Posted 05-03-2016 05:23 AM

    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.



  • 2.  Re: Actuals not visible in ad-hoc view created from financial Management domain in Advanced Reporting

    Posted 05-18-2016 02:56 PM

    Hi Anant,

     

    Could you please open a Support case for this issue. It may be something similar to ACTUAL_UNITS & ACTUAL_COST are not being populated in DWH_FIN_PLAN_PERIOD_FACTS

    We will have to take a closer look to identify it.

     

    Thank you -Nika



  • 3.  Re: Actuals not visible in ad-hoc view created from financial Management domain in Advanced Reporting

    Posted 05-19-2016 02:56 AM

    Hi Anant,

     

    For getting the actuals data you need to connect with the Live clarity database not with the DWH Database.

     

    Regards,

    Frederic