Our Nimbus Power BI Template is designed in a way it can be incrementally refreshed. This allows you to store data for a set period of time and then add data to it incrementally. This Use Case outlines prerequisites, required tools, and detailed steps for avoiding load failures when setting up and initiating the incremental refresh in Power BI. While the basic setup for this is straightforward, there may be scenarios in which you need to perform more advanced actions on the incrementally refreshed report for various reasons. 

Example scenarios could be:

• Initial refresh timeout: your tenant produces so much data that when you do the first manual refresh to initialize the incremental refresh in Power BI online, the first refresh times out when trying to fill the archive.
• You want to change the archive policy of your incrementally refreshed report without having to start all from scratch.
• The report failed and stopped loading data for a few days. Once you start it again, there is a gap in the archive that you want to refill.

It is possible to connect to the instance of your incrementally refreshed report with some advanced tools and refresh the partitions manually, so that you can address the above scenarios. 

Avoiding the initial refresh timeout

Scenario: You have a large amount of data and the report times out when doing the first manual refresh loading the archive.  

✅Step 1 - Set up the incremental refresh policy in the file

In Power BI desktop you have to set up the incremental refresh policy. This is explained under PBI Incremental Refresh.

☝Important: follow all the steps 1-4 the Incremental Refresh but stop there. Do not perform a manual refresh of the model once you load the file on Power BI online. This is what we do as part of the advanced Incremental Refresh instructions further below in this Use Case.

PBI Incremental Refresh steps

PBI Incremental Refresh

Starting with Template template version 1.96, the Nimbus Power BI Template enables users to set up the report so that it can be refreshed incrementally. This page shows specific aspects of the Incremental Refresh functionality applicable to the Nimbus template.

About Incremental Refresh in Power BI

Incremental Refresh is a functionality in the Power BI software that partitions the semantic model and enables incremental loading of new data instead of the entire dataset at each refresh.

This Power BI feature that can be enabled optionally, if needed to regularly refresh the report for rolling updates (e.g. you need to refresh the report for the last x number of days, months etc.).

💡 The template can continue to be used also for ad-hoc refresh operations, without incremental refresh. If not making use of incremental refresh, users must remember to set the required date range in the parameters before each refresh to ensure that the desired date range is loaded into the report. 

IMPORTANT TO KNOW

Although incremental refresh policy is set up on Power BI desktop, it comes into force only once the report is exported to the Power BI Services (online). In order to take advantage of the incremental refresh functionality, after the initial set up, the report must be uploaded and distributed via Power BI Services. This is how the Incremental Refresh functionality is designed to work by Microsoft. 

---

🔎 For additional information on what it is and how it works, refer to the Incremental Refresh documentation on the Microsoft website. 

 

Setting Up Incremental Refresh on the Template

PREREQUISITES

Note the prerequisites below before setting up incremental refresh:

Licensing plans

Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded models. To understand what your Power BI license allows, please refer to the Microsoft Power BI plans or contact your Microsoft representative.

 

 

Downloads to .pbix format

Incremental refresh is set up in Power BI desktop, however, it is only activated once the report is loaded and refreshed once in Power BI online. Once the Incremental Refresh is set up and running on Power BI Online, the download to *.pbix to Power BI desktop is disabled. This is by design by Microsoft. 

 

 

Nimbus Template version updates

Once the template is set up and running with incremental refresh, a semantic model is saved in Power BI online and the data will be incrementally refreshed. From then on, changes to the template can only be done in the Power BI online environment and the model cannot be edited easily. 

If a new template is loaded with the same name, a warning will be displayed asking if you want to replace it. 

When overwriting the model, the data archived will be lost and the incremental refresh will have to be started again (incremental refresh will have to be set up again from scratch). This needs to be considered when deciding about uploading a new template version with the latest changes.

Advanced techniques for managing the partitions and model changes can be explored using external tools. It is recommended that those who intend to use these advanced techniques and tools have some minimum technical knowledge of data management to understand the concepts and that they familiarize themselves with the Microsoft documentation on advanced incremental refresh as well as other sources available online. 

 

 

Initial refresh time limits

When setting up the Incremental Refresh, the report will need to be refreshed once to retrieve the initial archive. Depending on the size of your data and the length of the archive you set in the incremental refresh policy this could take some time. 

