DWH -- Actual Hours sliced by Resources, Tasks, and Projects

[Nate BI]

Hi folks,

All suggestions are appreciated,

I have connected Power BI to the DWH via the Odata integration. 

The data model uses the following tables, however, I can't slice the ACTUAL_HOURS at the resource by task level. For example, 'Resource X spent Y Hours on Task Z. Allowing us to the roll this up to the investment level.

The ACTUAL_HOURS metric from the DWH_RES_RESOURCE_FACTS table allows slicing by RESOURCE_NAME but not per task. 

I'd also be interested to know how the organisations are added to these tables. OBS data.

Kind regards,

[JP_Thompson]

Hi,

You could try this code....

SELECT
BUSINESS_LINE,
PILLAR,
DEPARTMENT,
GROUP_NAME,
SECTION_NAME,
RESOURCE_NAME,
BUSINESS_FUNCTION,
INVESTMENT_ID,
INVESTMENT_NAME,
PHASE,
ACTIVITY,
TASK,
RECORD_TYPE,
SUM(JAN_22) JAN,
SUM(FEB_22) FEB,
SUM(MAR_22) MAR,
SUM(APR_22) APR,
SUM(MAY_22) MAY,
SUM(JUN_22) JUN,
SUM(JUL_22) JUL,
SUM(AUG_22) AUG,
SUM(SEP_22) SEP,
SUM(OCT_22) OCT,
SUM(NOV_22) NOV,
SUM(DEC_22) DEC,
SUM(TOTAL) TOTAL
FROM
(SELECT
OBS.BUSINESS_LINE,
OBS.PILLAR,
OBS.DEPARTMENT,
OBS.GROUP_NAME,
OBS.SECTION_NAME,
RESR.RESOURCE_NAME,
MPJ.BUSINESS_FUNCTION,
INV.INVESTMENT_ID,
INV.INVESTMENT_NAME,
TSK1.TASK_NAME PHASE,
TSK2.TASK_NAME ACTIVITY,
TSK3.TASK_NAME TASK,
'Actuals' RECORD_TYPE,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-JAN-22' THEN PF.ACTUAL_HOURS ELSE 0 END) JAN_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-FEB-22' THEN PF.ACTUAL_HOURS ELSE 0 END) FEB_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-MAR-22' THEN PF.ACTUAL_HOURS ELSE 0 END) MAR_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-APR-22' THEN PF.ACTUAL_HOURS ELSE 0 END) APR_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-MAY-22' THEN PF.ACTUAL_HOURS ELSE 0 END) MAY_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-JUN-22' THEN PF.ACTUAL_HOURS ELSE 0 END) JUN_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-JUL-22' THEN PF.ACTUAL_HOURS ELSE 0 END) JUL_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-AUG-22' THEN PF.ACTUAL_HOURS ELSE 0 END) AUG_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-SEP-22' THEN PF.ACTUAL_HOURS ELSE 0 END) SEP_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-OCT-22' THEN PF.ACTUAL_HOURS ELSE 0 END) OCT_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-NOV-22' THEN PF.ACTUAL_HOURS ELSE 0 END) NOV_22,
SUM(CASE WHEN CMP.MONTH_START_DATE = '01-DEC-22' THEN PF.ACTUAL_HOURS ELSE 0 END) DEC_22,
SUM(PF.ACTUAL_HOURS) TOTAL

FROM
PPM_DWH.DWH_INV_INVESTMENT INV,
PPM_DWH.DWH_INV_ASSIGNMENT INVA,
PPM_DWH.DWH_INV_TEAM TM,
PPM_DWH.DWH_RES_RESOURCE RESR,
PPM_DWH.DWH_INV_TASK TSK1,
PPM_DWH.DWH_INV_TASK_HIERARCHY TSKPA,
PPM_DWH.DWH_INV_TASK TSK2,
PPM_DWH.DWH_INV_TASK_HIERARCHY TSKAT,
PPM_DWH.DWH_INV_TASK TSK3,
PPM_DWH.DWH_INV_ASSIGN_PERIOD_FACTS PF,
PPM_DWH.DWH_CMN_PERIOD CMP,


(SELECT DISTINCT OM.RESOURCE_KEY,
OM.OBS_LEVEL2 AS BUSINESS_LINE,
OM.OBS_LEVEL3 AS PILLAR,
OM.OBS_LEVEL4 AS DEPARTMENT,
OM.OBS_LEVEL5 AS GROUP_NAME,
OM.OBS_LEVEL6 AS SECTION_NAME
FROM PPM_DWH.DWH_RES_OBS_MAPPING OM
WHERE OM.OBS_TYPE_KEY = 5000011
ORDER BY 3 ASC, 4 ASC, 5 ASC) OBS,

(SELECT DISTINCT OM.OBS_TYPE_KEY,
OM.INVESTMENT_KEY,
OM.OBS_LEVEL3 AS BUSINESS_LINE,
OM.OBS_LEVEL3 AS BUSINESS_FUNCTION,
OM.OBS_LEVEL4 AS SUB_PRODUCT,
OM.OBS_LEVEL6 AS GROUP_TYPE,
OM.OBS_LEVEL7 AS MASTER_PROJECT
FROM PPM_DWH.DWH_INV_OBS_MAPPING OM
WHERE OM.OBS_TYPE_KEY = 5000017
ORDER BY 3 ASC, 4 ASC, 5 ASC, 6 ASC, 7 ASC) MPJ

WHERE
INV.INVESTMENT_TYPE_KEY <> 'idea' AND
INV.INVESTMENT_KEY = INVA.INVESTMENT_KEY AND
INV.INVESTMENT_KEY = MPJ.INVESTMENT_KEY (+) AND
INVA.INVESTMENT_KEY = PF.INVESTMENT_KEY AND
INVA.ASSIGNMENT_KEY = PF.ASSIGNMENT_KEY AND
INVA.INVESTMENT_KEY = TSKPA.INVESTMENT_KEY AND
INVA.TEAM_KEY = TM.TEAM_KEY AND
TM.RESOURCE_KEY = RESR.RESOURCE_KEY (+) AND
RESR.RESOURCE_KEY = PF.RESOURCE_KEY (+) AND
PF.RESOURCE_KEY = OBS.RESOURCE_KEY (+) AND
TSK1.TASK_KEY = TSKPA.PARENT_TASK_KEY (+) AND
TSK1.WBS_LEVEL = 1 AND
TSKPA.CHILD_TASK_KEY = TSK2.TASK_KEY (+) AND
TSK2.WBS_LEVEL IN (1,2) AND
TSK2.WBS_TYPE_KEY IN ('LEVEL2','TASK') AND
TSK2.TASK_KEY = TSKAT.PARENT_TASK_KEY (+) AND
TSKAT.CHILD_TASK_KEY = TSK3.TASK_KEY (+) AND
TSK3.WBS_TYPE_KEY = 'TASK' AND
TSK3.TASK_KEY = PF.TASK_KEY AND
PF.PERIOD_KEY = CMP.PERIOD_KEY AND
CMP.PERIOD_TYPE_KEY = 'MONTHLY' AND
CMP.MONTH_START_DATE BETWEEN '01-JAN-22' AND '01-DEC-22'

GROUP BY
OBS.BUSINESS_LINE,
OBS.PILLAR,
OBS.DEPARTMENT,
OBS.GROUP_NAME,
OBS.SECTION_NAME,
RESR.RESOURCE_NAME,
MPJ.BUSINESS_FUNCTION,
INV.INVESTMENT_ID,
INV.INVESTMENT_NAME,
TSK1.TASK_NAME,
TSK2.TASK_NAME,
TSK3.TASK_NAME

HAVING SUM(PF.ACTUAL_HOURS) != 0)

GROUP BY BUSINESS_LINE,
PILLAR,
DEPARTMENT,
GROUP_NAME,
SECTION_NAME,
RESOURCE_NAME,
BUSINESS_FUNCTION,
INVESTMENT_ID,
INVESTMENT_NAME,
PHASE,
ACTIVITY,
TASK,
RECORD_TYPE


ORDER BY BUSINESS_LINE ASC,
PILLAR ASC,
DEPARTMENT ASC,
GROUP_NAME ASC,
SECTION_NAME ASC,
RESOURCE_NAME ASC,
INVESTMENT_NAME ASC,
PHASE ASC,
ACTIVITY ASC,
TASK ASC

[Liz Williamson]

Hi Nate,
If you want actual hours but resource/task that would be at the assignment level and you may need to use the table 
DWH_INV_ASSIGN_SUMMARY_FACTS

If you want by period then you would need to use DWH_INV_ASSIGN_PERIOD_FACTS

These would need to join back to the task and investment tables.