Getting SQL Server Data Into Power BI

A report looks quick in the designer, then refresh and reader traffic reach the same SQL Server. Power BI needs a deliberate data contract and a storage mode that fits the workload.

Choose Import or DirectQuery First

Import copies data into a Power BI semantic model during refresh. Most reader interactions then use the model rather than querying SQL Server. DirectQuery sends queries to the source as readers interact. That changes freshness, latency, and server load. Choose the mode from the report’s needs, not from a default button.

I ask how fresh the data must be and how many people will use the report at once. If scheduled refresh meets the need, Import keeps interactive traffic off the operational server. If near live results matter, DirectQuery needs a database designed for repeated report queries.

Neither mode excuses a poor source query. Import can stress SQL Server during refresh. DirectQuery can stress it throughout the day. The difference is when and how the work arrives.

SELECT name, state_desc, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

Put a View Between Power BI and Tables

A reporting view can present stable names, types, and joins. It gives the BI model a contract while underlying tables evolve. Keep the view focused on business grain and columns the report needs. A view that joins everything for every page becomes hard to tune.

I prefer a distinct fact view and small dimension views to a single giant view with repeated text fields. The report model can then express relationships at known grains. Document keys and filters. A report developer should know whether one row means one order, one line, or one daily summary.

A view does not materialize data by itself. SQL Server still runs the underlying query. Inspect its plan and indexes. When transformations become expensive at refresh time, precompute them in a staged reporting table instead of piling more logic into the view.

Keep Query Folding Visible

Power Query folding means transformations can be translated into a source query. In DirectQuery, folding is central to the mode. In Import, folding still helps refresh by pushing suitable filters and projections to SQL Server. A transformation that stops folding can move much more data than expected.

Use Power BI’s native query inspection tools to see what reaches SQL Server. At the database, Query Store and Extended Events can help you see the generated workload. Test the actual report page with realistic slicers. A desktop preview is not a load test.

I check filters that wrap indexed columns in functions. A report filter on YEAR(OrderDate) is harder for an index to use than a date range. Shape the view and model so common filters become simple predicates.

SELECT q.query_id, q.object_id, qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
  ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%FactSales%';

Design Power BI for the Server Load

For Import, schedule refresh away from other heavy jobs when possible. Refresh can read whole tables unless incremental refresh and folding are configured correctly. For DirectQuery, each visual and interaction can create source queries. A page with many visuals multiplies that work.

I ask for expected concurrency, not only expected row count. A query that is acceptable once can become expensive when many readers open the same page. Review CPU, reads, waits, and plan stability during a representative test. Use a reporting replica or separate database when the operational workload needs protection.

A dashboard does not become cheaper because its SQL is hidden behind a visual. SQL Server still pays for scans, joins, and sorting. The browser simply gives the bill a nicer color palette.

SELECT TOP (20) rs.avg_duration, rs.avg_logical_io_reads,
       qt.query_sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
  ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_logical_io_reads DESC;

Give the Model Clear Grain and Keys

Power BI relationships rely on keys and cardinality. Confirm that dimension keys are unique and fact foreign keys have a clear meaning. A many-to-many relationship introduced to make a model load can produce confusing totals. Fix the grain or bridge design at the source when possible.

Do not expose ambiguous date columns without naming their roles. Order Date, Ship Date, and Invoice Date support different questions. A date dimension or clear role-specific views make filters predictable. Write down whether measures count orders, lines, or customers.

I check a few totals in T-SQL before trusting the report. The goal is to compare the same filter and grain. A mismatch can come from model relationships, source filters, or refresh timing. A known SQL query gives the investigation an anchor.

Secure the Power BI Path and Refresh

Use a dedicated identity with read access only to the needed views. Avoid granting report users broad table access through a shared connection. Plan gateway configuration for on-premises sources and test what happens when credentials expire. A report can show old imported data after refresh fails, so refresh status matters.

Row-level security can live in the model, the source, or both, depending on the design. Test it with representative identities. Do not assume a filter in a report page protects data outside that page. Security belongs in the access path, not in a visual default.

I keep refresh and query failures visible to the report owner and DBA. The owner knows what changed in the model. The DBA sees what SQL Server received. Both views are needed when a new page suddenly adds load.

Publish a Small Contract

Document view names, grain, keys, date meaning, allowed refresh schedule, and expected source load. Keep it close to the report project. If a view changes, test the semantic model and its measures before release. A renamed column can break refresh without touching the report layout.

Check real usage after publication. Query Store can show which statements consume resources. Review refresh history, report interaction patterns, and source waits. Adjust indexes or preaggregation where the evidence points, not where the chart looks most colorful.

The useful goal is a report that stays accurate and responsive while SQL Server continues its other work. Import and DirectQuery are tools for different operating patterns. Pick one deliberately and verify its cost on the server.

What happens to the operational server when every report reader opens the busiest page at once?

Related reading on this blog: What Is a Star Schema? and Create Efficient Query Plans Using Query Store: Analyzing SQL Server Query Plans: Part 3.

A BI connection is not just a data pipe, it is a workload contract with SQL Server.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.