Simplify Incremental Refresh: Step-by-Step Guide

Simplify Incremental Refresh: Step-by-Step Guide

AD

YouArt

YouArt

Agentic Creative OS

Agentic Creative OS

Try Now

Table of Contents:

1. Introduction
2. What is Incremental refresh?
3. Why configure incremental refresh without access to the .pbix file?
4. Requirements for configuring incremental refresh
5. Setting up Tabular Editor
6. Creating RangeStart and RangeEnd parameters
7. Writing code for the parameters
8. Enabling the refresh policy property
9. Configuring IncrementalGranularity and IncrementalPeriods
10. Setting RollingWindowGranularity
11. Specifying the SourceExpression
12. Saving and applying the refresh policy
13. Refreshing partitions in Tabular Editor 2
14. Refreshing partitions in Tabular Editor 3
15. Refreshing partitions in SQL Server Management Studio
16. Conclusion

Configure Incremental Refresh Without Access to the .pbix file

In today's video tutorial, we will dive into the process of configuring incremental refresh in Power BI when You do not have access to the .pbix file. This can happen when large models are enabled or when the model is accessed via the XMLA endpoint. We will explore how to set up incremental refresh using Tabular Editor and the steps involved in creating and configuring the necessary parameters. So, let's get started!

1. Introduction

Incremental refresh is a powerful feature in Power BI that allows you to optimize data refresh operations by only refreshing the portions of your dataset that have changed. This can significantly improve data refresh performance, especially when dealing with large datasets. However, configuring incremental refresh can be challenging when you don't have access to the .pbix file. In this article, we will guide you through the process of setting up incremental refresh without the .pbix file, using Tabular Editor.

2. What is incremental refresh?

Before we dive into the details, let's first understand what incremental refresh is. Incremental refresh is a technique that enables Power BI to refresh only a subset of data within a dataset, rather than refreshing the entire dataset. This is achieved by dividing the dataset into partitions Based on a specified criteria, such as time or any other suitable partitioning key. When data changes occur within a partition, only that specific partition needs to be refreshed, resulting in faster and more efficient data refresh operations.

3. Why configure incremental refresh without access to the .pbix file?

There are several scenarios where you may need to configure incremental refresh without access to the .pbix file. One common scenario is when the model has been enabled for large models. Large models allow for greater scalability and performance, but they come with certain limitations, such as the inability to download the .pbix file.

Another Scenario is when the model is accessed via the XMLA endpoint. The XMLA endpoint allows external tools, like Tabular Editor, to Interact with the Power BI model directly. While this provides flexibility and advanced capabilities, it also restricts direct access to the .pbix file.

Regardless of the scenario, configuring incremental refresh without the .pbix file is still possible using Tabular Editor and the XMLA endpoint.

4. Requirements for configuring incremental refresh

Before we proceed with the configuration, let's make sure we have all the necessary requirements in place.

To configure incremental refresh without access to the .pbix file, you will need the following:

• Power BI Premium or Premium Per User license: This is necessary to utilize the XMLA endpoint, which is required for configuring incremental refresh.
• Tabular Editor: Tabular Editor is a powerful tool that allows you to perform advanced operations on Power BI models, including configuring incremental refresh. You can download Tabular Editor from the official Website.

Once you have these requirements fulfilled, you are all set to configure incremental refresh without access to the .pbix file.

5. Setting up Tabular Editor

The first step in configuring incremental refresh without access to the .pbix file is setting up Tabular Editor. Tabular Editor is a third-party tool that provides advanced functionality for managing Power BI models.

You can choose between Tabular Editor 2 and Tabular Editor 3 for this process. Both versions work well for configuring incremental refresh, but there are some differences in the setup process. In this tutorial, we will be using Tabular Editor 3.

To begin, launch Tabular Editor 3 and make sure you are connected to your Power BI model in your workspace. Click on the "Load Model from Database" icon and enter your XMLA endpoint. Once authenticated, choose the database that contains your model.

6. Creating RangeStart and RangeEnd parameters

Next, we need to Create the RangeStart and RangeEnd parameters. These parameters define the range of data that will be refreshed incrementally.

To create the parameters, open the TOM Explorer in Tabular Editor. Right-click on "Shared Expressions" and choose "Create" and then "Shared Expressions". Give the parameters Meaningful names, as these will be associated with the parameters you would create in the desktop if you had access to the .pbix file.

7. Writing code for the parameters

Once the parameters are created, we need to write the code that defines the values for these parameters. This code determines the range of data that will be refreshed incrementally.

