cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joaod82
New Member

Is it the right approach to work with REST APIs?

Hi!

I'm facing some difficuties working with REST APIs. In my solution I've created a PBIX file where I use Power Query to call some APIs that return 1000 records per request and I need to paginate. As I said, I'm doing all of it with Power Query and the "worst" case I have is a table with something about 140k records. Other tables are smaller than this. The PBIX file size is 23mb.

 

Now the problem ...
This dataset is not too large. But each refresh (on the PBIX file or even in the Power BI Service) is taking too long, sometimes something about 1 hour to complete.

 

Is this approach the best one for this case? Maybe if I work with another service to load all the data on a SQL Server database and then build my Power BI dataset to consume SQL Server data can provide me more performance ... is this make sense?

 

Can you guys help me with any suggestions to gain some performance with this data refresh?

 

I appreciate all the help you can share! Thanks very much!

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @joaod82 

 

Here are some references, hope to be useful to you:

Refresh your Power BI dataset using Microsoft Flow 

Announcing Data Refresh APIs in the Power BI Service 

Refreshing all datasets in Power BI using REST API 

Unlimited data refresh in Power BI using the REST API and an Azure Function 

Greater control of your Power BI Datasets with the Power BI REST API 

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ranbeermakin
Resolver III
Resolver III

1 hour is not a long time.... pulling 140K records everytime is also not sane... you can pull data using azure functions and dump in a SQL DB... you can also then do incremental refresh for your 140K table data. hope this helps.

 

some guidelines in my old post here: https://bigintsolutions.com/2019/01/17/power-bi-get-data-apis/

TomMartens
Super User II
Super User II

Hey @joaod82 ,

 

whenever I have the possibility to retrieve data from endpoints that demand pagination I tend to use Azure Data Factory (ADF) and write the result to a Azure SQL DB. Of course this comes with additional costs.

 

Hopefully, this provides some new ideas on how to tackle this challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!