Connecting to SQL Server and Relational Databases in Power BI

Course: Microsoft Power BI Training

Topic: Connecting to SQL Server and Relational Databases

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to SQL Server and Relational Databases

Power BI is a business intelligence and data visualization platform used to connect to data, transform it, create data models, and build interactive reports and dashboards. Many organizations store their business information in relational databases such as Microsoft SQL Server, Azure SQL Database, PostgreSQL, MySQL, and Oracle Database.

Connecting Power BI to a relational database allows analysts to work with structured information such as customers, products, orders, employees, transactions, inventory, and sales. Instead of manually copying data into spreadsheets, Power BI can retrieve data directly from a database and use it for analysis.

For example, an online store might store customer details in a Customers table, product information in a Products table, and purchases in an Orders table. Power BI can connect to these tables, combine their data through relationships, and display sales trends, customer performance, and product revenue.

Learning Objectives

• Understand SQL Server and relational database fundamentals.
• Learn how to connect Power BI Desktop to SQL Server.
• Understand server names, database names, authentication, and connection settings.
• Choose tables, views, and SQL queries as data sources.
• Understand Import and DirectQuery storage modes.
• Use Power Query to clean and transform database data.
• Build relationships between relational tables in Power BI.
• Identify common connection errors and apply troubleshooting techniques.
• Follow security and performance best practices.

2. Understanding Relational Databases

A relational database organizes information into tables. Each table contains rows and columns, with each row representing a record and each column representing an attribute of that record.

2.1 Tables, Rows, and Columns

2.2 Example of a Customers Table

In this example, CustomerID uniquely identifies each customer. Power BI can use this column to connect customer records with related orders.

2.3 Example of an Orders Table

The CustomerID column in Orders refers to the CustomerID column in Customers. This allows Power BI to analyze order amounts by customer, city, or other customer attributes.

3. Introduction to Microsoft SQL Server

Microsoft SQL Server is a relational database management system (RDBMS) that stores and manages structured data. It supports SQL, a language used to query and manage relational data.

3.1 Common SQL Server Components

• SQL Server Database Engine: Stores, processes, and secures data.
• Database: A logical container for tables, views, and other database objects.
• Table: Stores structured records.
• View: Presents data through a saved SQL query.
• Stored procedure: A stored set of SQL statements that can perform operations or return results.
• SQL Server Management Studio (SSMS): A graphical tool used to administer SQL Server and run queries.

3.2 Why Connect Power BI to SQL Server?

• Access centrally stored business data.
• Reduce manual spreadsheet exports and repetitive copy-and-paste tasks.
• Analyze large volumes of structured information.
• Combine data from multiple related tables.
• Refresh reports as source data changes.
• Apply database permissions and governance policies.
• Create dashboards for sales, finance, operations, HR, inventory, and customer analytics.

4. Prerequisites Before Connecting

Before creating a connection, confirm that you have the required database details and access permissions.

4.1 Identifying the Server Name

A SQL Server name may appear in several forms depending on how the database is installed and hosted.

• Local default instance: localhost or .
• Local named instance: localhost\SQLEXPRESS
• Remote server: SQLSERVER01
• Server with a port: sqlserver.example.com,1433, when that endpoint and port are configured for SQL Server.
• Azure SQL Database: A fully qualified Azure SQL server hostname, supplied by the database administrator or Azure portal.

These are examples only. Use the actual server name and network endpoint provided by your administrator. Named instances, ports, and cloud services can require different connection settings.

5. Connecting Power BI Desktop to SQL Server

Power BI Desktop includes a SQL Server database connector. The connector lets you choose a database, select objects, and decide how data should be loaded into the Power BI model.

5.1 Step-by-Step Connection Process

1. Open Power BI Desktop.
2. On the Home ribbon, select Get data.
3. Select SQL Server. If it is not visible in the quick list, choose More and find the SQL Server database connector.
4. Enter the SQL Server name in the Server field.
5. Optionally enter the database name to narrow the connection to a specific database.
6. Choose the required data connectivity mode: Import or DirectQuery, where supported.
7. Open the advanced options if you need to provide a SQL statement or adjust supported connection settings.
8. Select OK.
9. Choose the authentication method and enter the required credentials if prompted.
10. In Navigator, select the tables or views you need.
11. Select Load to import data directly, or Transform Data to clean and shape the data first.
12. Review the loaded tables and relationships in Power BI Desktop.

5.2 Example Connection Details

Use the example values only when they match your environment. A server name that is valid on one computer may not be reachable from another computer.

