Learn how to setup and get the most out of the Microsoft Power BI Gateway. We explore the use cases and data security concerns that come with establishing a gateway to access on-premises data sources with the Power BI Service.
What is a Power BI Gateway?
A Power BI Gateway is used when you want to publish Power BI reports, apps, or reports to the Power BI Service and have them automatically refresh or direct query an on-premises dataset. Common on-premises data sources include locally hosted instances of SQL Server, Accounting Software, ERP Systems, UNC file storage, other local network files, or if data is protected behind a VPN. Power BI Gateways come in two versions, Enterprise or Personal and can be installed on a centralized Server or Personal Computer.
Does Everyone Need a Power BI Gateway?
It’s Important to note that a Gateways is not required when connecting to cloud services, such as files saved on SharePoint Online, Microsoft One Drive, or if you are connecting to other cloud applications such as a CRM, or ERP.
One Gateway for Multiple Power Platform Services
A Power BI Gateway enables organizations to access on-premises data with multiple Microsoft Services. You will be able to access these datasets with Microsoft Power Apps (Low-Code App Development), Power Automate (Robotic Process Automation or RPA), or Azure Analytics Services (A centralized platform for data modeling and relational databases)
Access has to be granted separately to each service and is not required. To blend on-premises data sources with cloud-based SQL deployments such as Azure SQL you would still need to use a solution such as Azure Data Factory or SQL Server Integration Services (SSIS)
Standard (Enterprise) vs. Personal Power BI Gateways
Microsoft offers two different types of Power BI gateways. A personal gateway can be installed on your laptop or desktop computer and will allow the Power BI Service to connect to files on your computer. The Power BI Enterprise gateway is intended to be installed on a full-time server or virtual machine and allow people within your organization to connect to network files saved on local network drives, and allows people to access local data sources such as on-premises SQL server. The Power BI service can be setup to refresh data from on-premises data sources or to direct query on-premises SQL server.
Installing the Power BI Enterprise Gateway
Step 1.) Start by downloading the Power BI Gateway here. This guide will walk through the Standard (Enterprise) Gateway, but install steps will be similar for the Personal Gateway.
Step 2.) The Microsoft account you use to log into the gateway during install becomes the default Power BI Gateway Admin login.
Step 3.) Log into www.powerbi.com and click on the ? symbol in the top right of the screen. The click on About Power BI to identify which Azure Region your data is stored in. It’s the line that says “Your Data is Stored in” and will say something like “US West”, “Central” Etc.
Step 4.) Click on Register New Gateway
Step 5.) Setup a gateway name, and a Recovery Key. Do be careful setting up the Recovery Key. If it gets lost, you may have to re-install a new instance of the gateway. Adding a new gateway to a gateway cluster is optional and covered in more detail later in this article.
Pay attention to the Azure Region. There is an option at the bottom to allow you to Change the Region. Best practice is to match this setting with the Region that matches your Power BI service. If you install the gateway on a region that’s different from the Power BI Service, you may not be able to find it when you go to add data sources later on.
Install Complete. Once the install and setup is complete it will show as Ready.
Assign a Service Account. If necessary you can update the service account that the gateway uses under Service Settings.
What are the Components used with a Power BI Gateway?
It’s important to understand the different components of Power BI when installing the Gateway. Once it’s setup, the end user will need to do additional setup within the Power BI Service to actually use it.
Standard (Enterprise) or Personal Gateway
Power BI Service
Power BI Reports, Apps, and Dashboards are published to the Power BI Service. It’s hosted on Microsoft’s Azure Cloud, and can be accessed by going to www.powerbi.com
Power BI Desktop
Power BI Desktop is a Windows application installed on a dashboard or report designer’s individual computer or laptop.
Adding a Gateway Data Source to the Power BI Service
Once the Gateway is installed and Ready, the next step is to add data sources to the Power BI Service using the Gateway.
1.) Go to Settings (Cog Icon in Top Right) then Manage Connections and Gateways
2.) Click on + New in the Top Left of the screen.
You will be presented with the New Data Source menu as shown below.
A few key considerations to keep in mind.
- If working with a File Data Source Type, the file path will be the path based on the machine that the Gateway is installed on.
- Authentication could be the Service Account or could be a Windows Account that a user can access. The Gateway will also have to have the ability to access it.
- A Windows account will need to include the domain name with the Windows username.
Privacy Levels are defined at the data source level and determine how data sources are isolated from each other when combining them in reports or queries. You can learn more here: Understand Power BI Desktop privacy levels – Power BI | Microsoft Learn
Assigning the Gateway Datasource to a Datasource
After setting up the data source as a gateway data source, you can assign it to an existing data source or use it to build reports off of.
If you’re adding the gateway to an existing data source, go to your workspace and click on Datasets + dataflows, and click on Schedule Refresh.
Next Go to Gateway Connection and map your previous datasets with the ones added with the gateway.
Finally, once the gateway connection is mapped to the data source. You can schedule the refresh up to 8 times per day with a Power BI Pro license.
To add multiple refreshes, you have to click “Add Another Time” and define the times that you want the refresh to begin. There are no settings to say refresh x number of times. Times must be added individually.
Power BI Gateway Data Governance and Security
A key concern with opening up on-premises data to the cloud and other users in your organization is data security. Microsoft gives companies a great deal of control over how data is shared and who can access it.
Which Files can a Gateway Access?
A Power BI Gateway can access all files that the assigned Service Account can access on the machine that the Power BI Gateway is installed on. Installing a Power BI Gateway and giving it access to a large number of network files does not mean that all of your Power BI end users will be able to access all network files that the gateway has access to.
Define a Power BI Gateway Administrator
During the Gateway installation process, you are prompted to login with a Microsoft Office account. This account becomes the default Power BI Gateway Administrator for the Power BI Service. The Power BI Gateway Administrator can then share admin access with subsequent users within your organization, but it is not required to do so.
Gateway Data Sources Can be Shared with Specific Users
Once a data source is added to the Power BI Service, the admin has the ability to share it with specific users to allow them to build new reports or enable scheduled refreshes with existing reports.
Power BI Gateway Clusters
A Power BI Gateway must be running for the Power BI Service to connect to on-premise data sources. This means that gateways can fail due to hardware or other system failures. If such a failure occurs, published reports, apps and dashboards will be unable to refresh. Failover becomes an increasing concern as your Power BI deployment grows.
To avoid service disruptions, multiple Power BI Gateways can be deployed on different physical or virtual machines and automatically re-route traffic if a gateway fails.
The second use case for Gateway Clusters is to allow for Load Balancing with large deployments. Queries and refreshes can be spread across multiple machines. Learn more here: Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn
Getting More out of Power BI With Chat GPT
If you’re new to Power BI or have been using the platform for years. The recent AI advancements in Natural Language Process bot Chat GPT is a game changer for getting the most out of Power BI. The bot has been trained on a massive general data set as well as a depth of information on computer programming, including Power BI DAX and M programming languages. Learn how to get the most out of this amazing new tool by reading our free guide: How to Use Chat GPT with Power BI (www.popautomation.com)