Employee.Details_Plus

Purpose

The Employee.Details_Plus view extends the foundational Employee.Details dataset, offering enriched employee data for segmentation, reporting, and integration across organisational systems. It consolidates employment, organisational, and job-related details into a comprehensive, consistent format to support analytical and operational needs.

Use Case

Owner: Sam Jopson-White
Typical Use Case: Integration with fact tables for enhanced reporting and analysis, such as employee header identification in Headcount Summary reports.v
Style: Dimension table with a single row per employee

Key Features:

1. Identity Management

• Person and User IDs: Ensures unique identification across systems.
• Email Integration: Provides SMTP and UPN addresses for direct communication.

2. Employment Details

• Start and End Dates: Tracks full employment lifecycle.
• Employment Types: Includes codes and descriptions for clarity.
• Agreements: Captures contract agreements and reasons for leaving.

3. Organisational Data

• Cost Centres and PRU: Detailed allocation of roles within organisational structures.
• Work Location: Includes codes and descriptions for all roles.

4. Job Assignments

• Primary Job Details: Captures job family, grade, and title.
• Occupancy and FTE: Includes degree of occupancy, assignment type, and calculated FTE.
• Supervisor Data: Names, positions, and contact details for employee oversight.

5. Payroll Information

• Payroll IDs: Includes year of birth (derived), gender, and citizenship.
• Work Location Metadata: Captures code and description for payroll location.

JSON Outputs:

Key attributes are encapsulated in JSON format for easy consumption and integration, enabling downstream systems to parse complex relationships with minimal effort.

Data Sources

Table

DIM_EMPLOYEE_OL

Integrations

• DIM_COMPANY_OL for company name and details.
• QFACT_BMT_FACT_EMP_ANALYS_IAS$ for employee analysis, including primary position and FTE calculations.
• DIM_JOB_DETAILS_OL for job family and related details.
• DIM_EMPLOYMENT_OL for detailed employment information.
• DIM_WORK_LOCATION_OL for work location description.
• QDIM_PERSON_INFO_OL$ for User ID association.
• QDIM_FND_USER_OL$ for User identifiers.
• QDIM_FND_USER_PROPERTY_OL$ for SMTP email address.

Sample Exec

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

Response

Identifiers

• Includes unique identifiers such as
  • COMPANY,
  • EMP_NO,
  • PERSON_ID,
• Hash-based identifiers for simplified integration:
  • EMPLOYEE_SEQ: Combines COMPANY and PERSON_ID.

Attributes:

Includes unique identifiers and basic employee information

• EMPLOYEE_NAME
• EMAIL ADDRESS
• Identity (array) : Identity references for other systems
• Email (array) : Includes other email addresses

Employment

Captures the employment lifecycle of an employee.

{
  "START_DATE": "2020-06-15",
  "END_DATE": "9999-12-31"
}

Agreement

Details employment agreements, types, and reasons for leaving.

{
    "EMPLOYMENT_CODE": "40",
    "EMPLOYMENT_TYPE": {
        "CODE": "83",
        "DESC": "Permanent Full Time"
    },
    "AGREEMENT": {
        "CODE": "10",
        "DESC": "37.5 hours per week"
    },
    "PERS_INACTIVE_FLG": "false",
    "REASON_FOR_LEAVING": {
        "CODE": "",
        "DESC": ""
    }
}

Payroll

Includes payroll-related attributes such as location and demographic data.

{
    "Company_Name": "BMT Group Limited",
    "PAYROLL_ID": "9999999",
    "YoB": "1977",
    "GENDER": "",
    "CITIZENSHIP": "",
    "WORK_LOCATION": {
        "CODE": "1004",
        "DESC": "Bath, UK (Maritime House)"
    }
}

Assignment

Represents job assignments, occupancy, and FTE metrics.

{
    "Has_JobAssign": "true",
    "Has_Job": "true",
    "Has_Assignment": "true",
    "Assgn": {
        "CODE": "10",
        "DESC": "5 out of 5 days",
        "Details": {
            "assg": "5",
            "days": "5"
        },
        "FTE": "1.00"
    },
    "DEGREE_OF_OCCUPANCY": "1.0",
    "PERIOD": {
        "FTE": "1.0",
        "HEADCOUNT": "1"
    }
}

Primary Job

Captures the primary job details, including job family and organisational level.

{
    "EMPLOYEE_CATEGORY": {
        "CODE": "22",
        "DESC": "NON-FEE EARNER"
    },
    "JOB_FAMILY": {
        "CODE": "360",
        "DESC": "IT",
        "ORG_LEVEL": "Global IT Business Services"
    },
    "JOB": {
        "CODE": "3600",
        "DESC": "IT (5)",
        "GRADE": "5",
        "TITLE": "The Boss"
    },
    "POSITION": {
        "CODE": "1009999",
        "DESC": "John Doe"
    }
}

Supervisor

Provides information about the employee’s supervisor.

{
    "PERSON_ID": "1009993",
    "NAME": "Jane Doe",
    "POSITION": {
        "CODE": "1009993",
        "DESC": "Jane Doe"
    },
    "Email": "Jane.Doe@uk.bmt.org"
}

Organisation

Details the employee’s organisational unit and PRU (Profit Responsibility Unit).

{
    "ORGANISATION": {
        "CODE": "1000",
        "COST_CENTRE": "59000",
        "NAME": "BMT"
    },
    "PRU": {
        "CODE": "9501072",
        "PROGRAMME": "950"
    }
}

SQL

To access the full SQL structure, refer to…

BMT-DWH-DEV/Employee_Details_Plus.sql