Forum Discussion

bonjourposte's avatar
bonjourposte
Icon for Helper V rankHelper V
4 years ago
Solved

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.  Wh...
Need Help
  • otravers's avatar
    otravers
    4 years ago

    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.

bonjourposte's avatar
bonjourposte
Icon for Helper V rankHelper V
4 years ago

Thanks for the reply.  Do you mean like this: 

Adding the API as a datasource in the Service: 

 

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

 

 

 

I want to do all my work in the desktop.

 

  • otravers's avatar
    otravers
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

    • bonjourposte's avatar
      bonjourposte
      Icon for Helper V rankHelper V
      4 years ago

      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.  

      • otravers's avatar
        otravers
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.