Power BI’s On-Premises Data Gateway acts as a bridge between on-premises data such as a SQL Server and Microsoft cloud services. A Power BI Gateway is installed on computer (preferably a VM) that has the capacity to handle brokering data between the database or data source and app.powerbi.com. But for a simple tool, things can get pretty complex so lets look at How to Correctly Setup and Install Power BI Enterprise Gateway.
Power BI Gateways Best Practices
- The gateway should be as geographically close to the source as possible.
Even in the world of lighting fast internet, distance still plays an issue. The further away the gateway is from the tenant, the higher the latency will be. With more milage to cover, millisecond or even seconds can be added onto transaction.
- Best practice is to not install the gateway on your database server.
Both the gateway and the database need to use and consume resources to do their job. For this reason, when a gateway is calling a database for data, you do not want them to compete over the same CPU and memory. It is best to not keep the gateway on the same location as the databases.
- Install “On-Premises Data Gateway” instead of the “On-Premises Data Gateway (Personal)”.
The main installation supports schedule refreshes, DirectQuery, and live connects to Analysis Services.
- Use Power BI Gateway Clusters
- Clusters are created to avoid single points of failure and to load balance traffic across gateways in a cluster.
- Distribute requests across all active gateways in this cluster so that queries are shared across different gateways. Queries are distributed to a single gateway in a round-robin fashion and not fragmented across a multiple gateway
- Although the selection of a gateway during load balancing is random, gateway admins can throttle the resources of each gateway member, making sure a gateway member or entire cluster is not overloaded.
- Keep all Gateways updated to newest version
Microsoft is constantly updating and patching the gateway. Make it a habit to open each gateway configuration every few weeks and upgrade to the newest version.
- User Separate Gateways for Live connections and Refresh Gateways
Live connections add variability to when the gateway will be under a lot of pressure to pull data whereas scheduled refreshes will occur at a set time. Create a separate gateway for the two so that the scheduled refreshes can have a more dependable constant availability of resources needed for refreshes.
- Use Services Account Connections or Single Sign On to Data Sources via Gateway
When setting up gateways, the option to install under your personal ID and account is available but avoid using your ID. Have an admin generate a service account which the correct access to the VM and other data sources so that only one ID needs to be maintained per gateway.
- Leverage Data Governance policy when adding data sources to Power BI Gateways
There is no technical enforcement of creating duplicate data sources in gateways or even prevent development gateways from pointing to production databases. For this reason, use documentation best practices to define naming standards and a process to request new data sources to ensure they are set up correctly by an admin.
Power BI Gateway Minimum Requirements
Not quite the basics that Microsoft recommends, these are the basic requirement you will want from your Gateway machine for decent performance to start.
- 8 Core CPU
- 8 GB Memory
- 64-bit version of Windows 2012 R2 (or later)
How to Correctly Setup and Install Power BI Enterprise Gateway