Using an OData4 feed on Invantive Cloud you can retrieve data from over 70 (cloud) platforms with Power BI and Power Query. Invantive Cloud employs various techniques to improve reliability and performance of data retrieval into Power BI and Power Query. These optimization techniques also apply to other OData4 consumers such as https://access-odata.com.
Make sure to understand the structure of Invantive Cloud before continuing. The structure is described on Invantive Cloud Structure.
What are the general caches of Invantive Cloud?
The general caches are available with all connectors. The general caches do not include the use of Data Replicator nor the use of connector-specific caches such as the Incremental APIs on Exact Online.
There are two general caches:
- HTTP-disk cache: memorizes in an encrypted format the answer from the cloud platform upon a specific request.
- OData response cache: memorizes in an encrypted format the answer on an OData4 request, as calculated from various data containers.
This article explains how you can finetune one of the techniques namely general caches.
What is the HTTP disk cache?
The HTTP disk cache memorizes in an encrypted format the answer from the cloud platform upon a specific request… The disk cache applies to all queries on a database in Invantive Cloud. Per query, a different cache behavior can be achieved using the http_disk_cache and http_memory_cache hint in Invantive SQL. For example:
select /*+ http_memory_cache(true, interval '5 seconds') */ *
from teamleader.v1.deals
Details on hints can be found in the Invantive SQL grammar. The HTTP disk cache only applies to backend cloud platforms accessed through HTTPS.
The default lifetime of the HTTP disk cache on new Invantive Cloud databases is 16 hours (57.600 seconds). The lifetime can be altered manually in the database definition on Invantive Cloud.
Once the data has been retrieved, it can be used in a query on the SQL editor, in Invantive Cloud applications as well as downloaded using Invantive Bridge Online into any OData4 consumer.
What is the OData response cache?
The OData response cache memorizes in an encrypted format the answer on an OData4 request, as calculated from various data containers. For each OData4 request a unique fingerprint is determined and the response cache is checked for an answer that is still sufficiently fresh according to the OData response cache setting. Whereas a HTTP disk cache element sometimes stores a very small answer, a response cache answer can be very large. For instance, a query across 1.000 companies can be 1 GB large after encryption and compression.
These response cache objects can be returned without any delay at speeds exceeding 500 MB/s to the OData4 consumer. This especially benefits developers when creating reports and reports with a shared data set. However, shared data sets should be introduced on the Power BI platform for large communities and/or data sets to avoid Invantive Cloud-based rate limits being applied.
Via the option “Reset cache” in the menu on the right of Invantive Bridge Online it is possible to empty the content of the OData cache (only for incidental use).
How do I change the general caches?
To change the general caches execute the following steps:
- Navigate to the virtual database in Invantive Cloud.
- Navigate to the HTTP disk cache maximum age.
- Enter the maximum age in seconds, with a minimum of 1 and maximum of 864000 (10 days).
- Go to the OData Response cache maximum age.
- Enter the maximum age in seconds, with a minimum of 1 and maximum of 864000 (10 days).
Also, an administrator can decide whether to turn off HTTP disk cache completely. The OData response cache can not be disabled.
A lifetime of the cache less than the value of “Maximum cache lifetime for Bridge Online in seconds” in the subscription conditions is ignored and replaced by the value of “Maximum cache lifetime for Bridge Online in seconds”. For most commercial subscription forms, the default minimum value is 1 hour (3.600 seconds) as visible on Invantive Cloud:
The form below displays the maximum age of cache entries stored in the general caches as defined on a database:
Update Response Cache May 2022
Recently it has also become possible to empty the cache manually, so that the user can decide that really fresh data is needed. To do this use the option “Reset cache” in the top right menu of Invantive Bridge Online:
Update Response Cache June 2023
Recently it has also become possible to use only very recent cache values by include Cache-Control headers for relatively fast queries. This enables interactive products such as Power Apps to continuously acquire very recent data without a switch to Invantive App Online.
For more details:
Please note that all caches on the servers involved are emptied for all databases. Reloading can take very long.