OData performance issues

[Anonymous]

We have OData endpoint where every record contains also curve data (array of {x: number, y:number} objects). Those curves have ~600 points and there are arround 30 curves per record. In other words, it's quite a lot of data. Anyway the performance is still much worse than expected.

Example data set: 40 MB.

Executing query with "curl":  ~11s (actual performance of backend)

For demonstation purposes I removed all visuals and replaced it with just getting count of the top level records.

Power BI (using pre-downloaded data as JSON source): ~21s

Power BI (using OData feed): 58s

Summary:

1. Bottlneck of request processing is on the client side (Power BI) - our backend is far from being fully utilized during query execution.
2. Using OData feed is significantly slower than manualy downloading JSON data and using them as a data source.
3. Power BI (query editor, report refresh) is extremly slow even if the data are not transformed nor visualized in any way.

Is this normal behavior of Power BI?

What would you sugest to improve the performance?

My only idea is to remove curve data from data set and replace them with direct query where only a subset of the data will be visible at once. Or use different tool.

Any ideas will be appreciated.

[trick73]

Hi,

I'm experiencing same disappointment with OData in PowerBI.

OData involves pagination by design, so many requestes have to be issued when dealing with "big" data sets.

My OData server has a default of 1000 rows per page. Increasing the page size on the server side has given some benefit but still it remains twice as slow in the average compared to a massive json download.

I have written a small Python script to download the same OData feed and the performance gets sensitively better though still worse than the best case.

So I suspect than beyond pagination Power BI does something else that makes it much slower.

Some reference numbers:

- 1.7 million rows dataset

- 5.5 minutes with Power BI OData Feed

- 3 minutes with Python OData Feed

- 2 minutes one-shot json Download

Regards

Riccardo

[v-yuta-msft]

@Anonymous ,

Please refer to the tips in doc and blog below:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuta-msft

Thanks for reply, but unfortunatelly it's not helpfull at all.

I can use google myself and I already read those before I posted this question.

I'm looking for an answer to question why OData feed is much slower then downloading the data manually and using them as JSON source. Is it doing some validation against model which is time consuming or what?

Anyway even then the performance is terrible. Somebody with experience could tell if it's even worth a try to optimize this. Is our approach - download all and filter in Power BI, wrong for our data model? Is direct query - where we will filter data serverside based on user selected parameters better approach for this case?

Regards,

Michal

[Anonymous]

Hi, 

Have you find an answer to the problem? 

I have same issue with OData it is just terrible! 

[Anonymous]

Hi,

Unfortunatelly no.

PowerBI is not cappable of processing high-volume OData payloads effeciently. If your endpoint supports filtering and projections ($filter, $select), you can apply those operators by yourself and import result into PowerBI as plain JSON.

[Anonymous]

Hi, 

How do I know if my end poind supports that? 

Can you please elaborate? Iam not well experienced in that part. 

Your help is really really appreciated.

Regards

[Anonymous]

Look at the examples here:

https://www.odata.org/getting-started/basic-tutorial/#filter

https://www.odata.org/getting-started/basic-tutorial/#select

If it will work, then it's supported 🙂

Of course you have to adjust it to your data model.

If you don't know how your data model looks like, you can get single record with: ?$top=1 or you can check the /$metadata endpoint.

[Anonymous]

also do i need Business Central (navision) developer license?

Best

[Anonymous]

is that a faster approach then? 🙂 

I haven't tried yet. Iam still wondering if its worth trying 🙂 

lastly do i need to hard code everything from scratch ?

Best