Invantive SQL provides real-time access to data from approximately 80 platforms. Some platforms have views based on table functions for use with Power BI, Qlik and/or Tableau. For improved performance, Invantive SQL now applies filters on report views also within the view, resulting in significant speed gains. This especially holds for Easyflex, Loket.nl, Minox, Nmbrs, Teamleader and Visma.net Financials.
Real-time Report Views for Table Functions
Invantive SQL provides real-time access to data in approximately 80 platforms. Many users process the data in Power BI, Qlik or Tableau reports. Some of the platforms have historically been based by their respective vendors on APIs that require parameter(s). Invantive SQL provides so-called “table functions” for this purpose. Table functions cannot be accessed via Invantive Bridge Online. Invantive SQL therefore offers many predefined “Views” in the schema “Views” for users of Power BI, Qlik and Tableau; these are similar to your own database views as described in Using custom database views for SQL and BI tools.
Performance Optimization
Many of these report views first retrieve a list from a table with the possible values, such as “Payroll Years” and then query the corresponding data on a row-by-row basis from the list table via a separate API call per row.
Often not all data is needed and a filter is applied, which arrives at Invantive SQL via, for example, query-folding. However, filters on a report view are only applied after the corresponding data has been retrieved for all rows from the list table. As a result, filters do not affect number of API calls of the view and only limit the resulting data volume.
A new performance optimization applies filters to a reporting view also within the view. This often increases performance by an order of magnitude or even more, while reducing the number of API calls on the platform by a similar order.
Example
An example of such a reporting view is Hours on Easyflex with the definition:
select ljr.year Year
, clr.month_number Month
, hour.*
from DataService.Operating company.Wage years ljr
join CALENDAR@DataDictionary clr
on clr.YEAR_NUMBER = ljr.year
and clr.day_in_month = 1
join DataService.Relationship.Hours(ljr.year, clr.month_number) hour
This view Hours in previous software versions will first calculate all rows by requesting hours from Easyflex for all periods in all payroll years. This causes at least 5*12=60 API calls for five payroll years, plus 1 for the payroll years list, assuming each period contains only 5,000 hour records. The expected run time is then, for example, (60+1) * 5 seconds = 305 seconds for up to 300,000 timesheet records.
If a report needs only the hours from January 2023, then this produces an SQL query such as:
select *
from Hours
where Year = 2023
and Month = 1
The number of rows is then, say, 5,000, but the running time remains roughly 305 seconds. After all, all hours are first fully calculated and only then filtered.
This changes dramatically thanks to the new performance optimization. A filter on a view is applied within the view whenever possible, so the view in this example contains the following logic:
select ljr.year Year
, clr.month_number Month
, hour.*
from DataService.Operating company.Pay years ljr
join CALENDAR@DataDictionary clr
on clr.YEAR_NUMBER = ljr.year
and clr.day_in_month = 1
join DataService.Relationship.Hours(ljr.year, clr.month_number) hour
where lyr.year = 2023
and clr.month_number = 1
Assuming the same key figures, this real-time query takes only 10 seconds and performs only 2 API calls. The performance gain in this example is a factor 30!
Platforms
These speed gains when adding filters will be most noticeable on the following platforms:
Views that users develop and apply themselves, for example via the startup SQL of an Invantive Cloud database, also benefit from the speed gains.
No changes are required to views to benefit from the speed gains as long as the filter on a view is translated 1-to-1 on a column of a list table used as input to a table function.
Availability
The performance enhancements are available in BETA releases from 22.1.272 and on Invantive Cloud from August 25, 2023 via Bridge Online (BETA) and App Online (BETA). The enhancements will not be available in release 22.0.