I want to retrieve all contract service units (ContractServiceUnits) from Autotask in Power BI. To reduce the data volume, how can I best specify only units with end date before eg. January 1, 2026?
Filtering is possible using a filter step and can reduce load times and data volumes dramatically, thanks to PowerBI’s query folding forwarding the filter to the server when added directly after the download. For more information please read Reduce data volume and improve performance of your Power BI report by Filtering.
Please note that Invantive Cloud focuses on real-time data imports into Power BI, so the behavior is totally different from alternatives that once per day update a replica in a database. Once a question for data has been answered in real-time, repeating occurrences of exactly the same questions are answered from a cache. This cache expires after a time set on the (virtual) Autotask database. Please read Differentiate OData4 for Power BI Cache Behavior.
Please note that you can directly copy Autotask data to your own report data warehouse such as on SQL Server or PostgreSQL using Invantive Cloud; see for example Elementary Data Replication Module between Exact Online and Azure SQL Server. There is no need to use Azure Data Factory for copying; just use create or replace table. For copying Autotask data to an on-premise SQL Server or a database which wants to pull data instead of getting them pushed, the article Store Exact Online data in an on-premise SQL Server through Invantive Cloud provides a working sample.
Such a report data warehouse can be used from Power BI Desktop, Power BI Service or another reporting tool as a data source. However, most users prefer the real-time behavior of the Autotask connector after getting acquainted with the cache settings.
Typically, Microsoft SQL Server views can be replaced by Invantive SQL views for better performance. Please read Using custom database views for SQL and BI tools for more information.
Part of the filtering is so-called “Server-side filtering”, in which the filter applied is not only forwarded in real-time from Microsoft Power BI to Invantive Cloud, but also from Invantive Cloud to the backing cloud platform (in this case Autotask).
We will need to test whether the filters are forwarded to Autotask on this table. For this, please grant delegation. This answer will be updated. Apologies for the inconvenience.
This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.
Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.