Your data sits in a SQL Server database or an Access file. Power BI can visualise it. But how do you connect them the right way? And what is the difference between DirectQuery and Import? This guide shows exactly how.

At Excel Department we have connected Power BI to over 100 databases – from small Access files to enterprise SQL Servers with billions of rows. We have made all the mistakes so you don’t have to. This guide gives you the methods that actually work.

Why connect Power BI to a database instead of Excel?

Before we dive into the technical side, let’s talk about why.

Excel limitations: – Max about 1 million rows (slow long before that) – Manual refresh or complex VBA – Risk of data becoming outdated – Hard to handle many users

Database advantages: – Millions or billions of rows without problems – Always up-to-date data (Power BI reads directly from the source) – Structured and relational data – One source of truth for the whole organisation

When you should connect to a database: – Data is updated continuously (sales, transactions, logging) – The data volume grows beyond Excel’s capacity – Several systems use the same data – You want to guarantee data consistency

If you already have SQL-connected Excel today, the step to Power BI is small but gives you much more power.

SQL Server: DirectQuery vs Import mode

This is the first big decision point. Both approaches work, but they suit different situations.

Import mode (recommended for most)

How it works: Power BI reads data from SQL Server, compresses it, and stores it locally in the Power BI model. When the report is opened, it reads from this copy, not from SQL Server.

Advantages: – Much faster reports (data is local) – Works offline (data is in the file) – Less load on SQL Server – Power BI’s compression reduces data size (often 10x)

Disadvantages: – Data is not real-time (updated on a schedule) – Limit on data volume (model size max 10 GB for Pro, 100+ GB for Premium) – Requires a refresh schedule

Suitable for: – Data warehouse or reporting database – Data that is updated daily or weekly – Historical analysis – When fast performance is critical – Most business intelligence scenarios

Example: A sales database with 5 million rows, updated every night at 02:00. Power BI fetches new data then. Users get lightning-fast reports during the day.

DirectQuery mode

How it works: Power BI sends SQL queries directly to SQL Server every time you interact with the report. No data is stored locally.

Advantages: – Truly real-time data (always current) – No data size limit – Security is handled by SQL Server

Disadvantages: – Slower reports (every click = a SQL query) – Requires a constant connection to SQL Server – Higher load on the database – Limited DAX functions (some measures don’t work)

Suitable for: – Real-time data where every minute counts (tills, trading, IoT) – Extremely large datasets (100+ GB) – Strict data security (data must not leave the server) – Operational reporting

Example: A logistics dashboard showing current stock levels. Every time someone picks a product the database is updated and Power BI shows the new figure within seconds.

Hybrid: Composite models

Since 2019 you can combine both. Some tables in Import, others in DirectQuery.

Typical use case: – Large transaction data in DirectQuery (real-time) – Smaller master data (products, customers) in Import (performance)

Complicated but powerful. We recommend consultant help for this.

Our recommendation

Start with Import mode in 95% of cases.

DirectQuery is for special cases. Most companies don’t need real-time data – a daily refresh is enough. Import gives better performance and is easier to manage.

Switch to DirectQuery only if: 1. You truly need real-time data (prove it) 2. Your data is >50 GB and growing fast 3. Data security requires that data is not copied

Step by step: Connect to SQL Server

Now it gets practical. Here is how to do it:

Step 1: Prepare SQL Server

You need: – Server name (example: sqlserver.dinserver.se) – Database name – Login credentials (Windows Authentication or SQL Server Authentication) – Network access to the server

Test access first: Open SQL Server Management Studio (SSMS) and connect with the same credentials. If it doesn’t work there, it won’t work in Power BI either.

Firewall rules: If SQL Server is on-premises behind a corporate firewall, you need to open port 1433 (the standard SQL Server port).

For Azure SQL: Add your IP address in the Azure Portal under Firewall settings.

Step 2: Connect from Power BI Desktop

Open Power BI Desktop.

Home → Get Data → SQL Server

Fill in: – Server: sqlserver.dinserver.se or dinserver.database.windows.net (Azure) – Database: The name of the database – Data Connectivity mode: Import (recommended)

Advanced options (optional): – SQL statement: If you want to write your own SQL query – Command timeout: Increase if large queries take a long time

Click OK.

Step 3: Authentication

Choose an authentication method:

