This article explains you how to reduce the number of records retrieved into Power BI from Invantive Bridge Online databases using filters, effectively improving performance and reducing network capacity needs.
After reducing the data volume of the data sets loaded into Power BI, you can further reduce the data volume by Reduce OData Download Size by Sharing Data Sets in Power BI .
Reducing data volume by filtering
Microsoft Power BI is capable of forwarding filter criteria to an OData source. This 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.
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 (not for instance on GL Account Code) 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, does not reduce the data volume and will trigger retrieval of all data across all Exact Online companies.
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:
- 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:
- 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:
- The properties of the step are:
- 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 (taken from actual log):
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.