Why and When to Use a Gateway in Power BI

A Gateway is required when you want to connect to data sources that are not in the cloud, such as an on-premises SQL Server database or a local file. If you want to use data from these sources in your Power BI reports and dashboards, you need to use a Gateway to securely transfer the data to the cloud.

Benefits of Using a Gateway in Power BI

• Quick and secure data transfer: The Gateway provides a quick and secure connection between your on-premises data, which is data that isn't in the cloud, and several Microsoft cloud services. These services include Power BI, Power Apps, Power Automate, Azure Analysis Services, and Azure Logic Apps. source and the cloud-based Power BI service.
• Automatic data refresh: You can configure the Gateway to automatically refresh data from your on-premises data source at regular intervals.
• Real-time data access: With the Gateway, you can access data in real-time from your on-premises data source, even if it is not stored in the cloud.

How to Set Up and Use a Gateway in Power BI

Step 1: Download and install the Gateway software

• Go to the Power BI service.
• Click on the download icon / Data Gateway.
• Click on the 'Download standard mode' or "Download personal mode" button to download the appropriate version (link).
• Run the installation wizard to install the Gateway software on the machine that will host the Gateway.

Step 2: Configure the Gateway

• Keep the default installation path, accept the terms of use, and then select Install:

• Enter the email address for your Office 365 organization account, and then select Sign in:

• Select Register a new gateway on this computer > Next:

• Enter a name for the gateway (unique across the tenant) and a recovery key (you'll need to recover or move your gateway) > Select Configure:

• Review the information in the final window > Select Close:

• After installing the Gateway software, you need to configure it by adding the data sources that you want to connect to. To do this, open the Gateway Configuration Manager, which can be accessed from the Start menu or by searching for "Gateway Configuration Manager" in the Windows search bar. From here, click on the 'Add Data Source' button and select the type of data source that you want to add (e.g. SQL Server database, SharePoint site, or file share). Follow the prompts to configure the connection to your data source, including specifying any necessary authentication details.

Step 3: Create a data source in Power BI

In the Power BI service, click on the 'Settings' icon and select 'Data sources'. From here, click on the 'Add data source' button and select the type of data source that you want to connect to. Specify the details of your data source, including the server name, database name, and authentication details.

Step 4: Connect to the data source in Power BI

Now that you have configured the Gateway and created a data source in Power BI, you can use the data in your reports and dashboards. To do this, open the Power BI Desktop application and click on the 'Get Data' button. From here, select the type of data source that you want to connect to and select the data source that you created in Step 3. Follow the prompts to connect to your data source and import the data into Power BI.

Step 5: Schedule data refresh

To ensure that your data stays up-to-date, you can configure the Gateway to automatically refresh data from your on-premises data source at regular

On-premises data gateway - Personal mode Vs Standard mode

Features	On-premises data gateway (personal gateway)	On-premises data gateway (recommended/ standard)
Cloud services supported	Power BI Only	Power BI, PowerApps, Azure Logic Apps, Power Automate, Azure Analysis Services, dataflows
Import data and set up scheduled refresh	Yes	Yes
Runs	As you for Windows authentication and as configured by you for other authentication types	As configured by users who have access to the gateway
Can install only as computer admin	No	Yes
Serves multiple users with access control per data source (Power BI Service)	No	Yes
Connection Type	Import	Import, Direct Query, Live Connection
Support for a live connection to Analysis Services	No	Yes
Usage	Analyst	BI admin, Developer

---

On-premises data gateway - MS