Graphed LogoGraphed
Login

What is On-Premise Gateway in Power BI?

Cody Schneider•

Using Power BI to create beautiful, interactive reports is one thing, but making sure they stay updated with fresh data is where things get interesting. If your data lives on a local server or a network drive inside your company’s four walls, Power BI, a cloud service, can't just reach in and grab it. This article breaks down the essential tool that makes this connection possible: the On-Premises Data Gateway.

What Exactly is a Power BI Gateway?

Think of the Power BI Gateway as a secure bridge or a translator between your local, on-premise data sources and the Power BI service in the cloud. It’s a small piece of software you install on a computer within your local network that securely handles communication, allowing you to refresh your cloud reports with data that isn’t publicly accessible on the internet.

Without a gateway, you’d have to manually republish your Power BI report every time you wanted to update the data. With a gateway, you can schedule automatic refreshes or even query your data in real-time, ensuring your reports are always up-to-date without any manual work.

Why Do You Need a Gateway? Cloud vs. Local Data

The core problem the gateway solves is a simple one of location and security. The Power BI Service lives in Microsoft's secure cloud (Azure). Your business data, however, might live in a variety of places, many of which are "on-premise."

On-premise data sources can include:

  • Databases like SQL Server, Oracle, or MySQL running on a server in your office.

  • Excel workbooks, CSV files, or folders of files sitting on a shared network drive.

  • SharePoint folders that are hosted on your company's internal network, not SharePoint Online.

  • Analysis Services (SSAS) models.

For Power BI to access this data for a refresh, it needs a secure tunnel from its cloud environment into your private network. Installing the on-premise gateway creates this secure and encrypted channel. It listens for requests from the Power BI Service, securely executes them against your local data source, and then sends the results back to the cloud to update your reports and dashboards.

The Two Gateway Modes: Standard vs. Personal

When you download the gateway, you'll be faced with a choice between two different modes. This is an important decision, and for nearly all business-use cases, one is clearly the right choice.

Standard Mode (The Team Player)

Standard Mode is the recommended choice for any business or enterprise scenario. It's designed to be a central, shared gateway for your entire team or organization. Think of it as a central bridge that multiple authorized users can use to connect their various Power BI reports to multiple on-premise data sources.

  • Who it's for: Teams, departments, and entire companies.

  • How it runs: As a service, which means it's always on and operates in the background, even if no one is logged into the computer it's installed on.

  • Use case: You have a marketing team that needs reports refreshed from a local SQL Server database, while the finance team needs access to Excel files on a network drive. A single Standard Gateway installed on a server can handle both requests securely. It's also required for enabling live-connection features like DirectQuery.

Personal Mode (The Solo Operator)

Personal Mode is designed for a single Power BI user. It's much simpler but also far more limited. When you install it, it runs as an application tied to your user account, not as a background service.

  • Who it's for: Individuals who are the only ones working with a specific dataset.

  • How it runs: As an application. This means it only works when you are logged into the computer where it's installed. If you log out, shut down, or the computer goes to sleep, your scheduled refreshes will fail.

  • Use case: A single analyst who wants to test out a concept by connecting to a local Excel file on their own machine. It cannot be shared with others and only supports importing data, not DirectQuery.

The bottom line: If you're working in a team or building reports that others will rely on, always choose Standard Mode. Install it on a reliable server that is always on, and save a lot of headaches in the long run.

How Does the Gateway Work? A Simple Explanation

You don't need to be a network engineer to understand the gateway's workflow. It’s a beautifully designed system focused on security and efficiency. Here’s a simplified look at what happens when you set up a scheduled refresh:

  1. Request Initiated: A user's dashboard needs an update, or a scheduled data refresh is triggered in the Power BI Service.

  2. Cloud Communication: The Power BI Service sends a query, along with the encrypted credentials for your data source, to the Gateway Cloud Service on Microsoft Azure.

  3. Secure Hand-off: The Gateway Cloud Service encrypts the request and places it in a queue to be picked up by your on-premise gateway. Your gateway regularly polls this location to see if there are any pending jobs.

  4. On-Premise Decryption: Your gateway retrieves the job specifics, decrypts the credentials on your local machine, and connects to the on-premise data source (e.g., your SQL Server).

  5. Data Retrieval: The query is executed. Your local database or file returns the requested data.

  6. Secure Return Trip: The data that is being sent back is encrypted by your gateway and sent back to the Gateway Cloud Service, which then forwards it to the Power BI Service to update the dataset.

