Data gateway setup in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a Power BI administrator at a retail company.

📋 Request: Your manager wants you to enable scheduled refresh for reports that use on-premises data sources.

📊 Data: You have sales data stored in an on-premises SQL Server database and reports published to the Power BI Service.

🎯 Deliverable: Set up and configure a Power BI data gateway to allow scheduled data refresh for the published reports.

Sample Data

SalesID	Product	Quantity	SaleDate	Region
101	Chair	5	2024-05-01	North
102	Table	2	2024-05-02	East
103	Lamp	10	2024-05-03	South
104	Sofa	1	2024-05-04	West
105	Desk	3	2024-05-05	North
106	Chair	7	2024-05-06	East
107	Table	4	2024-05-07	South
108	Lamp	6	2024-05-08	West

1

Step 1: Download and install the Power BI On-premises Data Gateway on a server that can access the SQL Server database.

Go to https://powerbi.microsoft.com/en-us/gateway/ and download the latest gateway installer. Run the installer and follow the prompts to complete installation.

Expected Result

Gateway software installed and running on the server.

2

Step 2: Sign in to the Power BI Service with your organizational account to register the gateway.

Open the installed gateway application and sign in using your Power BI credentials.

Expected Result

Gateway registered and linked to your Power BI tenant.

3

Step 3: Configure the gateway to connect to the on-premises SQL Server database.

In the Power BI Service, go to Settings > Manage gateways. Add a new data source with type 'SQL Server'. Enter server name, database name, and authentication details.

Expected Result

Data source added to the gateway with successful connection test.

4

Step 4: Assign users or groups who can use the gateway to refresh datasets.

In Manage gateways, select the data source and add users or security groups under 'Users'.

Expected Result

Users authorized to use the gateway for data refresh.

5

Step 5: Configure scheduled refresh for the Power BI report dataset using the gateway.

In Power BI Service, open the dataset settings. Under 'Scheduled refresh', enable refresh and select the configured gateway as the data source.

Expected Result

Scheduled refresh enabled and linked to the gateway.

6

Step 6: Test the scheduled refresh manually to confirm it works.

In dataset settings, click 'Refresh now' and monitor refresh status.

Expected Result

Dataset refresh completes successfully without errors.

Final Result

Power BI Data Gateway Setup Dashboard

+-----------------------------+
| Gateway Status: Running     |
| Data Source: SQL Server DB  |
| Scheduled Refresh: Enabled  |
| Last Refresh: Success       |
+-----------------------------+

✓The data gateway is installed and registered correctly.

✓The on-premises SQL Server data source is connected through the gateway.

✓Users have been authorized to use the gateway for refreshing datasets.

✓Scheduled refresh is enabled and working without errors.

Bonus Challenge

Configure high availability by installing a second gateway in cluster mode to avoid refresh downtime.

Show Hint

Install another gateway on a different server and add it to the same gateway cluster in Power BI Service.