Windows Authentication: Uses your Windows login credentials. Works for on-premises SQL Server if you are logged in to the corporate network.

Database: SQL Server username and password. Standard for Azure SQL.

Microsoft Account: For Azure SQL you can use your Microsoft 365 account.

Click Connect.

Step 4: Select tables

The Navigator window shows all tables and views in the database.

Tips: – Select only the tables you need (not the whole database) – Look at the data size (shown in Navigator) – Prefer views over base tables if IT has created them for you

Select tables → click Transform Data (not Load directly).

Step 5: Transform in Power Query

Now you see your tables in the Power Query Editor.

Common transformations: – Remove columns you don’t need (reduces data size) – Filter rows (for example only the last 2 years) – Change data types if Power BI guessed wrong – Merge queries to create relationships

Important: All filtering in Power Query reduces the data volume = faster reports.

Click Close & Apply when done.

Step 6: Set up automatic refresh (after publishing)

Publish the report to Power BI Service.

Go to the dataset settings: – Scheduled refresh: Choose frequency and time – Gateway: If SQL Server is on-premises (see below) – Credentials: Enter the database credentials

Note: For on-premises SQL Server a Power BI Gateway is required. More on that below.

Connect to Microsoft Access

Access databases are common in small Swedish companies. Power BI can connect to them, but with limitations.

Step 1: Connect to an Access file

Home → Get Data → Access Database

Browse to your .accdb or .mdb file → Open.

Navigator shows tables and queries (saved queries in Access).

Select what you need → Transform Data.

Step 2: Transform data

Access databases are often less structured than SQL Server. Common problems:

Problem 1: Blank rows and headers Access tables can have extra rows with formatting. Remove them in Power Query.

Problem 2: Incorrect data types Access Text columns with numbers mixed in. Fix this in Power Query.

Problem 3: Relationships Access relationships are not transferred automatically. You have to create them manually in the Power BI Model view.

Limitations with Access

No automatic refresh in the cloud: Access files cannot be refreshed automatically from Power BI Service (the cloud).

Solutions: 1. Manually: Republish from Power BI Desktop when Access has been updated 2. Gateway: Install Power BI Gateway on a computer that has access to the Access file 3. Migrate: Move the data to SQL Server or SharePoint (recommended long term)

Our recommendation: If you use Access actively, consider migrating to SQL Server. We help companies with this regularly.

Temporary use: For smaller datasets (<50,000 rows) that are not updated often, Access works fine with manual publishing.

Power BI Gateway: The bridge to on-premises

If your SQL Server or Access database is on-premises (in your company network, not in the cloud) you need a Power BI Gateway.

What is a gateway?

A gateway is a program installed on a computer in your network. It acts as a bridge between Power BI Service (the cloud) and your local database.

The flow: 1. Power BI Service requests data 2. The request goes to the Gateway 3. The Gateway fetches data from the local database 4. The Gateway sends data to Power BI Service 5. The report is refreshed

Two types of gateways

On-premises data gateway (Standard mode): – For the whole organisation – Requires installation by the IT department – Can be used by all report developers – Supports several data sources at the same time

On-premises data gateway (Personal mode): – For a single user – Simpler installation – Only works for that user’s reports

Recommendation: Standard mode for business use.

Install a gateway (simplified)

Step 1: Download from Microsoft (free)

Step 2: Install on a Windows server or dedicated computer that: – Is always switched on – Has access to the database – Has a stable internet connection

Step 3: Log in with a Power BI account

Step 4: Register the gateway in Power BI Service

Step 5: Configure data sources (server name, authentication)

IT work: 1-2 hours the first time. After that, automatic.

Performance: Best practices

Poor database performance ruins the user experience. Here is what we have learned:

1. Filter early and hard

Bad: Importing 10 years of data when you only analyse the last year.

Good: Filter in Power Query: WHERE Datum >= DATEADD(year, -1, GETDATE())

Result: 90% less data = 10x faster reports.

2. Use views for complex queries

Bad: Writing complex SQL in Power BI with many JOINs and subqueries.

Good: Ask your DBA to create a view on SQL Server. Power BI connects to the view.

Advantage: SQL Server optimises the query. Easier to maintain.

3. Partition large tables

For datasets >5 million rows, use Incremental Refresh in Power BI.

