ResourceInflowsAggregate 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 ResourceInflowsAggregate aggregate table stores the aggregated weight of each resource inflow by resource inflow, resource outflow, facility, and reporting period. The CreateAggregateForResourceInflowMetrics_INTB notebook contains the computation logic to aggregate the data from environmental, social, and governance (ESG) data model tables and generate the ResourceInflowsAggregate table.
Inputs
For this aggregate table, you should store your data in these ESG data model tables in the ProcessedESGData_LH lakehouse.
Facility Item Consumption: Used for storing all items that are in use but aren't part of the product. For example, cleaning fluids consumed by the machinery.
Facility Item Production: Used for capturing the resource outflows (for example, finished goods) produced. The raw materials and packaging material used for producing the resource outflow should be stored in:
- Item Bill of Material Line: For items other than packaging.
- Item Packaging Item: For packaging material.
For details on ingesting, transforming, and loading data into ESG data model tables, go to ESG data estate overview.
This table shows the column descriptions for the ResourcInflowsAggregate table and mapping of columns to ESG data model tables.
| Column name | Is nullable | Description | Mapping to ESG data model |
|---|---|---|---|
| FacilityId | Required | Unique identifier of the Facility | FacilityId |
| FacilityName | Optional | Name of the Facility | FacilityId > Facility.FacilityName |
| ReportingPeriod | Required | Specifies the period for which the resource inflow weight is aggregated. Currently, only calendar years are supported. | Year of Period end date if Period end date is available, else year of Period start date |
| ResourceOutflowId | Optional | Unique identifier of the resource outflow produced in the facility. Not applicable for data aggregated from FacilityItemConsumption table. |
FacilityItemProduction.ItemSKU |
| ResourceOutflowName | Optional | Name of the resource outflow produced in the facility. | FacilityItemProduction.ItemSKU > Item.ItemName |
| ResourceInflowId | Required | Unique identifier of the item consumed in the facility to generate resource outflow item. 1. For data aggregated from FacilityItemConsumption, this field maps to FacilityItemConsumption.ItemSKU. 2. For data aggregated from FacilityItemProduction, each ItemSKU (Resource outflow) maps to corresponding ComponentItemSKUs (Resource inflows) from ItemBillOfMaterialLine. Each ItemSKU and ComponentItemSKU have a record in the aggregate table by facility and Reporting period. 3. For each ItemSKU, find corresponding PackingItemSKU from ItemPackagingItem table. Each ItemSKU and PackingItemSKU have a record in the aggregate table by facility and reporting period. |
FacilityItemConsumption.ItemSKU (for consumption data) or ItemBillOfMaterialLine.ComponentItemSKU or ItemPackagingItem.PackingItemSKU (for production data) |
| ResourceInflowName | Optional | Name of the item that is produced in the facility. Logic same as for ResourceInflowId. |
ComponentItemSKU > Item.ItemName or PackingItemSKU > Item.ItemName |
| ResourceInflowWeight | Required | Weight of the resource inflow consumed. 1. FacilityItemConsumptionQuantity Item.ItemWeight for each ItemSKU (ResourceInflowId) from FacilityItemConsumption table. 2. BOMQuantity Item.ItemWeight FacilityItemProductionQuantity for each ComponentItemSKU of each ItemSKU produced as per FacilityItemProduction table. 3. ItemPackagingItemQuantity Item.ItemWeight FacilityItemProductionQuantity for each packaging item for each ItemSKU as per FacilityItemProduction table. |
Calculated from FacilityItemConsumption and FacilityItemProduction tables, based on consumption or production data. |
| UnitOfMeasureName | Required | Unit of measure for the Resource inflow weight | UnitOfMeasureId > UnitOfMeasure.UnitOfMeasureName |
| IsPackaging | Optional | Indicator to identify if a resource inflow is a packaging material for a specific resource outflow. Marked as true for cases where ResourceInflowId is from ItemPackagingItem’s PackingItemSKU. |
Mark as true if ResourceInflowId comes from ItemPackagingItem.PackingItemSKU |
| ResourceInflowTypeName | Optional | Type of the resource inflow (for example, components, materials, or associated process materials.) | ComponentItemSKU > Item.ItemTypeId > Item.ItemTypeName (for all ResourceInflowIds) |
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.
By default, the computation logic aggregates the weight in kg unit of measure. All the data from ESG data model table is converted to kg and then stored in the aggregate table. If you want to generate metrics in different unit of measure or multiple units of measure, then generate the aggregate table in the required unit of measure. Update the UnitOfMeasureNames key for ResourceInflowsAggregate in the aggregate_tables_config.json file in ESG metrics folder in the ConfigAndDemoData lakehouse. Ensure that UnitOfMeasureName specified is a valid UnitOfMeasureName in the UnitOfMeasure reference table and the conversion factor from source unit to target unit is available in the UnitOfMeasureConversion table.
After the aggregate table generates in the required unit of measure, you can update the metric definition or define custom metric. For more details on customizing metrics, go to Create custom metrics.
