PreCalculatedMetricsAggregate 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.
There are certain metrics, such as Annual total remuneration ratio or Gender pay gap, where the data collection or calculation methodology can be specific to the organization. In these cases, you can precalculate the metric based on source systems and store precalculated values in the PartyBusinessMetric table in environmental, social, and governance (ESG) data model.
The PreCalculatedMetricsAggregate table contains denormalized data for precalculated metrics at the party and reporting year level from the PartyBusinessMetric table of the ESG data model.
Inputs
This table shows the required fields you should include in the PartyBusinessMetric table to ensure that the PreCalculatedMetricsAggregate table can be generated.
| Column | Description |
|---|---|
| BusinessMetricId | The foreign key to the BusinessMetric reference table. The BusinessMetric table stores the names of the precalculated metrics. If you're storing data for any of these metrics, use the corresponding BusinessMetricId from this table. You can also create other business metrics and map data to the custom Business metric. In such cases, you need to modify the aggregate table computation logic and metric computation logic accordingly. |
| PartyBusinessMetricValue | Map the business metric’s value in this field. |
| PeriodStartDate | Start date of the reporting period. Currently, only calendar years are supported. For example, use 1/1/2021 for the reporting year 2021. |
| PeriodEndDate | End date of the reporting period. Currently, only calendar years are supported. For example, use 12/31/2021 for the reporting year 2021. |
| PartyId | Unique identifier of the party. |
This table provides a list of business metrics already available in the BusinessMetric reference table.
| BusinessMetricName | PartyId to be mapped in PartyBusinessMetric table |
|---|---|
| Male female pay gap Annual total remuneration ratio Board Gender Diversity Percentage of independent Board members Number of fatalities related to nonemployees due to work-related injuries and ill health Number of fatalities related to employees due to work-related injuries and ill health Rate of recordable work-related accidents (Non-Employee) Rate of recordable work-related accidents (Employee) |
Stores organization-level data at reporting year level. Map Organization’s PartyId with PartyType=Legal Entity. |
| Total training hours completed | Stores each employee’s total training hours completed in a particular reporting year; map Employee’s PartyId. Note: Employee table needs to be populated to store this metric. |
For details on ingesting, transforming, and loading data into ESG data model tables, go to ESG data estate overview.
This table shows the mapping of ESG data model tables with the PreCalculatedMetricsAggregate columns.
| Column name | Mapping to ESG data model or aggregate table |
|---|---|
| PartyId | PartyId |
| BusinessMetricName | BusinessMetricId > BusinessMetric.BusinessMetricName |
| BusinessMetricValue | PartyBusinessMetricValue |
| ReportingPeriod | Year of PeriodEndDate Note: For this aggregate table, only records where PeriodStartDate and PeriodEndDate match the first and last day of the calendar year. For example, Period start date is 1/1/2021 and end date is 12/31/2021 are considered. |
| GenderName | PartyId > Employee.GenderId > Gender.GenderName |
| PartyTypeName | PartyId > Party.PartyTypeId > PartyType.PartyTypeName |
The CreateAggregateForPreCalculatedMetrics_INTB notebook contains the computation logic to calculate and store the PreCalculatedMetricsAggregate table in computedESGMetrics_LH.
