The Power BI on-premises data gateway gives us the ability to get the data from the intranet server (on-premises server) to the cloud service. Basically, it is a bridge to give access to the on-premises data that is not in the cloud. All the data is securely transferred into the cloud there is no compromise on the security because of encryption.
There are 2 types of gateways each for a different use.
Standard Mode - Used for the entire organization
Personal Mode - Only used by the single person in the organization
Architecture
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 analyzes 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. Thanks to #Microsoft for this explanation!
Installing and Using on-premises gateway
To download the gateway, go to Microsoft website you can use this link or You can find the link in the download section once you login to the Power BI service. And choose the appropriate version of the on-premises gateway, my recommendation is to go with Standard Mode because of wide range functionalities.
Open the gateway .exe file and run installation, choose the location, accept the terms and Click on Install.
After completion of the installation, it will ask for the Power BI account credentials that is your #Office365 account and click on sign in to register the email address.
After you click on sign in it will ask for the on-premises server credentials to authenticate. If the credentials are correct it will register your account.
Once you register your account setup will ask whether you have an existing gateway that you want takeover or you want to create a new gateway. Will choose Register a new gateway and hit next
Note, to connect with the existing gateway you should have recovery key.
Give a name to your gateway and give recovery key (recovery key is nothing but password) and click on Configure
the gateway is online ready to be used and we can use the gateway in #PowerApps, Power Automate (Flows) and Power BI.
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 can see the list of gateways that are being used. Steve’s GatewayCheck which I have installed right now is up and ready to go.
Adding a Data source
To add Data source, select the gateway and 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 SQL Server database, I choose SQL Server. However, this can be File (#excel, text, csv) or any other data sources.
After choosing the data source type, you need to enter other configurations for that source. We have used the #SQL Server, so we need to specify the database. This #database should be from the machine that gateway is installed on it.
You need to enter username and password to access the data source as well. Username should always have a domain name leading it (domain\username).
If everything is correct, it will establish the connection with database.
Now I have developed a report using the same on-premises SQL AdventuresWork database and published the report into the Power BI Service.
When I try to refresh the data source it is giving me an error because there is no gateway connected to the data source that will get the data from on-premises and the schedule refresh also disabled for this data source
To connect the gateway to the data source. Go to datasets choose the appropriate dataset and under gateway connect click, on toggle and choose the gateways for the dataset and map the data source.
After mapping the gateway schedule refresh and refresh on demand will be enabled and
whenever the refresh takes place the data in the datasets will get updated.
This is it! We went through installation, configuration and connecting the gateway to the datasets. The key to use gateway is to add all required data sources under it, and then map it to the dataset.
What do you think about it? Please Comment and Share and Follow!!
Comments