Datawarehouse Quarterly and Yearly Time Slice
Article ID: 126175
Updated On:
Products
CLARITY PPM FOR ITG
CLARITY PPM FEDERAL
Clarity PPM SaaS - Application
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
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
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
Feedback
Powered by
