Connecting to Relational Databases (Power BI)

You know how Excel and CSV files feel like cosy little cottages? Relational databases are the sprawling city with layers, traffic rules, and sometimes a grumpy mayor named Locking. Welcome.

This lesson builds directly on what you already learned about Import vs DirectQuery vs Live Connection and the simpler Connecting to Excel and CSV Files. We're moving from local files to actual database servers — which means authentication, gateways, query folding, and a pinch of SQL sorcery.

Why this matters (and why it will haunt your dreams in the best way)

Relational databases are where most enterprise data lives. If you can’t get to them reliably, your shiny dashboard is just wishful thinking.
How you connect affects performance, refreshability, governance, and security. Remember the Import/DirectQuery/Live triad? These choices start here.

Quick roadmap: what you’ll learn

Common relational connectors and when to use them
Step-by-step: connect to a SQL Server from Power BI Desktop
Authentication, on-prem gateway, and service considerations
Query folding: the secret sauce for performance
Best practices, gotchas, and a cheat sheet

Common relational connectors (who’s at the party)

Connector	Native in Power BI?	Supports DirectQuery?	Quick notes
`SQL Server`	Yes	Yes	Best integration, supports Windows/Database/Azure AD; native query folding is excellent.
`Azure SQL Database` / `Azure Synapse`	Yes	Yes	Cloud-native; often easiest for DirectQuery and service integration.
`Oracle`, `PostgreSQL`, `MySQL`	Yes (connectors vary)	Varies	Use native connector where possible; PostgreSQL may require Npgsql driver.
`ODBC` / `OLE DB`	Yes	Usually	Good fallback for unsupported drivers, but may lose some folding/features.

Short takeaway: Use native connectors where available. They understand your DB dialect and try hard to fold queries back to the source.

Step-by-step: Connect to a SQL Server (the canonical example)

In Power BI Desktop: Home → Get data → Database → SQL Server database.
In the dialog:
- Server: servername\instance or servername,port
- Database (optional): type database name to limit Navigator results
- Data connectivity mode: choose Import or DirectQuery (refer back to the Import vs DirectQuery lesson)
- Advanced options: paste a native SQL statement if you want Power BI to fetch exactly what you need (use sparingly)
Click OK. In Navigator you can preview and choose tables/views or click Transform Data to open Power Query.

Example native SQL you might paste:

SELECT CustomerID, OrderDate, Total
FROM dbo.Orders
WHERE OrderDate >= '2023-01-01'

Notes:

If you paste SQL, Power BI treats it as a native query and may prevent query folding for transformations done in Power Query.
Always prefer a view or parameterized query on the DB side over SELECT *.

Authentication & gateway: because the cloud can’t magically reach your office server

Authentication options: Windows/Integrated, Database (username/password), Microsoft account/Azure AD depending on connector.
If your database is on-premises and you publish reports to Power BI Service:
- Import mode: you need an On-premises Data Gateway for scheduled refresh.
- DirectQuery mode: the gateway is required so the service can run queries live.
Gateways also control datasource credentials and act as the bridge with encryption and a queue.

Security tip: use least-privilege database accounts and avoid connecting with SA or overly powerful admin users.

Query folding — what it is and why it matters

Query folding = Power Query's ability to translate your transformation steps back into SQL that runs on the database. Imagine: you ask Power BI for coffee, and instead of grinding beans locally, it tells the coffee machine to do it — much faster.

Why you care:

Folding pushes heavy lifting to the database (better CPU, indexing benefits).
Folding reduces data transferred across the network.

What breaks folding:

Native SQL queries used in the initial connection (Power BI assumes you’ve already tailored the query).
Certain Power Query transformations (especially complex M functions or custom columns with non-foldable logic).

How to check folding:

In Power Query Editor: right-click a step → View Native Query. If greyed out, that step doesn't fold.

Rule of thumb: design transformations that fold as far as possible; do late-stage shaping in Power BI only when necessary.

DirectQuery and modelling caveats

In DirectQuery, calculations run on the source or as queries against it — so indexing matters, and bad model design = slow reports.
Cross-database joins in DirectQuery can be limited; composite models and relationships have constraints depending on connector.
Aggregate tables and hybrid models (Import + DirectQuery) can help performance — consider import for historical heavy data, DirectQuery for live transactional slices.

Best practices & practical tips (the crunchy center)

Prefer views or stored procedures over SELECT * from app tables. Views centralize logic and can be optimized by DBAs.
Limit columns returned. Don’t fetch every column just because you can.
Use parameters in Power Query for server/database names; makes development → production easier.
Test transformations for folding early. Ask: “Does this step cause a full table pull?”
Use incremental refresh when importing large tables (requires proper watermark columns and Power BI Pro/Premium features).
Monitor performance using SQL Profiler (or Extended Events) and Power BI Performance Analyzer.

Common gotchas:

Using native SQL disables some Power Query optimizations.
Non-standard drivers (older ODBC) may block DirectQuery or folding.
Mismatched data types between DB and Power BI cause modeling pain and incorrect relationships.

Real-world example (mini-case)

Company X has 500M rows of orders in OrderDB. They need dashboards showing last 90 days near real-time and historical trends.

Strategy:

Create a filtered view vw_RecentOrders for last 90 days — used in DirectQuery for live KPIs.
Import aggregated historical data nightly (daily summary table) for trends and heavy analytics.
Use incremental refresh on the imported historical table.
Monitor and add indexes to columns used in filters/join keys.

This hybrid pattern gives live insight where it matters and performance for deep analysis.

Quick checklist before you click `OK` on that connection

Chosen connector supports features you need (DirectQuery, folding)
Correct authentication method and gateway planned
Will transformations fold? (check steps)
Use views/stored procs where appropriate
Consider incremental refresh for big tables

Closing — TL;DR & next steps

Relational databases are powerful but require planning: connector choice, authentication, query folding, and gateway setup all affect your experience.
If you remember two things: 1) use native connectors and push work back to the database (folding), and 2) pick Import vs DirectQuery strategically (we covered this earlier), you’ll avoid most performance disasters.

Next practice steps:

Connect to a small SQL Server sample database; experiment with folding and native queries.
Create a view on the DB server and compare performance vs a raw table.
Publish a DirectQuery report to the service and configure the gateway — see what happens.

Parting wisdom: Power BI is part artistry, part database engineering. Learn to speak both SQL and M — your dashboards will thank you.

Connecting to Data Sources

Content