Employee.Hours

Purpose

The Employee Hours fact table is designed to display quantitative data related to employees. This table integrates with dimension tables, such as Employee_Details, to provide detailed insights into various aspects of employee performance, compensation, and other metrics. The primary purpose is to support data-driven decision-making across the organisation.

Use Case

Owner: Sarah Martino
Typical Use Case: Calculation of Employee Utilisation
Style: Fact, meaning it consists of the measurements, metrics or facts of a business process

DataMarts

Company
Project
Employee
Person/Resource

Source

QFACT_BMT_ACTUAL_HOURS_BY_IAS$

From

ifs.QFACT_BMT_ACTUAL_HOURS_BY_IAS$ AS src
	LEFT JOIN ifs.DIM_REPORT_CODE_OL AS dim_rpt ON dim_rpt.COMPANY = src.COMPANY_ID
		AND dim_rpt.CODE = src.REPORT_CODE
	LEFT JOIN (SELECT *, 'LEAVE' as REPORT_CODE_GROUP
	FROM ifs.DIM_ABSENCE_GROUP_OL
	WHERE SHOW_IN_ABSENCE_LIMITS = 'true') AS rpt_leave ON rpt_leave.GROUP_NAME = [LINE_TYPE]
		AND rpt_leave.COMPANY = src.COMPANY_ID
	LEFT JOIN (SELECT *, 'ABSENCE' as REPORT_CODE_GROUP
	FROM ifs.DIM_ABSENCE_GROUP_OL
	WHERE SHOW_IN_ABSENCE_LIMITS = 'false') AS rpt_abs ON rpt_abs.GROUP_NAME = [LINE_TYPE]
		AND rpt_abs.COMPANY = src.COMPANY_ID

Sample Exec

EXEC get.myView @dataMart = 'Employee', @viewName = 'Hours', @token = '<myToken>', @version = '<202404>'

Response

src.COMPANY_ID,
src.PROJECT_ID,
EMP_NO,

SEQ

	REPORT_CODE_GROUP,
	REPORT_CODE,
	LINE_TYPE,
	src.SUB_PROJECT_ID,

Dates

CONVERT(date, src.TIMESHEET_DATE, 23) AS TIMESHEET_DATE,

Measures

cast([PROJECT_HOURS] as decimal(10,3)) as [PROJECT_HOURS],
cast([NETT] as decimal(10,3)) as [NETT],
cast([UPLIFT] as decimal(10,3)) as [UPLIFT],
cast([GROSS] as decimal(10,3)) as [GROSS],
cast([BASE_RATE] as decimal(10,3)) as [BASE_RATE],

Codes

Project

ent_proj.item_label AS PROJECT_NAME,
src.PROGRAM_ID,
src.SUB_PROJECT_ID,
ent_proj.PRU,
ent_proj.[Project Category],

Resource

RESOURCE_ID = COALESCE (src.RESOURCE_ID, src.POS_CODE),
RESOURCE_DESCRIPTION = COALESCE (ent_res.item_desc, src.RESOURCE_ID, src.POS_CODE),
src.EMPLOYEE_CATEGORY,
JOB_FAMILY,
JOB_GRADE,

Other Codes

ORG_CODE,
APPROVAL_STATUS,

SQL

BMT-DWH-DEV/Employee_Hours.sql