Labs · Power BI

Lab: Connect Power BI to a Local SQL Server Express Database and Compare Import with DirectQuery

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we are the reporting analyst at a Reliance Digital-style electronics chain. The store billing system writes every order into a SQL Server database called ShopDB. The sales head asks two things: "Can my report be fast?" and "Can it show an order the minute it is billed?" These two wishes pull in opposite directions, and the answer is the choice between Import and DirectQuery:

• Import copies the rows into the Power BI file. Very fast, but it only knows what was there at the last Refresh.
• DirectQuery keeps no copy. Every click sends a new SQL query to the database. Always fresh, but slower and with some limits.

We build ShopDB on our own laptop with 24 sample orders (made up for practice, not real company data), connect it both ways and insert one new order.

Goal of this lab

By the end you will have:

• A local SQL Server Express database ShopDB with a table dbo.Orders (24 rows).
• Two Power BI files: Lab-04-import.pbix and Lab-04-directquery.pbix.
• Seen with your own eyes that a new order appears in DirectQuery at once, but in Import only after Refresh.

What you need (all free)

• Windows 10 or 11 with about 6 GB of free disk space.
• SQL Server Express (free edition of SQL Server) and SQL Server Management Studio (SSMS, the free tool to run SQL).
• Power BI Desktop.
• The script: Download shopdb_sqlserver.sql (creates ShopDB with 24 orders)
• 45 minutes (most of it is the install).

The data: before and after

Before. The table dbo.Orders in SQL Server after running the script. Same 24 orders as pbi_sales.csv, total 1,302,000.

After. We insert one Pune order of 50,000. The DirectQuery report shows Pune = 396,000 straight away; the Import report still shows 346,000 until we click Refresh.

The formula

Two lines of SQL do the work. The first one adds the new order in SQL Server:

INSERT INTO dbo.Orders (OrderID, OrderDate, City, Product, Units, Sales, Cost)
VALUES ('SO-1025', '2026-06-30', 'Pune', 'Laptop', 1, 50000, 42500);

The second one you never type: in DirectQuery mode, when you click Pune in the slicer, Power BI writes and sends a query to SQL Server that means:

SELECT SUM(Sales) FROM dbo.Orders WHERE City = 'Pune';

So the DirectQuery answer is always what the database holds right now: 346,000 + 50,000 = 396,000. The Import file answers from its own copy, made at the last load or refresh, so it still says 346,000.

Steps

Part A: build ShopDB

1. Go to the official SQL Server downloads page, https://www.microsoft.com/sql-server/sql-server-downloads, and under Express click Download now. Run the file and choose Basic. Accept the licence and wait.

   👁 What you should see: "Installation has completed successfully" with INSTANCE NAME: SQLEXPRESS.

2. On the same screen click Install SSMS (or search "Download SSMS" on learn.microsoft.com) and install SQL Server Management Studio.

3. Open SSMS. In Connect to Server, type the server name localhost\SQLEXPRESS, keep Windows Authentication, tick Trust server certificate and click Connect.

   👁 What you should see: the Object Explorer on the left with localhost\SQLEXPRESS and a Databases folder.

4. Click File → Open → File, pick shopdb_sqlserver.sql and press F5 (Execute).

   👁 What you should see: a result grid with Orders = 24 and TotalSales = 1302000.

Part B: the Import report

5. Open Power BI Desktop, click Blank report, then Home → Get data → SQL Server.
6. Type Server localhost\SQLEXPRESS and Database ShopDB. Under Data connectivity mode choose Import. Click OK.
7. If asked for credentials, choose Windows → Use my current credentials → Connect. If a box says it could not use an encrypted connection, click OK to continue (this is fine for a local training database only).
8. In the Navigator, tick Orders and click Load.

9. Click the Card icon in Visualizations and tick Sales. Then click an empty part of the canvas, click the Slicer icon and tick City.

   👁 What you should see: the card shows 1.30M and the slicer lists Mumbai, Nagpur, Nashik and Pune.

10. Do not click any city yet. Save as Lab-04-import.

Part C: the DirectQuery report

11. Click File → New (a second Power BI window opens). Repeat steps 5 to 9, but in step 6 choose DirectQuery.

    👁 What you should see: the same card (1.30M) and slicer, and at the bottom right of the window: Storage Mode: DirectQuery.

12. Do not click any city yet. Save as Lab-04-directquery.

Part D: insert one order and compare

13. Go back to SSMS, click New Query, paste the INSERT from "The formula" above with USE ShopDB; as the first line, and press F5.

    👁 What you should see: (1 row affected).

14. In the DirectQuery window, click Pune in the slicer.

    👁 What you should see: the card shows 396K. The new order is already there.

15. In the Import window, click Pune in the slicer.

    👁 What you should see: the card shows 346K. The Import file does not know about SO-1025.

16. In the Import window, click Home → Refresh and wait for it to finish.

    👁 What you should see: the card changes to 396K. Clear the slicer (click Pune again): 1.35M in both windows.

17. Save both files. Write one sentence in your notes: "Import = fast copy, fresh after Refresh. DirectQuery = live questions to the database every click."

Common mistakes

Self-check checklist

Try-at-home challenge

Delete the extra order in SSMS with DELETE FROM dbo.Orders WHERE OrderID = 'SO-1025';. Without clicking Refresh in either window, predict what each card shows for Pune when you click it again. Then check.

Samjla ka? Import = a fast copy you refresh; DirectQuery = a live question every click. Aata pudhe jaauya: load data from the web and from a Google Sheet, and refresh both.