SustainabilityContentResourceOutflowsAggregate 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 SustainabilityContentResourceOutflowsAggregate aggregate table stores the aggregated sustainable content of each resource outflow for applicable sustainable content types by resource outflow, facility, and reporting period.
The CreateAggregateForResourceOutflowsSustainabilityContentMetrics_INTB notebook contains the computation logic.
Input
To generate this aggregate table, the ResourceOutflowsAggregate table should already exist. In addition, you need to store sustainable content baseline percentage for the applicable resource outflows in the ItemSustainableContentBaseline environmental, social, and governance (ESG) data model table in the ProcessedESGData_LH lakehouse.
Note
SustainableContentType is a reference table with a default set of sustainable content types. You can add required sustainable content types to this table.
For details on ingesting, transforming, and loading data into ESG data model tables, go to ESG data estate overview.
This table shows the SustainabilityContentResourceOutflowsAggregate table column details and mapping of columns to ESG data model tables.
| Column name | Description | Mapping to ESG data model or aggregate table |
|---|---|---|
| FacilityId | Unique identifier of the Facility (Required) | ResourceOutflowsAggregate.FacilityId |
| FacilityName | Name of the Facility | ResourceOutflowsAggregate.FacilityName |
| ReportingPeriod | Indicates the aggregation period for ResourceOutflowSustainableContentWeight. Currently only calendar years are supported. (Required) | ResourceOutflowsAggregate.ReportingPeriod |
| ResourceOutflowId | Unique identifier of the resource outflow produced in the facility (Required) | ResourceOutflowAggregate.ResourceOutflowId |
| ResourceOutflowName | Name of the resource outflow produced in the facility | ResourceOutflowAggregate.ResourceOutflowName |
| SustainableContentTypeName | Name of the Sustainable content type, such as Recyclable or Reused. (Required) | ItemSustainableContentBaseline.SustainableContentTypeId > SustainableContentType.SustainableContentTypeName |
| ResourceOutflowSustainableContentBaselinePercentage | The percentage of sustainable content in the resource outflow, based on sustainable content type (Required) | ItemSustainableContentBaseline.ItemSustainableContentBaselinePercentage |
| ResourceOutflowSustainableContentWeight | The weight of the sustainable content of a specific sustainable content type in the resource outflow (Required) | ResourceOutflowSustainableContentBaselinePercentage * ResourceOutflowsAggregate.ResourceOutflowWeight |
| UnitOfMeasureName | Unit of measure for the ResourceOutflowSustainableContentWeight (Required) | ResourceInflowAggregate.UnitOfMeasureName |
Aggregation logic
For each reporting Period (from ResourceOutflowsAggregate.ReportingPeriod), for a ResourceOutflowId (ResourceOutflowsAggregateTable.ResourceOutflowId = ItemSustainableContentBaseline.ItemSKU) find the SustainableContentType and the ItemSustainableContentBaselinePercentage that is applicable for the reporting period (ItemSustainabilityContentPeriodStartDate <= ResourceOutflowsAggregate.Reporting Period's end date <= ItemSustainabilityContentPeriodEndDate).
For each resource outflow, calculate ResourceOutflowSustainabilityContentWeight = Item SustainableContentBaselinePercentage * ResourceOutflowsAggregate.ResourceOutflowWeight for each sustainable content type for each reporting period.
Based on this logic, if more than one row is found for a specific resource outflow, the sustainability content type for a specific reporting period, then it's an error scenario and the SustainabilityContentResourceOutflowAggregate table generation errors out.
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.
