Clarity

Expand all | Collapse all

Data Warehouse - Project Management Domain and Calendar Periods

  • 1.  Data Warehouse - Project Management Domain and Calendar Periods

    Posted 10-26-2017 05:15 AM

    Dear Community Members

     

    We've just started using the Ad Hoc View functionality in Advanced Reporting, running against the Data Warehouse. The Project Management Domain has a set of fields called "Calendar Periods" with Month, Quarter and Year values, along with their relative start and finish dates.

     

    If I just bring in those calendar fields on a Table for example, it returns the data correctly.

     

    However, if I try and use it with something like the Project Finish Date, eg if I want to plot project finish dates on a simple timeline, then no data is returned.  It feels as though the Calendar Periods haven't associated to any Project data (so I get the same with Tasks, Risk, Issues etc).

     

    Am I missing something in the configuration or in what I am expecting from those Calendar Period fields?

    We're on a SaaS instance, on version 15.2

     

    Any help / insight would be greatly appreciated.

    Regards

    Deepak



  • 2.  Re: Data Warehouse - Project Management Domain and Calendar Periods

    Broadcom Employee
    Posted 10-26-2017 06:15 AM

    Hi Deepak

     

    we need to analyze what query is formed via Show query button on Adhoc Page and see where the problem is. 

     

    Regards

    Suman Pramanik 



  • 3.  Re: Data Warehouse - Project Management Domain and Calendar Periods

    Posted 10-26-2017 06:48 AM
    • Step 1 without the Calendar Fields

      select * from ( select "DWH_INV_INVESTMENT"."INVESTMENT_NAME" as "DWH_INV_INVESTMENT_INVESTME4",

      "DWH_INV_INVESTMENT"."INVESTMENT_ID" as "DWH_INV_INVESTMENT_INVESTMENT_",

      "DWH_INV_INVESTMENT"."IS_ACTIVE" as "DWH_INV_INVESTMENT_IS_ACTIVE",

      "DWH_INV_INVESTMENT"."SCHEDULE_FINISH" as "DWH_INV_INVESTMENT_SCHEDULE_FI",

      "DWH_INV_INVESTMENT"."SCHEDULE_START" as "DWH_INV_INVESTMENT_SCHEDULE_ST",

      "DWH_INV_INVESTMENT"."SCHEDULE_START" as "DWH_INV_INVESTMENT_inv_calc2",

      "DWH_INV_INVESTMENT"."SCHEDULE_FINISH" as "DWH_INV_INVESTMENT_inv_calc_sc"

      from "MUN1269PDWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"

      inner join "MUN1269PDWH"."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" = '43898677') and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project' and "DWH_INV_INVESTMENT"."INVESTMENT_NAME" like 'OpEf%' and "DWH_INV_INVESTMENT"."IS_ACTIVE" = 1 and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project'

      ) where ROWNUM <= 1000

       

       

      Step 2 Adding the Month from Calendar Periods

       

      select * from ( select "DWH_CMN_PERIOD_M_V"."MONTH_NAME" as "DWH_CMN_PERIOD_M_V_MONTH_NAME",

      "DWH_INV_INVESTMENT"."INVESTMENT_NAME" as "DWH_INV_INVESTMENT_INVESTME4",

      "DWH_INV_INVESTMENT"."INVESTMENT_ID" as "DWH_INV_INVESTMENT_INVESTMENT_",

      "DWH_INV_INVESTMENT"."IS_ACTIVE" as "DWH_INV_INVESTMENT_IS_ACTIVE",

      "DWH_INV_INVESTMENT"."SCHEDULE_FINISH" as "DWH_INV_INVESTMENT_SCHEDULE_FI",

      "DWH_INV_INVESTMENT"."SCHEDULE_START" as "DWH_INV_INVESTMENT_SCHEDULE_ST",

      "DWH_INV_INVESTMENT"."SCHEDULE_START" as "DWH_INV_INVESTMENT_inv_calc2",

      "DWH_INV_INVESTMENT"."SCHEDULE_FINISH" as "DWH_INV_INVESTMENT_inv_calc_sc"

      from "MUN1269PDWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"

      inner join "MUN1269PDWH"."DWH_INV_PER_FACTS_M_V" "DWH_INV_PER_FACTS_M_V" on ("DWH_INV_INVESTMENT"."INVESTMENT_KEY" = "DWH_INV_PER_FACTS_M_V"."INVESTMENT_KEY")

      inner join "MUN1269PDWH"."DWH_CMN_PERIOD_M_V" "DWH_CMN_PERIOD_M_V" on ("DWH_INV_PER_FACTS_M_V"."PERIOD_KEY" = "DWH_CMN_PERIOD_M_V"."PERIOD_KEY" and "DWH_CMN_PERIOD_M_V"."LANGUAGE_CODE" = 'en')

      inner join "MUN1269PDWH"."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" = '43898677') and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project' and "DWH_INV_INVESTMENT"."INVESTMENT_NAME" like 'OpEf%' and "DWH_INV_INVESTMENT"."IS_ACTIVE" = 1 and "DWH_INV_INVESTMENT"."INVESTMENT_TYPE_KEY" = 'project'

      ) where ROWNUM <= 1000



  • 4.  Re: Data Warehouse - Project Management Domain and Calendar Periods

    Broadcom Employee
    Posted 10-26-2017 10:39 AM

    I see you opened a case also, we will work on it and anlayze the query and let you know



  • 5.  Re: Data Warehouse - Project Management Domain and Calendar Periods

    Broadcom Employee
    Posted 10-26-2017 12:28 PM

    Hi Deepak,

     

    I just tested in my lab and can see it works and the only thing i can think of is if any of tables DWH_INV_PER_FACTS_M_V or DWH_CMN_PERIOD_M_V dont have data it won't show as there is inner join in them. We will work on the case raised and come back to you.

     

    Regards

    Suman Pramanik 



  • 6.  Re: Data Warehouse - Project Management Domain and Calendar Periods

    Posted 10-27-2017 06:37 AM

    Suman

     

    Thanks for your input on this – if I just bring in the calendar fields, then data is being displayed.  It is if it is combined with project or task or risk or issue data that no data is returned.

     

    Deepak BHADRI

    TEST & DEPLOYMENT MANAGER | FCR TRANSFORMATION | HSBC BANK PLC HBEU

    32nd Floor

    8 Canada Square, Canary Wharf, London, United Kingdom