Import vs. DirectQuery, Gateways and Scheduled Refresh in Power BI

So far, Summit Supply has lived in an Excel file. At a real company, data sits in an order management system, an ERP or a database such as SQL Server, PostgreSQL or MySQL, and reports need to refresh themselves every morning. In this lesson you'll connect Power BI to those sources and keep the data current without lifting a finger, which is the difference between a report and an information system.

Connecting to a database

1. Home › Get data and choose your database's connector (for example, SQL Server database).
2. Enter the server and, optionally, the database.
3. Choose the Data Connectivity mode: Import or DirectQuery (more on this below).
4. Sign in with the credentials IT gives you. Ask for a read-only user: a report never needs to change data.
5. In the Navigator, check the tables or views you need and click Transform data to clean them in Power Query, as in Lesson 2.

Tables, views or a SQL statement?

You can write your own query under Advanced options › SQL statement:

SELECT s.OrderID, s.OrderDate, s.CustomerID, s.ProductID, s.RepID,
       s.Quantity, s.UnitPrice
FROM dbo.Sales AS s
WHERE s.OrderDate >= '2025-01-01';

That works, but it has a cost: steps you add afterward in Power Query usually stop "folding" (you'll see what that means in a moment). The recommended practice is to ask the data team for a view with that query and connect to it as if it were a table. If SQL is new to you, Introduction to SQL: SELECT, WHERE and ORDER BY covers the essentials.

Import or DirectQuery

Rule of thumb: use Import unless you have a clear reason for DirectQuery, such as data that must be current to the minute (an operations dashboard) or tables too large to fit in the model. There are also composite models that mix both: the giant fact table in DirectQuery and the dimensions imported. And organizations that use Microsoft Fabric have a third mode, Direct Lake, which reads files straight from the data lake.

Query folding

When you apply Power Query steps (filtering rows, removing columns, grouping) to a database, Power Query tries to translate them into SQL so that the database runs them and only the result travels over the network. That's folding. If a step can't be translated, that step and every step after it run inside Power BI after the data downloads: much slower.

• To check, right-click a step: if View Native Query is available, the step folds.
• Put the steps that reduce data first (filter dates, remove columns) and save the ones that can break folding for last (some complex custom columns, merging with an Excel file).

The on-premises data gateway

When you publish (Lesson 10), the report lives in the Power BI service in the cloud. If your data sits on a server in the office or in a file on someone's computer, the cloud can't reach it on its own. The on-premises data gateway is software you install on a machine inside the company network that acts as a secure bridge.

Scheduled refresh

In the Power BI service, open the published semantic model's settings:

1. Under Data source credentials, enter the user name and password (and choose the gateway if needed).
2. Under Scheduled refresh, turn it on and choose the time zone and times. A Pro license allows up to 8 scheduled refreshes a day; Premium and Fabric capacities allow more.
3. Turn on failure notifications by email and add an owner.

Schedule the refresh after the source system finishes loading, and show the last refresh date in the report so nobody makes decisions on stale data without knowing it. Here's what a well-designed morning looks like:

1. 5:00 a.m.The ERP finishes its nightly jobYesterday's sales are now in the database.
2. 6:30 a.m.Scheduled refreshPower BI queries the sales view through the gateway; with incremental refresh, only the last few days.
3. 6:45 a.m.Alert if anything failsThe owner gets the failure email and reacts before leadership logs in.
4. 8:00 a.m.Leadership opens the dashboardWith the data date visible on the front page.

Incremental refresh

If Sales has 50 million rows, reloading the whole table every day is slow and pointless: sales from three years ago don't change. Incremental refresh partitions the table by date and reloads only the recent period. Here's how to set it up:

1. In Power Query, create two Date/Time parameters named exactly RangeStart and RangeEnd.
2. Filter the Sales date column: greater than or equal to RangeStart and less than RangeEnd (the filter must fold to the database).
3. In Power BI Desktop, right-click the table › Incremental refresh: for example, archive 5 years and refresh only the last 10 days.
4. Publish. From then on, each refresh only processes the recent days.

The savings are huge. Suppose Summit's sales in SQL Server add up to 20 million rows since January 2019, spread evenly: that's about 7,000 rows a day. A full refresh processes all 20 million; an incremental refresh of the last 7 days, about 48,700:

20,000,000 rows between 1/1/2019 and 11/14/2026 (2,875 days) is about 6,957 a day.

Parameters for the server and database

Parameters also keep you from hard-coding the server name. Create two text parameters in Power Query, ServerName and DatabaseName, and use them in the source:

let
    Source = Sql.Database ( ServerName, DatabaseName ),
    Sales = Source{[Schema = "dbo", Item = "Sales"]}[Data],
    Filtered = Table.SelectRows (
        Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
    )
in
    Filtered

Now you can switch from the test database to production by changing one value; in the service, parameters are edited in the semantic model settings. Note: for incremental refresh, the OrderDate column must be Date/Time, like RangeStart and RangeEnd, and the filter must fold to the database.

Common database connection mistakes

Leadership dashboard
- Sales from a VIEW in SQL Server, Import mode.
- Incremental refresh: archive 7 years, refresh the last 7 days.
- Standard-mode gateway on an office server (installed by IT).
- Products and customers Excel file in SharePoint (no gateway for that source).
- Scheduled refresh at 7:00 a.m. Monday to Saturday, with failure emails
  to the analyst who owns it. A "Data as of: mm/dd hh:mm" card on the
  front page.

Warehouse dashboard
- A separate report in DirectQuery on today's shipments table,
  with few visuals and filtered to the current date to keep the load low.
- A read-only database user for both reports.

Summary

• Connect to databases with a read-only user, ideally to prepared views.
• Import is the default; DirectQuery is for near-real-time needs or enormous volumes.
• Order your Power Query steps so they fold to the database.
• On-premises sources need a gateway; OneDrive, SharePoint and many cloud sources don't.
• Schedule refresh after the source system loads, with alerts, and use incremental refresh for large tables.
• Parameterize the server and database to move from test to production without editing queries.

Next lesson: ABC (Pareto) Analysis and RFM Customer Segmentation in Power BI. You'll classify products with Pareto and segment customers with the RFM model, all in DAX.