5.3 Navigator Window

After connecting, Power BI displays a Navigator window containing available databases and objects that your account is allowed to access.

• Tables: Physical database tables.
• Views: Saved database queries presented as objects.
• Preview: A sample preview of the selected object's data.
• Select multiple items: Lets you choose more than one table or view.
• Load: Loads the selected data into the model.
• Transform Data: Opens Power Query Editor before loading the final result.

Choose only the data needed for your report. Importing unnecessary tables and columns can increase model size and reduce performance.

6. Authentication and Database Permissions

Authentication verifies the identity of the user or service connecting to SQL Server. Authorization determines which data and operations that identity is permitted to access.

6.1 Common Authentication Options

Available options depend on the connector, server type, and environment. Use the authentication method approved by your organization. Never embed passwords in SQL statements, report notes, or shared files.

6.2 Principle of Least Privilege

Grant only the permissions needed to complete the reporting task. Most report creators need read access to approved tables or views rather than administrative permissions.

• Use dedicated reporting accounts where appropriate.
• Grant read-only access when write access is unnecessary.
• Restrict access to confidential columns and records.
• Follow organizational policies for credential storage and rotation.
• Review permissions periodically, especially when staff or reporting responsibilities change.

7. Import Mode and DirectQuery Mode

When connecting to SQL Server, one of the most important decisions is whether Power BI should import data into its model or query the source database when users interact with the report.

7.1 When to Use Import

• When the dataset can fit comfortably within the model's capacity limits.
• When report responsiveness is a priority.
• When scheduled refreshes are sufficient for the business.
• When data transformations and modeling work well with imported data.

7.2 When to Use DirectQuery

• When data volume or governance requirements make importing less suitable.
• When the report needs to query source data as users interact with visuals.
• When source database performance and capacity are sufficient for report queries.
• When DirectQuery limitations are acceptable for the required calculations and modeling.

DirectQuery does not automatically guarantee real-time data or better performance. Query folding, source design, caching, network latency, and database workload all affect the result. Evaluate the mode against actual report requirements and test with representative data.

8. Using Tables, Views, and SQL Queries

Power BI can retrieve data from database tables and views. In supported connector scenarios, you can also provide a SQL statement to return a specific result set.

8.1 Connecting to Tables

Tables are appropriate when you want to retrieve records directly from a database object and apply transformations in Power Query or the Power BI model.

For example, selecting the Orders table may provide OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount fields.

8.2 Connecting to Views

A view is a database object based on a saved query. Views can simplify access to joined or filtered data and provide a consistent reporting layer.

For example, a view named vw_SalesSummary might combine order details with product categories and sales regions. The view's definition and permissions are managed in the database.

8.3 Using a SQL Statement

The SQL Server connector supports an advanced option for specifying a SQL statement in supported connection scenarios. A SQL statement can retrieve only the required fields or filter records before the data reaches Power BI.

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    SalesAmount
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01';

This example returns selected order fields for dates beginning January 1, 2026. Change the date and table or column names to match your database and reporting needs.

8.4 Aggregating Data in SQL

SELECT
    CustomerID,
    COUNT(*) AS TotalOrders,
    SUM(SalesAmount) AS TotalSales
FROM dbo.Orders
GROUP BY CustomerID;

This query produces one row per customer, including the number of orders and total sales amount. Pre-aggregating data can be useful when detailed row-level records are not required for the report, but it may limit the types of analysis available later.

8.5 SQL Query Best Practices

• Select only the columns required by the report.
• Apply filters that match the business requirement.
• Use explicit column names instead of SELECT * in production reporting queries.
• Use appropriate indexes and database execution plans for performance-sensitive queries.
• Use views for reusable reporting logic where suitable.
• Test SQL statements in an approved database tool before connecting them to Power BI.
• Use read-only permissions when possible.
• Do not concatenate untrusted input into SQL statements in custom applications or workflows.

9. Transforming SQL Server Data with Power Query

Power Query Editor allows you to clean and reshape source data before loading it into the Power BI model. Transformations are recorded as applied steps, which can be reviewed and adjusted later.

9.1 Common Transformations

9.2 Example: Filtering Sales Data

1. Select the Orders query in Power Query Editor.
2. Review the available columns and data types.
3. Filter OrderDate to the period required for the report.
4. Remove columns that are not needed.
5. Confirm SalesAmount is a numeric data type.
6. Inspect the preview to ensure that the transformation has not removed required records.
7. Select Close & Apply to load the result into the model.

