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: