PowerBI enables us to deliver business insights faster. However, we need to refresh the underlying data to keep the PowerBI reports/dashboards current.
You might be experiencing failed or longer refresh times due to the large volume of data. This blog post is going to discuss how to improve your data refresh performance.
PowerBI reports normally contain many tables which require scheduling and refreshing according to business requirements. During the phase of PowerBI development, a developer might have used the PowerBI desktop to author the reports. They have data refreshed and stored in PBIX files already. We will be discussing how we can refresh the data more efficiently.
There are two options: “Enable Load” and “Include in Report Refresh” in Power Query Editor. Enable Load means results provided by the query are available for report builder. You may use it in other queries like merging data. However, it doesn’t have to be shown in the report builder. Include in Report Refresh means query gets refreshed automatically when you press the refresh button on the ribbon or schedule refresh via PowerBI service.
How to find both in Power Query Editor?
- Under the Home tab, click on Transform data, after that one Power Query window will appear.
- Under Power Query Editor window, right side under Queries section > Right-click on the name of query/ table and you can see both options.
- Enable load and Include report in refresh
However when we need to refresh a large table (> millions of rows) which contains many years of data on daily basis, for example, the table: DailyAdSpending from Google Analytics, we know that DailyAdSpending from 2019-01-01 and 2021-12-09 is not changing as the day goes on, so it seems logical to create a table to hold historical data and uncheck the Include in Report Refresh option, so we save the time for a refresh this part of data as it already exists as part of PBIX file on the PowerBI service portal.
At presentation time in Report Builder, we can use DAX (union function) to combine these two tables which have the same structure into a new table.
So, the rule of thumbs for unchecking Include in Report Refresh option is that if a table content is static don’t need to be refreshed each day or a table contains a lot of rows and most of the rows don’t change or we know how to identify the unchanged rows, then we can consider the incremental load method to improve the report refresh performance.
If you don’t want to go through the above steps, another option is to use PowerBI dataflow, which has some built-in incremental refresh functionality, but beware PowerBI dataflow will need a premium license.
We hope you have enjoyed reading this article. To stay connected with the latest at Stellar Consulting please follow us on any of the following social media channels.
