Power BI Dataset Refresh Scheduling Using Outlook And Power Automate

When the new “Refresh a dataset” action for Power Automate (formerly Flow) was released last year I couldn’t help thinking there must be something really cool you could do with it – I just didn’t know what. There are lots of great resources explaining the basics (see Jon Levesque’s video here for example) and Adam Saxton did a nice demo here using the “When an item is modified” trigger to show how to refresh a dataset when a value in a SQL Server table is updated, but that’s it. So I got thinking about the types of problem it could solve and the fun I could have with it…

While Power BI’s scheduled refresh functionality is great, it doesn’t give you as much flexibility as you might need. For example:

  • You might want to schedule your dataset to refresh only on weekdays, not weekends, and you might also want to cancel refresh on certain days like public holidays. You might also only want to refresh a dataset on a monthly basis or at less than the half-hourly granularity that the UI allows. Why? Perhaps because it’s important to minimise the load you put on your source systems; it’s also the case that for many cloud data sources the more data you read, the more you pay.
  • If you have a lot of datasets to refresh you might want to control which datasets are refreshing in parallel, again to reduce the load on your data sources and if you’re using Premium, to reduce the load on your capacity. It’s hard to get an overview of when all your refreshes are scheduled in the Power BI Portal and manage what’s happening when.

The ideal way to view when multiple events are scheduled is a calendar and we’ve got great calendar functionality in Outlook. What if you could schedule refresh of your datasets from a calendar in Outlook? It turns out to be easier than you might think! Here’s how.

The first thing I did was create a new calendar in Outlook called Power BI Refreshes:

Calendar

In this calendar I created appointments (either recurring or one-off) for every dataset refresh:

Cal2

For each appointment, I entered the unique identifier of the dataset in the Title and the unique identifier of the workspace in the Location like so:

Event

You can find these unique identifiers by going to the Settings screen for your dataset in the Power BI Portal and looking at the url:

Settings

Cathrine Wilhelmsen has more details on finding these ids here.

Last of all, I created a very simple Flow in Power Automate:

Flow

The “When an upcoming event is starting soon” trigger is fired when each of the appointments on the Power BI Refreshes calendar is about to start. It then passes the Location and Subject from the event – which of course contain the ids the workspace and dataset to be refreshed – to the Refresh a dataset action, which does the refresh.

This isn’t something I recommend putting into production but I think it’s very interesting as a proof-of-concept. I guess Logic Apps would be a more robust alternative than Power Automate and I would want to be 100% sure that events fired when I was expecting them to fire, so some thorough testing would be needed. I’m not experienced enough with Power Automate/Logic Apps to know if I’m doing the right thing, to be honest. I also feel like using ids in the meeting title and location is a bit hacky and there must be a nicer way of handling this.

On the Power BI side, it’s worth remembering that when a refresh is kicked off in Power BI the actual refreshing only starts when the Power BI Service has the required resources available, and especially in Shared capacity this can involve a wait of several minutes. What’s more the “Refresh a dataset” action does not know whether the refresh it kicks off succeeds or fails; I guess if you wanted to handle retries or notifications on failure then you would need to call the Power BI API get the refresh history of a dataset – there’s no built in action to do it, but it’s possible with a Power Automate custom connector.

If you have any thoughts about this, ideas on how to make this better or if you do put something like this into production, let me know – I would love to hear from you!

9 responses

  1. I found that if you enter emails into the “Email these users when the refresh fails” box on the Schedule Refresh page of a dataset, emails will be sent to the those users if a refresh executed via the API fails, even without Schedule Refresh turned on. This way you can at least know if a refresh fails.

  2. does the refreshes capped at eight for datasets in the shared workspaces? And, a general question to the data refresh for the datasets in the shared workspaces: is “refresh now”/manual refresh counts towards the eight time limitation? I read in documentation and seems it does. Actually, it seems there is no time limitations to use “refresh now”.

    • Tony – you are allowed up to 8 scheduled or API refreshes per day. So if you have scheduled refreshes at 1, 3, 5, 7 AM, and then call a refresh via the API 4 times later in the day, you would be at your limit at your 8th API call.

      Manual refreshes (Refresh Now) do not count towards the 8 times per day – as long as there are resources and a refresh isn’t already running you can do a manual refresh as often as you wish.

  3. Pingback: Refreshing Power BI from Your Outlook Calendar – Curated SQL

  4. Pingback: Power BI Dataset Refresh Scheduling Using Outlook And Power Automate | Pardaan.com

  5. Why should I use Dataset Id or Workspace Id when I can select the Dataset/Workspace name from the drop down directly. Let me know if I missed something!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: