Extend the Cloud for Sustainability data model

Free trial

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:

• Dataverse documentation
• Tables in Dataverse
• Create and edit columns in Dataverse using Power Apps
• Solutions in Power Apps
• Security concepts in Microsoft Dataverse

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
• Create a new Dataverse table
• Create columns in the table
• Grant access to the new table
• Provision the table
• Add a reference lookup
• Customize data views
• Ingest data
• Calculate scope 3 transportation emissions
• Enable a calculation profile
• View emissions

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.

   

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.

   

   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

   

   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)

   

   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.

   

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.

   

2. In the Choose data source window, 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.

   

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

   

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).

   

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

   

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.

   

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.

   

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.

   

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.

   

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.

   

5. On the menu bar, select Create view, and then select 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.

   

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

   

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

   

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

   

   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.

   

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.

   

   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

   

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
   

   

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

   

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

   

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

   

Related information

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

📄 Product page
🎓 Training

✅ Free trial
🎥 YouTube channel

💻 Community
LinkedIn group