Differentiate OData4 for Power BI Cache Behavior

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.

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.
  • Bridge 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 achied 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 HTTP(S). The default of the HTTP disk cache on Invantive Cloud databases is 4 hours (14.400 seconds).

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 Bridge Online response cache?

The Bridge Online 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 Bridge 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.

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 Bridge 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 Bridge Online response cache can not be disabled. Invantive Cloud can apply minimum values such as a fifteen-minute minimum value for Bridge Online response cache.

The form below displays the maximum age of cache entries stored in the general caches: