Optimising OData Refresh Efficiency in Energy Question for Energy BI and Excel


OData has been adopted by many software program options and has been round for a few years. Most options are utilizing the OData is to serve their transactional processes. However as we all know, Energy BI is an analytical resolution that may fetch tons of of hundreds (or thousands and thousands) rows of knowledge in a single desk. So, clearly, OData isn’t optimised for that type of objective. One of many largest challenges many Energy BI builders face when working with OData connections is efficiency points. The efficiency depends upon quite a few components resembling the scale of tables within the backend database that the OData connection is serving, peak learn knowledge quantity over intervals of time, throttling mechanism to regulate over-utilisation of sources and many others…

So, typically talking, we don’t anticipate to get a blazing quick knowledge refresh efficiency over OData connections, that’s why in lots of circumstances utilizing OData connections for analytical instruments resembling Energy BI is discouraged. So, what are the options or alternate options if we don’t use OData connections in Energy BI? Effectively, the most effective resolution is emigrate the information into an middleman repository, resembling Azure SQL Database or Azure Knowledge Lake Retailer or perhaps a easy Azure Storage Account, then join from Energy BI to that database. We should resolve on the middleman repository relying on the enterprise necessities, expertise preferences, prices, desired knowledge latency, future help requirement and experience and many others…

However, what if we should not have another choices for now, and we now have to make use of OData connection in Energy BI with out blasting the scale and prices of the undertaking by shifting the information to an middleman area? And.. let’s face it, many organisations dislike the concept of utilizing an middleman area for numerous causes. The only one is that they merely can’t afford the related prices of utilizing middleman storage or they don’t have the experience to help the answer in long run.

On this submit, I’m not discussing the options involving any alternate options; as an alternative, I present some ideas and methods that may enhance the efficiency of your knowledge refreshes over OData connections in Energy BI.


The information on this submit is not going to provide you with blazing-fast knowledge refresh efficiency over OData, however they may show you how to to enhance the information refresh efficiency. So for those who take all of the actions defined on this submit and you continue to don’t get a suitable efficiency, then you definitely would possibly want to consider the alternate options and transfer your knowledge right into a central repository.

If you’re getting knowledge from a D365 knowledge supply, chances are you’ll need to have a look at some alternate options to OData connection resembling Dataverse (SQL Endpoint), D365 Dataverse (Legacy) or Frequent Knowledge Companies (CDS). However take note, even these connectors have some limitations and won’t provide you with a suitable knowledge refresh efficiency. As an example, Dataverse (SQL Endpoint) has 80MB desk dimension limitation. There is perhaps another causes for not getting a great efficiency over these connections resembling having further large tables. Consider me, I’ve seen some tables with greater than 800 columns.

Some recommendations on this submit apply to different knowledge sources and will not be restricted to OData connections solely.

Suggestion 1: Measure the information supply dimension

It’s at all times good to have an concept of the scale of the information supply we’re coping with and OData connection isn’t any completely different. In actual fact, the backend tables on OData sources may be wast. I wrote a weblog submit round that earlier than, so I counsel you utilize the customized perform I wrote to grasp the scale of the information supply. In case your knowledge supply is massive, then the question in that submit takes a very long time to get the outcomes, however you’ll be able to filter the tables to get the outcomes faster.

Suggestion 2: Keep away from getting throttled

As talked about earlier, many options have some throttling mechanisms to regulate the over-utilisation of sources. Sending many API requests could set off throttling which limits our entry to the information for a brief time frame. Throughout that interval, our calls are redirected to a special URL.

Tip 1: Disabling Parallel Loading of Tables

One of many many causes that Energy BI requests many API calls is loading the information into a number of tables in Parallel. We are able to disable this setting from Energy BI Desktop by following these steps:

  1. Click on the File menu
  2. Click on Choices and settings
  3. Click on Choices
  4. Click on the Knowledge Load tab from the CURREN FILE part
  5. Untick the Allow parallel loading of tables possibility
Disabling Parallel Loading of Tables in Power BI
Disabling Parallel Loading of Tables in Energy BI Desktop

With this selection disabled, the tables will get refreshed sequentially, which considerably decreases the variety of calls, subsequently, we don’t get throttled prematurely.

Tip 2: Avoiding A number of Calls in Energy Question

One more reason (of many) that the OData calls in Energy BI get throttled is that Energy Question calls the identical API a number of occasions. There are a lot of identified causes that Energy Question runs a question a number of occasions resembling checking for knowledge privateness or the best way that the connector is constructed or having referencing queries. Here’s a complete checklist of causes for working queries a number of occasions and the methods to keep away from them.

Tip 3: Delaying OData Calls

If in case you have accomplished all of the above and you continue to get throttled, then it’s a good suggestion to overview your queries in Energy Question and look to see when you’ve got used any customized capabilities. Particularly, if the customized perform appends knowledge, then it’s extremely probably that invoking perform is the perpetrator. The wonderful Chris Webb explains find out how to use the Operate.InvokeAfter() perform on his weblog submit right here.

Suggestion 3: Think about Querying OData As a substitute of Loading the Whole Desk

This is without doubt one of the greatest methods to optimise knowledge load efficiency over OData connections in Energy BI. As talked about earlier, some backend tables uncovered through OData are fairly large with tons of (if not hundreds) of columns. A standard mistake many people make is that we merely use the OData connector and get all the desk and suppose that we’ll take away all of the pointless columns later. If the underlying desk is massive then we’re in hassle. Fortunately, we will use OData queries within the OData connector in Energy BI. You’ll be able to study extra about OData Querying Choices right here.

If you’re coming from an SQL background, then chances are you’ll love this one as a lot I do.

Let’s take a look on the OData question choices with an instance. I’m utilizing the official take a look at knowledge from the OData web site.

  1. I initially load the OData URL within the Energy Question Editor from Energy BI Desktop utilizing the OData connector
Using OData connector in Power BI Desktop
Utilizing OData connector in Energy BI Desktop
  1. Choose the tables, keep in mind we are going to change the Supply of every desk later
Selecting the tables from an OData connection
Deciding on the tables from an OData connection

Be aware

That is what many people sometimes do. We connect with the supply and get all tables. Hopefully we get solely the required ones. However, the entire objective of this submit isn’t to take action. Within the subsequent few steps, we modify the Supply step.

  1. Within the Energy Question Editor, choose the specified question from the Queries pane, I chosen the PersonDetails desk
  2. Click on the Superior Editor button
Advanced Editor in the Power Query Editor
Superior Editor within the Energy Question Editor
  1. Substitute the OData URL with an OData question
Querying OData in Power Query in Power BI
Querying OData in Energy Question in Energy BI
  1. Click on Finished

As you’ll be able to see, we will choose solely the required columns from the desk. Listed below are the outcomes of working the previous question:

Querying OData in Power Query
Getting knowledge utilizing OData question

In real-wrold situations, as you’ll be able to think about, the efficiency of working a question over an OData connection can be significantly better than getting all columns from the identical connection after which eradicating undesirable ones.

The chances are countless on the subject of querying a knowledge supply and OData querying in no completely different. As an example, let’s say we require to analyse the information for folks older than 24. So we will slim down the variety of rows by including a filter to the question. Listed below are the outcomes:

Using OData query filter
Utilizing OData question filter

Some Additional Sources to Be taught Extra

Listed below are some invaluable sources in your reference:

Whereas I used to be on the lookout for the sources I discovered the next wonderful weblogs. There are superb reads:

As at all times, I might be completely happy to find out about your opinion and expertise, so depart your feedback beneath.

Have enjoyable!

Supply hyperlink


Please enter your comment!
Please enter your name here