Data Warehouse - Project Management Domain and Calendar Periods

[Legacy User]

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

[Suman Pramanik]

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 

[Legacy User]

• 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

[Suman Pramanik]

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

[Suman Pramanik]

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 

[Legacy User]

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