Employee.Item_Codes_Absence

by

🧾 Overview

This view provides attribute-level values related to employee absence and leave entitlements, enabling structured DataMart-level analysis across both transactional absence events and annual entitlement summaries.

📚 Description

The Employee Item Codes Absence view translates raw absence and entitlement data into attribute-coded rows that support dimensional modelling. Each row identifies:

  • The employee via a JSON OBJECT_SEQ
  • The absence or entitlement event via a JSON ITEM_SEQ
  • The type of item (e.g. ABSENCE, LeaveEntitlement)
  • The attribute name and code value
  • A JSON metadata field offering additional context

🏗️ Source Tables

  • ifs.DIM_EMPLOYEE_OL
  • ifs.QFACT_ABSENCE_DETAILS_OL$
  • ifs.QFACT_BMT_ABSENCE_SUMMARY_IAS$

📦 Output Structure

ColumnDescription
OBJECT_SEQUnique employee identifier in JSON format
ITEM_SEQUnique absence or entitlement item identifier in JSON format
ITEM_TYPEType of the item (e.g. ABSENCE, LeaveEntitlement)
ATTRIBUTEAttribute name (e.g. TYPE_ID, ABSENCE_TYPE)
CODE_VALUEAttribute value (code ID)
VALUE_METADATAJSON-formatted metadata describing the code

🔁 Join & Matching Logic

  • Employees are extracted from ifs.DIM_EMPLOYEE_OL
  • Absence details (from QFACT_ABSENCE_DETAILS_OL$) and leave entitlement records (from QFACT_BMT_ABSENCE_SUMMARY_IAS$) are cross-applied into attribute-level rows
  • All rows are joined back to employees using a constructed OBJECT_SEQ JSON key

Leave a Comment