Datawarehouse Quarterly and Yearly Time Slice

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

 We are not able to move Quarterly and yearly time slice into Data warehouse. These slices are not selectable for movement. (For eg – Yearly & Quarterly Hard Allocation) QUARTERLYRESOURCEAVAILCURVE YEARLYRESOURCEALLOCCURVE

 

Environment

Release:
Component: ODTSL

Resolution

Not all slices are available for the DWH.  The slices consume a large amount of data and it is not feasible to bring in all slices.

However, the DWH does aggregate the Monthly Slices to be available to show in Quarterly or Yearly groups.

In the DWH the slices are referred to as FACTS.

There are several views built into the DWH to show data in Weekly, Monthly, Fiscal periods.

There is also a table called DWH_CMN_PERIOD which is used to join the Monthly fact views to determine which quarter or year the months fall into.

For any custom report you may need to build the aggregation.  For ad-hoc views these calculations are already built in.

This is a simple example of the Year Start Date to aggregate 2018 actual hours by project using the project domain

 select * from ( select "DWH_CMN_PERIOD_M_V"."YEAR_START_DATE" as "DWH_CMN_PERIOD_M_V_YEAR_START_DATE", 
"DWH_INV_INVESTMENT"."INVESTMENT_NAME" as "DWH_INV_INVESTMENT_INVESTMENT_NAME", 
"DWH_INV_PER_FACTS_M_V"."ACTUAL_TOTAL_HOURS" as "DWH_INV_PER_FACTS_M_V_ACTUAL_TOTAL_HOURS"
from "CLARITY1541DWH"."DWH_INV_INVESTMENT" "DWH_INV_INVESTMENT"
inner join "CLARITY1541DWH"."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 "CLARITY1541DWH"."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") a