With the latest release of Power BI Report Server, we introduced the ability to share and view Excel Workbooks in a web browser (assuming you Office Online Server setup for your server farm). This included workbooks that had Power Pivot models embedded in them with external data connections that might need to be kept up to date. However, there isn’t currently a way to automatically refresh the data for those workbooks on the server currently like you can in SharePoint. Today, I’m excited to show you a way to get around that limitation using a tool called Power Update from a Power BI partner called PowerOn.
First announced on Rob Collie’s blog a couple years ago, Power Update is a standalone program that allows you to automatically refresh Excel files and load them to several destinations, including SharePoint, OneDrive, and Power BI Report Server. Let me walk you through how simple it is to get started and use with PBIRS –
1. When you first start Power Update, you’ll want to create a new task. Click “New” to get started –
2. Give your task a name and click Next –
3. You can decide how often you’d like to refresh your Excel workbook. I chose to do it every hour on a daily basis
4. I can choose to either refresh a single file, or all the files in a particular folder. I just need one workbook refreshed, so I choose that and proceed
5. For my publish location, I want to publish to Power BI, and then select the “Power BI Report Server” option.
6. I finish setting up the options for my server location and I’m done! (There are some optional items I can also choose in the last two screens, but I didn’t change anything on those for this exercise)
After I’ve set everything up, you’ll see the first task in my list is now populated. As a test, I clicked the “Run Now” button to see if it works. Everything was setup properly, so it’ll refresh the workbook, publish it to the server and then let me know it was successful. I can even setup e-mail notifications to tell me this after each time it runs!
That’s it – it’s very simple to set up and manage your Excel Workbooks in Power BI Report Server and make sure they always have the latest data for your users. And yes, anyone can use the public REST API’s for Power BI Report Server to do something like this on their own if they’d like. Power Update just makes it easy to enable this functionality for their organization.
Power Update offers users both a free and paid version of their product – the free version offers all the functionality of the paid version, but is limited to only one workbook you can schedule and use.
Thanks for reading!