Regardless of whether incremental refresh is enabled or not, Power BI Pro models have a refresh time limit of two hours. For models in a Premium capacity, the time limit is five hours. If the initial refresh for your tenant hits these limits, advanced incremental refresh techniques for loading the initial archive will have to be adopted. For more information refer the following Microsoft documentation and guidelines: 

• Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
• Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Microsoft Learn

 

 

Memory size and capacity

Depending on the incremental refresh policy set, the number of sessions generated by your tenant and the amount of data you intend to archive in the report, the semantic model may grow into large sizes. 

Irrespective of whether you decide to take advantage of the incremental refresh or not, your Power BI license and Power BI environment settings will impact the amount of data you can load into your semantic model. When limits are reached, errors may occur. 

As explained by Microsoft, what you can do in the Power BI service depends on three things:

1. The type of license and subscription you are using.
2. The type of workspace that stores the content.
3. The roles and permissions you are assigned in a workspace.

If you are not familiar with the license and capabilities of your Power BI deployment, please consult with your IT department. If you need advice on which Power BI license is best to fulfil your business requirements, please contact Microsoft. 

 

 

 

Set up the Incremental Refresh policy

✅ The Power BI Template must be set up. If you have not set it up yet, go through the steps in Setting Up Power BI, then return to this page once you have the template saved as Nimbus.pbix.

1.  Navigate to the model view in the Nimbus template in Power BI desktop.
2.  Go to the tab “Inc Refresh Facts”.
   
3. Each of the tables visible in this tab can be set for incremental refresh. To do so, start by clicking on the 3 dots on the right-hand corner of the first table. 
4. Select Incremental Refresh from the dropdown menu.
   ⮑ The window will open to show the incremental refresh policy window and the selected table. 
5.  Toggle on “Incrementally refresh this table”.
    ⮑ The options to incrementally refresh the table appear.
6. Toggle on the “Set import and refresh ranges”:
   ⮑ You can now define the policy to incrementally refresh this table. 
7. Repeat all these steps for each of the fact tables shown in this tab and save the Nimbus.pbix report. 

Incremental Refresh policy options

✅ We recommend you to set the policy accordingly to your requirements. 

• When selecting the archive data range, consider the capacity and memory allocated to your environment, based on the licensing and Power BI deployed by your organization.
• Optional settings are not possible with the Nimbus OData connection/data model. You can ignore these.

🔎 All the steps for setting the incremental refresh policy are explained in the Microsoft documentation Define Policy.

Learn about the “Incremental Refresh” initial messages…

• You can ignore the warning about being "unable to fold the M query”. 
• Direct query is not applicable with OData connections. This option is not available. 
• Follow the “Learn more” link provided by Microsoft in this window to learn more about Incremental Refresh.

 

 

Publish the report to Power BI Services

1. Save the report and publish to a Power BI workspace. 
   🔎If you are not sure about this step, refer to our KB page Publish to Power BI Service.

Activate the incremental Refresh policy

1. Once publishing to Power BI service is complete, go to the semantic model settings.
2. Ensure that the connection credentials are correct. 
3. Check if the parameters are set as required (enable optional tables if needed).
   💡 Notice that RangeStart and RangeEnd are no longer visible. This is because Power BI will automatically set the dates based on the Incremental Refresh policy you have set.
4. Set the Large semantic model storage format to ON if you have a premium license.
   
   💡 This must be done before the first refresh. It is required if your tenant generates huge amount of data and you are loading an extensive range of dates into your archive which may grow your semantic model beyond 1GB. This setting can improve refresh operation performance and ensure the model does not max out size limits.
   🔍 Refer to the Microsoft documentation about large semantic models in Power BI premium for more information.
5. Once the above is set, perform an initial refresh operation on the model. This refresh should be an individual (manual) refresh so that you can monitor the progress. This will load the initial archive based on the set incremental refresh policy.
   💡The initial refresh operation can take quite a while to complete. Partitions must be created, historical data loaded, objects such as relationships and hierarchies built or rebuilt, and calculated objects recalculated. If the load fails at this stage you may need to consider Use Case - Avoiding load failures with PBI incremental refresh.

Subsequent Data Refresh 

At this point, you can set a scheduled refresh or you can refresh the report manually when you need it. 

