Power BI Advanced Series · Data Gateway & Enterprise Integration · by Raushan Ranjan, MCT
This detailed, step-by-step demo provides a real-world workflow for Power BI Developers:
Creating a Power BI Report from SQL Server ➜ Publishing it ➜ Installing Data Gateway ➜ Scheduling Refresh
This end-to-end process is a core skill, enabling you to connect your on-premises SQL data to Power BI Service and automate data refreshes for your reports.
✅ What You'll Learn from This Demo
| Step | Goal |
|---|---|
| Step 1 | Connect Power BI Desktop to a local SQL Server and create a basic report. |
| Step 2 | Publish that report to Power BI Service (the cloud). |
| Step 3 | Install and configure the Power BI Data Gateway on your local machine. |
| Step 4 | Set up scheduled refresh in Power BI Service for automatic data updates via the Gateway. |
🛠️ Prerequisites
| Item | Description |
|---|---|
| Power BI Desktop | Installed from https://powerbi.microsoft.com/desktop. |
| Power BI Pro Account | Required to publish reports to the cloud and use scheduled refresh. |
| SQL Server | Installed locally or on your LAN. You'll need a sample database (e.g., AdventureWorks) or your own table with some data. |
| Admin access to install Gateway | On a PC that can connect to your SQL Server instance. This PC needs to be online for scheduled refreshes. |
| Internet connection | For Gateway installation and synchronization with Power BI Service. |
✅ STEP 1: Create Power BI Report from SQL Server
🧩 Objective:
Connect Power BI Desktop to your local SQL Server instance and build a basic report to visualize some data.
🔁 Instructions:
- Open Power BI Desktop.
- Click Home tab ➜ Get Data ➜ SQL Server.
- In the SQL Server database dialog:
- Enter your Server Name.
- Example:
localhostorDESKTOP-YOURPCNAME\SQLSERVEREXPRESS(if using a named instance).
- Example:
- (Optional) If you have a specific database name you want to connect to, enter it (e.g.,
AdventureWorksDW2019). - Choose Import mode (not DirectQuery) for this demo. Import mode brings the data into Power BI's model, which is what the Gateway refreshes.
- Enter your Server Name.
- Click OK.
- If prompted for credentials, select your authentication method (e.g., Windows or Database) and enter details.
- In the Navigator window, select the required tables (e.g.,
DimProduct,FactInternetSalesfrom AdventureWorks). - Click Load – the selected data will be imported into your Power BI Desktop model.
- Create a Basic Report:
- Drag and drop fields to create simple visuals. For example, create a Bar chart showing "Total Sales by Product Category."
- Add some Slicers, Filters, or a Title to make it look like a basic report.
- Save the file: Click File ➜ Save As and save it as
SalesReport.pbix.
✅ STEP 2: Publish Report to Power BI Service
🧩 Objective:
Push your .pbix report from Power BI Desktop to your workspace in the Power BI cloud service.
🔁 Instructions:
- In Power BI Desktop, click File ➜ Publish ➜ Publish to Power BI.
- Select your target workspace (e.g.,
My Workspaceor a specific workspace where you have Premium/Pro access). - If prompted, log in with your Power BI account.
- Wait for the publishing process to complete. A success message will appear with a link to open your report in Power BI Service.
✅ Now your report and its associated dataset are in Power BI Service. However, the dataset is currently "offline" because Power BI Service doesn't yet know how to connect to your local SQL Server. This is where the Gateway comes in.
✅ STEP 3: Install & Configure Power BI Data Gateway
🧩 Objective:
Install the Power BI Data Gateway on your local PC to create a secure bridge between your on-premises SQL Server and the Power BI cloud.
🔁 Instructions:
- 🖥️ On your PC (the one that can connect to your SQL Server):
- Download the Data Gateway installer from https://powerbi.microsoft.com/en-us/gateway/.
- Run the installer.
- Choose On-premises data gateway (recommended) and click
Next. - Select Standard mode (recommended for most scenarios) and click
Next. - Accept the terms and click
Install. - After installation, sign in with your Power BI Pro account (the same one you used to publish the report).
- On the next screen, select Register a new gateway on this computer.
- Assign a name to your gateway (e.g.,
MyGatewayorMyHomeSQLGateway). - Set a recovery key: This is crucial! Create a strong recovery key and store it in a safe place. You'll need it if you ever need to restore or move your gateway.
- Click
Configure. - Once configured, you'll see a success message. Click
Close.
✅ Your Gateway is now installed and registered with your Power BI Service.
✅ STEP 4: Set Up Scheduled Refresh in Power BI Service
🧩 Objective:
Configure your dataset in Power BI Service to automatically fetch fresh data from your local SQL Server via the newly installed Gateway.
🔁 Instructions:
- 🔗 Go to Power BI Service:
- Open your browser and go to https://app.powerbi.com.
- Open the workspace where you published the report (
My Workspaceor your custom workspace). - Under the "Datasets + dataflows" tab, find your
SalesReportdataset. - Click the three dots (
... More options) next to the dataset name, then select Settings.
- ⚙️ Configure Gateway Connection:
- In the Settings page, navigate to the Gateway connection section.
- You will likely see a message indicating that your dataset is offline or has no gateway configured.
- Under "Gateways and cloud connections," ensure Use a data gateway is enabled.
- Under "Map to gateways," select your registered gateway (e.g.,
MyGateway).
- 🔐 Configure SQL Credentials:
- Scroll down to the Data Source Credentials section.
- You'll see your SQL Server data source listed. Click Edit credentials.
- Choose the Authentication method that matches how you connect to your SQL Server (e.g.,
Basicfor SQL Server Authentication with username/password, orWindowsfor Windows Authentication). - Enter the appropriate SQL Server credentials (username and password for SQL Login, or your Windows domain credentials).
- Click Sign in or Privacy level (set to Organizational or Private as appropriate), then Sign in.
- ⏰ Schedule Refresh:
- Still in the dataset settings, go to the Scheduled refresh section.
- Toggle it to On.
- Set the Refresh frequency (e.g.,
DailyorWeekly). - Add specific times for the refresh (e.g.,
9:00 AM). - (Optional) You can set email notifications on refresh failure.
✅ DONE! Power BI Service will now automatically refresh data from your local SQL Server via the gateway at the scheduled times.
📌 Summary Workflow
SQL Server (On-Prem)
↓
Power BI Desktop (Import Data)
↓
Power BI Service (Publish .pbix)
↓
Power BI Gateway (Bridge from cloud to on-prem SQL)
↓
Scheduled Refresh (Daily auto-update)
🧪 Test the Setup
- Modify data: Make a small change to the data in one of your tables in your local SQL Server.
- Wait for refresh: Either wait for the next scheduled refresh time or manually trigger a refresh by going to your dataset in Power BI Service, clicking the three dots (
...), and choosing "Refresh now". - Verify: Go to the Power BI report online and confirm that the visuals have updated with the new data from your SQL Server.
🎁 Bonus Tip: Troubleshooting Gateway
| Issue | Common Fix |
|---|---|
| Dataset not showing Gateway option / Offline | Ensure the data source connection in Power BI Desktop (Get Data steps) exactly matches the schema and server name that the Gateway is configured for. Sometimes, a slight mismatch in server name can cause issues. |
| Authentication error during refresh | Double-check the SQL login credentials entered in Power BI Service (Step 4, Data Source Credentials). Ensure the SQL user has sufficient permissions to read the data. |
| Refresh failed / Gateway offline | Verify that the PC where the Gateway is installed is online, powered on, and has an active internet connection. Also, ensure the SQL Server instance is running and accessible from that PC. Check the Gateway application on the PC for any error messages. |
Quick Knowledge Check
Q1. You are migrating a Power BI dataset from a local SQL Server connection to an Azure SQL Database. What changes are required in Power BI after the database migration?
Show Answer
Update the data source connection string and credentials in Power BI Desktop, then republish the dataset. In Power Query, change the Server and Database values in the SQL Server connector to point to the Azure SQL endpoint. Update credentials (usually SQL authentication or Azure AD). Crucially, because Azure SQL is a cloud source, you can remove the gateway requirement — the dataset can now refresh directly from Power BI Service without a gateway, saving maintenance overhead and latency.
Q2. A report currently uses an on-premises SQL Server gateway with a 30-minute scheduled refresh. After migrating the source to Azure SQL, what is the maximum refresh frequency available without a gateway?
- A) Still 30 minutes — Azure SQL has the same refresh limit as on-premises sources
- B) Up to 48 refreshes per day (every 30 minutes) on Pro, or up to 48 per day on Premium per dataset
- C) Cloud sources support near-real-time DirectQuery — scheduled refresh is not needed
- D) 8 refreshes per day maximum for all cloud sources regardless of licence
Show Answer
B. Import mode datasets on Power BI Pro can be refreshed up to 8 times per day. With Power BI Premium, up to 48 times per day. Switching to Azure SQL (cloud) doesn't change the Import refresh schedule limit, but removes the gateway dependency. Option C is partially true — Azure SQL supports DirectQuery, eliminating the need for scheduled refresh entirely — but DirectQuery has performance trade-offs.
Q3. What are the two main reasons an organisation might choose to keep the on-premises gateway even after migrating to Azure SQL?
Show Answer
1. Other datasets still use on-premises sources — the gateway handles SQL Server, file shares, or SSAS connections not yet migrated. Removing it breaks those datasets. 2. VNet (Virtual Network) data gateway for private endpoints — Azure SQL deployed in a private VNet is not publicly accessible. A VNet data gateway (a cloud-hosted gateway) bridges Power BI to the private network without exposing the database publicly, maintaining security while eliminating the on-premises server.
5 Things to Remember
- Cloud sources eliminate the gateway requirement — Azure SQL, Azure Synapse, and Blob Storage are reachable directly from Power BI Service. Remove the gateway reference after migrating.
- Update Power Query connection strings after migration — change the Server/Database values and re-enter credentials. Republish the dataset.
- DirectQuery on Azure SQL removes refresh windows — data is always live but queries run at report load time. Trade-off: higher Azure SQL query load vs. staleness from Import mode.
- VNet gateway for private Azure SQL — if Azure SQL is in a private VNet, use the cloud-hosted VNet data gateway. No on-premises server needed; runs in your Azure subscription.
- Don't remove the gateway if other datasets still use it — audit all datasets connected through the gateway before decommissioning. Breaking one dataset breaks all reports built on it.
Quick Knowledge Check
Q1. You are migrating a Power BI dataset from a local SQL Server connection to an Azure SQL Database. What changes are required in Power BI after the database migration?
Show Answer
Update the data source connection string and credentials in Power BI Desktop, then republish the dataset. In Power Query, change the Server and Database values in the SQL Server connector to point to the Azure SQL endpoint. Update credentials (usually SQL authentication or Azure AD). Crucially, because Azure SQL is a cloud source, you can remove the gateway requirement — the dataset can now refresh directly from Power BI Service without a gateway, saving maintenance overhead and latency.
Q2. A report currently uses an on-premises SQL Server gateway with a 30-minute scheduled refresh. After migrating the source to Azure SQL, what is the maximum refresh frequency available without a gateway?
- A) Still 30 minutes — Azure SQL has the same refresh limit as on-premises sources
- B) Up to 48 refreshes per day (every 30 minutes) on Pro, or up to 48 per day on Premium per dataset
- C) Cloud sources support near-real-time DirectQuery — scheduled refresh is not needed
- D) 8 refreshes per day maximum for all cloud sources regardless of licence
Show Answer
B. Import mode datasets on Power BI Pro can be refreshed up to 8 times per day. With Power BI Premium, up to 48 times per day. Switching to Azure SQL (cloud) doesn't change the Import refresh schedule limit, but removes the gateway dependency. Option C is partially true — Azure SQL supports DirectQuery, eliminating the need for scheduled refresh entirely — but DirectQuery has performance trade-offs.
Q3. What are the two main reasons an organisation might choose to keep the on-premises gateway even after migrating to Azure SQL?
Show Answer
1. Other datasets still use on-premises sources — the gateway handles SQL Server, file shares, or SSAS connections not yet migrated. Removing it breaks those datasets. 2. VNet (Virtual Network) data gateway for private endpoints — Azure SQL deployed in a private VNet is not publicly accessible. A VNet data gateway (a cloud-hosted gateway) bridges Power BI to the private network without exposing the database publicly, maintaining security while eliminating the on-premises server.
- Cloud sources eliminate the gateway requirement — Azure SQL, Azure Synapse, and Blob Storage are reachable directly from Power BI Service. Remove the gateway reference after migrating.
- Update Power Query connection strings after migration — change the Server/Database values and re-enter credentials. Republish the dataset.
- DirectQuery on Azure SQL removes refresh windows — data is always live but queries run at report load time. Trade-off: higher Azure SQL query load vs. staleness from Import mode.
- VNet gateway for private Azure SQL — if Azure SQL is in a private VNet, use the cloud-hosted VNet data gateway. No on-premises server needed; runs in your Azure subscription.
- Don't remove the gateway if other datasets still use it — audit all datasets connected through the gateway before decommissioning. Breaking one dataset breaks all reports built on it.