Near real time reports in PBI + Kusto

Summary

Real time and near real time are subjective terms.

For some businesses, real time is up to 1 ms latency and for other cases 10 minutes latency is considered close to real time.

Lately I encountered a few cases in which Page refresh in PBI was used on a dataset using Direct Query against Kusto/ADX/RTA.

In this article I’ll cover a few best practices to ensure that such an implementation will be successful and conserve on resources.

Page refresh options

In PBI, you can see the page refresh options from the format page area:

When you turn Page refresh on, you’ll see the different settings.

You can refresh as frequent as 1 second but once you publish the report, you will be limited to the minimum frequency set by the tenant admin.

Also 1 second refresh is not realistic because most pages will take more than a second to refresh.

It’s a good idea to use performance analyzer and see how much time it takes to refresh the slowest visual on the page.

In this case the last visual takes 1.5 seconds to refresh, so the lowest refresh visual would be 2 seconds.

In order to distinguish between te different visuals use headers for each.

How to improve the performance of frequent refreshes

When using page refresh, PBI will send a very high number of queries. This number of queries can overwhelm the Kusto cluster and impact performance of other activities.

There are a few things you can do to lower the cost of the queries.

Results cache and shard level caching

You can read about caching results here.

The main decision you’ll have to make is how long to keep the results cache.

You have to determine what is the acceptable latency and set the max age for the cache accordingly.

For example, if the accepted latency is 25 seconds, you could set the max age to 15 seconds and refresh every 10 seconds.

Even if the setting is for a very short max age like 5 seconds, this setting will also set the shard level caching.

This way at least partial results for the query can come from cache instead of scanning the data.

Read more about it here.

Use materialized views(MV) for getting the latest values

A typical scenario for page refresh is when you get snapshots with the status of queues and you always want to show the latest results using the arg_max summary operator.

This is a classic example for using materialized views that will contain the latest values .

Instead of looking for the latest value when the query is running, the MV is always maintaining the latest values calculated once when new data arrives.