Power BI is a data analysis tool that connects to many data sources. If the data source for Power BI is located in an on-premises location, then the connection from cloud-based Power BI service, and on-premises located data source should be created with an application called Gateway. In this post, you will learn what the Gateway is, what are types of the gateway, their differences, installing the gateway, and scheduling a data set with that gateway.
What is Gateway?
Gateway creates the connection between Power BI cloud-based data analysis technology and the data source located on-premises. Gateway is an application that can be installed on any servers in the local domain. Gateway is responsible for creating the connection and passing data through.
Diagram below shows the role of gateway in connection;
Do you need gateway always?
You don’t need a gateway in all scenarios. Only if the data source is located on-premises, you need a gateway. For online or cloud-based data sources, no gateway is required. For example; if you are getting data from CRM Online, you don’t need a gateway. However, if you are getting data from SQL Server database located on your local domain server, then you need a gateway. For Azure SQL DB you don’t need a gateway. However, a SQL Server database located on Azure Virtual Machine is considered as on-premises and needs gateway.
Types of Gateway
Gateway comes in two different modes: Personal Mode, and On-premises. The difference between these two is not the paid or licensing plan. Both gateways are free to use. The difference is the way that you want to use the gateway. The personal mode is mainly used for one-person use, not for the team. On-premises gateway, on the other hand, is a choice when you want to work in a collaborative environment. Let’s look at their differences in details.
When you install gateway in personal mode, you can use it yourself only. You can connect it to local data sources such as SQL Server, Excel, and other data sources. However, the gateway installed as personal mode only supports one type of connection: Import data or schedule refresh (We will talk about these types of connections in very next few posts). This gateway is only used for Power BI; you cannot use it for other applications.
Because this gateway is personal, you cannot use it in a team development scenario. Multiple developers cannot leverage this gateway. You can create reports and connect it to this gateway and share it with multiple users. However, only one developer can use the gateway. That is why it is named personal mode.
Installing Personal model and configuring it is easier than the on-premises gateway. When you install gateway in personal mode, you don’t have the configuration option to set data sources for it. There is no place to configure it after installation. This mode of gateway meant to be used for business analysts with least amount of hassle to get their report published and get refreshed.
This type of gateway is usually for one business analysts you want to publish Power BI reports and schedule it to refresh and share it for users to use easily. Not many configurations options, easy to setup and single developer features of it, make it a good option for such scenarios.
On-premises Recommended Mode
On-premises has a recommended mode to install. This mode of installation supports multi-developer environment. Multiple developers can use the gateway installed. This type of gateway is built for team development; you can have a gateway administrator. For adding data sources and controlling it, there is a central configuration section for gateways.
On-premises recommended gateway supports not only Power BI, but also PowerApps, Azure Logic Apps, and Microsoft Flow, which are other Microsoft cloud-based technologies.
On-premises recommended gateway also supports all types of connections from Power BI. Not only the import data or scheduled refresh is supported, but also DirectQuery and Live Connection are supported with this gateway.
This type of gateway is for enterprise usage of Power BI, or where Power BI needs to be used alongside with other applications such as PowerApps. Multiple developers can work with the same gateway if the gateway administrator authorizes them to use it. More centralized control and monitoring exist for this type of gateway.
here are the details of architecture of the gateway;
- Gateway installed on a machine in the on-premises domain. During this installation credentials stored in local and Power BI service.
- Credentials entered for the data source in Power BI are enrypted then stored in the cloud. only the gateway can decrypt the credentials.
- Power BI service kick off a dataset refresh, this happens through a service named Scheduler service in Power BI.
- Data Movement Service analyses the query and pushes to appropriate service bus instance.
- There is a queue of requests in service bus. Gateway pulls bus for pending requests.
- Gateway gets the query, execute it on the data source.
- After getting the result, gateway push that back to Power BI.
What happens in step 5 is very important. Gateway is pulling the bus to check if there are any pending requests. The bus cannot trigger gateway. The reason for this architecture is security. If the bus can trigger the gateway, the inbound security ports need to be open, which is not a good practice for security. So, we can say that gateway connection is very secure because it is only using outbound ports.
Important things to consider before installing the gateway
The gateway can be installed on any machines in the on-premises domain. However, it is not recommended to be installed on the Domain Controller itself. Here are requirements for gateway installation:
.NET 4.5 Framework
64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
8 Core CPU
8 GB Memory
64-bit version of Windows 2012 R2 (or later)
How many gateways is required?
One gateway should be enough for many situations. However, there are sometimes that you would get more benefit with having more gateways. As an example, if you have a gateway that used for scheduled data refresh, and the same gateway is used for a Live Connection, then you get slow performance for the live connection if there is a scheduled data refresh in process at that time. So, in this scenario, you might consider having one gateway for your Live Connection, and another one for a scheduled refresh.
The gateway can be installed only on 64bit Windows operating system.
If you install gateway in personal mode, and then you decide to install on-premises recommended gateway on that machine, you need to clean the registry. This process is not an easy process to go through. My recommendation is to choose the version of gateway that you need on that machine carefully. If this is a server, then I highly recommend installing on-premises recommended gateway on it rather than personal.
Gateway machine should be always up and running to cater for data refresh queries.
Do not install the gateway on a machine which is connected through a wireless network. Gateway will perform more slowly in a wireless network.
Ports that need to be open for gateway are all outbound ports: TCP 443 (default), 5671, 5672, 9350 thru 9354. The gateway does not require inbound ports.
You can download Gateway from this link:
Or you can find the link when you logged in to Power BI service, under download; Data Gateway;
There is only one gateway to download. At the time of installation, you choose the type of gateway.
After running the installation file, you will see the option to choose the gateway type
For this example, we are going to install on-premises recommended gateway option, because it supports Live connection and DirectQuery connection which we will talk about it in other posts.
After choosing the gateway type, the installer downloads remaining files required for install, and then you can continue the installation. You need to choose the folder to install the gateway. For this example, we’ll keep the folder as is with no change. The installation process is simple. After installation, you need to register your gateway.
To register your gateway, you need to use your Power BI email account, and then sign in.
You can then Register a new gateway or migrate or restore an existing gateway.
Select Register a new gateway, and continue. You will need to enter two important information;
- Gateway name: a name that can remind you where this gateway is installed. For example, Reza-Vaio-Gateway or something like that.
- Recovery Key: this is a very important key and is required for recovering the gateway later. If you want to uninstall it and install again, or if you want to move gateway from one machine to another without the hassle of changing all connections, then keep the gateway name and recovery key in a safe place.
- You can also add the gateway to an existing gateway cluster. This option is added recently for having high availability through gateways. For this example, leave that unchecked.
After successful registrations of your gateway, you should see a message that says the gateway is online and ready to go.
Now you can see the gateway in Power BI service under your account as well. In Power BI Service, click on Setting Icon, and then click on Manage Gateways.
You should see all gateways set up under your account. In the screenshot below, you can see that I have four gateways under my account, and only one of them (Reza-Vaio-Gateway) which I have installed right now is up and ready to go.
What is different in Personal Mode
If you install gateway in personal mode, you don’t need to register gateway with a name and recovery key. And you cannot see your gateway in Manage Gateway section in the Power BI Service. As mentioned, this is to make it easier to set up and use.
Adding Data Sources
The gateway itself is just for creating the connection from cloud to the local domain. For your datasets to refresh through this gateway, you need to add Data Sources. Data sources are connections to every on-premises database, file, folder, etc. that have been used in Power BI as a connection.
To add data sources to the gateway, first, you need to check the Power BI file and see what data sources have been used. One easy way of finding that out is to open the *.pbix file in the Power BI Desktop.
After opening the file, then you click on Edit Queries -> Data Source Settings
In the Data Source Settings, you will see all data sources used in the current file. Click on every data source, click on Change Source, and copy the path for the file.
Now you can go to Power BI Service, Manage Gateway section, select the gateway we installed previously, and then click on Add Data Source.
In Add Data Source tab, you need to set some options. Name of the data source is only important for remembering it later easily. The first important option is; Data Source Type. In this example, because my source is an excel file, I choose File. However, this can be SQL Server database or any other data sources.
After choosing the data source type, you need to enter other configurations for that source. We have used the file, so we need to specify the full path of the file. This path should be the path of the file from the machine that gateway is installed on it. If the file is in a shared folder path, then that path should be accessible from the machine that gateway is installed on it. And this should also be the same path that has been used in the data source configuration of Power BI Desktop.
You need to enter username and password to access the data source as well. In this case, because we have used a file, then the username and password should be local username and password that have access to that data source from the machine that gateway is installed on it. Username should always have a domain name leading it (domain\username) like the screenshot below.
If everything is set up correctly, you should see a message that mentions the Connection Successful.
Important note: If you have multiple sources, then you must do this process for every single one of them.
Set up Gateway Connection to The Dataset
After adding the required data sources, then you can create the connection through the gateway. You should select the data source that you want to configure the Power BI Service. You can go to the Setting in Power BI Service
Click on Datasets, find your dataset, and then click on Gateway Connection. If your gateway has all data sources needed for this dataset, then you will see it under Use a data gateway, and you can select it, then click on Apply.
You have now configured your dataset to refresh through this gateway. You can now test it with manually refreshing your dataset. To manually refresh your dataset, first find it in your workspace.
After finding your dataset, then you can click on refresh. If everything set up correctly, the last refresh time should update with no error occurred. Congratulations! You have set up the gateway for your dataset.
There are a few scenarios that you may face an issue when setting up the gateway. Here I explained them one by one;
- You cannot see the gateway listed when you go to your dataset setting. The reason is most probably because you did not add all data sources needed for that dataset. Go to Power BI Desktop and check have you added all data sources or not.
- Analysis Services Live Connection with gateway requires more configuration which we will talk about it separately in another section.
- Gateway has a logging system which can be helpful when an issue comes up. You can enable additional logging and access the gateway logs from on-premises data gateway installed the application.
- There are a few known issues with on-premises gateway, which you can read more about them here: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot
- There are a few known issues with personal gateway, which you can read more about them here: https://docs.microsoft.com/en-us/power-bi/personal-gateway
In this section, you’ve learned about Gateway. Gateway is a connection between Power BI cloud-based dataset and the data source on-premises. You learned that gateway is only required for on-premises connections. There are two modes to install gateway; personal and the recommended (on-premises). You learned that on-premises recommended gateway can serve more than one developer at a time, be used for Power BI, PowerApps, and few other applications, and it also supports multiple connection types.
We went through installation and configuration of the gateway and the connecting one Power BI dataset to it. The key to use gateway is to add all required data sources under it, and then map it to the dataset. In next few sections, we are going to talk about the different types of connections in Power BI and how gateway works with them.