How to setup a gateway for Power BI

December 22 2020

Before setting up a gateway, let’s understand what a gateway is and how it works. When you create Power BI reports based on on-premises data and publish them online, to refresh your datasets, you will need a way to access your on-premises data sources. This can be achieved with a data gateway.

In most cases, to share the reports you created on Power BI Desktop, you need to publish them to the Power BI service in the cloud, also known as PowerBI.com. Once this happens the mechanics of refreshing your dataset change, which means the cloud, not your machine, needs to have access to your data sources.

A gateway is a piece of software that acts as a bridge between the cloud and your on-premises data sources. With a data gateway, you can not only access your on-premises data sources but also schedule refresh for the datasets published to the Power BI service.

The Power BI Gateway can be installed in two modes

1)     On-premises data gateway:

  • This gateway can be used by multiple users that have access to the server on which you install the gateway.
  • It can be used for both scheduling refresh and live queries in Power BI.
  • You can also use it for PowerApps, Logic Apps, and Microsoft Flow.
  • This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources

2)     On-premises data gateway (personal mode):

  • Only you can use this, and you can use it only for scheduling refresh in Power BI.
  • Allows one user to connect to sources, and can’t be shared with others.
  • An on-premises data gateway (personal mode) can be used only with Power BI.
  • This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share any data sources with others.
  • The Live Connection connectivity mode, PowerApps, Logic Apps, Microsoft Flow are not supported.

You can install up to one gateway in each mode on the same computer, and you can manage multiple gateways from the same interface on PowerBI.com.

How the gateway works

Let’s first look at what happens when you interact with an element that is connected to an on-premises data source.

1)     The cloud service creates a query and the encrypted credentials for the on-premises data source. The query and credentials are sent to the gateway queue for processing.

2)     The gateway cloud service analyzes the query and pushes the request to Azure Service Bus.

3)     Azure Service Bus sends the pending requests to the gateway.

4)     The gateway gets the query, decrypts the credentials, and connects to one or more data sources with those credentials.

5)     The gateway sends the query to the data source to be run.

6)     The results are sent from the data source back to the gateway and then to the cloud service. The service then uses the results.

In step 6, queries like Power BI refreshes and Azure Analysis Services refreshes can return large amounts of data. For such queries, data is temporarily stored on the gateway machine. This data storage continues until all data is received from the data source. The data is then sent back to the cloud service. This process is called spooling.
Download and install a standard gateway

1)     Download Standard Gateway

2)     In the gateway installer, keep the default installation path, accept the terms of use, and then select Install.

3)     Enter the email address for your Office 365 organization account, and then select Sign in.

The gateway is associated with your Office 365 organization account. You manage gateways from within the associated service.

You're now signed in to your account.

4)     Select Register a new gateway on this computer > Next.

5)     Enter a name for the gateway. The name must be unique across the tenant. Also, enter a recovery key. You'll need this key if you ever want to recover or move your gateway. Select Configure.

Note the Add to an existing gateway cluster checkbox. We'll use this checkbox in the next section of this article.

6)     Review the information in the final window. Because this example uses the same account for Power BI, Power Apps, and Power Automate, the gateway is available for all three services. Select Close.

Now that you've installed a gateway, you can add another gateway to create a cluster.

Add another gateway to create a cluster

A cluster lets gateway admins avoid having a single point of failure for on-premises data access. If the primary gateway is unavailable, data requests are routed to the second gateway that you add, and so on.

Because you can install only one standard gateway on a computer, you must install each additional gateway in the cluster on a different computer.

To create high-availability gateway clusters, you need the November 2017 update or a later update to the gateway software.

1)     Download the gateway to a different computer and install it.

2)     After you sign in to your Office 365 organization account, register the gateway. Select Add to an existing cluster. In the Available gateway clusters list, select the primary gateway, which is the first gateway you installed. Enter the recovery key for that gateway. Select Configure.

Download and install a personal mode gateway

1)     Download the personal mode gateway

2)     In the gateway installer, enter the default installation path, accept the terms of use, and then select Install.

3)     Enter the email address for your Office 365 organization account, and then select Sign in.

The gateway is associated with your Office 365 organization account. You manage gateways from within the associated service.

4)     You're now signed in to your account. Select Close.

 

 

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About Gandhali Joshi

Gandhali Joshi is a Data Analyst and consultant in Addend Analytics. She is a Data enthusiast, a newbie, into the Technical world, and has spent more than 4 months in the consulting department of Addend Analytics.  

Addend Analytics is a Microsoft Certified Partner which is a young and rapidly growing Data Analytics consultancy. It specializes in the application of Business Intelligence and Data Science tools to solve business challenges some of which includes extracting data from disparate data sources like Databases, SaaS applications, ERP, QuickBooks, IoT devices, spreadsheets, creating on-premises or Cloud Data Warehouses using ETL process, Analyzing Data using Microsoft Power BI, Creating Insightful Reports and embedding Power BI reports into Apps. Addend Analytics has been credited with the successful implementation of Power BI projects for 100+ clients across multiple sectors like Finance, Professional Services, Retail, Sales, Real estate, Inventory, and countries like the US, Europe, Australia, and India. You can explore their services at www.addendanalytics.com 

More about Gandhali Joshi