Power bi data gateway is used as a bridge between on premise database and cloud services to have up to date data.
POWER BI DATA GATEWAY
Power BI can connect many data sources. The On-premises data gateway acts as a bridge which provides quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services. Gateway is mainly used for data refresh.
It used to work as gate-keeper that listens for connection requests and grants them only when a users' requests meet certain criteria.
Types of gateways
There are two types of gateways:
1) Personal Gateway– When you install gateway in personal mode, you can use it yourself only. This type Can only be used 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 the data sources with others.
2) On-premises data gateway (Recommended mode) – It will allow multiple users to connect to multiple on-premises data sources. It Can be used with different services like Power BI, Azure Logic apps, PowerApps, and Azure Analysis Services, all with a single gateway installation. This gateway is well-suited to more complex scenarios with multiple people accessing multiple data sources.
Actual Execution Plan
To get to the Actual Plan:
- Click the ‘Include Actual Execution Plan’ icon on the toolbar
- Right click the query window and choose ‘Include Actual Execution Plan’ option
- Hit CTRL+M
1) In the on-premises domain machine, Gateway is installed. During this installation credentials stored in local and Power BI service.
2) In Power BI, Credentials entered for the data source are encrypted, then stored in the cloud. only the gateway can decrypt those.
3) A query is created by the cloud service with the encrypted credentials for the on-premises data source. It is then sent to a queue for the gateway to process.
4) The gateway cloud service analyzes the query and pushes the request to the Azure Service Bus. Power BI manages the Service Bus for you, so there are no additional costs or configuration steps required.
5) The on-premises data gateway polls the Azure Service Bus for pending requests.
6) The gateway gets the query, decrypts the credentials, and connects to the data sources with those credentials.
7) The gateway sends the query to the source for execution.
8) The results are sent from the data source, back to the gateway, and then onto the cloud service and your server.
Important things to consider before installing the gateway
- .NET 4.6 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)
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.
Using a gateway
There are four main steps:
- Install the gateway on a local computer, using the appropriate mode
- Add users to the gateway, so they can access on-premises data sources
- Connect to data sources, so they can be used in reports and dashboards
- Refresh on-premises data, so Power BI reports are up to date
Hopefully this provided you with a baseline on how to use Data gateway to have UpToDate data between Power BI cloud-based dataset and the data source on-premises.