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 in Power BI Desktop, you need to publish them to 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 Power BI service.
The Power BI Gateway can be installed in two modes
- On-premises data gateway –
- This gateway can be used by multiple users that have access to the server onto 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
- 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.
- 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.
- The gateway cloud service analyses the query and pushes the request to Azure Service Bus.
- Azure Service Bus sends the pending requests to the gateway.
- The gateway gets the query, decrypts the credentials, and connects to one or more data sources with those credentials.
- The gateway sends the query to the data source to be run.
- 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
- Download Standard Gateway
- In the gateway installer, keep the default installation path, accept the terms of use, and then select Install.
- 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.
- Select Register a new gateway on this computer > Next.
- 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.
- 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.
- Download the gateway to a different computer and install it.
- 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
- Download the personal mode gateway
- In the gateway installer, enter the default installation path, accept the terms of use, and then select Install.
- 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. Select Close.
Gandhali Joshi
Data Analyst & Marketing Consultant
Addend Analytics