9.3 Query Folding

Query folding occurs when Power Query translates supported transformation steps into a query that the source database can execute. When folding is available, SQL Server can perform filtering, projection, and other supported operations before the results are transferred to Power BI.

Benefits may include:

• Reducing the volume of data transferred over the network.
• Using database processing resources for supported transformations.
• Improving refresh performance for suitable workloads.
• Helping large datasets remain manageable.

Not every transformation folds in every connector or query. Check folding indicators or query diagnostics where available, and validate performance with your actual source and transformation steps.

10. Working with Relationships Between Tables

After loading related tables, Power BI can connect them through model relationships. Correct relationships allow report filters to flow between tables and produce accurate aggregations.

10.1 Primary and Foreign Key Example

Customers
---------
CustomerID (Primary Key)
CustomerName
City

Orders
------
OrderID (Primary Key)
CustomerID (Foreign Key)
OrderDate
SalesAmount

Customers contains one row for each customer. Orders can contain multiple rows for the same customer. The typical relationship is therefore one-to-many from Customers to Orders.

10.2 Creating a Relationship

1. Load the Customers and Orders tables.
2. Open Model view in Power BI Desktop.
3. Locate the CustomerID field in both tables.
4. Create a relationship between Customers.CustomerID and Orders.CustomerID if Power BI has not already detected it correctly.
5. Verify the cardinality and filter direction.
6. Test the relationship by creating a visual that displays customer names and total sales.

10.3 Relationship Best Practices

• Use unique keys on the one side of a one-to-many relationship.
• Check for nulls, duplicates, and inconsistent data types in key columns.
• Prefer a clear star schema for analytical models when appropriate.
• Avoid unnecessary many-to-many relationships.
• Use bidirectional filtering only when the model requires it and its behavior is understood.
• Validate totals against trusted database results.

11. Creating Measures for SQL Server Data

Once the data is loaded and relationships are established, DAX measures can calculate business metrics dynamically based on report filters.

11.1 Total Sales

Total Sales = SUM(Orders[SalesAmount])

This measure sums the SalesAmount column in the Orders table. Use the actual table and column names from your model.

11.2 Total Orders

Total Orders = DISTINCTCOUNT(Orders[OrderID])

This measure counts distinct order identifiers. It is suitable when an order can appear in multiple rows, such as in an order-line table.

11.3 Average Order Value

Average Order Value =
DIVIDE([Total Sales], [Total Orders], 0)

The DIVIDE function safely handles a zero denominator by returning the specified alternate result. Confirm that the definition of an order and the grain of the source data match the intended business calculation.

11.4 Using Measures in Reports

• Use a Card visual to show Total Sales.
• Use a line chart to show sales over time.
• Use a bar chart to compare sales by customer or product category.
• Use a table or matrix to compare orders and sales by region.
• Add slicers for dates, regions, and product categories.

12. Refreshing Data from SQL Server

For Import models, the report reflects the source data captured during the last successful refresh. Refreshing retrieves updated data and recalculates the imported model as required.

12.1 Refresh in Power BI Desktop

1. Open the Power BI report.
2. Confirm that the SQL Server is reachable and the credentials are valid.
3. Select Refresh on the Home ribbon.
4. Wait for the refresh process to complete.
5. Review any errors reported by Power BI.
6. Check important totals and report visuals after the refresh.

12.2 Refresh in Power BI Service

When a report is published to Power BI Service, refresh configuration depends on where the SQL Server is hosted and how the service can reach it.

• Cloud-accessible source: Configure the supported cloud connection and credentials as required.
• On-premises SQL Server: An appropriately configured on-premises data gateway is commonly required for the Power BI Service to reach the source.
• Credentials: Configure valid credentials for the data source in the service or gateway connection.
• Refresh schedule: Configure a schedule that meets the business need and service limits.
• Monitoring: Review refresh history and resolve failures promptly.

DirectQuery reports have different data-access behavior from Import models, but still require appropriate credentials, connectivity, and source availability. Follow the relevant Power BI and database configuration requirements.

13. Connecting to Other Relational Databases

Power BI supports connectors for multiple relational database systems. The general workflow is similar, although server details, authentication, supported modes, drivers, and gateway requirements vary by connector.

To connect, choose the matching connector from Get data, enter the required connection details, authenticate, select objects, and load or transform the data. Consult the official documentation for connector-specific requirements.

14. Common Connection Errors and Troubleshooting

