This is the fourth installment of this post series, and will demonstrate the process for publishing and sharing Power BI Reports. Key things we’ll look at here are how to publish the dashboard, keep it up to date, and share it with users outside our company. (We will look at sharing with internal users the next post.)
Series Table of Contents
This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard. You’ll want to follow along in order, so here’s the table of contents for the series:
- Get Data From OneDrive for Business
- Creating Power BI Visuals
- Creating Power BI Card Visuals
- Sharing Power BI Reports (for External Users) (this post)
- Sharing Power BI Dashboards
In order to make this whole solution work, I’m assuming the following:
- The person publishing the dashboard has a Power BI Pro subscription. This is needed to enable the feature to schedule refresh.
- All other internal users have a Power BI Free subscription… because I’m cheap like that.
- External users may or may not have a Power BI subscription at all.
So basically, to sum this up really quick, if you only have one person authoring and publishing your dashboards in your company, you only need one Power BI Pro license, and the rest of your team can be on the free version.
The good news is that it’s totally free to sign up for Power BI, you just need a “work” email. (I.e. it won’t let you sign up with your hotmail, gmail, or yahoo address, but if it’s some kind of custom domain, you should be fine.) In fact, you can most likely even sign up even if your IT department hasn’t yet. If you want to try it, head over to the Power BI site.
Publishing the Report to Power BI
The first thing we need before we can start sharing our reports is publish them. Doing that is simple once we have our account set up. We start by going to Power BI Desktop and opening our Power BI Desktop file.
Now, if you haven’t signed in to Power BI, you’ll want to do that first:
- Go to File –> Sign In
- Enter the email and credentials to your Power BI account and commit them
Once you have successfully logged in, that will make things easier, as it won’t prompt you when you’re publishing. So assuming you’ve done this, you can now:
- Go to File –> Publish –> Publish to Power BI.
Because my company uses Office 365 Unified Groups, I actually get a long list of locations where I can publish my report, but I’m going to choose the very top one call “My Workspace”. (You may not have or get any groups, but everyone has a the My Workspace area.)
A few seconds later I’m given a happy little message about how it was successful:
And that’s it for Power BI Desktop. You can click the link or dismiss the message and log in at http://powerbi.microsoft.com. No matter which, you can shut down Power BI Desktop and move to your web browser.
Exploring the Power BI Interface
On the left, you’ll see that you’ve got a few areas that you can navigate into, as shown below (yours may be expanded, I collapsed them for easier reading):
The important pieces for us here are the Reports and Datasets areas of the navigation pane. The data for our file is separated from our report, so we need to talk to both portions. But first, I’m just going to take a look at the report in the Power BI web interface. To do that II :
- Selected reports on the left (only required if it is collapsed)
- Selected the Hotel Stays report
And, as you can see, I’ve got a nice report that looks just like it did in Power BI desktop. It even cross filters the same:
So this is kind of cool. It’s no longer on my PC, it’s stored in the cloud, as is the data set. So in theory, I could update my Excel file with a new hotel, and it should refresh since that file is stored in my OneDrive for Business, right?
Sorry, but not yet. See the Refresh button the red arrow is pointing at? That will refresh the report from the data set (although I believe you need to refresh the data set first). So manual, so let’s fix that first.
Keeping the Reports up to date with Scheduled Refresh
Again, in order to set up scheduled refresh of our reports, we must have a Power BI Pro subscription. If you don’t… well… you can still use the solution, but you’ll need to come in and manually click the button to refresh it (and seriously, who wants to do that?) I’m going to assume you do have a Power BI pro subscription, and we’ll carry on here.
So to set this up, I clicked on the Datasets button in the navigation window on the left, then selected the Hotel Stays data set. That took me to a blank page that has a very Power BI Desktop like experience, including a Refresh button in the top right:
This area can be used to manually force the data set to update, and can even be used to build additional reports. But neither is what we want. What we need is a way to schedule the refresh, which is found by clicking the … icon beside the data set name:
Clicking Shedule Refresh takes me to a page and – after about a 1 second delay – gives me some other important info:
Okay, so I need to confirm my password. I’m not going to cover this in much detail, as Chris Webb recently covered this part in detail. I will point out that OneDrive for Business is, in fact, SharePoint (just in case you’re wondering why we see a reference to SharePoint there.) So all we need to do is click the Edit Credentials link and re-login to the Office 365 account. Just make sure you change it from Anonymous to oAuth2 before trying to sign in.
Once done, you can expand the Schedule Refresh area, flip the flag to Yes, and you should be good to go:
A few of key points to be aware of here:
- Sometimes Power BI seems to forget that it has been authorized. You may need to come back in and re-login. That’s only happened to me once so far, but I’ve seen people mention that it happens to them more frequently.
- The above is why that last checkbox is SUPER IMPORTANT. Microsoft will email you whenever a scheduled refresh fails, so that you are aware and can fix it. This is huge, and I’d recommend you leave that box checked.
- You can add more frequent refreshes although – in my experience – you’re limited to about 10 times per day. I really don’t need more than daily here, but for other dashboards I’ve set them to refresh every 2 hours during business hours.
At any rate, with this set, we click Apply and forget about manual until Microsoft emails us to let us know something stopped working.
Sharing Power BI Reports Externally
Now, the data is being kept up to date, and now we want to share the report with others externally. How?
- Click Reports and drill into the report you want (Hotel Stays for me)
- On the report, go to File –> Publish to Web
- Click Create Embed Code then Publish
This will give you a nice dialog that provides two things:
Here’s a direct link to the email-able URL, which you can see renders nicely in the online app.
And here is what happens when you embed the iframe in your site. (Yes, it’s fully clickable!)
So that’s pretty cool and pretty easy. The only caveat (which works fine for me for my purposes) is that this is 100% public. While the end audience can’t see any of the underlying data, anyone on the internet who gets this url can see and play with the dashboard.
So this is a pretty cool thing so far. We’ve got a solution where the data set can be access from OneDrive for Business via the Excel app on my phone. I can add new records there and they are saved, then automatically refreshed in the Power BI report that lives in my Office 365 tenant. And better yet, I have been able to publish a fully interactive report to the web that will update over time and that you can click and drill into. Neat stuff for a pretty low cost.
But I know what you’re thinking… you’re thinking that this is all great, but what if we want to share it with users INSIDE the company, and we don’t want to just publish it as a public facing page. Well hang tight, because that is the focus of the next post in the series.