Ravindra BagaleCourses & study guides मराठी Track your progress

25. Power BI Service

25.7 Gateways

By Last updated:

Start with a problem

Nashik orders are in a SQL Server inside the warehouse office. The Service in the cloud cannot reach it.

Explain simply

An on-premises data gateway is software on an always-on machine in the office network. It carries refresh requests from the Service to local sources.

Show the data

Source Needs gateway?
SharePoint Excel No
SQL Server in office Yes
Azure SQL No (usually)

About this data: This table uses columns: Source, Needs gateway?. This table uses columns: Item, Status. This table uses columns: Gateway type, Description. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: refresh_history_sample.csv (source: src/practice/).

Demonstrate

  1. Install the gateway (standard mode) on an always-on server.
  2. Sign in and register it with a name and recovery key.
  3. Service › Manage connections and gateways › add the SQL connection with the exact server name.
  4. Semantic model › Settings › Gateway connection › map the source › Apply.

Show the result

Service to gateway to on-premises source flow, sample layout with fictional data

Your result should look like this (fictional sample):

Item Status
Gateway Online
Data source mapping Mapped
Refresh Completed

Explain what happened

The gateway makes an outbound connection, so no inbound firewall port is opened.

Explore further

  • Personal mode is for one user only – avoid it for team reports.

Detailed reference for this lesson:

The Service runs in the cloud. To reach data inside your company network (on-premises SQL Server, files on a network share, etc.), you need an on-premises data gateway.

Gateway type Description
On-premises data gateway (standard mode) Installed on an always-on server; shared by many users and reports; admins add data source connections; supports scheduled refresh, DirectQuery and Live connections
On-premises data gateway (personal mode) For one user only; supports Import refresh only; runs on the user's machine (which must be on)
Virtual network (VNet) data gateway Managed by Microsoft for data sources in Azure virtual networks (requires capacity)

Cloud sources such as SharePoint Online, OneDrive for work, Azure SQL Database (with public access) usually do not need a gateway. Step-by-step gateway installation and connection mapping are in Module 4.8.

Ravindra Bagale's Tip

Friends, many students think cloud sources like SharePoint Online need a gateway and add one anyway, making the setup more complicated. Use a gateway only for sources inside your private network. The full setup is in Module 4.8. Remember this rule.

Fix common mistakes

Wrong result Reason Correction
Gateway offline PC switched off/restarted Use an always-on server
Mapping fails Server name differs (QCSERVER01 vs QCSERVER01.corp.local) Use identical names
Refresh stops when laptop closes Personal gateway on laptop Standard gateway on a server

Practise independently

Practice task

Which sample refresh failure is a gateway problem?

Hints
  • Reason column.
Solution

05-May 07:00 – gateway offline.

Think like a professional

Treat the gateway as production infrastructure: owner, recovery key, monitoring.

Deepen this topic (Why / What / How)

Fictional Maharashtra example. Numbers and workspace names below are practice data only.

Why: Cloud Service cannot see on-prem SQL without a bridge — the gateway.

What: On-premises data gateway (standard mode preferred) lets scheduled refresh reach private sources.

How — plan gateway use:

Vertical steps (one under another)

  1. Confirm source is on-prem or VNet-private.
  2. Prefer a standard gateway on a always-on machine, not personal mode for team refresh.
  3. Map data source credentials in gateway settings.
  4. Test refresh history after mapping.
  5. Deep patterns in Ch 39.

Tiny example: Fictional: SQL Server in Pune office, then standard gateway on always-on VM.

Ravindra Bagale's Tip

Friends, personal gateway is for you alone; team refresh needs standard mode.

Sample gateway flow
Sample layout — fictional data

Go deeper next

This Chapter 25 page is the overview. For the full Service deep lesson, open Ch 39 · Scheduled Refresh, Incremental and Gateways. Chapters 37–44 are the deep Service series; Chapter 44 is the publish–secure–refresh–share capstone.

Quiz (5 questions)

1. A gateway is:

2. Team scheduled refresh prefers:

3. After mapping credentials you should:

4. Gateway offline often shows as:

5. Deep chapter:

Practice tasks (12) — hints only, no full answers

  1. Task. Write the business question this 25 7 gateways page answers in one sentence.
    Hint: Name the user and the decision.
  2. Task. List three mistakes learners make with 25 7 gateways.
    Hint: Use Fix common mistakes as a seed.
  3. Task. Draw a 5-box flow on paper for 25 7 gateways (fictional MH Ops).
    Hint: Photo counts as artefact.
  4. Task. Explain 25 7 gateways to a friend in 60 seconds using Why/What/How.
    Hint: Record voice memo if you can.
  5. Task. Open the linked deep chapter and write three new facts not on this overview page.
    Hint: Cite section titles only.
  6. Task. Create a checklist of 7 ticks before you call 25 7 gateways “done”.
    Hint: Include a verify step.
  7. Task. Invent fictional refresh/share/role data that fits 25 7 gateways.
    Hint: Keep IST times.
  8. Task. Map who is Admin / Member / Contributor / Viewer for a fictional workspace using 25 7 gateways.
    Hint: Least privilege.
  9. Task. Write an interview STAR story that uses 25 7 gateways.
    Hint: 45–60 seconds.
  10. Task. Find one unused habit you will stop (e.g. My workspace for team content) related to 25 7 gateways.
    Hint: Be specific.
  11. Task. Link 25 7 gateways forward to the correct Ch 37–44 lesson and say why.
    Hint: One sentence why.
  12. Task. Teach 25 7 gateways back using Marathi or Hindi tip language once.
    Hint: Keep English terms for product UI.

Ravi Sir's Cards for this lesson

Gateways. The cloud cannot reach data inside your office network by itself.Revision cardWhen do you need a gateway? When the Service must refresh data that sits inside a private network, like an office SQL Server or files on a PC. Cloud sources like SharePoint Online do not need one.Interview card

मराठी cards