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.

Practice

1. What is the main purpose of a data gateway in Power BI?

easy

A. To store data permanently in the cloud

B. To securely connect Power BI service to local data sources

C. To create visual reports inside Power BI Desktop

D. To design dashboards without internet connection

Solution

1. Step 1: Understand the role of data gateway

   A data gateway acts as a bridge between Power BI service and local data sources, allowing secure data access.

2. Step 2: Compare options with gateway purpose

   Only To securely connect Power BI service to local data sources describes this secure connection role; others describe unrelated tasks.

3. Final Answer:

   To securely connect Power BI service to local data sources -> Option B

4. Quick Check:

   Gateway = Secure connection to local data [OK]

Hint: Gateway connects local data to cloud securely [OK]

Common Mistakes:

• Confusing gateway with report creation
• Thinking gateway stores data in cloud
• Assuming gateway works offline without connection

2. Which of the following is the correct first step to set up a Power BI data gateway?

easy

A. Install the data gateway software on a local machine

B. Create a new Power BI report

C. Upload data directly to Power BI service

D. Configure data source credentials in Power BI Desktop

Solution

1. Step 1: Identify setup starting point

   Setting up a data gateway begins by installing the gateway software on a local machine that can access the data source.

2. Step 2: Eliminate unrelated steps

   Creating reports or uploading data are separate tasks; configuring credentials happens after installation.

3. Final Answer:

   Install the data gateway software on a local machine -> Option A

4. Quick Check:

   First step = Install gateway software [OK]

Hint: Install gateway software before configuring anything else [OK]

Common Mistakes:

• Starting with report creation instead of gateway install
• Skipping software installation step
• Confusing data upload with gateway setup

3. After installing and registering a data gateway, what happens when you schedule a data refresh in Power BI service?

medium

A. The gateway fetches data from local sources and updates the dataset automatically

B. Power BI service downloads data to your local machine

C. The data refresh only works if you manually run it on Power BI Desktop

D. The gateway uploads all data to the cloud permanently

Solution

1. Step 1: Understand scheduled refresh with gateway

   When scheduled, the gateway securely connects to local data sources and refreshes the dataset in Power BI service automatically.

2. Step 2: Evaluate other options

   Options B, C, and D describe incorrect behaviors: data is not downloaded locally, refresh is automatic, and data is not permanently uploaded.

3. Final Answer:

   The gateway fetches data from local sources and updates the dataset automatically -> Option A

4. Quick Check:

   Scheduled refresh = Gateway updates dataset automatically [OK]

Hint: Scheduled refresh uses gateway to update data automatically [OK]

Common Mistakes:

• Thinking refresh requires manual action
• Believing data moves permanently to cloud
• Assuming data downloads to local machine

4. You installed a data gateway but scheduled refresh fails with an error saying 'Gateway not found'. What is the most likely cause?

medium

A. The Power BI report has no visuals

B. The data source is already in the cloud

C. The gateway software is not running or not registered properly

D. The Power BI Desktop version is outdated

Solution

1. Step 1: Analyze error message context

   'Gateway not found' means Power BI service cannot detect the installed gateway, often due to it not running or registration issues.

2. Step 2: Check other options relevance

   Report visuals, cloud data sources, or desktop version do not cause gateway detection errors.

3. Final Answer:

   The gateway software is not running or not registered properly -> Option C

4. Quick Check:

   Gateway error = Software not running or registered [OK]

Hint: Check gateway service is running and registered [OK]

Common Mistakes:

• Ignoring gateway service status
• Blaming report visuals for gateway errors
• Assuming cloud data source needs gateway

5. You want to allow multiple users in your company to manage and use a single data gateway for scheduled refreshes. What is the best practice to achieve this?

hard

A. Have each user create their own gateway and connect separately

B. Install multiple gateways on each user's machine

C. Share the gateway software installation file with all users

D. Add users as gateway administrators in the Power BI service gateway settings

Solution

1. Step 1: Understand gateway user management

   Power BI allows adding multiple users as gateway administrators to share management and usage of a single gateway.

2. Step 2: Evaluate other options

   Installing multiple gateways or sharing installation files is inefficient and unnecessary; separate gateways complicate management.

3. Final Answer:

   Add users as gateway administrators in the Power BI service gateway settings -> Option D

4. Quick Check:

   Multiple users = Add as gateway admins [OK]

Hint: Use gateway admin settings to share access [OK]

Common Mistakes:

• Installing multiple gateways unnecessarily
• Sharing installation files instead of permissions
• Each user managing separate gateways