Extend the Cloud for Sustainability data model

Microsoft Cloud for Sustainability Tech Summit November 2024.

Microsoft Sustainability Manager is built on top of Dataverse as a model-driven power app. Users can use the native Dataverse capabilities to extend the data model and use it in carbon calculations and custom reporting. You can add an attribute to any Microsoft Sustainability Manger table in Dataverse, refer to it in calculations using Power Fx, and add it to grid views and edit forms.

Examples of when you might want to extend the Cloud for Sustainability data model include the following scenarios:

  • Associate produced items with purchased electricity activity
  • Associate fugitive emission with laboratory, or leased assets
  • Qualify purchased electricity with custom impact

For a step-by-step walk-through of extending the data model, go to Example: Electric Mode table in this article.

To extend Microsoft Sustainability Manager, you'll need to do the following procedures:

  1. Create a new Dataverse table to capture your organization's data. For an example, go to Create a new Dataverse table.

  2. Ingest the new table directly into Dataverse or extend the Microsoft Sustainability Manager UI to ingest it as reference data within the embedded Microsoft Sustainability Manager experience. For an example, go to Ingest data.

    Note

    You can't use the table as reference data in calculations. You can only use it as reference data within the Power Fx editor.

  3. Extend the Cloud for Sustainability data model with a reference to the new table. For an example, go to Add a reference lookup.

  4. Enable Microsoft Sustainability Manager access to the new table by giving it the Sustainability Service Application Role - Custom security role. For an example, go to Grant access to the new table.

  5. Ingest Microsoft Sustainability Manager data with additional attributes. For an example, go to Ingest data

  6. Use the additional attributes in calculations. For an example, go to Calculate scope 3 transportation emissions.

  7. Modify grid views and edit forms to show new attributes. For an example, go to Customize data views.

  8. Create custom Power BI reports with the new attributes.

Keep the following information in mind while following the above steps:

  • If the extension is simple, such as a boolean value, you might not need to create a new Dataverse table, as indicated in step 1. In step 3, just extend a Microsoft Sustainability Manager table with a new boolean attribute. In this case, there's no need for an extra custom table.

  • If you're using an external table, add the Read privilege to the Sustainability Service Application Role - Custom role on the external table. The role should be at the Organization level.

    Note

    If the calculation for an activity says a given value is null or blank, and if the record does have a value for that custom lookup, then you should ensure this security role has access to that other table.

  • Ingestion via the Microsoft Sustainability Manager UI is optional. For more elaborate extensions such as referencing third party solutions, ingestion of referenced data is likely to take place elsewhere.

  • Modifying grid views and edit forms is only a convenience if you want to view or edit the extended data within Microsoft Sustainability Manager.

  • Custom Power BI reports are outside the scope of this article.

  • To enable access in step 4 and modify edit forms, you must create a Dataverse solution and perform these steps in it.

Actions

To view new attributes in Microsoft Sustainability Manager grid view:

  • In Microsoft Sustainability Manager, edit grid view columns, add desired attributes, and save the view.

To add a form in Microsoft Sustainability Manager:

  • In Power Apps, add an existing form for Microsoft Sustainability Manager table to be extended, and then modify the main form to add anew attribute.

Note

You must do this step in a new Dataverse solution.

To make a new table available for ingestion within Microsoft Sustainability Manager as reference data:

  1. Modify the reference data table in Power Apps to add a new table.

  2. Add Write and Append privileges to the role for that table.

For more information about performing these actions, go to the following articles:

Example: Electric Mode table

More transportation and distribution organizations are shifting from combustion engines to hybrid and fully electric solutions. This shift makes the carbon calculation for scope 3 category 4 and 9 slightly more complex, but necessary to separate and understand vehicle fuel sources to realize carbon reductions. Microsoft Sustainability Manager provides this flexibility through simple extensions using Power Apps.

In this example, we're going to expand our data model to account for electric vehicle options, ingest new data, and then use that data to calculate newly reduced emissions and some custom emission factors. This example uses no-code and low-code customizations that use Microsoft Sustainability Manager's user interface.

Our example will guide you through the following steps:

Prerequisites

For this example, you'll need to meet the following prerequisites:

  • Microsoft Sustainability Manager is already installed.
  • Proficiency with Microsoft Sustainability Manager.
  • Proficiency with sustainability practices, particularly with scope 3 category 4 and 9: Transportation and Distribution
  • Advanced understanding of Dataverse.
  • Admin privileges to manage Dataverse tables and security roles.

