Level: Intermediate
One of the many excellent sessions I attend this week at the PASS Business Analytics Conference in San Jose was a session titled “Get Latest Insights by connecting your data using Power BI Content Packs and PBI Gateways”. The title was interesting but the content presented by Dimah Zaidalkilani and Theresa Palmer-Boroski (both Program Managers on the Power BI team at Microsoft) was truly excellent. I am going to share here what I learnt about the Power BI Personal Gateway, to help you understand if you need it, and if so how to get it installed and running.
There are actually 2 gateways that Microsoft currently has available for Power BI, the other being the Enterprise Gateway (for companies and the like). I will not be covering the Enterprise Gateway in this post. You would generally look to use the Enterprise Gateway if you refresh from work from a corporate network and need to connect through the firewall.
What is Power BI Personal Gateway?
As the name suggests, this gateway is for “personal” use. To use it you simply install it on your own laptop, desktop computer or even a server if you want. “Personal” does not mean you can’t use it for business if you want to, it is just a term to distinguish from the other gateway. What the Personal Gateway does is create a “gateway” or connection between your computer (that has the software installed) and the Power BI Service in the cloud. It is kind of like a dedicated VPN designed just to allow Power BI to talk to the data on your computer.
Why do I need it?
Well you probably don’t “need” it, but you certainly might “want” it. Once you have installed and configured the Personal Gateway on your computer, you are then able to refresh your Power BI datasets directly in the cloud. You have the option to either set up an automated schedule (up to 8 times per day) or you can trigger the refresh manually on demand.
When you refresh a Power BI Desktop file on your PC, Power BI Desktop connects to all the data sources it needs to refresh the report. Data sources can include things such as SQL Server, local Excel files, CSV files, or what ever you have set as your data sources for your reports. The Power BI Personal Gateway simply allows http://PowerBI.com to access those same data sources that are visible on your PC transparently over the Internet.
The Regular Refresh Process
If you are anything like me, your standard refresh process for Power BI workbooks will go like this.
1. Open the Power BI Workbook on your PC via Power BI Desktop
2. Click on the refresh button
3. Wait for 1 to 10 minutes for the workbook to refresh while doing something else. 30 minutes later you realise the refresh finished 20 minutes ago.
4. Save and then “Publish” the workbook to PowerBI.com via the menu shown below.
5. You then get 1 or more prompts about where to load the workbook and also “do you want to replace the existing version” (seriously – what else would I want to do?).
Repeat process steps 1 through 5 over and over again for each Workbook you have developed.
The Power BI Personal Gateway Process
Option 1:
- Configure a daily or weekly refresh once and then do nothing but sit back and relax.
Option 2:
- Log on to the Power BI Service
- Click on the ellipsis next to each data source and click “refresh” for each workbook you need to refresh. There is no waiting and the refreshes can be triggered in parallel (whether you should or not will depend on your computer and Internet connection.
Things You Should Know
There are a couple of things you should be aware of.
- Gateways are part of the paid subscription to PowerBI.com. If you currently don’t have a paid subscription then you can’t use it. Everyone has to decided for themselves if it is worth what it costs. Keep in mind there are other benefits of the paid subscription, not least of which are “content packs”. I will come back and talk about that another time. Also keep in mind that Microsoft is currently not invoicing personal users for the professional version unless they are part of some broader corporate licencing agreement. In my experience you will be given a 60 day free trial, and after 60 days you will be given a further 60 days free. I guess this will end one day, but I haven’t seen the end yet. If it still isn’t right for you, consider purchasing Power Update as a local refresh tool.
- Your computer needs to be turned on and connected to the Internet at the time of the refresh if you want it to work. Computer servers are “by design” always on and hence it is not a problem. But your laptop or personal computer may not be on all the time and hence you need to keep this in mind.
How to Install the Power BI Personal Gateway
Installation couldn’t be easier. Go to https://powerbi.microsoft.com/en-us/downloads/ and find the Personal Gateway download. There is a link to the downloads page on the https://PowerBI.com home page before you log in.
Launch the software then follow the steps in the install wizard and close then you are done.
Set your Data Source Credentials
The only thing that wasn’t obvious to me when I started using the Personal Gateway was that you need to manually set the data source credentials for each dataset you want to refresh through the gateway. The first time I realised this needed to be done was when I got the following error message.
Clicking through the error took me to the settings section. You can get to this session at any time from the regular “cog” settings menu.
For each data source in your datasets, you will have to “edit credentials” and then tell Power BI what the connection authentication method will be.
Here is an example of the dialog from one of my workbooks that has a SQL connection.
The dialog for regular PC files is very similar.
Once it is done, you should see a confirmation message something like this for each connection.
Time to Refresh the Datasets
In https://PowerBI.com, click on the ellipsis next to one of your datasets, and then you can either set up your Scheduled Refresh settings (2 below) or manually refresh the report on demand (3 below).
If you select Schedule Refresh above, you will be taken to the settings panel (also clickable from the cog in the top right of the page). From there you can set up the refresh schedule for all of your loaded datasets. You may choose to stagger the refresh times across the data sets so that they don’t all hit your computer at once. You can have up to 8 refresh events each day per dataset.
I hope you have found this article interesting. I would to hear from different readers as to why they have decided to use or “not use” the personal gateway, so please share your story in the comments section below.
If you are looking for more detailed information, you can read about it at the official Power BI Site.
Matt,
Thank you for another insightful post. Day or night it is always great to read your thoughts and learn from you.
I am going to try the gateway now,- although I probably don’t need it, but I am certainly keen to have it!
Cheers
Thank you for the post, as always very helpful
Thanks, Matt!
Great information, as always.
I’ll be able to reference this when my managers and IT guys start drilling me with questions.
Chris
Thanks Matt for this early post!
I am so not familiar with all the available options of connection to dataset, hence I like your post!
Maybe I am asking a basic question (?): if I want to connect to a dataset which is on a sharepoint drive, would it be the same method with the personal gateway?
Hans from PUG convinced me to send them out earlier so he could read them in the morning – glad you like that too :-). I see no reason why SharePoint wont also work. If you PC is connected to the server and is authenticated against SharePoint, then I think it will work. But what about your firewall. You will have to check that out.
Post back how it goes.