The Autotask API driver for SQL and Power BI has been improved for optimized performance.
The Autotask API allows for simple and complex filters to allow server-side selection of rows matching filter criteria. This concept is called “server-side filtering” in Invantive SQL and “query folding” in Power BI.
An example of such a filter is retrieval of the an time entry with a specific ID:
select *
from TimeEntries
where id = 123
Previously, this would require a download of all time entries, possible including hundreds of thousands of rows, and subsequent Invantive-side filtering to retrieve solely the time entry with id 123.
More complex filters are possible too such as retrieving today’s hours not yet approved for billing:
select *
from timeentries
where startDateTime >= trunc(sysdateutc)
and billingApprovalDateTime is null
For data integration between Autotask and other platforms, this typically yields a performance gain of approximately a factor 50. For reporting purposes, the typical performance improvement is a factor 5. Actual performance gains depend on the number and type of filters applied, and total data volume in the respective table in Autotask.
The optimization is available on all Autotask tables. For a reference of available Autotask tables see:
To achieve performance gains on Power BI, please remember to make sure query folding is possible; place any filter steps close to the navigation step.
The improved Autotask driver is bundled starting release 22.0.500 and will be available in online products starting January 19, 2023.