Honor ETag or Cache-Control to reduce OData network traffic by an order of magnitude, including developer experience

In this idea, I will explain how Finance and Data Professionals can experience a significant improvement in their productivity when Power BI Desktop would honor the ETag or Cache-Control headers of OData feeds.

The idea also explains how the quantity of avoidable network traffic for OData can be reduced by up to 1% when used with Power BI Desktop.

On an OData Producer over 20,000 companies access their cloud sources (typically European cloud apps such as Twinfield or Exact Online) as an OData feed.

Each API on the cloud platforms becomes an entity set ultimately; interim step is a table in our SQL engine.

Over 90% users are finance and data professionals relatively new to Power BI. They often stick to Power BI Desktop for quite some time.

The technology stack of Power BI, M, Invantive SQL and cloud paltforms allows filters to be automatically rewritten and forwarded across the whole chain up-to into the ultimate cloud platforms connected to.

Problem is that during developing and testing the reports, Power BI Desktop continuously retrieves the same OData4 data set again and again. Using caching on the OData Producer, subsequent request for a data set are sent back instantly.

However, the data sets are typically large, even up to over 1 GB gzip-compressed network size and over 10 GB uncompressed due to the large amount of financial transactions and number of companies contained.

We find that during normal use 75% of the OData requests can be answered by the OData response cache, and during development of reports up to 99% of the requests are answered from the OData response cache by the OData producer.

The OData answers include ETag and Cache-Control, but their values are ignored by Power BI Desktop. Google Chrome (the vi-variant of Power BI Desktop :-)) honors them correctly and this extremely reduces network traffic.

So, a large amount of avoidable network traffic is initiated when using OData feeds.

Of course experienced users vertically filter the data (for instance by company or year) and make the development process run faster. But our platform focuses on Finance and Data Professionals, not on IT-professionals. This audience has typically less experience and is less well-trained to act within some arbitrary technical boundaries.

It would improve performance for developers by an order of magnitude when Power BI Desktop would honor the ETag and Cache-Control. Downloading a 200 MB dataset typically takes a few minutes due to network bandwidth restrictions, plus some processing.

For more background see https://forums.invantive.com/t/sneller-data-verwerken-uit-invantive-cloud-op-geselecteerde-odata-clients/1706.

Summarizing: when Power BI Desktop would honor the ETag or Cache-Control headers on OData feeds, inexperienced users would experience a significant improvement in their productivity, while network traffic drops to as little as 1%.