EmissionsAggregate 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.
This aggregate table stores aggregated CO2 equivalent emissions data from the following environmental, social, and governance (ESG) data model tables in these dimensions:
- Reporting period
- Scope
- Emission source
- Unit of measure
- CountryOrRegion
- Greenhouse Gas
- Accounting Method
- Party
- Secondary Party
The CreateAggregatesForEmissionMetrics_INTB notebook contains the computation logic for the same.
Inputs
For generating the EmissionsAggregate table, you need to map your source data to the ESG data model tables for the applicable scenario.
| Scenario | Scope | ESG data model tables |
|---|---|---|
| Customer stores Total CO2 equivalent emission quantity | Scope 1 Scope 2 Scope 3 |
PARTY SCOPE CATEGORY PURPOSE GREENHOUSE GAS EQUIVALENT EMISSIONS |
| Greenhouse gas specific CO2 equivalent emission quantity | Scope 1 | PARTY GREENHOUSE GAS PARTY PROCESS TYPE GREENHOUSE GAS PROCESS GREENHOUSE GAS PROCESS ASSET GREENHOUSE GAS |
| Scope 2 | PARTY INDIRECT EMISSIONS PARTY VENDOR INDIRECT EMISSIONS PARTY VENDOR GENERATOR INDIRECT EMISSIONS |
|
| Scope 3 | PARTY SCOPE CATEGORY GREENHOUSE GAS |
Note
- Refer to the ESG data estate documentation to understand ingesting, transforming and loading data into the ESG data model tables.
- Ensure that data isn't duplicated across the ESG data model tables. For example, the Contoso POD Factory facility emits 200 mtCO2e of CO2 gas as scope 1 emissions, 100 mtCO2e of CH4 gas as Scope 1 emissions for the month of Jan 2024. They store this data in the Party Greenhouse Gas table as two records, one for each gas. They also store the total scope 1 emissions as 300 mtCO2e in the Party Scope Category Purpose Greenhouse Gas Equivalent Emissions table. In this case, the same emission will be double counted in the EmissionsAggregate table calculation. You should store either the data at the gas level or as total emissions.
This table shows the EmissionAggregate table’s column descriptions and mapping with ESG data model tables.
| Columns | Is nullable | Description | Mapping with ESG data model table columns |
|---|---|---|---|
| Co2eEmissionUnits | Required | Stores the aggregated CO2 equivalent emission value. | Co2EEmissionsUnits |
| ReportingPeriod | Required | Specifies the period for which the emission value is aggregated. Note: - Currently only calendar years are supported in aggregate tables. - The logic to consider a PeriodStartDate or PeriodEndDate for finding the reporting year depends on the Emission source. Refer to Logic emission sources to find logic for each emission source. |
PeriodStartDate or PeriodEndDate |
| UnitOfMeasureName | Required | Specifies the unit of the emission value. | CO2eEmissionsUnitOfMeasureId > UnitOfMeasure.UnitOfMeasureName UnitOfMeasure is a reference table in the ESG data model. The default unit of measure name is mtCO2e. All the CO2eEmissions data from the ESG data model tables is converted to mtCO2e and then stored in the aggregate table. |
| Scope | Required | Specifies the Scope of emission. It can be Scope 1 - Direct emissions, Scope 2 - indirect emissions, and Scope 3 – emissions from the value chain. | The mapping varies with the ESG data model as follows: - In Party Scope Category Purpose Greenhouse Gas Equivalent Emissions or Party Scope Category Greenhouse Gas tables, this field maps to GreenhouseGasEmissionsScopeID > GreenhouseGasEmissionsScope.GreenhouseGasEmissionsScopeName. - In Party Greenhouse Gas, Party Process Type Greenhouse Gas, Party Process Greenhouse Gas, and Party Asset Greenhouse Gas, this field is mapped to scope 1, because these four tables are used to only store scope 1 greenhouse gas level CO2 equivalent emissions. - In Party Indirect Emissions, Party Vendor Indirect Emissions, and Party Vendor Generator Indirect Emissions the value is mapped to scope 2, because these tables are supposed to store data only for scope 2 emissions. |
| EmissionSource | Required | Used for classifying emissions within a specific scope. For example, for scope 1, EmissionSource can be Stationary, Industrial Processes, Mobile, or Fugitive. For scope 3, it can be Category 1 - Purchased Goods and Services, Category 2 - Capital Goods, for example. | The mapping varies with the ESG data model as follows: - In Party Scope Category Purpose Greenhouse Gas Equivalent Emissions or Party Scope Category Greenhouse Gas this field maps to GreenhouseGasEmissionsCategoryId > GreenhouseGasEmissionsCategory.GreenhouseGasEmissionsCategoryName. GreenhouseGasEmissionsCategory is a reference table in the ESG data model. - In PartyGreenhouseGas, PartyProcessTypeGreenhouseGas, PartyProcessGreenhouseGas, and PartyAssetGreenhouseGas, this field is mapped to GreenhouseGasSource TypeId> GreenhouseGasSourceType.GreenhouseGasSourceTypeName. GreenhouseGasSourceType is a reference table in ESG data model. - In PartyIndirectEmissions, Party VendorIndirectEmissions, and PartyVendorGeneratorIndirectEmissions map to PurchasedEnergyTypeId> PurchasedEnergyType.PurchasedEnergyTypeName. PurchasedEnergyType is a reference table in ESG data model. |
| AccountingMethod | Optional | Approach or method to quantify and report greenhouse gas emissions. | CalculationAlgorithmId > CalculationAlgorithm.CalculationAlgorithmBasisId > CalculationAlgorithmBasis.Calculation AlgorithmBasisName Note - Currently this attribute is supported only for Scope 2 emissions. For Scope 1 and 3, you should keep this attribute as null while mapping data from outside data sources. For scope 1 and 3, the data coming from Sustainability manager, is tagged as Location based. |
| CountryOrRegion | Optional | Country/region associated with the emissions record. | Maps the country/region from the location details of the Party or secondary party associated with the emission record as follows: 1. If Party or Secondary Party is of PartyType = Facility, then map corresponding Facility.Location.Country.ISOCountryName. 2. If this condition isn't satisfied, then check if Party or Secondary Party is of PartyType = Organization unit, then map corresponding PartyOrganization.Location.Country.ISOCountryName. 3. If this condition isn't satisfied, then map Party.PartyLocation.Country.ISOCountryName. 4. If this condition isn't satisfied, then map secondary party's Partylocation.Country.ISOCountryName. 5. Else country/region for the record remains null. |
| GreenhouseGasName | Optional | Name of the greenhouse gas. | Not applicable if data is mapped from PartyScopeCategoryPurposeGreenhouseGasEquivalentEmissions. For other ESG data model tables, this field maps to GreenhouseGasId > GreenhouseGas.GreenhouseGasName. GreenhouseGas is a reference table in ESG data model. |
| PartyName | Required | Name of the Party | PartyId> Party.PartyName Note: Based on the scenarios explained in Configuration parameters, the PartyName here can be the measurement table's PartyId>Party.PartyName or RelatedParty.RelatedPartyId>Party.PartyName. |
| PartyId | Required | Unique identifier of the Party | PartyId Note: Based on the scenarios explained in Configuration parameters, the PartyId here can be measurement table's PartyId or RelatedParty.RelatedPartyId. |
| PartyTypeName | Required | The Party type at which data is aggregated and stored in the aggregate tables. | PartyId >Party.PartyTypeId >PartyTypeName Note: Based on the scenarios explained in Configuration parameters, the PartyId here can be measurement table's PartyId or RelatedParty.RelatedPartyId. |
| Secondary PartyName | Optional | Name of the Party. Only applicable when the measurement data in the ESG data model table is at a combination of two parties. For example, Facility and organization unit combination. In such cases, Party and Secondary Party store the constituent parties of the combination, such as Facility and Organization unit. | PartyId > RelatedParty.RelatedPartyId >Party.PartyName |
| Secondary PartyId | Optional | Unique identifier of the Party. Only applicable when the measurement data in the ESG data model table is at a combination of two parties, such as Facility and organization unit combination. In such cases, Party and Secondary Party store the constituent parties of the combination. | PartyId > RelatedParty.RelatedPartyId |
| Secondary PartyType | Optional | The Party type at which data is aggregated and stored in the aggregate tables. Only applicable when the measurement data in the ESG data model table is at a combination of two parties, such as Facility and organization unit combination. | PartyId> RelatedParty.RelatedPartyId >Party.PartyTypeId >PartyType.PartyTypeName |
| IsRolledUp | Optional | IsRolledUp flag indicates whether a row is a rolled-up record or not. The computation logic always generates the aggregate table with this flag as false. Capability provides a utility function to roll up records in the aggregate table along the organization's hierarchy (Party or secondary party dimension). The rolled-up records are also stored in the same aggregate table. | Calculated column. Value not mapped from ESG data model tables. |
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.
Logic for emission sources
| Emission Source | Reporting year is year of PeriodStartDate | Reporting year is year of PeriodEndDate |
|---|---|---|
| 6. Business travel | X | |
| Fugitive emissions (Emissions) | X | |
| 1. Purchased goods and services (Emissions) | X | |
| 2. Capital goods (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Mobile combustion (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased cooling (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased electricity (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased heat (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased steam (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Stationary combustion (Emissions) | X | |
| 3. (Preview) Fuel- and energy-related activities - Unspecified (Emissions) | X | |
| 4. Upstream transportation and distribution (Emissions) | X | |
| 8. Upstream leased assets - Fugitive emissions (Emissions) | X | |
| 9. Downstream transportation and distribution (Emissions) | X | |
| 10. Processing of sold products (Emissions) | X | |
| 13. Downstream leased assets - Fugitive emissions (Emissions) | X | |
| 7. Employee commuting | X | |
| 12. End-of-life treatment of sold products | X | |
| 11. Use of sold products - Fugitive emissions | X | |
| 14. Franchises - Fugitive emissions | X | |
| Industrial Process | X | |
| 10. Processing of sold products - Industrial process | X | |
| 11. Use of sold products - Industrial process | X | |
| 14. Franchises - Industrial process | X | |
| 15. Investments - Business loans and unlisted equity | X | |
| 15. Investments - Listed equity and corporate bonds | X | |
| 15. Investments - Commercial real estate | X | |
| 15. Investments - Sovereign bonds | X | |
| 15. Investments - Project finance | X | |
| 15. Investments - Motor vehicle loan | X | |
| 15. Investments - Mortgages | X | |
| Mobile combustion | X | |
| 3. (Preview) Fuel- and energy-related activities - Mobile combustion | X | |
| 8. Upstream leased assets - Mobile combustion | X | |
| 10. Processing of sold products - Mobile combustion | X | |
| 11. Use of sold products - Mobile combustion | X | |
| 13. Downstream leased assets - Mobile combustion | X | |
| 14. Franchises - Mobile combustion | X | |
| Purchased cooling | X | |
| Purchased electricity | X | |
| Purchased heat | X | |
| Purchased steam | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased cooling | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased electricity | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased heat | X | |
| 3. (Preview) Fuel- and energy-related activities - Purchased steam | X | |
| 8. Upstream leased assets - Purchased steam | X | |
| 8. Upstream leased assets - Purchased heat | X | |
| 8. Upstream leased assets - Purchased cooling | X | |
| 8. Upstream leased assets - Purchased electricity | X | |
| 10. Processing of sold products - Purchased electricity | X | |
| 10. Processing of sold products - Purchased cooling | X | |
| 10. Processing of sold products - Purchased heat | X | |
| 10. Processing of sold products - Purchased steam | X | |
| 11. Use of sold products - Purchased heat | X | |
| 11. Use of sold products - Purchased cooling | X | |
| 11. Use of sold products - Purchased electricity | X | |
| 11. Use of sold products - Purchased steam | X | |
| 13. Downstream leased assets - Purchased electricity | X | |
| 13. Downstream leased assets - Purchased heat | X | |
| 13. Downstream leased assets - Purchased cooling | X | |
| 13. Downstream leased assets - Purchased steam | X | |
| 14. Franchises - Purchased heat | X | |
| 14. Franchises - Purchased electricity | X | |
| 14. Franchises - Purchased cooling | X | |
| 14. Franchises - Purchased steam | X | |
| Stationary combustion | X | |
| 3. (Preview) Fuel- and energy-related activities - Stationary combustion | X | |
| 8. Upstream leased assets - Stationary combustion | X | |
| 10. Processing of sold products - Stationary combustion | X | |
| 11. Use of sold products - Stationary combustion | X | |
| 13. Downstream leased assets - Stationary combustion | X | |
| 14. Franchises - Stationary combustion | X | |
| 5. Waste generated in operations | X | |
| 10. Processing of sold products - Waste generated in operations | X |
For these categories, in scenarios where data is mapped from Microsoft Sustainability Manager, the transaction date attribute of the emission record mapped to PeriodEndDate and the same considered in deciding the reporting year.
- Fugitive emissions (Emissions)
- 1 - Purchased goods and services (Emissions)
- 2 - Capital goods (Emissions)
- 4 - Upstream transportation and distribution (Emissions)
- 8 - Upstream leased assets - Fugitive emissions (Emissions)
- 9 - Downstream transportation and distribution (Emissions)
- 10 - Processing of sold products (Emissions)
- 13 - Downstream leased assets - Fugitive emissions (Emissions)
- 14 - Franchises - Fugitive emissions
Configuration parameters
You can specify the parameters to compute EmissionsAggregate table for the applicable scenario. You can set the configuration parameters in the aggregate_tables_config.json file under the ESGMetrics/Config folder in the ConfigAndDemoData_LH lakehouse.
Scenario 1
Measurement data (for example, CO2 equivalent emission) is collected at a combination of parties. The measurement data is stored in the ESG data model table as a joint party. For example, a joint party record for the combination of facility F1 and organization unit OU1. To learn more about storing organizational hierarchy data, go to Store organizational hierarchy data.
Example hierarchy:
Sample records:
| Facility | Organization Unit | Scope | Emission Source | Period Start Date | Period End Date | CO2e Emissions | Unit |
|---|---|---|---|---|---|---|---|
| Facility 1 | Contoso HQ | Scope 1 | Stationary combustion |
1/1/2023 | 1/31/2023 | 100 | mtCO2e |
| Facility 1 | Contoso EUR | Scope 1 | Stationary combustion |
1/1/2023 | 1/31/2023 | 200 | mtCO2e |
Stored in the PartyScopeCategoryPurposeGreenhouseGasEquivalentEmissions table as:
| Party | Scope | Emission Source | Period Start Date | Period End Date | CO2e Emissions | Unit |
|---|---|---|---|---|---|---|
| Facility1_Contoso_HQ | Scope 1 | Stationary combustion |
1/1/2023 | 31/1/2023 | 100 | mtCO2e |
| Facility1_ContosoEUR | Scope 1 | Stationary combustion |
1/1/2023 | 31/1/2023 | 200 | mtCO2e |
In this case, in the aggregate table, data needs to be stored at the facility and organizational unit level. Data can easily be rolled up along organization hierarchy as required and metrics can be generated for specific nodes in the hierarchy.
Specify these parameters:
Party Type: The party type at which data is aggregated and stored in the aggregate tables. Specify it as the constituent party’s party type. In this case, Facility or Organization unit. Specify Party Type as a value such that all parties in the measurement table can be rolled up to a party of the specified party type or the party type matches the party type of parties in the measurement table.
Secondary Party Type: Specify it as the second constituent party’s Party type. In the considered example, if Party type is specified as Organization unit, this one can be Facility. This parameter is optional and only applicable for this scenario where measurement data is stored at combination of two parties.
Relationship type name: The relationship type between the party at which the measurement data is stored in the measurement tables and the Party types for the aggregate table. For example, if you use FacilityOrganizationUnitJointPartyRelation as the RelationshipType to store the relation between joint party and facility and joint party and organization unit, then here you specify relationship type name as FacilityOrganizationUnitJointPartyRelation.
IsRelationshipHierarchical: Not applicable for this scenario. Can be kept blank.
Scenario 2
Organization hierarchy is a parent child hierarchy where each child has single parent. Data in the measurement table is stored as parties that are nodes in the organization hierarchy.
Example hierarchy:
Specify these parameters:
Party Type: The party type at which data is aggregated and stored in the aggregate tables. Data in the aggregate table can be aggregated as a single party type. Specify the party type as such a value that party of each measurement table record can be rolled into a party of the specified party type using the Relationship type name specified in the subsequent parameter. For this scenario:
- If measurement data is stored at leaf nodes (Facility 1, 2, 3 level), then you can specify a party type of aggregation as Facility or Organization unit or Legal entity.
- If measurement data is stored at leaf nodes and in some cases at Organization unit level as well, then Party type of aggregation can be organization unit or legal entity.
Secondary Party Type: Not applicable for this scenario. Keep blank.
Relationship type name: Specify the relationship name (Party Relationship Type) used to store the relationship between child and parent node.
IsRelationshipHierarchical: Set to true for this scenario.
Note
Metrics are computed on top of aggregate table. If you want to generate metric that contains specific party type break ups (for example, organization unit level break up), then ensure that the aggregate table stores the aggregate data at organization unit level or at more granular level than organization unit level so that you can roll up the data to organization unit level for the metrics.
Unit of measure:
By default, the UnitOfMeasureNames parameter in the config file for the EmissionAggregate table is specified as mtCO2e. If you want to generate the aggregate table and metrics in an alternate unit of measure, update the unit of measure in the parameter accordingly.
Note
The specified unit of measure names should be valid unit of measure names in the UnitOfMeasure ESG data model reference table, and the conversion record should exist in the UnitOfMeasureConversion ESG data model reference table to convert measurement data from ESG data model tables to the specified unit of measure.
