SharePoint Online sources in Power BI do not require a gateway to schedule a refresh, making them a very convenient storage solution for file sources – in addition to things like SharePoint lists.
You can use a gateway for SharePoint Online sources, but you would need to create a connection in the gateway for every single source. This can be unwieldy, particularly if your sources are individual Excel files.
Service accounts are quite useful
I find it easier to skip the gateway and schedule refresh directly, preferably with a service account.
We use service accounts for scheduled refresh in Power BI so that reports don’t stop refreshing when individuals leave an organization. If you give a SharePoint license and a Power BI license to a generic service account and log in with it, you can “take over” the dataset as an owner. This does not affect your ability to update or republish the report, the data ownership will be maintained through those events.
You might think this is a bit of a pain to set up initially, but it’s good to get in the habit – because you don’t want to be responsible for fixing 50+ broken refreshes when a teammate leaves, or transferring your own reports later. It can be very daunting.
Something to keep in mind is that the refresh failure notifications will go to the dataset owner by default, so you will probably want to set a mail forward on the service account in Exchange to a distribution group (if you don’t have permission to do this, IT can do it for you) and/or specify alternate recipients in the scheduled refresh settings.
Make sure you grant the service account “Read”permission to whatever sources you are using in your reports – whether that’s a specific file, a SharePoint site, Smartsheets, Google Drive, or whatever – it needs to have access to it in order to run the refresh.
You need to enter credentials when you publish a new report in the web app
Each time you publish a new report that uses an authenticated source, you need to add credentials in the scheduled refresh settings, even if you entered them in the Desktop client. To do this, go into the scheduled refresh settings with the ellipsis menu next to your dataset in the Power BI web app:
And under “Data source credentials“:
- click on “edit credentials”
- select OAuth 2
- enter a privacy level, and
- Sign in!
After this is done, you can set the schedule under the “Refresh” section.
UPDATE: I did a video tutorial on how to schedule refresh for SharePoint Online here.