From now on, each time the report is refreshed, Power BI will follow the original Incremental Refresh policy and will incrementally refresh the data since the previous refresh.

🔎Additional Sources

• Types of licenses for Power BI business users - Power BI | Microsoft Learn
• Pricing & Product Comparison | Microsoft Power BI
• How content is organized in workspaces - Power BI | Microsoft Learn

 

 

 

 

✅ Step 2 - Create the partitions with Tabular Editor

In order to create the partitions while avoiding the time-out for the initial refresh, we connect with Tabular Editor and we create the partitions with it.

Connect with Tabular Editor  

1. Copy the connection string from the Server Settings of the Semantic Model in Power BI
   Data Source=powerbi://api.powerbi.com/v1.0/myorg/<YourSpaceName>;
2. Paste the connection string into Tabular Editor connection.
   Make sure you use the string starting from “powerbi” (e.g. remove “DataSource=”)
   Stop at the “;” after the space name (remove the report name) 
   Example:
   Data Source=powerbi://api.powerbi.com/v1.0/myorg/IncrementalRefresh;Initial Catalog=Nimbus-1.110;
   Becomes → powerbi://api.powerbi.com/v1.0/myorg/IncrementalRefresh;
   ⮑ Once authenticated and connected you will be presented with a list of reports semantic models available to you. 
3. Select the one you need to work on.
   ⮑ Once connected to the report it will show the model in Tabular Editor.
4. Expand tables and go to each of the incremental refresh tables one by one to create the partitions: 
5. Right-click on each Incremental Refresh table and select “Apply Refresh Policy”.
   ⮑This will create the partitions which you can see when you expand the table:
6. Repeat this action for each of the Incremental Refresh tables. 
7. Finally, click on Save.

✅Step 3 - Fill the partitions with data using SQL Server Management Studio

Once the partitions are created each one of them can be refreshed manually. To do this connect to the model via MS SQL Server Management Studio 

1. Open and connect to MS SQL Server Management Studio
   Server type: "Analysis Services"
   Server Name: same connection string used for Tabular Editor above
   Authentication: “Microsoft Entra MFA”
   User Name: your Power BI user name with XMLA edit rights (💡Not the Nimbus User for OData access). 
   ⮑ Once connected you have the list of databases available to you in Power BI.
2. Select the database you need and navigate to Tables to see the list of tables available in that semantic model.
3. Select one by one the list of fact tables that need to be incrementally refreshed.
4. Right click and select: Partitions
   A window will pop up showing the created partitions and which data has already been loaded.
5. From here select one or (CTRL-Key) multiple partitions you wish to refresh and click on the process icon.
   💡Note: You can select multiple partitions to refresh together – however be aware of the processing limits of your environment and the amount of data you are trying to process. If a failure occurs try again selecting single partitions or make your partitions smaller. 
   ⮑A new window opens up 
6. Select mode: “Process Data”
   ⮑The system will start refreshing and filling the partition with data and give a positive signal once completed successfully.
7. ☝Important: you must repeat this step for each partition you want to fill with data/refresh AND for each incrementally refreshable table in the report.  
   Once you finished processing each partition, you can close the windows.

✅Step 4 - Process Recalc after loading facts

Once you have loaded all the partitions you want to load into the report, you must do a “Process Recalc” in order for the data to link to the other tables. A Process Recalc also applies after loading data with MS SQL Server Management and before you can use the report. 
Without a Process Recalc you may see the following error when trying to visualize the data in the actual report (or in existing visualizations):

Error fetching data for this visual

The expression referenced a relationship between '<oii>ServiceSessions</oii>'[<oii>ServiceId</oii>] and '<oii>Services</oii>'[<oii>Id</oii>], which does not hold any data because it needs to be recalculated.

Please check the technical details for more information. If you contact support, please provide these details. 

💡The Process Recalc will resolve this situation. 

Steps within MS SQL Server Management Studio:

1. Right click on the Database name itself from the list of Databases.
2. Select “Process Database”.
3. In the window that pops up choose “Process Recalc”.
4. Let this process run. Note that this…
   ⮑… will simply update and recalculate hierarchies, relationships, and calculated columns without querying any additional data. 
   ⮑ … may take a while depending on model size 
5. Click close when finished and successful.

Useful Links and Resources