Secrets of Power BI Performance: Power BI Gateways

Microsoft’s Power BI is one of the world’s most popular and widely used tools for business intelligence and analytics. It’s no wonder why IT research and advisory firm Gartner has named Power BI a “leader” in the field of analytics and BI platforms for 13 years in a row. Yet despite the widespread adoption of Power BI, many organizations face challenges and stumbling blocks when trying to maximize the software’s performance and efficiency.

In a previous article, we discussed tips and tricks for Power BI dataflows, which create an abstraction on top of your enterprise data sources to make it easier for users to access them. Below, we’ll continue the series by going over Power BI gateways, another essential feature to get the most out of your Power BI performance.

What Are Power BI Gateways?

Power BI gateways are a feature in Power BI to connect to your on-premises data sources without having to migrate the data into the cloud. You can think of a Power BI gateway as a kind of “bridge” between legacy on-premises data and your Power BI software in the cloud. Gateways can also connect on-premises data with other Microsoft cloud services such as Power Apps, Power Automate, Azure Analysis Services, and Azure Logic Apps.

Why should businesses use Power BI gateways instead of migrating on-premises data into the cloud? There are several good reasons to keep data on-premises:

• You may be required to keep certain data on-premises due to certain laws, regulations, or standards.
• Your on-premises assets may be highly entangled and difficult to sort out, forcing you to keep data on-premises for at least the time being.
• The costs of storing data in the cloud (especially the costs of data egress) can be prohibitive for organizations that have already purchased an on-premises server.

 
No matter the reason, using Power BI gateways gives you a more complete picture into your enterprise data, helping unite your cloud and on-premises information under one roof.

Power BI gateways come in two different types: standard and personal mode.

• Standard gateways allow multiple users to connect to multiple on-premises data sources, and use them with all the Microsoft services listed above—both Power BI as well as Power Apps, Power Automate, etc.
• Gateways in personal mode only allow a single user to connect to multiple on-premises data sources, and can only be used with Power BI. This setting is preferable for situations where users do not need to collaborate or share data sources

Tips and Tricks for Power BI Gateways

Although Power BI gateways are fairly quick and easy to install, using them effectively for maximum Power BI performance can be a little more challenging. Below are a few questions you need to consider when using a Power BI gateway:

• Should you register the gateway with a Power BI user account or service account?By default, gateways are configured to use the NT SERVICE\PBIEgwService Windows service account.
• Does the organization need more than one gateway?Although one gateway per data source is usually sufficient, there are situations where you may wish to use more than one. For example, if you have a gateway with a live connection to a data source, you can experience performance downgrades when you use the same gateway for scheduled data refreshes.
• Do you need features such as alerts, monitoring, and data refresh failover capabilities?
• How often should the gateway receive maintenance and upgrades?
• Who is responsible for administering the gateway’s operations?

 
Once installed, Power BI gateways should be regularly managed and monitored in order to optimize their performance. Traditionally, Power BI users have manually monitored gateways with Windows Performance Monitor. In 2019, however, Microsoft released features for additional query logging, as well as a Gateway Performance PBI template file for visualizing the results.

Solving the issue of slow-performing Power BI queries may require you to look at 4 different gateway log files:

• The query execution file contains information about each query sent through a gateway to an on-premises data source.
• The query start file contains information about the start times of each query.
• The query execution aggregation file groups data by query type, query status, and data source.
• The system counter aggregation file contains performance data about the system’s CPU and memory resources.