The OData specification provides many options to forward filters to the OData-server. Power BI does a good job of mapping steps to OData filters thanks to query folding, but especially with a large Power BI environment with dozens or hundreds of companies it may be necessary to verify and improve performance of queries. Improving query performance reduces runtime, subscription costs and costs for bandwidth, processing power and user time.
Invantive Bridge Online, part of Invantive Cloud, is an OData4 proxy which manages your OData4, translating them into real-time requests for over 70 (cloud) platforms. Invantive Bridge Online can handle OData requests that run across hundreds of companies in popular accounting packages such as Twinfield, AFAS, Exact Online or Visma.net Financials.
In this topic, the impact of various filters upon resource consumption are shown.
Guidance
Before continuing, make sure to understand the structure of Invantive Cloud by studying the content on Invantive Cloud Structure.
A list of performance techniques and tips on optimizing performance and download size for Power BI is available on Overview of Power BI Performance and Download Size Improvement Techniques - 2 van forums help you to improve Power BI report performance even further.
Query Measurements
Exact Online Test Set
The Power BI environment used in these measurements was a test environment with 200 small Exact Online companies. The table TransactionLinesIncremental was used, since that is the most intesively used table on Exact Online.
A count query of transaction lines across the 200 companies is solely 217,668 rows. The compressed download size over OData4 is 16 MB (uncompressed 245 MB).
All tests were run twice to ensure all caches were loaded and fresh. Solely the measurement of the second test are included.
Query measurements were established using Invantive Bridge Online Monitoring.
Full Download
First, all transactions were downloaded unfiltered (without any query option):
- Duration: 30 seconds
- Data row count: 217,668
- Data download size: 15,522,956
- URL: BASE
where “BASE” is the URL of the server plus the table name ExactOnlineREST.Incremental.TransactionLinesIncremental@url.
The generated SQL is:
select t.*
from ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
The Invantive SQL engine running on Invantive Cloud will execute multiple requests, at least one per Exact Online company, to retrieve all relevant data. Power BI only needs to execute a single request on the OData endpoint; Invantive Cloud merges data from all companies into one data set.
In this scenario, using a single request on OData per data set considerably increases OData performance: Invantive Cloud returns in a streaming fashion data across all selected Exact Online companies.
Other data sets can be retrieved in parallel to further enhance OData performance. These also mix data from multiple companies.
Download one Exact Online Company
In the Power Query Editor, solely a single Exact Online division 868045 was selected:
A filter step was added to retrieve solely one company:
The measurements were:
- Duration: 1 second
- Data row count: 172
- Data download size: 17,986
- URL:
BASE?$filter=Division eq 868045
The query in the Advanced editor is:
let
Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each ([Division] = 868045))
in
#"Filtered Rows"
The generated SQL for Invantive SQL is:
select t.*
from ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
where ([Division] = :w1)
The Invantive SQL generated has a filter on division, which enables the SQL-engine to solely retrieve and return this company.
In this scenario, both between Power BI and Invantive Bridge Online, and between Invantive Bridge Online and Exact Online, the filters are forwarded. Client-side filtering is still applied, but does not trash a significant percentage of the payload, if any.
Download Four Exact Online Companies
In the Power Query Editor, four Exact companies were selected:
The filter rows displays as:
The measurements were:
- Duration: 1 second
- Data row count: 1,114
- Data download size: 102,562
- URL:
BASE?$filter=Division eq 868041 or Division eq 868045 or Division eq 868046 or Division eq 868047
The query in the Advanced editor is:
let
Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each [Division] = 868041 or [Division] = 868045 or [Division] = 868046 or [Division] = 868047)
in
#"Filtered Rows"
Note that Power BI rewrites the selection of values into an OR instead of using the IN-operator.
The generated Invantive SQL is:
select t.*
from ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
where (((([Division] = :w1) or ([Division] = :w2)) or ([Division] = :w3)) or ([Division] = :w4))
The Invantive SQL generated has a filter group, consisting of OR-ed elements on division. The filter on division will not be forwarded and all companies configured in the startup SQL will be queried and charged.
In this scenario, between Power BI and Invantive Bridge Online, the filters are forwarded. However, between Invantive Bridge Online and the Exact Online API server, the Invantive SQL engine applies excessive client-side filtering on the response from the API servers. The client-side filtering trashes 90+% of the payload.
A performance enhancement has been made on Invantive SQL; starting July 2021, only the specified companies will be accessed. Previously, all companies with app privileges were accessed. For more details: see Betere prestaties op Power BI voor Exact Online accountants.
Four Companies and Year Filter
The selection of companies is enhanced by applying a filter on the fiscal years:
The measurements were:
- Duration: 1 second
- Data row count: 482
- Data download size: 44,809
- URL:
BASE?$filter=(Division eq 868041 or Division eq 868045 or Division eq 868046 or Division eq 868047) and (FinancialYear eq 2011 or FinancialYear eq 2012 or FinancialYear eq 2013)
The query in the Advanced editor is:
let
Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each ([Division] = 868041 or [Division] = 868045 or [Division] = 868046 or [Division] = 868047) and ([FinancialYear] = 2011 or [FinancialYear] = 2012 or [FinancialYear] = 2013))
in
#"Filtered Rows"
The OData request is translated into the following Invantive SQL:
OData query was transformed into: select t.*
from ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
where ((((([Division] = :w1) or ([Division] = :w2)) or ([Division] = :w3)) or ([Division] = :w4)) and ((([FinancialYear] = :w5) or ([FinancialYear] = :w6)) or ([FinancialYear] = :w7)))
The Invantive SQL hits the same Exact Online companies as before, but per company applies a neat filter on financial year.