14.1 A Systematic Troubleshooting Checklist

1. Confirm that the server name and database name are correct.
2. Test connectivity using an approved database client, if available.
3. Verify that the account can sign in and read the required objects.
4. Check whether VPN, firewall, DNS, or network restrictions apply.
5. Review the Power BI error message and identify whether the failure occurs during connection, transformation, loading, or refresh.
6. Check the data source permissions and saved credentials in Power BI when appropriate.
7. For Power BI Service refreshes, verify the gateway and its data source mapping where required.
8. Test a small table or a simple query to isolate the problem.
9. Compare row counts and totals with the source database.

15. Security Best Practices

• Use approved authentication methods and protect credentials.
• Never publish passwords, connection secrets, or sensitive server details in shared documentation.
• Apply least-privilege access to database objects.
• Use encrypted connections where supported and required by your organization's security policy.
• Restrict access to sensitive information before it enters the Power BI model whenever appropriate.
• Use row-level security in Power BI when different report users should see different rows, and validate the rules carefully.
• Remember that report-level security does not replace database permissions or organizational data governance.
• Use approved gateway infrastructure for on-premises sources.
• Follow data retention, privacy, and compliance requirements.

16. Performance Optimization Best Practices

16.1 Optimize Data Retrieval

• Import only required columns and rows.
• Prefer selective queries over retrieving every row from every table.
• Use well-designed database views where they simplify or standardize reporting logic.
• Push suitable filters and transformations to SQL Server when query folding supports it.
• Use incremental refresh when appropriate and supported by the model and service configuration.

16.2 Optimize the Data Model

• Use a star schema where suitable, with fact tables for transactions and dimension tables for descriptive attributes.
• Use appropriate data types and avoid unnecessary high-cardinality columns.
• Hide technical key columns from report view when users do not need them.
• Prefer explicit DAX measures for important business calculations.
• Remove unused tables, columns, and relationships.
• Test report responsiveness using representative data and expected user interactions.

16.3 Optimize SQL Server

• Review query execution plans for expensive queries.
• Evaluate indexes on frequently filtered or joined columns with a database administrator.
• Avoid unnecessary functions or conversions on filtered columns when they prevent efficient query plans.
• Monitor source workload during refreshes and peak report usage.
• Use suitable views, aggregations, or database-side logic when they improve maintainability and performance.

17. Practical Example: Building a Sales Report

In this project, you will connect Power BI to a SQL Server database containing Customers, Orders, and Products tables.

17.1 Sample Database Structure

RetailSalesDB
|
|-- dbo.Customers
|   |-- CustomerID
|   |-- CustomerName
|   |-- City
|
|-- dbo.Orders
|   |-- OrderID
|   |-- CustomerID
|   |-- ProductID
|   |-- OrderDate
|   |-- Quantity
|   |-- SalesAmount
|
|-- dbo.Products
    |-- ProductID
    |-- ProductName
    |-- Category

17.2 Project Steps

1. Open Power BI Desktop.
2. Select Get data and choose SQL Server.
3. Enter the approved server and database details.
4. Authenticate with an authorized account.
5. Select Customers, Orders, and Products in Navigator.
6. Choose Transform Data and inspect each query.
7. Remove unused columns and correct data types.
8. Apply transformations and load the tables.
9. Open Model view and establish the appropriate relationships.
10. Create the Total Sales, Total Orders, and Average Order Value measures.
11. Create a Card visual for Total Sales.
12. Create a line chart for monthly sales and a bar chart for sales by product category.
13. Add slicers for date, city, and category.
14. Compare key totals with the source database.
15. Save the report and document the connection and refresh requirements.

17.3 Example SQL Validation Query

SELECT
    SUM(SalesAmount) AS TotalSales,
    COUNT(DISTINCT OrderID) AS TotalOrders
FROM dbo.Orders;

Use the query to validate the corresponding Power BI measures when both calculations use the same rows, filters, and business definitions. If the report applies date or category filters, use equivalent filters in the SQL comparison.

17.4 Expected Outcome

• A Power BI model connected to the selected SQL Server tables.
• Relationships that support analysis by customer and product.
• Measures for sales and order counts.
• Interactive visuals with relevant slicers.
• A documented process for refreshing and troubleshooting the report.

18. Best Practices Summary

