OData causes massive data load (Especially with nested Expand)

[A_Yao]

I am aiming to use OData to automate the process of updating data for a Power BI dashboard from a Dynamics 365 instance. Currently, we simply use a filtered view and download it as an excel file, before manually swapping the source file in the dashboard to it. These files contain more columns than we actually use in the dashboard, but are still only around 6 MB or so in size at the very most.

I was intending to use OData to both automate the process and cut down on unnecessary columns to save on data transfer, using $Filter and $Select. The preview updated rather quickly, but when I tried to close and apply, the Load process took around 5-8 minutes to process, and was guzzling data upwards of 300-400 MBs for what should be a query resuling in something smaller than 6 MB. 

This is in contrast to the Dataverse connector using SQL syntax I was using for a different set, which completed in less than 3 seconds for about 1/4th of the data size. The whole power BI file is less than 10 MBs after the process as well. Where did all the extra data Odata downloaded go?

Selectively removing parts of the OData prompt revealed that the main culprit was a single nested expand that expanded twice through structured columns to reach the value I needed; though, even without that single part, it would still guzzle maybe 80 MB before completing.

Why is Odata so data-hungry and taking so long to request and download data from Dynamics? And why does a nested expand cause it to bog down so much? Is it downloading the entire sheet and then filtering/selecting locally? Is there anything I can do to reduce this cost and streamline the load process?

[pankajnamekar25 · Accepted solution]

Hello @A_Yao 

To improve OData performance in Power BI, avoid using nested $expand as it leads to large and complex backend queries that significantly slow down data loading. Instead, load related tables separately and merge them within Power Query. Always use $select explicitly not just for the main table but also within expanded entities to limit the number of fields retrieved. Applying $filter early in the query helps reduce the number of rows before they reach Power BI. If available, it's better to use the Dataverse connector, which is optimized for performance and handles queries more efficiently than OData. Additionally, turning off background data previews in Power BI can prevent unnecessary data loading during report development.

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.