SustainabilityContentResourceInflowsAggregate 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 SustainabilityContentResourceInflowsAggregate aggregate table stores the aggregated "sustainable content" of each resource inflow for applicable sustainable content types by resource inflow, resource outflow, facility, and reporting period.
The CreateAggregateForResourceInflowsSustainabilityContentMetrics_INTB notebook contains the computation logic.
Inputs
To generate this aggregate table, the ResourceInflowsAggregate table should already exist. In addition, you need to store sustainable content baseline percentage for the applicable resource inflows 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 SustainabilityContentResourceInflowsAggregate table column details and mapping of columns to ESG data model tables.
| Column name | Is nullable | Description | Mapping to ESG data model or aggregate table |
|---|---|---|---|
| FacilityId | Required | Unique identifier of the Facility. | ResourceInflowAggregate.FacilityId |
| FacilityName | Optional | Name of the Facility | ResourceInflowsAggregate.FacilityName |
| ReportingPeriod | Required | Indicates the aggregation period for ResourceInflowSustainableContentWeight. Currently only calendar years are supported. | ResourceInflowAggregate.ReportingPeriod |
| ResourceOutflowId | Optional | Unique identifier of the resource outflow produced in the facility | ResourceInflowAggregate.ResourceOutflowId |
| ResourceOutflowName | Optional | Name of the resource outflow produced in the facility | ResourceInflowAggregate.ResourceOutflowName |
| ResourceInflowId | Required | Unique identifier of the item consumed in the facility to generate resource outflow item. | ResourceInflowAggregate.ResourceInflowId |
| ResourceInflowName | Optional | Name of the item that is produced in the facility | ResourceInflowAggregate.ResourceInflowName |
| IsPackaging | Optional | Indicator to identify if a resource inflow is a packaging material for a specific resource outflow | ResourceInflowAggregate.IsPackaging |
| SustainableContentTypeName | Required | Name of the Sustainable content type, such as Recyclable or Reused. | ItemSustainableContentBaseline.SustainableContentTypeId > SustainableContentType.SustainableContentTypeName |
| ResourceInflowSustainableContentBaselinePercentage | Required | The percentage of sustainable content in the input material/resource inflow, based on sustainable content type. | ItemSustainableContentBaseline.ItemSustainableContentBaselinePercentage |
| ResourceInflowSustainableContentWeight | Required | The weight of the sustainable content of a specific sustainable content type in the input material/resource inflow. | ResourceInflowSustainableContentBaselinePercentage * ResourceInflowAggregate.ResourceInflowWeight |
| UnitOfMeasureName | Required | Unit of measure for the ResourceInflowSustainableContentWeight. | ResourceInflowAggregate.UnitOfMeasureName |
| ResourceInflowTypeName | Required | Type of the resource inflow, such as components, materials, or associated process materials. | ResourceInflowAggregate.ResourceInflowTypeName |
Aggregation logic
For each reporting Period (from ResourceInflowsAggregate.ReportingPeriod), for a ResourceInflowId (ResourceInflowsAggregateTable.ResourceInflowId = ItemSustainableContentBaseline.ItemSKU) find the SustainableContentType and the ItemSustainableContentBaselinePercentage that is applicable for the reporting period (ItemSustainabilityContentPeriodStartDate <= ResourceInflowsAggregate.Reporting Period's end date <= ItemSustainabilityContentPeriodEndDate).
For each resource inflow, calculate ResourceInflowSustainabilityContentWeight = Item SustainableContentBaselinePercentage * ResourceInflowsAggregate.ResourceInflowWeight for each sustainable content type for each reporting period.
Based on this logic, if more than one row is found for a specific resource inflow, the sustainability content type for a specific reporting period, then it's an error scenario and the SustainabilityContentResourceInflowAggregate 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.
