Reduce data volume and improve performance of your Power BI report by Filtering

Wiki (en)
, , ,
1

Go to Dutch version

Learn how to reduce the number of records retrieved into Power BI Desktop and/or the Power BI Service from Invantive Bridge Online databases using filters. Using Power BI filters applied to import data instead of an import of all the data, the performance of Power BI reports is effectively improved, while also reducing network usage.

These tips work for all of Power BI Desktop, Power BI Service, Azure Data Factory, Microsoft Integration Services and the Power Query engine for Excel.

The performance and needed network capacity of Power BI reports can be reduced through various means. Combining means can significantly further improve the performance and download time into Power BI. After reducing the data volume of the data sets loaded into Power BI by vertical filtering, the steps given on Overview of Power BI Performance and Download Size Improvement Techniques - 2 van forums help you to improve Power BI report performance even further.

Make sure you understand the structure of Invantive Cloud before starting to optimize the performance and reducing the download. Learn the structure on Invantive Cloud Structure.

Reducing Power BI Data Download by Filtering

Invantive Bridge Online provides cloud data sources through the OData protocol. Microsoft Power BI is capable of forwarding its filter criteria to an OData data source. This forwarding capability matches or exceeds the Direct Query Options offered on a limited number of traditional data sources.

For instance, this feature saves approximately 90% in run time when you just need to analyze data of the current fiscal year and you have ten years of fiscal history in Exact Online. Instead of downloading all the data, just one of those years is downloaded from Invantive Cloud. And even better: often these filters are forwarded from Invantive Cloud to the platform using server-side filtering.

Invantive Bridge Online is capable of recognizing the filter criteria from Power BI and translate them into server-side filters to the respective data sources. Not all data sources support server-side filters and/or to the same degree. Some examples:

  • the Exact Online REST table TransactionLines allows filtering on almost any column,
  • the table TransactionLinesBulk allows it only on column in the base table (such as GLAccount and not on GLAccountCode) and
  • the XML table GLTransactionLines has almost no filtering capabilities.

On Exact Online the filter on Division is always available. It allows you to restrict your query to data retrieved from one or few Exact Online companies (REST API-based tables) by specifying a filter rows step that specifies Division equals NUMBER.

The use of a filter rows step on columns containing a division code somewhere, such as DivisionLabel, also reduces the data volume as described on Improved Very Large Company Volume Performance on Exact Online. So, you can just as easy filter on DivisionName or DivisionLabel and still enjoy high performance when selecting a few companies from thousands.

Note that the filter step should be as close possible to the download. This ensures that Power BI can apply query-folding. If there are steps between the download and the filter, the filter may not be forwarded to Invantive Cloud.

Using Invantive Query Tool you can assess what filtering capabilities are available by entering the table name and pressing F4 or choosing “Describe Object” from the context menu.

How to reduce data by filtering OData source in Power BI

This example illustrates the steps to apply filtering:

  • Connect to an Exact Online subscription using an OData feed from Power BI.
  • Choose the table TransactionLinesBulk.
  • Let it load completely.
  • Check the amount of data; it can be very large such as 10 million rows:

Large Exact Online transactions table in Power BI
Large Exact Online transactions table in Power BI1477×481 37.8 KB

  • The associated Invantive SQL query used by Invantive Bridge Online is:
select t.*
from   ExactOnlineREST.FinancialTransaction.TransactionLinesBulk t
  • For example, include only data from the current fiscal year.
  • Click on the “Edit Queries“ button.
  • Choose the query.
  • Navigate to the FinancialYear column.
  • Select the current fiscal year:

Select Exact Online fiscal year in Power BI
Select Exact Online fiscal year in Power BI1348×610 60.5 KB

  • Note that the steps are solely “Source“ and “Navigation“.
  • Click on the “OK” button.
  • The data volume goes down significantly.
  • An extra step “Filtered Rows“ is added:

Filter step on fiscal year in Power BI
Filter step on fiscal year in Power BI1044×312 23.1 KB

  • The properties of the step are:

Filter Rows step properties on fiscal year
Filter Rows step properties on fiscal year774×378 10.4 KB

  • This filter can of course be fine-tuned with lots of options.
  • But the performance magic has already happened!
  • Click on the “Close & Apply“ button.
  • Refresh the data.
  • Note that the refresh is done significant faster.
  • The filter on FinancialYear is forwarded to Invantive Bridge Online.
  • Invantive Bridge Online transforms this filter into the following query (visible in Bridge Online Monitoring details):
select t.*
from   ExactOnlineREST.FinancialTransaction.TransactionLinesBulk t
where  (FinancialYear = :w1)
  • Where w1 is bound to the value 2018.
  • Additional filters can be applied for further improvements.
  • Filter on division and/or partition on platforms such as Exact Online.

The filter on FinancialYear can also be applied earlier in the process by selecting the “Transform” button on table selection.