Clarity

 View Only
  • 1.  Weekly Plan Costs in Datawarehouse

    Posted Aug 09, 2017 05:00 AM

    Is there a view to access weekly planned cost in DWH Schema? We can see Planned Costs where the period type is Monthly but for Weekly Period Type, Planned costs are set to 0.

    [niku].[DWH_FIN_PLAN1_PER_FACTS_V] has data only for monthly, or 13 month Fiscal Periods. Is this the reason why DWH does not have info about weekly info for Plan Costs.



  • 2.  Re: Weekly Plan Costs in Datawarehouse

    Posted Aug 09, 2017 07:59 AM

    With respect to your query is the DWH weekly timeslices setup ? If so, are these Weekly DWH slices , are all having the same From Date and the same Number of Periods. Note these should be the same.

     

    Refer to more details on related content:

    CA PPM Tuesday Tip: How to Set Up the DWH (Data Warehouse) time slices to avoid errors with the 'Update Report Tables' and 'Load DWH' jobs 

     

    Regards,

    Samik



  • 3.  Re: Weekly Plan Costs in Datawarehouse

    Posted Aug 09, 2017 08:31 AM

    Hi Samik,

     

    We have all the weekly DWH slices starting from 4 Jun 2016 for 112 periods. We get information when we look at monthly period type.

     

    Just wondering if the below query in DWH_FIN_PLAN1_PER_FACTS_V is causing this issue(p.period_type IN ('MONTHLY','13_PERIODS_PER_YEAR')).

    SELECT pp.id parent_period_key,

    pp.period_type parent_period_type,

    p.id period_key,

    pp.start_date parent_period_start,

    p.start_date period_start,

    p.end_date period_finish

    FROM biz_com_periods pp, biz_com_periods p

    WHERE pp.entity_id = p.entity_id

    AND p.start_date >= pp.start_date

    AND p.end_date <= pp.end_date

    AND p.id IN (SELECT DISTINCT d.period_key

    FROM (SELECT p.id period_key,

    p.start_date period_start,

    r.row_count,

    ROW_NUMBER () OVER (PARTITION BY p.entity_id ORDER BY p.start_date) AS row_key

    FROM biz_com_periods p,

    dwh_settings s,

    (SELECT MIN (from_date) from_date,

    MAX (num_periods) row_count

    FROM prj_blb_slicerequests

    WHERE is_dwh_request = 1

    AND request_name LIKE '%dwh_fiscal') r

    WHERE p.entity_id = s.entity_key

    AND p.period_type IN ('MONTHLY','13_PERIODS_PER_YEAR')

    AND p.start_date >= r.from_date) d

    WHERE d.row_key <= d.row_count)

     

    Regards,

    Benitha