cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bonjourposte
Helper I
Helper I

Faster API updates

Good day, 

We get data from Walkscore for our properties through an API.  Every month, I update our properties with a new table in Power Query, re-add custom columns with the API code, and save.  When I do so, my file is out of commission for the next ten minutes because it's fetching data from the API.  Same applies when I save and close, and refresh the data in the home screen.

 

(I ran the same table in DAX studio, and it only took 36 milliseconds.)

 

How can I make our API run faster??

1 ACCEPTED SOLUTION

Yep, dataflows have been in in General Availability since April 2019 and it's generally considered a best practice to at least stage, and often transform, your source data in them, upstream of datasets. This gives you separation of concerns with ETL in dataflows and data modeling in datasets.

 

I'd do the raw API calls in one dataflow, or if there's a lot of historical data I'd partition API calls in two dataflows (historical + refreshable). I'd then input that into another dataflow where additional logic (e.g. calculated columns) is performed, and that's what I would finally ingest in the dataset in Power BI Desktop.

 

You don't have to do it this way, but this is my considered advice based on having been there and done that quite a few times.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

View solution in original post

8 REPLIES 8
bonjourposte
Helper I
Helper I

Thanks for the reply.  Do you mean like this: 

Adding the API as a datasource in the Service: 

bonjourposte_1-1657303981372.png

 

And then connecting to it once I'm back in the desktop?  

 

 

bonjourposte_0-1657303905862.png

 

I want to do all my work in the desktop.

 

Yes, that's what I mean, though those screenshots are old as dataflows went out of beta years ago.

 

If you don't want to use dataflows then don't, I guess being locked out of Power BI Desktop for ten minutes is not such a big deal after all.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

I just copied the screenshots off a 2018 instructional video.   

 

https://www.youtube.com/watch?v=veuxofp0ZIg

 

I do want to circumvent these constant updates.  I just added a calculated column to the table, and that alone prompted another 10-minute call to the API.  

Yep, dataflows have been in in General Availability since April 2019 and it's generally considered a best practice to at least stage, and often transform, your source data in them, upstream of datasets. This gives you separation of concerns with ETL in dataflows and data modeling in datasets.

 

I'd do the raw API calls in one dataflow, or if there's a lot of historical data I'd partition API calls in two dataflows (historical + refreshable). I'd then input that into another dataflow where additional logic (e.g. calculated columns) is performed, and that's what I would finally ingest in the dataset in Power BI Desktop.

 

You don't have to do it this way, but this is my considered advice based on having been there and done that quite a few times.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

So I've put my API in the service, but I'm not sure how to integrate this into my data model.  It's a post request, so I have brought in the necessary address columns into the service for it to figure out the scores it needs to return.  

 

bonjourposte_0-1657643857102.png

 

 

Now when I'm back in the desktop, I have connected to the dataflow, but it returns nothing, even after I refresh in both the service and desktop.  

 

bonjourposte_1-1657643965273.png

 

How do I get it to populate?

Have you saved and refreshed the dataflow? It's distinct from dataset refreshes.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Yes I had... but I just ran it again, and now it's working.  Thanks.

 

otravers
Super User
Super User

Sounds like you're doing all of this in Power BI Desktop, correct? If that's the case consider moving your API calls to dataflows. You might have to adapt your Power Query code so that it can work in the service but in my experience it's well worth it.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.