The most important part of this process is that your on-premise gateway only establishes outbound connections to Azure. It doesn't require any inbound ports to be opened in your firewall, which makes it a very secure solution that most IT departments are comfortable with.

Step-by-Step: Installing the Standard Gateway

Getting the gateway up and running is more straightforward than it sounds. Here’s how to do it in Standard mode.

1. Check the Prerequisites

Before you begin, make sure the machine you’re installing on is a good fit. The best practice is to install it on a dedicated machine, ideally a server that is:

  • Always on and connected to the internet.

  • Not a Wi-Fi-connected laptop that an employee takes home at night.

  • Running a supported version of Windows (e.g., Windows Server 2012 R2 or later).

  • Has .NET 4.8 Framework installed.

Avoid installing it on a domain controller for security reasons.

2. Download and Run the Installer

You can download the gateway directly from the Power BI Service. Click the downloads icon (a downward arrow) in the top-right corner and select "Data Gateway."

Run the installer. Accept the terms and choose an installation path. You’ll then be asked to sign in with the same credentials you use for your Power BI account.

3. Register the Gateway

On the next screen, you’ll choose "Register a new gateway on this computer." Here, you’ll be prompted for two critical pieces of information:

  • Gateway Name: Give it a descriptive name that your team will recognize, like "Marketing Department Gateway."

  • Recovery Key: This is hyper-important. The recovery key is your password for restoring or migrating your gateway. Save this key somewhere secure, like a password manager. If you lose it, you cannot recover it, and you'll have to set up all your data source connections again.

After you configure this, the installation is complete.

4. Add Data Sources in Power BI

The final step happens back in the Power BI Service. You need to tell Power BI which data sources this gateway is allowed to connect to.

  1. Go to Settings > Manage connections and gateways.

  2. Select your new gateway.

  3. Click the New button at the top to add a new connection.

  4. Fill out the data source information: the server name, database name, and importantly, the authentication credentials (username and password).

Once saved, you can go into any dataset's settings, find the "Gateway connection" section, and map it to use the new gateway and data source connection you just created. From there, you can configure your scheduled refresh.

Best Practices and Common Pitfalls

To ensure your gateway runs smoothly, keep these key points in mind.

Best Practices

  • Use a Server, Not a Laptop: Install the gateway on a machine that has a stable network connection and is always on. A virtual machine works perfectly for this.

  • Keep it Updated: Microsoft releases updates for the gateway monthly. It's a good practice to keep it on the latest version to get performance enhancements and security fixes.

  • Monitor Performance: If a gateway is heavily used with many refreshes, it can become a bottleneck. The gateway software has performance monitoring settings you can enable to watch for issues. For large organizations, you can even set up gateway clusters for high availability and load balancing.

Common Pitfalls to Avoid

  • Losing the Recovery Key: It bears repeating: save your recovery key somewhere safe. Losing it creates so much unnecessary clean-up work.

  • Firewall & Network Issues: The gateway must be able to communicate with Azure. If refreshes are failing, the most common culprit is a network firewall blocking its connection. You may need to work with your IT team to whitelist the necessary ports and domains.

  • Credential Mismatches: Make sure the credentials you enter for the data source connection in Power BI have the appropriate permissions to access the database or file share. If a password changes, you must update it in Power BI's gateway settings.

Final Thoughts

The on-premise gateway is the single most important component for any organization using Power BI with local data sources. It is the secure, reliable workhorse that bridges the gap between your private network and the Power BI cloud, allowing you to automate refreshes and deliver timely insights without having to live in a fully cloud-based world.

While the gateway is essential for on-premise data, managing data analysis across multiple cloud platforms presents a different challenge. That’s where tools like our own come in. With Graphed, we make connecting to cloud sources like Google Analytics, Shopify, Facebook Ads, and dozens of others instant and painless. You can build real-time, cross-platform dashboards simply by describing what you want to see - no need to worry about gateways, pipelines, or APIs. It's all your cloud marketing and sales data, accessible in seconds.