Employee.Headcount

Purpose

The Employee Headcount 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: Sam Jopson-White
Typical Use Case: Resource & Headcount Analysis
Style: Fact, meaning it consists of the measurements, metrics or facts of a business process

Source

FACT_EMPLOYEE_ANALYSIS_OL

Parameters

Company
Employee
Person/Resource

Sample Exec

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

Response

       src.COMPANY_ID as COMPANY,
       src.EMP_NO,
       src.PERSON_ID,

SEQ

       [YEAR_KEY],
       [PERIOD_KEY],
       [EMP_SEQ_NO],
       [PRIMARY_POSITION],
       [PRIMARY_JOB],
       [SEQ_NO_KEY],

Entity

employee_item_id,

Dates

	convert(date,[EMPLOYMENT_START_DATE],23) as [EMPLOYMENT_START_DATE],
	convert(date,[EMPLOYMENT_END_DATE],23) as [EMPLOYMENT_END_DATE],
	datefromparts([YEAR_KEY]-cast([AGE] as int),10,01) as YOB,

Measures

       Cast([PERIOD_FTE] as decimal(6, 3)) as [PERIOD_FTE],
       Cast([PERIOD_HEADCOUNT] as decimal(6, 3)) as [PERIOD_HEADCOUNT],
       Cast([START_EMPLOYMENT_HEADCOUNT] as decimal(6, 3)) as [START_EMPLOYMENT_HEADCOUNT],
       Cast([START_EMPLOYMENT_FTE] as decimal(6, 3)) as [START_EMPLOYMENT_FTE],
       Cast([END_EMPLOYMENT_HEADCOUNT] as decimal(6, 3)) as [END_EMPLOYMENT_HEADCOUNT],
       Cast([END_EMPLOYMENT_FTE] as decimal(6, 3)) as [END_EMPLOYMENT_FTE],

Codes

Employee

       [AGE],
       [GENDER],
       [CITIZENSHIP],

Work

       [EMPLOYEE_STATUS_CODE],       [EMPLOYEE_STATUS],
       [EMPLOYEE_WORK_LOCATION],       [WORK_LOCATION_DESCRIPTION],
       [ASSIGNMENT_ID_CODE],       [ASSIGNMENT_ID],

Organisation

       [CODE_E] as PRU,       [PRU_Name],
       [JOB_ID],       [JOB_TITLE],       [JOB_FAMILY] as [JOB_FAMILY_ID],
       TRIM(LEFT([JOB_TITLE], CHARINDEX(' -', [JOB_TITLE]))) AS JOB_FAMILY_DESC,
       [ORG_CODE],       code_org.[STRUCTURE_ID],       code_org.[PARENT_ORG_CODE],       code_org.ORGANIZATION_NAME,
       [CODE_B],       code_b.[DESCRIPTION] as [Code_B_DESCRIPTION]

SQL

BMT-DWH-DEV/Employee_Headcount.sql