Using On-Prem Data Gateway for OData Feed

[lsimon]

Hi there!

In an effort to decrease load time and time-outs for customers who are attempting to load DBs from an OData feed, we decided to implement a data gateway they could use in the Power BI desktop app. We thought that the limitations of a users' machine was the reason they were experiencing such slow load times.

Initially, we had separated web traffic away from the OData web service traffic and the test response times were quicker and had less timeouts than before. After this, we then decided to try and implement the data gateway.  Our understanding was that the gateway would act as a bridge between on-prem data and various apps or services the customers may use. 

After installating and testing with a copy of a production DB for a few days, we began to notice errors (see below) that would arise after attempting to load more than one table in the DB (The DB is a little over 150GB in total). Load times for 1 table with around 6.5+ million rows would take upwards of 35+ minutes or spit these errors out.

In a perfect world, we would like to have the ability for customers to connect to the OData feed either from the Power BI desktop or online version with little to no hiccups. (This is also my first experience with using PowerBI and OData feeds, so this is a lot of new information!)

Thanks for the help!

[dax]

Hi Isimon

You need to know, gateway is use to connect  clodu service and on-prem datasource. You could refer  to What is an on-premises data gateway? for details.

In addition, you need to know, the data limit for import and direct query. You could refer to this post and Power BI Tips: Import vs Direct Query for details.

By the way, you also need to check whether there is something wrong in one dataset which will casuse the  next dataset can't be processed.

Best Regards,
Zoe Zhi

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

[lsimon]

Hi dax,

How would we go about changing from an OData feed to SQL server + our on-premise gateway + DirectQuery in Power BI/Power BI Service?

[dax]

Hi lsimon, 

Did you mean that you want to import OData feed data into sql server , then use SQL server as datasource and add gateway, right? If so, you could refer to How to import data into SQL Server databases using the OData source for SQL Server Integration Servi...  for details.

Best Regards,
Zoe Zhi

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