The Essentials of Microsoft Sustainability Manager | Part 4: Data Ingestion

Hello everyone! In my previous article, I introduced Settings tab to you. Now it’s time for the most comprehensive and important tab of Sustainability Manager, Data tab. In the Data tab, in general terms, we manage data import and how these data will be calculated.

Here is a list of operations that can be performed within the Data tab:

• Data import

• Management of factor libraries

• Management of calculation models & profiles

As you can see the tasks that can be performed here are very comprehensive, so I decided to break the data tab down into three parts. This is the first part of the data tab and it will focus on data ingestion.

Before delving into the details of data ingestion, I would like to discuss the types of data in question. There are five types of data:

• Activity data: consumption data to be calculated for emissions
• Emission data: pre-calculated emission data
• Reference data: categorical data such as fuel, vehicle, transportation type, etc.
• Water data: data related to the quantity and quality of water
• Waste data: data related to the quantity and quality of waste

We can use four methods importing data:

1. Excel templates
2. Power Query guided experience
3. Manually
4. Partner connectors

In this article, we will pass the Partner connectors method; instead, we will discuss the other three methods in detail. Let’s begin from Excel templates.

1. Data Import with Excel Template

Importing data using an Excel template is the simplest one among the data import methods. There are separate templates for each data category. The process is as follows:

1. Download and fill the template: After downloading and filling in the relevant template, proceed with the import.

As seen here, there are five different types of data: reference data, carbon activity data, carbon emissions data, water data, and waste data.

The reason reference data is shown in step 1 is that it feeds the data in step 2 at multiple points. To give an example, if we enter a fuel type that is not included in the system into a carbon activity data related to a stationary combustion category, it will lead to an error.

All of these templates have multiple sheets containing sub-categories. These templates also serve as guides. Each sheet provides information about the columns that need to be filled out and includes an example a single row entry. These Excel files are imported into the system using the ‘Import spreadsheet’ button.

2. Review and name: The step where the import is named and added a description (optional).

Once the connect button is clicked on, import creation process is completed.

On the data import page, when we first created the import, its status can be seen as ‘Scheduled’. While importing, its status will set as ‘Processing’. When it is successfully completed, it will return to the ‘Complete’ status as shown above.

2. Data Import with Power Query

Power Query is the most comprehensive and detailed data import method. With Power Query, we can retrieve data from numerous sources through connectors and, regardless of the source, manipulate and transform the data using the Power Query interface.

Additionally, we can set schedules and policies for data refresh and perform mapping in the final step. Mapping becomes especially important here if we have used custom columns or if we have custom dimensions within Sustainability Manager.

Data import with Power Query consists of the following stages:

1. Data Category Selection:

As seen, the data categories are listed on the left. The entities belonging to these categories are also listed on the right under the Category name header.

If desired, multiple entities under the same category and/or different entities from different categories can be selected in a single connection. As these are selected, they will be displayed in the Data Transformation Tools panel on the right.

2. Data Source Selection:

There are many source categories available such as File, Database, Microsoft Fabric, Power Platform, Azure, Online services, and more. After selecting the data source, relevant connection configurations are made, and in the next step, we preview the data to ensure it has been successfully retrieved. If there are no issues, we can proceed by clicking ‘Transform Data’ to navigate the Power Query screen.

Here, desired transformations are made. For example, if I need to multiply the quantity to be used for emissions calculation by an additional factor, I can perform this operation. Or if there are reference data values different from those defined in Sustainability Manager, I can update them in bulk using the replace feature. If data types are incorrect, I can correct them.

3. Data Refresh Schedule:

Here, settings related to data refresh method and timing are configured, and decisions are made regarding whether the incoming data from the refresh will overwrite the existing data.

4. Review and Name:

This stage provides a summary view of the imported data categories and configured data refresh schedule settings. We can proceed by giving a name and, if desired, a description to this import.

After clicking ‘Connect,’ we complete the setup of our import process. In its current state, since mapping has not been done and it has not been marked as ready for import, this import remains in a passive state.

5. Mapping:

We can perform the mapping process from here. Or we click skip for now and come back to it later to map fields. Let’s take a look at the mapping process as well.

Since we have imported Mobile Combustion data, we selected ‘Mobile combustion’ as the Data source from the dropdown list and filled in the mappings using the Auto map method. If we notice any errors, we can manually correct them. If we want to map a custom dimension, we can do that in Custom Dimensions section. After saving it in this state, finally, if we want to initiate the import process, we can toggle the ‘Ready to import’ switch on the right to ‘yes’.

If we didn’t perform the mapping and mark it as ‘Ready to import’ in the previous step, we can also initiate the import process from here. Changes related to mapping are also available by clicking Edit mapping button.

If we want to modify the connection, we can click edit import.

Before continuing with manual import method, I want to summarize the differences between excel template and power query experience with the comparison table below:

Excel template vs. Power query experience

3. Manual Import

In manual data import, records are entered one by one. As seen below, by clicking ‘View’ from the Carbon Activities page, you can access the list of records for the category to which you want to enter data. Here, you can also see the number of connections have been created for these categories, which are the imports on the data imports page.

For example, here, let’s say we want to enter a new record for Mobile Combustion. After clicking on ‘View’ next to Mobile Combustion, the page below opens:

This page is called view. This is view of Mobile Combustion carbon activities of Scope 2. Here, records related to Mobile Combustion listed.

I would like to explain the functions of a few buttons that all views have in their ribbons.

New Button: It allows us to access the screen where we can enter a new record, and we will provide an example of this shortly.

Excel Templates Button: When we click the arrow next to ‘Excel templates’ and select ‘Download template,’ we download a suitable template for the selected Excel source in the Power Query experience. It automatically comes pre-filled if records already exist. We can use this template for the Excel source in the Power Query experience by clearing its contents and filling it with new data. It’s also possible to decide which columns will be included into that template by selecting them just before downloading the template.

Export to Excel Button: When clicked the arrow next to it, the list including tasks such as opening in Excel online, downloading as a dynamic PivotTable, and more shows up. So it offers various Excel-specific functions. Directly clicking it performs the same action as the ‘Download template’ button located under the Excel Templates button, with the exception that it doesn’t allow you to choose the desired columns.

We saw how view page looks like, now let’s open a random record that view has.

Here, fields marked with an asterisk (*) are mandatory fields. Speaking specifically for this category, ‘Fuel’ or ‘Distance’ must be entered. The reason there is no red asterisk indicating that these fields are mandatory is that it is not known which of these two fields the user will use. For example, immediately after filling in ‘Distance,’ ‘distance’ and ‘distance unit’ become mandatory.

Additionally, when hovering over the fields, if you see the phrase ‘Look for…’ or if there is a magnifying glass icon next to them, it means that the data to be entered here is kept in lookup tables. In other words, these data should be created as reference data in Sustainability Manager, and selections are made from among those data here.

If desired, custom dimensions can also be entered. For example, there could be a custom dimension called ‘Vehicle Sub Type.’ This can be selected here, filled in, and saved. Now, this record will be added to the view where all mobile combustion activity data is located.

NOTE: I have explained examples of all these data import methods using carbon activity data, but these methods are also applicable to other data types such as reference data and follow the exact same logic. Only the column names may vary. Manual entry for reference and emission data can be done through the relevant category under the Reference data page in the Settings tab for reference data and through the All emissions page in the Analytics tab for emission data.

To Sum Up and What’s Next

In this article, I discussed data ingestion in Sustainability Manager. After talking about the data categories that can be imported, I covered the data import methods and the steps that need to be followed. To enhance understanding, I made an effort to reinforce my explanations with examples and screenshots wherever possible. In my next article, I will talk about a newly introduced feature, Data capture, which uses the AI Builder tool to import purchased electricity bills and record the values from these bills as activity data in Sustainability Manager. See you in my the next article!