Incremental data refresh in Microsoft Sustainability Manager
Microsoft Sustainability Manager provides incremental data imports, so you can bring in only new data when it's available for each data import refresh. Incremental imports reduce the processing time for every refresh, providing access to newer data more quickly and efficiently. It also eliminates the need for custom approaches to bring in only new data with every refresh.
You can easily switch between full refresh or incremental refresh as needed by editing the import/connection. Incremental refresh settings are available only during editing to ensure that you bring all your data into Sustainability Manager during connection setup. It also ensures that you can verify that everything works as expected, such as having all required transformations and mappings correctly made before setting up an incremental refresh. This step is required because after you set up incremental refresh, Power Query doesn't fetch the failed data again. If there's Power Query failure, it automatically fetches the old data.
Incremental refresh is scheduled at the query level. If a data import has multiple queries, you must schedule incremental refresh for all queries within that import. Incremental refresh is supported for all data sources and data types and provides flexibility to schedule incremental refresh across different time-bound parameters.
Schedule an incremental refresh
To schedule incremental refresh for a Power Query connection import, follow these steps.
Select the Power Query import from the Data imports page, and then select Edit refresh policy.
On the Refresh policy page, the Settings section has Full refresh selected. Select Incremental refresh to open a set of dropdowns to define the incremental refresh.
A date or time column is required within the source data to filter the new data. Typical examples include Consumption start date, Consumption end date, or Transaction date.
Store rows from the past indicates how far back the data from the source needs to be stored and must be defined for a specific unit of time, such as number of days, months, quarters, or years.
Refresh rows from the past indicates how far back the data from the source needs to be refreshed and must be defined for a specific unit of time, such as number of days, months, quarters, or years.
Note
The value for Store rows from the past and Refresh rows from the past must be a whole number between 1 and 120. Also, the refresh period must be less than or equal to the storage period.
Select Detect data changes if you want to refresh data only when the maximum value of a particular date or time column changes. You only need to check this field when you're sure that only the selected column has changed and nothing else in the source data.
Select Only refresh data for complete period when you want to refresh data for complete periods. When you save these settings, data from the past will be loaded to your dataflow storage the next time this dataflow is refreshed. Each subsequent refresh updates only data that has changed in the past.
Select Save to schedule the incremental refresh for the selected query. Repeat these steps for all queries where incremental refresh must be scheduled.
Error handling for incremental refresh
Error scenarios in incremental refresh don't include accurate error messaging within the data imports due to data flow limitations. However, you can get detailed error messages by using the data flow ID in Power Apps portal with the following steps.
Go to the import/connection refresh by selecting the failed incremental refresh import on the Data imports page. Note the Dataflow Name in the Error message field.
Go to Power Apps portal and select Dataflows from the left pane. Search for the dataflow name obtained in the previous step, and then select Show refresh history.
Select the latest start time from the list of failed dataflows. A more detailed error message displays with details on the cause of the incremental refresh failure.
Note
Sustainability Manager will provide detailed error messaging for incremental refresh within the data imports like the error messaging for full refresh within a future release.
Important
You must first set up a data import with full refresh to schedule an incremental refresh. This step ensures that all the historical data available within the source is imported into Sustainability Manager before incremental refresh is set up for all subsequent new data.
You need to set up incremental refresh for each query individually within a given import or connection.
Whenever you edit a connection, Sustainability Manager resets the data import and all queries back to full refresh. You must reconfigure the incremental refresh for all the queries within that edited import.
If a data import or connection has Remove previously imported data enabled, the incremental refresh button is disabled in the user interface.
Related information
- Overview of data import
- Import data with Excel templates
- Import data with Power Query
- Import data with data provider connectors
- Import data manually
- Review data imports
- Considerations and FAQ
- Integration
