Definitive Guide to Power BI Personal Gateway

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

0

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;

0

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:

1

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.

3

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

4

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)

5

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.

6

Then you can simply schedule refresh as you want

7

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

8

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

9

 

Limitations of Power BI Personal Gateway

file891271793979

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

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

15 Comments

  • Hi.
    Great post as usual!

    when I try to configure schedule refresh I get the following message:
    Cluster URI: https://wabi-west-europe-redirect.analysis.windows.net
    Details: [DataSource.Error] Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

    I work on Windows 7 64 with power bi desktop 32 k and oracle 32 k.

    Do you have any idea what sholud i do to fox the problem?

    Many thanks.

    Nir.

  • Excellent post! I would love to see Power BI with different options to manage on premises row level security – not limited to SSAS tabular only… For instance, I would like to have the possibility to connect to an access database or excel spreadsheet and according to the logged user, be able to only show relevant information to that user….
    Do you know if it’s something we could have in a near future?
    Thanks and congratulations to your initiative !
    Daniel

    • Hi Daniel,

      Thanks for your kind words and great feedback.
      This feature is something that I look forward to it as well.
      At the moment unfortunately it is possible through SSAS Tabular with live connection (as you know), and Azure SQL Database (because it supports row-level security).
      I would love to see that feature for all data sources on-premises, and I know Microsoft team are putting great effort on Power BI and the gateway improvements. However I’m not sure about any dates that these feature might be available

      Cheers,
      Reza

      • Great Reza! Thanks again for your commitment to share your knowledge with us…
        Excellent blog and I’m always looking forward anxiously to read your posts!
        Have a nice weekend!

  • Hi Reza,
    great post. Small comment:
    “If you get data from online data sources you don’t need the Personal Gateway” This is not 100% true. I recently had a client which had an online PostGreSQL database. So the database is online (you could connect with the browser) but you still had to use the Personal Gateway to refresh the data. I guess this is because PostGreSQL falls in the “on-premises” category despite that databse being online. Also, maybe because the server hosting Power BI in the cloud doesn’t have the necessary client tools installed to connect to PostGreSQL?

    • Hi Koen,

      You are absolutely right. By online data in that sentence I meant getting data from web URLs, and content packs mostly such as Mailchimp and all other content packs. So you are right that the sentence doesn’t mentioned that. Thanks for the correction. I appreciate that :)

      Cheers,
      Reza

      • Hi Reza,
        Thanks for your reply.

        We already have ASP.Net MVC multi tenant web application which is using different schema for their client specific data.

        New requirement for the application.
        1) User should be able to see the dashboard details in our application from Power BI web app

        Our Approach:
        1) Planning to have one Azure AD user account for one client and same account will be used for authenticating all the exisitng users with in the client
        2) Using PowerBI desktop applicaiton, client specific admin will create the dataset from on premise database(can create reports) and publish to Azure againest the client specific Azure AD account.Using the personal gateway we are refreshing the data at regular intervals.
        3) As per the existing application client specific Admin can create new table as and when required for client specific schema and add data to it
        4) When ever there is new table creation happens from the admin, admin will update the dataset to include the new table and re publish it again using power bi desktop applicaiton.
        Here we are asking the client admin to refresh the dataset by logging into power bi desktop application manually whenever there is new tables included.

        Questions:
        In order to avoid manual intervention of Admin pushing the new dataset to Azure, we thought of pushing the data using Rest API, but we are unable to refresh the data using personal gateway for dataset which are pushed via rest Api.
        Is there any way we can configure the personal gateway to refresh the data automatically without manual process.

        • Hi Sri,

          I’m still not sure if I get your situation correctly. You are saying that you push data set into Azure. If so, then why do you use Personal Gateway? gateway is only required when you connect to on-premises data source.

          Cheers,
          Reza

    • Hi John.
      Gateway should be usually On all times. for Power BI website to be able to access to on-premises data at the time of refresh. However you can stop the service if you want.

      Cheers,
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">