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. Clearly, this view must be used with filters, otherwise it will run forever…
Because of data source type (Oracle), only DirectQuery can be used as a storage mode. 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 (b|t) 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 (for the sake of simplicity I decided to use SQL Server,but with DirectQuery mode) and created source view:
DROP VIEW IF EXISTS dbo.OnlineSales; GO CREATE VIEW OnlineSales AS SELECT TOP 1000000 SalesOrderNumber, SalesOrderLineNumber, SalesQuantity, SalesAmount, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, FactOnlineSales.UnitCost, FactOnlineSales.UnitPrice, FullDateLabel, DateDescription, CalendarYear, CalendarYearLabel, CalendarHalfYear, CalendarHalfYearLabel, CalendarQuarter, CalendarQuarterLabel, CalendarMonth, CalendarMonthLabel, CalendarWeek, CalendarWeekLabel, CalendarDayOfWeek, CalendarDayOfWeekLabel, FiscalYear, FiscalYearLabel, FiscalHalfYear, FiscalHalfYearLabel, FiscalQuarter, FiscalQuarterLabel, FiscalMonth, FiscalMonthLabel, IsWorkDay, IsHoliday, HolidayName, EuropeSeason, NorthAmericaSeason, AsiaSeason, DimProduct.ProductKey, ProductLabel, ProductName, ProductDescription, DimProduct.ProductSubcategoryKey, Manufacturer, BrandName, ClassID, ClassName, StyleID, StyleName, ColorID, ColorName, Size, SizeRange, SizeUnitMeasureID, Weight, WeightUnitMeasureID, UnitOfMeasureID, UnitOfMeasureName, StockTypeID, StockTypeName, DimProduct.UnitCost AS [Product Unit Cost], DimProduct.UnitPrice AS [Product Unit Price], AvailableForSaleDate, StopSaleDate, DimProduct.Status AS [Product Status], ImageURL, ProductURL, ProductSubcategoryLabel, ProductSubcategoryName, ProductSubcategoryDescription, StoreManager, StoreType, StoreName, StoreDescription, DimStore.Status, OpenDate, CloseDate, EntityKey, ZipCode, ZipCodeExtension, StorePhone, StoreFax, DimStore.AddressLine1 AS [Store AddressLine1], DimStore.AddressLine2 AS [Store AddressLine2], CloseReason, EmployeeCount, SellingAreaSize, LastRemodelDate, CustomerLabel, Title, FirstName, MiddleName, LastName, NameStyle, BirthDate, MaritalStatus, Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren, NumberChildrenAtHome, Education, Occupation, HouseOwnerFlag, NumberCarsOwned, DimCustomer.AddressLine1, DimCustomer.AddressLine2, Phone, DateFirstPurchase, CustomerType, CompanyName, ValidFrom, ValidTo FROM dbo.FactOnlineSales JOIN dbo.DimDate ON DimDate.Datekey = FactOnlineSales.DateKey JOIN dbo.DimProduct ON DimProduct.ProductKey = FactOnlineSales.ProductKey JOIN dbo.DimProductSubcategory ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey JOIN dbo.DimStore ON DimStore.StoreKey = FactOnlineSales.StoreKey JOIN dbo.DimCustomer ON DimCustomer.CustomerKey = FactOnlineSales.CustomerKey;
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.
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:
Now we are ready for testing.
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 make our 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 duration for table visual in Performance Analyzer:
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 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.
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 mode. Sure, it’s still a workaround with its own drawbacks – the users must hide expensive visuals when they’re closing the report, so next time report is opened faster. But if you don’t have other options, this workaround can make your life (or life of your users) easier, until Microsoft will fix it.
I hope some of you will find this tip useful.
Thanks for reading