Create a new Dataverse table

We'll start in Dataverse, which is a no-code or low-code developer's solution for business applications. You can access Dataverse through Power Apps. For more information about Dataverse, go to What is Microsoft Dataverse?.

First, create a new table to store the new modes of transportation and distribution, which will indicate the level of efficiency provided by electric vehicles. This table will allow you to add details about electric hybrid vehicles that you use in your supply chain for transportation and distribution.

  1. Go to Power Apps.

  2. On the left navigation pane, select Dataverse, and then select Tables.

  3. On the top menu bar, select New table.

    Select New table.

  4. In the New table panel, enter the following values:

    Property Value
    Display name Electric Mode
    Plural name Electric Modes
    Schema name TDElectricMode

    Note

    Be sure to remember the full schema name, including the five characters preceding the name you entered, for later use.

    Create Electric Mode table.

    For more information about creating or editing Dataverse tables, go to Create and edit tables using Power Apps.

  5. Select Save at the bottom of the panel.

Create columns in the table

Next, you'll create new columns in the Electric Mode table you created. The columns in the table will be used in data and calculations to describe the different levels of electrification.

One of these columns contains a multiplier factor that will be used in the calculations, plus a description field.

  1. In the Electric Mode columns and data section of the Electric Mode table page, select + to add a column.

    Note

    The Name property for the column already exists by default.

    For more information about creating or editing columns, go to Create and edit columns in Dataverse using Power Apps.

  2. Create a Description column: In the New column panel, enter or select the following values:

    Property Value
    Display name Description
    Data type Single line of text
    Format Text
    Behavior Simple
    Required Optional
    Searchable (Select the checkbox for Yes)
    Schema name Description
    Maximum character count 100
    Input method editor (IME) mode Auto

    Create Description column.

    Select Save at the bottom of the panel to save the column.

  3. Create a Multiplier column: Select + again to create a new column. In the New column panel, enter or select the following values:

    Property Value
    Display name Multiplier
    Data type Decimal
    Behavior Simple
    Required Optional
    Searchable (Select the checkbox for Yes)

    Create Multiplier column.

    Select Save at the bottom of the panel to save the column.

Grant access to the new table

You'll need to grant Microsoft Sustainability Manager access to the new table.

  1. In a new browser window, Sign in to the Power Platform admin center.

  2. Select Settings > Security > Security Roles.

  3. Select the Sustainability Service Application Role - Custom role.

  4. In the Custom Entities tab, grant the Electric Mode table Read (or Read and Write) access.

  5. Select Save and Close.

    Grant access to the Electric Mode table.

Provision the table

Provision the table with fleet electrification types. In this example, we're adding them via Power Query.

  1. In your Power Apps browser window, on the menu bar, select Import, and then select Import data.

    Import data.

  2. In the Choose data source window, select Text/CSV.

    Select Text/CSV.

  3. In the Connect to data source window, under Connection settings, link to the file that contains your data, and then select Next at the bottom of the window.

    Link to your data file.

  4. In the Preview file data window, verify the data, and then select Next.

    Verify the data.

  5. In the next screen, select Next.

  6. In the Map tables window, under Load settings, select Load to existing table, and then select the Destination table (in this case, TDElectricMode).

    Select Load to existing table.

  7. While still in the Map tables window, under Column mapping, map the Description, Multiplier, and Name columns. Select Next.

    Add Name to the Source column.

  8. In the Refresh settings window, select Publish.

Add a reference lookup

Now we'll reference the extended table within the existing scope 3 category 4 Transportation and distribution table by adding a reference lookup from the Transportation and distribution table to the Electric Mode table.

  1. At the top of the screen in the breadcrumb, select Tables.

    Select Tables in the breadcrumb.

  2. In the Tables screen, select All, enter Transportation in the search box on the upper right, and then select the Transportation and distribution table.

    Select the Transportation and distribution table.

  3. On the Transportation and distribution table page, in the Transportation and distribution columns and data grid, select + to add a lookup column.

  4. Enter or select the following values:

    • In Display name, enter Vehicle Electric Mode.
    • Under Data type, select Lookup, and then select Lookup.
    • In Related table, select Electric Mode.
    • The Schema name and Relationship name fields will populate automatically.

    Add lookup column.

  5. Select Save.

