Power BI SQL Server integration that refreshes on time
Power BI SQL Server integration is the most common way companies feed their reports, because so many business systems keep data in SQL Server. Power BI has a native connector that supports both import and DirectQuery. So the choice is less about whether it works and more about how. Gateway setup, views and query folding decide whether refresh runs in minutes or fails at night.
- Native connector
- Gateway set up properly
- Queries that fold
Last updated
How does Power BI SQL Server integration work?
In Power BI Desktop you enter a server name and, if you like, a database. Then you pick tables or views, or paste a SQL statement. Power Query shapes the result, and the model stores it.
Once the report moves to the Power BI service, the cloud needs a path back to the server. For a database inside your network, that path is the on-premises data gateway. It is a small Windows service that sits near the database and relays queries outward.
Azure SQL Database usually needs no gateway, since it already lives in the cloud. However, a database behind a private network still does. We confirm that point first, because it shapes the rest of the setup.
Choices that shape a SQL Server connection
Six decisions matter most, and each one is easy to get right early.
Import mode
Data lands in the model, so visuals respond quickly and the database only works during refresh.
DirectQuery mode
Each visual queries the server live, which keeps numbers current but leans on database speed.
Views over tables
A reporting view hides joins and odd column names, and it also shields reports from schema changes.
Gateway placement
The gateway belongs on an always-on machine close to the server, never on someone's laptop.
Sign-in method
Windows or database credentials both work, while a service account keeps refresh alive when staff change.
Incremental refresh
Large fact tables then reload only recent periods, so nightly refresh stays short.
Why does query folding matter so much?
Query folding means Power Query hands its steps to SQL Server as one SQL statement. The server then filters and joins before any rows travel. As a result, refresh moves far less data.
Some steps break folding. For example, a custom column with complex logic can force Power Query to pull the whole table first. After that, every later step runs on the gateway machine instead of the database.
We check folding on each large table. When a step cannot fold, we move that logic into a view on the server. This keeps Power Query thin, and it also makes incremental refresh work as designed.
Signs a Power BI SQL Server integration needs work
If two or three of these sound familiar, a review will likely pay off.
- Refresh fails whenever one person is on holiday
- The gateway shows offline several times a month
- DirectQuery pages take long to load
- Reports read production tables during busy hours
- Every report repeats the same SQL statement
- Nobody knows which account the refresh uses
How we deliver Power BI SQL Server integration
The work follows four stages, and your DBA stays in the loop throughout.
Assess the database
First we look at table sizes, indexes and busy hours, so reporting does not compete with daily transactions.
Install the gateway
Then we set up the gateway in standard mode with a service account and add a second node when uptime matters.
Build views and model
Next we agree reporting views with your DBA and shape a star schema that folds cleanly.
Tune and document
Finally we time each refresh, fix the slow steps and leave notes on accounts, schedules and recovery.
Questions on SQL Server sources for Power BI
Does Power BI with SQL Server always need a gateway?
No. A gateway is for databases that the Power BI service cannot reach over the public cloud, which covers most on-premises servers. Azure SQL Database and Azure SQL Managed Instance with a public endpoint connect directly. When a cloud database sits behind a private network, a gateway or a virtual network data gateway is still the usual answer.
Is DirectQuery better than import for SQL Server?
Not by default. Import is faster for readers and gives DAX its full range, so we start there. DirectQuery suits cases where data must be current to the minute or the table is too large to copy. However, it puts every click on the database. A mixed model often gives the best of both.
Can Power BI read stored procedures from SQL Server?
Yes, in import mode you can call a stored procedure through a native SQL statement. Still, a procedure rarely folds, and it cannot serve DirectQuery. So we prefer views or table functions for reporting. They behave like tables in Power Query, which keeps filters and incremental refresh working the way Microsoft describes.
Will reporting slow the production database?
It can, if nobody plans for it. Import refresh at night keeps the load away from working hours. For heavier use, a read-only replica or a reporting copy takes the queries instead. We look at that during the assessment and suggest the lightest option that protects your transaction system.
Who can set up a SQL Server integration for our Power BI tenant?
Our consultants do this work remotely. Your IT team grants access to a machine for the gateway and a read-only database login. Everything else happens inside your own tenant. Call (480) 485-7373 or send a note, and we reply within 2 hours.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.