If you’ve followed my Power BI from Rookie to Rockstar book so far, you know that Power BI can reach on-premises data sources as well as cloud based. With Power BI Desktop you simply connect to on-premises data stores, however when the report published into Power BI website there should be a bus connection between Power BI (on cloud) and on-premises data stores (such as SQL Server, Oracle, SSAS Multi-Dimensional and so on). Here is where Power BI Personal Gateway come to play its role. Personal Gateway create the connection path from the data set in Power BI on cloud to the data store on -premises (on your organization server, or even your laptop!). Personal Gateway creates that connection line through Azure Service Bus.

In this section you will learn

• In which scenarios Power BI Personal Gateway would be helpful?
• What are limitations of Personal Gateway?
• Myths and Misconceptions about Personal Gateway

We are in Get Data section of Power BI book and this is the best time to talk about Personal Gateway because most of the data sources in organizations I believe still are on-premises, and Power BI fortunately can connect to it. Let’s start from the definition of Personal Gateway;

What Is Power BI Personal Gateway?

Power BI Personal Gateway is an application and service that creates the bus connection between Power BI data set on cloud to on-premises data store. This is an small application that you download, install and configure.  You can use Personal Gateway to connect to any on-premises data stores that is listed in Power BI Get Data section except ODBC, Active Directory, and Microsoft Exchange. Screenshot below show a diagram of gateway role for Power BI and on-premises data stores;

Where Power BI Personal Gateway Would be Useful?

When you get data from an on-premises data store, and your on-premises data store is not ODBC, Active Directory, or Microsoft Exchange. So don’t limit yourself, you can connect to SQL Server On-premises, SSAS multi-dimensional on-premises (You can do SSAS Tabular on-premises as well, but there is a better type of connection – live/direct connection – for it which would serve purposes much better).

What about Online Data?

If you get data from online data sources, such as CRM Online, Mailchimp and other Content packs of Power BI, data from a website and etc, then you don’t need Personal Gateway. Online data can be refreshed as long as required credential is set up correctly. However remember if you are using combination of an online data store and on-premises, then you need personal gateway to get on-premises refreshed.

How to Install Power BI Personal Gateway?

Installation is really easy and straight forward, you would be able to follow installation steps easily. Download it from the Power BI download menu option as screenshot below mentioned:

Or you can download it directly (Note that direct download link might not have the latest Power BI Personal Gateway version):

http://go.microsoft.com/fwlink/?LinkId=534231

After installation all you need to do is to configure it with a windows/Active Directory user and password. The Gateway then connected and running.

Do I Need More than One Instance of Gateway Installed?

No. One Gateway will provide service bus connection to all supported on-premises data stores on your machine. In other words; One Gateway to Rule Them All 😉

How to Set Scheduled Refresh in Power BI Website?

The whole purpose of Gateway is for being able to schedule a data refresh or be able to refresh the data set on Power BI website with Refresh Now button. You can Set a Scheduled Refresh in this way:

Click on ellipsis button on the right hand side of the data set in Power BI website

In the dataset setting you can see if Gateway is online or not (if there is no gateway installed, you can install it from here as well)

You can set credentials. Example below belongs to an Analysis Services Multi Dimensional data store. SSAS MD doesn’t support basic authentication, but don’t worry this bug has been reported, and will be fixed.

Then you can simply schedule refresh as you want

Not that you can also Refresh dataset anytime you want with Refresh Now option

When refresh happens you will see a refresh icon besides the data set

Limitations of Power BI Personal Gateway

This Gateway in its early life stages, suffer some limitations, which most of them will hopefully go away soon. Here are list of existing limitations;

1. Limitation On Data Sources; As mentioned above, all data sources on-premises and supported by Power BI are supported except ODBC, Active Directory, and Microsoft Exchange.
2. Gateway can be only installed on 64 bit machine.
3. Gateway cannot be installed on a machine that has Power BI Analysis Services Connector Installed. (SSAS Connector will be discussed in another section)
4. The machine that has gateway running, should not be turned off, shut down, or stand by.  (At the time of scheduled refresh happening at least)
5. Power BI Personal Gateway would be only available for Power BI Pro. (You can try trial version of it for free for 60 days I reckon)

Myths and Misconceptions About Power BI Personal Gateway

Gateway doesn’t pass user credential from Power BI website

Gateway creates the service bus for cloud based Power BI data set to connect to the on-premises data stores. However the connection won’t by dynamic. What I mean by Dynamic? I’ll explain it here:

Let’s consider that you have a SQL Server Analysis Services Multi-Dimensional set up. Let’s assume that you have set up cubes for HR data marts. As you probably now HR contains sensitive data that needs to be filtered for each user/department based on their access level. someone from one department shouldn’t be allowed to see someone else’s information from another department. Fortunately in SSAS multi-dimensional this is supported through defining Roles and row level security applied on them. Then users and groups on active directory will be mapped to roles, and anytime they login through a client (let’s say Excel PivotTable for example) their credential will pass through down to SSAS engine, and data will be returned only based on their authorized permissions defined in their role in SSAS.

This scenario works perfectly when client tool or user report is on-premises, such as Excel, or even SSRS reports on your organizational servers can leverage this method using Kerberos installed and configured. However when client tool is on cloud then things should be set up differently. Power BI report is a cloud based tool which connects to on-premises data store (In this example SSAS multi-dimensional) through Personal Gateway. So you might end up with this conclusion that the user credential from the person who logged in to Power BI website will pass through the gateway down to SSAS on-premises and he/she would access only to their allowed data portion defined in SSAS Roles. This is a misconception however.

Unfortunately Power BI Personal Gateway doesn’t pass user credential from cloud to on-premises. Personal Gateway actually works with a SAVED credential (the one that you defined at the time of configuring Personal Gateway), and users will have access to any data that the saved credential does. So as a result data level Role-based security with personal gateway is not possible (I mean for on-premises data stores, and only with Personal Gateway). For SSAS Tabular things are not similar however, Fortunately SSAS Tabular supported through direct connection with Power BI Analysis Services Connector. SSAS Connector passes credentials through, and credential works with Active Directory and Azure Active Directory (I’ll explain that in separate section).

Same principal applies on any on-premises data stores that Power BI connects to it through personal gateway. In Nutshell Power BI Personal Gateway doesn’t pass credentials to data stores.

Personal Gateway is not a Live Connection

The other misconception that you might have is that the connection to on-premises with Personal Gateway is Live. The actual fact is that connection is off-line, and is not live. the data set needs to be refreshed anytime for the data to be updated.

You Can Read Data From On-Premises Data Stores Not Only Databases But Also Files

Personal Gateway is not just for databases. You can connect to a CSV file stored on-premises shared folder on your organization server or even your laptop. As long as the saved credential in personal gateway has access to it, Power BI can read it.

Personal Gateway and Analysis Services Connector Are Different!

I’ve heard this question a lot! Is Personal Gateway same as AS connector? The answer is No. You probably concluded that so far after reading this section. But they are two separate applications, here is their purpose:

• Power BI Personal Gateway is for connecting on-premises data stores (except those that mentioned above) in an off-line connection to Power BI
• Power BI Analysis Services Connector is for connection only and only SQL Server Analysis Services Tabular on-premises LIVE and Directly to Power BI. (stay tuned for a whole section about Power BI AS Connector)

I would like to mention the Power BI site’s article about Personal Gateway as well, where you can study more about it;

https://support.powerbi.com/knowledgebase/articles/649846-power-bi-personal-gateway

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.