Instead of writing the code manually, we can extract it from the Power BI Desktop. Open the Power BI Desktop and access the Query Editor. Under "Manage Parameters," create the RangeStart and RangeEnd parameters. Once created, go to the "Advanced Editor" and copy the code.

Back in Tabular Editor, paste the code into the respective parameters, making sure to adjust the dates if required. This saves time and ensures accuracy in setting up the parameters.

8. Enabling the refresh policy property

To enable the refresh policy property for incremental refresh, expand the "Tables" section in the TOM Explorer and select the table you want to configure. In the properties window, you will find a refresh policy section. Switch the refresh policy property from false to true.

Once enabled, you will Notice that several properties become available for configuration.

9. Configuring IncrementalGranularity and IncrementalPeriods

The IncrementalGranularity property determines the level of granularity for incremental refresh. This property determines which partitions will be refreshed incrementally. For example, if you set the IncrementalGranularity to "Years," only the Current year's partition will be refreshed incrementally.

To configure this property, select the desired table in the TOM Explorer and find the IncrementalGranularity property in the properties window. Set the IncrementalGranularity to the desired granularity, such as "Years."

Next, adjust the IncrementalPeriods property to specify the number of partitions that will be refreshed incrementally. For instance, if you want to refresh only the current year's partition, set IncrementalPeriods to 1.

10. Setting RollingWindowGranularity

The RollingWindowGranularity property determines how many years, days, or months of data will be kept in the model. This property allows you to control the retention period of data in your dataset.

To set the RollingWindowGranularity, select the table in the TOM Explorer and locate the RollingWindowGranularity property in the properties window. Specify the desired number of years, days, or months to retain in the model. For example, if you want to retain 10 years of data, enter 10 in the RollingWindowGranularity property.

11. Specifying the SourceExpression

The SourceExpression property specifies the expression that retrieves data from the model for the configured table. This property is where you define any filters or transformations needed to retrieve the data effectively.

In the SourceExpression property, you can freely write the M code required to filter the data using the RangeStart and RangeEnd parameters. Alternatively, you can generate the code in the Power BI Desktop and copy and paste it into the SourceExpression property in Tabular Editor.

Make sure to test the SourceExpression code to ensure it retrieves the desired data correctly.

12. Saving and applying the refresh policy

Once you have configured all the necessary properties, it's time to save your model. In Tabular Editor, click on "Save Changes Back to the Database" to save your changes.

Here, Tabular Editor 2 and Tabular Editor 3 differ slightly in how the refresh policy is applied.

In Tabular Editor 2, right-click on the table and choose "Apply Refresh Policy" to create the necessary partitions for incremental refresh.

In Tabular Editor 3, the "Apply Refresh Policy" option is not available directly in the Context menu. However, you can create a C# script or macro to simulate the behavior. Contact the developer of Tabular Editor for guidance on implementing this script as per your requirements.

13. Refreshing partitions in Tabular Editor 2

In Tabular Editor 2, refreshing partitions is straightforward. Simply right-click on the table, choose "Apply Refresh Policy," and all the partitions will be created for you.

14. Refreshing partitions in Tabular Editor 3

In Tabular Editor 3, the process of refreshing partitions is slightly different. As Mentioned earlier, the "Apply Refresh Policy" option is not available directly in the context menu. However, you can create a C# script or macro to replicate the behavior.

Create a C# script or macro that includes the necessary code to apply the refresh policy to the selected table. This script should execute the same actions as the "Apply Refresh Policy" option in Tabular Editor 2. Once created, associate the script with a button for easy access to apply the refresh policy in all subsequent models.

15. Refreshing partitions in SQL Server Management Studio

Another option for refreshing partitions is using SQL Server Management Studio (SSMS). SSMS allows you to directly interact with the Power BI model using XMLA scripts.

To refresh individual partitions using SSMS, connect to your Analysis Services database and locate the partitions you want to refresh. Right-click on the partitions and choose "Refresh Full Partition" or other Relevant options based on your needs.

You can also refresh the entire table or apply other refresh operations using SSMS. There are multiple ways to achieve this, including executing XMLA scripts or using the SSMS user interface.

16. Conclusion

In conclusion, even if you don't have access to the .pbix file, you can still configure incremental refresh in Power BI using Tabular Editor and the XMLA endpoint. By following the steps outlined in this article, you will be able to set up incremental refresh and optimize your data refresh operations.

Remember to save your changes and test the refresh policy to ensure it works as expected. By utilizing the advanced capabilities of Tabular Editor and other tools like SSMS, you can effectively manage and maintain your Power BI models.

Thank you for reading this article. If you have any questions or comments, please feel free to leave them below. Happy configuring!