EmployeeDataAggregate table
Important
Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.
The EmployeeDataAggregate aggregate table stores reporting year wise employee details so that employee count metrics can be easily computed.
Inputs
It's computed by fetching data from the following environmental, social, and governance (ESG) data model tables:
Employee: This table stores employee details like employee name, gender, and date of birth. To generate the aggregate table, populate these fields:
- EmployeeId: Unique identifier of the employee.
- PartyId: Unique identifier of the party entry of the employee.
- GenderId: Captures the gender of the employee. Gender is a reference table in ESG data model schema. By default, it supports Male, Female, Not Reported, and Other.
EmployeeRelatedParty: This table stores the relationship between employee and a party, which can be a legal entity or organization. To generate the aggregate table, populate these fields:
- EmployeeId: Employee’s unique identifier from Employee table.
- PartyId: PartyId of the organization/legal entity from Party table.
- EmployeePartyRelationshipTypeId: EmployeePartyRelationshipType.EmployeePartyRelationshipTypeId. EmployeePartyRelationshipType is a reference table that captures the nature of relationship between employee and party. Employee-Employer is a default value already present in the table. Use the same to populate relationship between employee and organization. Adding a custom value requires modifying the aggregate table computation logic accordingly.
- RelationshipPeriodStartTimestamp: The starting timestamp of the employee-party relationship.
- RelationshipPeriodEndTimestamp: The ending timestamp of the employee-party relationship.
EmployeeLocation: Stores the location of the Employee. Used for finding the country/region in which employee works. To generate the aggregate table, populate these fields:
- EmployeeId: Employee’s unique identifier from Employee table.
- LocationId: The unique identifier of a Location from Location table. Location table should be used to store the location details of the employee. In the location table, country/region should be mapped. Country is a reference table in the ESG data model schema. Map a valid CountryId from this table. You can also add entries to the country table as required.
- PeriodStartTimestamp: Indicates the timestamp when employee started working from the specified location.
- PeriodEndTimestamp: Indicates the timestamp when employee ended working from the specified location.
For details on ingesting, transforming, and loading data into ESG data model tables, go to ESG data estate overview.
This table shows the EmployeeDataAggregate table column details and mapping of columns to ESG data model tables.
| Column name | Is nullable | Description | Mapping to ESG data model |
|---|---|---|---|
| ReportingPeriod | Required | Currently represents calendar years. | Minimum year of EmployeeRelatedParty.PeriodStartDate or EmployeeRelatedParty.PeriodEndDate. Stores the list of years from the minimum year (based on PeriodStartDate or PeriodEndDate) to the current year for aggregation. |
| EmployeeId | Required | Unique identifier for Employee. | EmployeeId |
| CountryOrRegion | Required | CountryOrRegion of the location in which the employee works. | EmployeeId > EmployeeLocation.LocationId > Location.CountryId > Country.ISOCountryCode |
| EmployeeCategory | Required | Employee category. | Employee.PartyId > Party.PartyTypeId > PartyType.PartyTypeName |
| GenderName | Required | Gender of the employee. | Employee.GenderId > Gender.GenderName |
| PartyId | Required | PartyId of the employee. | Employee.PartyId |
Aggregation logic
EmployeeDataAggregate stores reporting year wise employee details so that employee count metrics can be easily computed. To consider an employee for a particular reporting year, the "point in time" method is used. For each employee record, the logic finds corresponding EmployeeRelatedParty records where EmployeePartyRelationshipTypeId is Employee-Employer.
In the employee related party records, if Period end date is present, then it checks whether Period start date <= Reporting year's end date <=Period end date. If the condition is met, then employee considered for that reporting year.
In the employee related records, if Period end date isn't present, then it checks whether Period start date <=Reporting year's end date. If the condition is met, then employee considered for that reporting year.
To map the country/region of an employee, this logic is used:
For each employee that is being considered for a specific reporting year, find the record in the EmployeeLocation table where EmployeeLocation.PeriodStartTimestamp <= Reporting year’s end date <=EmployeeLocation.PeriodEndTimestamp.
Note
If there are records in the measurement tables, but the attributes mapped to required fields in the aggregate table are missing, then aggregate table generation fails. You need to ensure that data for required fields is available for all the records in the ESG data model measurement tables.
The CreateAggregateForEmployeeMetrics_INTB notebook contains the computation logic to generate EmployeeDataAggregate.