Instead of refreshing the whole table every time, only new/changed rows are refreshed.

Result: Refresh time from 2 hours to 10 minutes.

4. Index the right columns

On the SQL Server side: make sure the columns you filter on (Date, CustomerID, etc.) have indexes.

Test: If a SQL query takes >5 seconds in SSMS, Power BI will be slow.

Contact your DBA to add indexes.

5. Avoid SELECT *

Bad: SELECT * FROM Försäljning

Good: SELECT OrderID, Datum, KundID, Belopp FROM Försäljning

Fetch only the columns you actually use.

Security and Row-Level Security (RLS)

When many users share reports you must control who sees what.

Scenario: Sales dashboard

Requirements: – The sales manager sees the whole country – The regional manager sees their region – The salesperson sees only their own customers

Solution 1: Security in SQL Server

Create SQL Server views with WHERE clauses based on the user:

CREATE VIEW SäljareVy AS
SELECT * FROM Försäljning
WHERE Säljare = USER_NAME()

Power BI connects to the view. SQL Server handles the security.

Advantage: Centralised security. Disadvantage: Requires separate views for different roles.

Solution 2: Row-Level Security in Power BI

Define roles in Power BI Desktop:

Role: Salesperson DAX filter: [Säljare] = USERNAME()

Role: Regional manager DAX filter: [Region] = LOOKUPVALUE(Användare[Region], Användare[Email], USERNAME())

After publishing, users are assigned roles in Power BI Service.

Advantage: Flexible, controlled in Power BI. Disadvantage: RLS rules can become complex.

Our recommendation: SQL Server security for simple scenarios. RLS for complex or dynamic permissions.

Common problems and solutions

Problem: “Couldn’t connect to SQL Server”

Causes: – Wrong server name (test in SSMS first) – Firewall blocks port 1433 – SQL Server does not accept remote connections

Solution: – Verify the server name and port – Check the firewall rules – On SQL Server: Enable TCP/IP in SQL Server Configuration Manager

Problem: “Data source too large”

Cause: The Import model exceeds 10 GB (the Power BI Pro limit).

Solutions: 1. Filter more data in Power Query 2. Remove unnecessary columns 3. Use aggregations (summarise on the SQL Server side) 4. Upgrade to Power BI Premium (100 GB+ limit) 5. Switch to DirectQuery

Problem: “Gateway is offline”

Cause: The gateway computer is switched off or has lost its connection.

Solution: – Check that the gateway computer is switched on – Test the internet connection – Restart the gateway service – Update the gateway to the latest version

Problem: “Refresh failed”

Causes: – Authentication error (password changed) – SQL Server temporarily unavailable – Timeout (the query takes too long)

Solution: – Update the credentials in the dataset settings – Increase the timeout in the gateway settings – Optimise SQL queries for faster execution

Next steps

You now have the foundation for connecting Power BI to SQL Server and Access. Here is your checklist:

For SQL Server: – [ ] Verify access with SSMS – [ ] Decide Import vs DirectQuery (Import for most) – [ ] Connect from Power BI Desktop – [ ] Filter data in Power Query – [ ] Publish and set up scheduled refresh – [ ] Install Gateway if on-premises

For Access: – [ ] Connect to the Access file – [ ] Clean data in Power Query – [ ] Build the report – [ ] Consider migrating to SQL Server for a long-term solution

Optimisation: – [ ] Filter data early (Power Query) – [ ] Remove unnecessary columns – [ ] Use SQL Server views for complex queries – [ ] Test performance and adjust – [ ] Implement security (RLS or SQL Server)

Need help?

Database integrations can get complex. At Excel Department we have done this hundreds of times.

We can help with: – Evaluating Import vs DirectQuery for your needs – Setting up Power BI Gateway – Optimising SQL queries for performance – Implementing Row-Level Security – Migrating from Access to SQL Server – Troubleshooting connection problems

Contact us for a free consultation (30 min).

Read more: – Power BI for business: Complete guide – SQL-connected Excel – Access consultant

Contact Excel Department: – Phone: 010-264 20 20 – Email: info@exceldepartment.se – Address: Slottsbacken 8, 111 30 Stockholm

Need help with this? Our Power BI consultants can help you – the first consultation is free of charge.

Book a free call or phone 010-264 20 20.