Customize data views

  1. In Microsoft Sustainability Manager, navigate to the Electric Upstream transportation and distribution page, and then select Edit columns.

  2. In the Edit columns: Transportation and distributions side panel, select Add columns.

  3. Select Vehicle Electric Mode, and then select Close.

    Add Vehicle Electric Mode column.

  4. Change the order of the columns as needed. In this case, we've moved Vehicle Electric Mode to appear after Description. Select Apply when done.

    Move Vehicle Electric Mode to appear after Description.

  5. On the menu bar, select Create view, and then select Save changes to current view.

    Save changes to current view.

  6. On the popup that says Filters have been saved for the current view, select OK.

Ingest data

Next, we need to ingest activity data for transportation and distribution, our new Electric Mode reference data, and factors to map that data to. Each of these types of data follow the same process.

You can ingest data into Microsoft Sustainability Manager in four ways, which allows you to pull data in from just about any source:

  • Manually
  • From Excel or CSV files
  • New connections through Power Query
  • Prebuilt connectors

In this case, we're going to set up a new connection through Power Query.

  1. In Microsoft Sustainability Manager, under Data management in the left navigation pane, select Data connections, and then select New.

    Create new data connection.

  2. On the Select data type screen, select Reference data, select Emission factors, and then select Next.

    Select data type.

  3. On the Choose connector screen, select CSV file.

    Select connector.

  4. On the Connect to data source screen, select the CSV file to link to, and then select Next.

    Select a CSV file to connect to.

    On the Preview file data screen, verify the data, and then select Transform data.

  5. On the Power Query menu bar, select Map to entity. In the Map to CDM entity window, select Auto map, and then select OK.

    Map to Common Data Model entity.

  6. On the Schedule data import screen, select Next.

  7. On the Review and finish screen, in the Name field, enter Improved Electric Mode Factors, and then select Connect.

    Review and finish import.

    When complete, select Done.

Calculate scope 3 transportation emissions

Our next step is to calculate scope 3 transportation emissions. You can use the following methods for calculations:

  • Use prebuilt calculation models
  • Tailor calculations
  • Bring your own factor libraries
  • Bring precalculated emissions

For this scenario, we're using custom data and formulas to generate more accurate results for our organization. You can use existing emission factors or update them as required. For more information about updating emission factors, go to Emission factors.

  1. Under Calculations on the left navigation pane, select Calculation models, and then select New.

  2. Select + to add Source. In the Source Details pane, enter or select the following values:

    Field Value
    Category name Upstream T&D with Electric Vehicles
    Activity data 4. Upstream transportation and distribution

    Edit source details.

  3. Select + to add Calculate. In the Calculate Details pane, enter or select the following values:

    Field Value
    Category name Apply Electric Mode Multiplier
    Define calculate function (Copy and enter the text following this table, changing the variables as necessary; you'll need to change cr59b to the prefix assigned in your environment.)
    Output unit Distance Unit
    Output variable name EVDistance
       Activity.msdyn_distance*Activity.cr59b_tdvehicleelectricmode.cr59b_multiplier
    

    Edit calculate details.

  4. Select + to add Report. In the Report Details pane, enter or select the following values:

    Field Value
    Category name Report T&D with EV
    Emission report value EVDistance
    Emission factor library Electric Mode Factors
    Emission factor Transport mode

    Edit report details.

  5. Select Save.

Enable a calculation profile

Calculation profiles enable you to narrow data applied to a calculation and control the frequency of the calculation.

  1. Under Calculations on the left navigation pane, select Calculation profiles, and then select New Calculation profile.

  2. In the Calculation profile set up screen, enter or select the following values, and then select Next:

    Field Value
    Calculation profile name Upstream T&D with Electric Vehicles (execute)
    Emissions source 4. Upstream transportation and distribution
    Activity data to include in calculation Vehicle Electric Mode | Contains data
    Calculation model Upstream T&D with Electric Vehicles

    Set up calculation profile.

  3. Verify the results and let it run.

View emissions

You can now see the results of your emissions reduction project.

  1. On the left navigation pane, select Analytics, and then select All emissions.

  2. On the All emissions page, filter the Emissions column by Upstream transportation and distribution

    Filter the emissions column.

Microsoft Cloud for Sustainability data model
Emission factors
Record uniqueness in Microsoft Sustainability Manager.