PowerBI – Direct Query lazy loading optimization
Recently I was asked by one of my friends for help with slow PowerBI report. The scenario is that the data source is view from Oracle database, built as a join from several tables and returning a lot of rows. Without parameters, this view is running forever (they cancelled it after one hour …). Because of Oracle database, only DirectQuery can be used as a storage model. The problem with DirectQuery is that it doesn’t support query parameters, so in our case all data from the source view must be read into PowerBI desktop and filters can be applied later. The challange here is how to apply filters to source view first? While considering all possible solutions, I noticed short message on Twitter from Phil Seamark mentioning PowerBI bookmarks. After little research, I realized that this feature might be used as a workaround I was looking for! So how we can use bookmarks in our scenario? The trick is that when you click on or refresh report page in PowerBI, all visible visuals are refreshed at the same time – tables, charts, slicers etc. The key word here is visible – what if we hide visuals showing data from the source view, set up the filter first and show main visual(s) with the data afterwards? Let’s have a look if this idea would work. First, I prepared the source data. I decided to use ContosoRetailDW (and yes, instead of Oracle I use SQL Server 😊) and created source view:
Then I’ve created simple report in PowerBI desktop with DirectQuery connection:
So this is how my report looks like – 8 slicers, 1 table grid and 2 buttons. The two buttons on the report’s bottom are the ones which allows us to manipulate with the table visual – hide it or show it.
Configuration
Now that’s the important part – configuring bookmarks. With bookmarks, you can save current view of your report including state of the visuals and filtering. We will create two bookmarks – one for hiding table visual and another one for showing it.
One of the important steps here is to unselect Data option in bookmarks settings, otherwise when bookmark is selected, the report page will restore to the saved state including state of the filters, and this is something what we don’t want.
When bookmarks are created, we need to make a link between buttons and appropriate bookmarks:
And we are ready for some performance tests.
Performance tests
To make sure that our tests will not be influenced by caching data, we need to disable caching data in PowerBI desktop first:
Finally, let’s start with our first test – baseline. Let’s say that we want to see data for:
- Date = 2017/01/02
- Product Subcategory = Cofee machines
Make sure that all filter settings are cleared out, table visual is visible and Performance Analyzer is started. Click on Refresh visuals and wait until all visuals are refreshed. Then select date 2017/01/01 in Date slicer and Coffee Machines in Product Subcategory slicer. Check the time in Performance Analyzer. – Insert video here – As you can see, first loading of table visual took 53 seconds on my machine. After we set date and product subcategory filters, table visual refresh took almost another 22 seconds. In our second test, we will hide table visual first, then we select the same date and product category as in the previous test and after all slicer are refreshed, we will unhide the table visual and measure time in Performance Analyzer.
This time the table visual refresh time is much better – it’s between 1-2 seconds. The time difference depends on whether you re-open the PowerBI desktop between tests or restart underlying SQL Server to clear data cache, but still we are talking about 1-2 seconds versus 53+22 seconds.
Conclusions
It seems that this technique can be usable in some scenarios, especially when you don’t have a data source under control, it’s not possible to modify underlying data model and you have to use DirectQuery storage model. Sure, it’s still a workaround with its own drawback – the users must hide expensive visuals when they’re closing the report, so next time report is opened faster.
I hope some of you will find this tip useful.