• Confirm the correct server, database, authentication method, and permissions before connecting.
• Choose Import or DirectQuery based on data freshness, performance, size, and governance requirements.
• Load only necessary data.
• Use Power Query to standardize data types and clean source data.
• Prefer well-designed tables and views with clear definitions.
• Check query folding and database performance for large datasets.
• Create relationships using valid keys and appropriate cardinality.
• Validate important metrics against the database.
• Configure gateway, credentials, and refresh schedules when publishing reports that need them.
• Protect sensitive information and follow organizational security policies.

19. Practice Exercises

1. Connect Power BI Desktop to an available SQL Server instance using authorized credentials.
2. Locate a database and identify three tables containing related business information.
3. Load one table using Import mode and describe how a refresh updates its data.
4. Explore DirectQuery for a compatible source and compare the behavior with Import mode.
5. Write a SQL query that returns selected columns and filters records by date.
6. Use Power Query to rename a column, filter rows, and correct a data type.
7. Create a one-to-many relationship between a customer table and an orders table.
8. Create a Total Sales measure and compare its value with a SQL aggregation.
9. Investigate a simulated connection error and document the troubleshooting steps.
10. Publish a sample report, if your environment permits, and identify the gateway and credential requirements for refresh.

20. Interview Questions and Answers

Q1. What is SQL Server?

Answer: SQL Server is Microsoft's relational database management system for storing, querying, and managing structured data.

Q2. How do you connect Power BI to SQL Server?

Answer: In Power BI Desktop, select Get data, choose SQL Server, enter the server and optional database names, select the appropriate connection mode, authenticate, select tables or views, and load or transform the data.

Q3. What is the difference between a table and a view?

Answer: A table stores records directly. A view presents data through a saved query and can simplify access to selected or combined information.

Q4. What is Import mode?

Answer: Import mode loads source data into the Power BI model. The imported data is updated when a refresh succeeds.

Q5. What is DirectQuery?

Answer: DirectQuery keeps source data in the database and sends queries to the source as needed, subject to connector behavior, caching, and model configuration.

Q6. What is query folding?

Answer: Query folding translates supported Power Query transformations into source queries so the database can perform those operations.

Q7. What is the purpose of a primary key?

Answer: A primary key uniquely identifies each row in a table and helps maintain record integrity.

Q8. What is a foreign key?

Answer: A foreign key refers to a key in another table and helps represent relationships between related records.

Q9. Why might a Power BI SQL Server connection fail?

Answer: Common causes include incorrect server details, network restrictions, an unavailable server, authentication errors, missing database permissions, or firewall configuration issues.

Q10. Why is a gateway needed for some Power BI Service connections?

Answer: A gateway enables the Power BI Service to reach supported data sources that are not directly accessible from the cloud service, commonly including on-premises SQL Server.

Q11. How can you improve SQL Server report performance?

Answer: Retrieve only necessary data, optimize SQL queries and indexes, use suitable data models, reduce unnecessary transformations, and select a storage mode that meets the report's performance and freshness needs.

Q12. How do you validate Power BI totals?

Answer: Compare Power BI measures with equivalent SQL queries using the same data scope, filters, relationships, and business definitions.

21. Quick Revision Checklist

• Understand relational databases, tables, rows, columns, keys, and views.
• Know how to find and enter SQL Server connection details.
• Understand supported authentication methods and database permissions.
• Know the difference between Import and DirectQuery.
• Know how to select tables and views through Navigator.
• Understand SQL statements and the purpose of filtering source data.
• Know how Power Query transformations and query folding work.
• Understand primary keys, foreign keys, and Power BI relationships.
• Know how to create and validate DAX measures.
• Understand Desktop refresh, Power BI Service refresh, and gateway requirements.
• Know common connection errors and their possible solutions.
• Apply security, data quality, and performance best practices.

22. Official Learning Resources

• Microsoft Learn: SQL Server Connector for Power Query
• Microsoft Learn: DirectQuery in Power BI Desktop
• Microsoft Learn: Refresh Data in Power BI
• Microsoft Learn: On-premises Data Gateway

23. Conclusion

Connecting Power BI to SQL Server and relational databases is an essential skill for building reliable business intelligence solutions. By understanding connection settings, authentication, tables, views, SQL queries, storage modes, Power Query transformations, and data modeling, you can build reports from structured business data efficiently.

A strong workflow includes retrieving only the necessary data, creating accurate relationships, validating measures, protecting credentials, and configuring refresh correctly. Practice these steps with an approved database and gradually expand from a simple table connection to a complete interactive sales dashboard.

Explore the course: Microsoft Power BI Training

Register for a course demo: Book a JustAcademy Course Demo

Visit: JustAcademy