Connecting to SQL Server and Writing Data Queries in Power BI

Course: Microsoft Power BI Training

Topic: Connecting to SQL Server and Writing Data Queries

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to SQL Server and Power BI

Microsoft Power BI is a business intelligence platform used to connect to data sources, transform data, create interactive reports, and develop dashboards for business analysis. Microsoft SQL Server is a relational database management system that stores structured data in tables and provides SQL (Structured Query Language) for retrieving, filtering, joining, and aggregating that data.

Connecting Power BI to SQL Server allows analysts to access business data such as sales transactions, customer records, product details, inventory information, financial data, and employee records. Once connected, the data can be prepared in Power Query, modeled in Power BI, and visualized through reports.

Writing data queries is an important part of this process because it allows developers to retrieve only the required columns and rows, combine related tables, calculate summary values, and prepare data for analysis.

1.1 Learning Objectives

• Understand SQL Server and its role in business intelligence.
• Identify the information required to connect Power BI to a database.
• Connect Power BI Desktop to SQL Server.
• Understand Import and DirectQuery storage modes.
• Write SQL queries using SELECT, WHERE, ORDER BY, GROUP BY, and HAVING.
• Combine data from multiple tables using JOIN operations.
• Use aggregate functions to summarize business data.
• Understand how SQL queries can be used with Power Query.
• Apply basic security, troubleshooting, and query optimization practices.

2. Understanding Microsoft SQL Server

2.1 What Is SQL Server?

Microsoft SQL Server is a database management system designed to store, organize, manage, and retrieve structured data. It supports relational tables, constraints, indexes, views, stored procedures, transactions, and other database features.

Organizations use SQL Server to manage operational and analytical information. For example, an e-commerce business might store customers, orders, products, payments, and inventory in separate but related tables.

2.2 Important SQL Server Components

2.3 Example of a Relational Database

Suppose a company maintains the following tables in its SalesDB database:

Customers Table

Orders Table

The CustomerID column connects the two tables. Power BI can use this relationship to analyze order amounts by customer, city, or other customer attributes.

3. Introduction to SQL Queries

SQL stands for Structured Query Language. It is used to communicate with relational databases. A SQL query can retrieve selected columns, filter records, sort results, combine tables, and calculate summaries.

3.1 Basic SQL Query Structure

SELECT column_name
FROM table_name
WHERE condition
ORDER BY column_name;

The SELECT statement identifies the columns to return, FROM specifies the source table, WHERE filters the records, and ORDER BY sorts the results.

3.2 Why Write SQL Queries for Power BI?

• Retrieve only the columns required for reporting.
• Filter large datasets before importing them when appropriate.
• Combine information from related database tables.
• Calculate totals, averages, and counts.
• Prepare reusable datasets for business analysis.
• Reduce unnecessary data transfer where query folding or source-side execution is supported.
• Investigate database results before building Power BI visuals.

4. Requirements Before Connecting Power BI to SQL Server

Before creating the connection, confirm that the database server is accessible and that you have permission to read the required data.

4.1 Required Information

4.2 Understanding Server Names

A SQL Server connection can use a server hostname, a server and named instance, or a server with a specified port. The exact format depends on the database configuration.

Server: SQLSERVER01
Database: SalesDB

Named instance example:
SQLSERVER01\REPORTING

Server with TCP port example:
tcp:sqlserver01.example.com,1433

These examples are illustrative. Use the actual server address and connection details provided by your database administrator.

5. Connecting Power BI Desktop to SQL Server

5.1 Step-by-Step Connection Process

1. Open Power BI Desktop.
2. On the Home ribbon, select Get data.
3. Choose SQL Server database. If it is not immediately visible, search for SQL Server in the data source list.
4. Enter the SQL Server name in the Server field.
5. Optionally specify the database name to connect directly to the required database.
6. Choose Import or DirectQuery, depending on your reporting requirements.
7. If needed, expand Advanced options to enter a SQL statement.
8. Select OK to initiate the connection.
9. Choose the appropriate authentication method and provide credentials when prompted.
10. In Navigator, select the required tables or views.
11. Select Load to load the data into the model or Transform Data to prepare it in Power Query first.

5.2 Understanding the Navigator Window

The Navigator window displays the database objects that the authenticated user is permitted to access. Depending on permissions and the connector, these may include tables and views.

Before loading data, check the object names and preview the data when available. Select only the objects required for the report rather than loading every table by default.

5.3 Loading Data Directly

Select Load when the selected data is ready for modeling. Power BI loads the data into the semantic model when using Import mode. You can then create relationships, measures, calculated columns, and visuals.

5.4 Transforming Data Before Loading

Select Transform Data to open Power Query Editor. There you can remove unnecessary columns, filter rows, change data types, rename fields, merge tables, and apply other transformations before applying the changes to the model.

6. Import Mode and DirectQuery Mode

When connecting Power BI to SQL Server, choosing the appropriate storage mode is important because it affects refresh behavior, performance, and data freshness.

6.1 When Should You Use Import Mode?

• When fast report interactions are a priority.
• When the dataset fits within model and capacity constraints.
• When scheduled or on-demand refresh is sufficient.
• When complex modeling and calculations benefit from the imported semantic model.

6.2 When Should You Use DirectQuery?

• When importing the required data is unsuitable for the scenario.
• When source-side querying is a requirement.
• When data freshness requirements justify the additional source-query dependency.
• When the SQL Server infrastructure can support the expected query workload.

Important: DirectQuery does not guarantee real-time data or faster performance. Caching, source latency, query translation, and model design all affect the outcome.

7. Writing Your First SQL Query

After connecting to SQL Server, you can write SQL statements to retrieve the information required for your report. The following examples use illustrative Customers, Orders, and Products tables.

7.1 Selecting All Columns

SELECT *
FROM Customers;

This query returns all columns and rows available in the Customers table, subject to the user's permissions and any applicable database rules.

Although SELECT * is useful for exploration, production queries should usually specify the columns needed by the report. This makes the query clearer and can reduce unnecessary data transfer.

7.2 Selecting Specific Columns

SELECT
    CustomerID,
    CustomerName,
    City
FROM Customers;

This query retrieves only CustomerID, CustomerName, and City. Selecting specific columns is a good practice when preparing datasets for Power BI.

7.3 Using Column Aliases

SELECT
    CustomerID AS [Customer ID],
    CustomerName AS [Customer Name],
    City AS [Customer City]
FROM Customers;

Aliases provide more readable column headings. Use aliases carefully and consistently because field names may be referenced by later Power Query steps, relationships, or report visuals.

8. Filtering Data with WHERE

The WHERE clause filters rows based on a specified condition. Filtering at the SQL source can reduce the number of records retrieved when the query is executed by SQL Server.

8.1 Filtering by a Single Condition

SELECT
    OrderID,
    CustomerID,
    Amount
FROM Orders
WHERE Amount > 10000;

This query returns orders whose Amount is greater than 10,000.

8.2 Filtering with AND

SELECT
    OrderID,
    CustomerID,
    Amount
FROM Orders
WHERE Amount > 10000
  AND CustomerID = 1;

The AND operator requires both conditions to be true.

8.3 Filtering with OR

SELECT
    OrderID,
    CustomerID,
    Amount
FROM Orders
WHERE CustomerID = 1
   OR CustomerID = 2;

The OR operator returns rows satisfying at least one of the conditions.

8.4 Filtering with BETWEEN

SELECT
    OrderID,
    OrderDate,
    Amount
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2026-02-01';

This date-range pattern returns records from January 2026, including any time on January 31 when OrderDate is a datetime column. Using a half-open date interval can avoid problems caused by time components in date and datetime values.

8.5 Filtering with IN

SELECT
    CustomerID,
    CustomerName,
    City
FROM Customers
WHERE City IN ('Mumbai', 'Pune');

The IN operator checks whether a value matches any value in the specified list.

8.6 Filtering with LIKE

SELECT
    CustomerID,
    CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%';

The LIKE operator supports pattern matching. In this example, the percent sign represents zero or more characters, so the query returns names beginning with A. Case sensitivity depends on the database collation.

9. Sorting Data with ORDER BY

The ORDER BY clause sorts the query result by one or more columns.

9.1 Ascending Order

SELECT
    CustomerName,
    City
FROM Customers
ORDER BY CustomerName ASC;

ASC sorts values in ascending order. It is the default sort direction when no direction is specified.

9.2 Descending Order

SELECT
    OrderID,
    Amount
FROM Orders
ORDER BY Amount DESC;

DESC sorts the results in descending order, placing higher numeric amounts first.

9.3 Sorting by Multiple Columns

SELECT
    City,
    CustomerName
FROM Customers
ORDER BY City ASC, CustomerName ASC;

This query sorts customers by city and then sorts customer names within each city. SQL results are not guaranteed to have a particular order unless ORDER BY specifies it.

10. Using Aggregate Functions

Aggregate functions summarize multiple rows into a single result for the current query group.

10.1 Calculating Total Sales

SELECT
    SUM(Amount) AS TotalSales
FROM Orders;

This query returns the total of the Amount column for the selected records.

10.2 Calculating Average Order Value

SELECT
    AVG(Amount) AS AverageOrderValue
FROM Orders;

AVG ignores NULL values. If there are no non-NULL values to average, the result is NULL.

10.3 Counting Orders

SELECT
    COUNT(*) AS TotalOrders
FROM Orders;

COUNT(*) counts the number of rows returned by the query, including rows where some columns contain NULL.

11. Grouping Data with GROUP BY

The GROUP BY clause groups rows that share the same values in specified columns. Aggregate functions can then calculate a summary for each group.

11.1 Total Sales by Customer

SELECT
    CustomerID,
    SUM(Amount) AS TotalSales
FROM Orders
GROUP BY CustomerID;

This query returns one row for each CustomerID represented in the Orders table and calculates the total amount for that customer.

11.2 Total Sales by Month

SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    SUM(Amount) AS TotalSales
FROM Orders
GROUP BY
    YEAR(OrderDate),
    MONTH(OrderDate)
ORDER BY
    OrderYear,
    OrderMonth;

This query groups orders by year and month. In production reporting, a proper calendar table in Power BI is often helpful for consistent date analysis, including months with no transactions.

11.3 Using HAVING

WHERE filters individual rows before grouping, while HAVING filters the groups created by GROUP BY.

SELECT
    CustomerID,
    SUM(Amount) AS TotalSales
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 20000;

This query returns only customer groups whose total sales exceed 20,000.

12. Joining Multiple Tables with JOIN

The JOIN operation combines rows from two or more tables using related columns. It is particularly useful when business information is stored in separate tables.

12.1 INNER JOIN

INNER JOIN returns only records that have matching values in both tables.

SELECT
    Customers.CustomerID,
    Customers.CustomerName,
    Orders.OrderID,
    Orders.Amount
FROM Customers
INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID;

This query returns orders along with the names of customers who placed them. Customers without matching orders are excluded.

12.2 LEFT JOIN

LEFT JOIN returns all records from the left table and matching records from the right table. If no matching record exists, columns from the right table contain NULL.

SELECT
    Customers.CustomerID,
    Customers.CustomerName,
    Orders.OrderID,
    Orders.Amount
FROM Customers
LEFT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID;

This query includes customers who have not placed any orders, making it useful for customer coverage and inactivity analysis.

12.3 RIGHT JOIN

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT
    Customers.CustomerName,
    Orders.OrderID,
    Orders.Amount
FROM Customers
RIGHT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID;

In this example, all order records are retained. For readability, developers often prefer rewriting a RIGHT JOIN as a LEFT JOIN by reversing the table order.

12.4 FULL OUTER JOIN

FULL OUTER JOIN returns matching rows and unmatched rows from both tables. Missing values on either side are represented by NULL.

SELECT
    Customers.CustomerID,
    Customers.CustomerName,
    Orders.OrderID,
    Orders.Amount
FROM Customers
FULL OUTER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID;

This operation can help compare two datasets and identify unmatched records. Its availability and behavior should be considered when working with different database systems.

12.5 Joining Three Tables

Suppose the database contains Customers, Orders, and Products tables, with an OrderDetails table connecting orders to products.

SELECT
    c.CustomerName,
    o.OrderID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails AS od
    ON o.OrderID = od.OrderID
INNER JOIN Products AS p
    ON od.ProductID = p.ProductID;

This query combines customer, order, order-detail, and product information into one result. The example assumes that the listed tables and columns exist in the database.

13. Writing Business-Focused SQL Queries

Power BI reports are generally designed to answer business questions. SQL queries can prepare the underlying data for these questions before it reaches the report.

13.1 Finding the Top 5 Customers by Sales

SELECT TOP 5
    c.CustomerID,
    c.CustomerName,
    SUM(o.Amount) AS TotalSales
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID,
    c.CustomerName
ORDER BY
    TotalSales DESC;

TOP 5 limits the result to five rows after sorting by total sales in descending order. If ties must be handled in a particular way, the query should define the required ranking behavior explicitly.

13.2 Finding Customers Without Orders

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

This query identifies customers who have no matching orders. It can support customer engagement and sales follow-up reporting.

13.3 Calculating Monthly Sales

SELECT
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    SUM(Amount) AS MonthlySales
FROM Orders
GROUP BY
    YEAR(OrderDate),
    MONTH(OrderDate)
ORDER BY
    SalesYear,
    SalesMonth;

The result can be used to create monthly sales trend charts in Power BI. For full calendar reporting, consider using a dedicated date table and handling months without transactions.

13.4 Calculating Sales by City

SELECT
    c.City,
    SUM(o.Amount) AS TotalSales,
    COUNT(o.OrderID) AS NumberOfOrders
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
GROUP BY c.City
ORDER BY TotalSales DESC;

This query produces city-level sales totals and order counts. It can support regional comparisons and geographic reporting.

13.5 Finding Orders Above the Average Amount

SELECT
    OrderID,
    CustomerID,
    Amount
FROM Orders
WHERE Amount > (
    SELECT AVG(Amount)
    FROM Orders
);

The subquery calculates the average order amount, and the outer query returns orders above that average. This is an example of a scalar subquery.

14. Using SQL Queries in Power BI Advanced Options

Power BI allows users to enter a native SQL statement while connecting to a supported database. This is useful when the required dataset can be retrieved more conveniently through a custom SQL query than through selecting an entire table.

14.1 Steps to Enter a Native SQL Query

1. Open Power BI Desktop.
2. Select Home and then Get data.
3. Choose SQL Server database.
4. Enter the server name and, optionally, the database name.
5. Expand Advanced options.
6. Enter the SQL statement in the SQL statement field.
7. Choose the appropriate connection mode and select OK.
8. Provide authentication details when prompted.
9. Review the query results in Power Query Editor.
10. Apply any required transformations and select Close & Apply.

14.2 Example of a Native SQL Query

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Amount
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01'
  AND Amount > 5000;

This query retrieves orders from calendar year 2026 with amounts greater than 5,000. The database executes the SQL statement and returns the result to Power Query.

14.3 Benefits of Native SQL Queries

• Provides precise control over the columns and rows retrieved.
• Supports joins and aggregations defined in SQL.
• Can reuse existing database query logic.
• May reduce unnecessary data transfer when filtering is performed at the source.
• Can simplify data preparation for complex reporting requirements.

14.4 Important Limitations

• Native queries must be valid for the connected database system.
• Power Query transformations applied after a native query may not all fold back to the source.
• Query folding behavior depends on the connector and query structure.
• Changes to source tables or column names can break the query.
• Native queries can trigger security approval prompts.
• Native database queries in Power Query are intended for retrieving data, not for performing unsupported database definition operations such as CREATE TABLE or DROP TABLE.

Use Microsoft's official guide for additional details: Import data from a database using native database query.

15. Understanding Power Query and SQL Queries

Power Query is the data connection and transformation engine used by Power BI. It provides a graphical interface for preparing data and uses the M language to represent its transformation steps.

SQL and M serve different purposes. SQL retrieves and manipulates data within the relational database, while M describes data acquisition and transformation steps in Power Query.

15.1 SQL Query Example

SELECT
    CustomerID,
    CustomerName,
    City
FROM Customers
WHERE City = 'Mumbai';

This SQL statement asks SQL Server to return customers located in Mumbai.

15.2 Equivalent Power Query Transformation Concept

let
    Source = Sql.Database(
        "SQLSERVER01",
        "SalesDB"
    ),
    Customers = Source{[Schema="dbo", Item="Customers"]}[Data],
    FilteredRows = Table.SelectRows(
        Customers,
        each [City] = "Mumbai"
    ),
    SelectedColumns = Table.SelectColumns(
        FilteredRows,
        {"CustomerID", "CustomerName", "City"}
    )
in
    SelectedColumns

This M example connects to SQL Server, selects the Customers table, filters the rows, and keeps the specified columns. Replace the server, database, schema, and table names with the values used in your environment.

15.3 What Is Query Folding?

Query folding occurs when Power Query translates supported transformation steps into a query that can be executed by the source system. With SQL Server, folding can allow filters, column selections, and certain other operations to run at the database level.

For example, when a filter folds successfully, SQL Server may return only the rows required by Power BI rather than sending the entire table to Power Query for filtering.

15.4 Benefits of Query Folding

• Reduces unnecessary data transfer in suitable scenarios.
• Can improve refresh performance.
• Uses the source database's processing capabilities.
• Can make large data preparation workflows more efficient.

15.5 How to Check Query Folding

1. Open Power Query Editor.
2. Select a step in the Applied Steps pane.
3. Where supported, check the step's context menu for View Native Query.
4. Review whether Power Query can show the SQL statement generated for the step.
5. Use additional diagnostics if the folding behavior is unclear.

The availability of View Native Query depends on the connector and transformation. Its absence does not always prove that no folding occurred.

16. Working with SQL Data Types

SQL Server supports multiple data types. Choosing and understanding data types is important because Power BI needs to interpret dates, numbers, text, and other values correctly.

16.1 Why Data Types Matter in Power BI

• Dates must be recognized correctly for time-based analysis.
• Numeric values should use suitable numeric types.
• Text identifiers should not be treated as quantities.
• Decimal precision matters for financial calculations.
• Inconsistent data types can cause errors during joins, filters, and transformations.

16.2 Handling NULL Values

SQL NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the text "NULL". Use IS NULL and IS NOT NULL to check for NULL values.

SELECT
    CustomerID,
    CustomerName,
    City
FROM Customers
WHERE City IS NULL;

This query returns customers whose City value is NULL.

For numeric calculations, determine whether missing values should remain unknown, be excluded, or be handled using an explicit business rule. Avoid replacing NULL with zero unless that interpretation is correct.

17. SQL Views and Stored Procedures in Power BI

17.1 What Is a SQL View?

A view is a database object that exposes the result of a saved query. It can simplify reporting by presenting selected columns, joining tables, or defining reusable query logic.

For example, a database administrator might create a view named vw_SalesSummary that exposes sales data in a reporting-friendly structure.

SELECT
    OrderDate,
    CustomerID,
    Amount
FROM dbo.vw_SalesSummary;

Power BI can connect to the view in the same general way it connects to a table, provided the user has the necessary permissions.

17.2 Advantages of Views

• Centralize reusable query logic.
• Present a consistent structure to reporting tools.
• Limit exposure to unnecessary columns.
• Help separate reporting logic from the underlying table layout.
• Allow database administrators to manage access to approved reporting objects.

17.3 What Is a Stored Procedure?

A stored procedure is a named routine stored in SQL Server. It can accept parameters and execute SQL statements or other database operations.

Stored procedures are not interchangeable with ordinary SELECT queries in every Power BI connection scenario. Support and behavior depend on the connector, storage mode, and method used to execute the procedure. For many reporting scenarios, a view or a supported native SQL SELECT statement is a simpler choice.

18. SQL Server Authentication and Data Security

Power BI must authenticate to SQL Server before retrieving protected data. The available authentication methods depend on the SQL Server environment and connector configuration.

18.1 Windows Authentication

Windows authentication uses an authorized Windows identity to access the database. It is common in organizations that manage database permissions through Windows or Active Directory-based identities.

18.2 SQL Server Authentication

SQL Server authentication uses a database username and password. The database administrator must configure the login and grant the appropriate permissions.

18.3 Organizational Account Authentication

Organizational account authentication may be available when the SQL Server environment supports the relevant Microsoft Entra ID authentication method. It is not automatically supported by every SQL Server installation.

18.4 Security Best Practices

• Use an authorized account with only the permissions required for reporting.
• Prefer read-only access for reports that only retrieve data.
• Never place passwords or sensitive credentials directly inside SQL statements or shared M code.
• Use encrypted connections where supported and configured.
• Review the permissions required by native SQL queries.
• Do not execute unfamiliar queries without understanding their effects.
• Keep credentials and data source permissions properly managed.
• For Power BI Service refreshes involving on-premises SQL Server, configure an appropriate gateway and credentials.

19. Refreshing SQL Server Data in Power BI

When using Import mode, Power BI stores a copy of the retrieved data in the semantic model. A refresh retrieves data again from the configured source so the model can reflect changes made in SQL Server.

19.1 Refresh in Power BI Desktop

1. Open the Power BI report.
2. Select Refresh.
3. Allow Power BI to execute the relevant source queries.
4. Review the refresh status and any error messages.
5. Confirm that the report displays the expected updated information.

19.2 Scheduled Refresh in Power BI Service

After publishing a report, configure the semantic model's data source credentials and refresh settings in Power BI Service. For an on-premises SQL Server, an appropriately configured on-premises data gateway is generally required for cloud refresh.

19.3 Refresh Best Practices

• Use appropriate SQL filters to avoid retrieving unnecessary historical data.
• Keep source queries and data transformations efficient.
• Check gateway connectivity and credentials.
• Review refresh failures and their error messages.
• Use incremental refresh when the dataset and source configuration support it and the scenario benefits from it.

20. SQL Query Optimization for Power BI

Efficient SQL queries can improve data retrieval and reduce unnecessary work. Query performance depends on database design, indexes, filters, joins, data volume, execution plans, and server workload.

20.1 Select Only Required Columns

Avoid retrieving every column when only a few are needed.

SELECT
    OrderID,
    OrderDate,
    Amount
FROM Orders;

20.2 Filter Rows Appropriately

SELECT
    OrderID,
    OrderDate,
    Amount
FROM Orders
WHERE OrderDate >= '2026-01-01';

Filtering at the source can reduce the volume of returned data when the query executes as intended.

20.3 Avoid Unnecessary Functions on Filtered Columns

Applying a function to an indexed column in a filter can sometimes prevent an efficient index seek. The optimizer may still choose a good plan in some cases, but range-based predicates are often preferable when they express the same business rule.

For example, a half-open date range is often preferable to filtering on YEAR(OrderDate) when selecting a particular calendar year.

SELECT
    OrderID,
    OrderDate,
    Amount
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

20.4 Use Appropriate Joins

Join tables using the correct keys and verify that the join does not unintentionally multiply rows. Unexpected duplicates can affect both performance and report totals.

20.5 Review Indexes with Database Administrators

Indexes may improve queries that frequently filter, join, or sort on particular columns. However, indexes also consume storage and can add overhead to data modifications. Index design should be based on actual workload and execution-plan evidence.

20.6 Avoid Unnecessary DISTINCT Operations

DISTINCT removes duplicate result rows, but it may require additional processing. Use it only when duplicate elimination is required by the reporting logic, not as a substitute for understanding an incorrect join.

20.7 Examine Query Execution Plans

SQL Server execution plans help database developers understand how a query is processed. They can reveal expensive scans, joins, sorts, and other operations. Database administrators can use these plans alongside query-duration and resource metrics to diagnose slow source queries.

21. Common SQL Server Connection Errors

22. Troubleshooting SQL Query Problems

22.1 Query Returns No Rows

• Check whether the table contains data.
• Review the WHERE conditions.
• Check the date range and data types.
• Verify that the correct database and schema are being queried.

22.2 Query Returns Duplicate Rows

• Inspect the join conditions.
• Check whether the joined tables have multiple matching records.
• Verify the intended relationship between the tables.
• Use aggregation only when it matches the required business logic.

22.3 Power BI Shows Unexpected Totals

• Compare the SQL result with the Power BI model data.
• Check for duplicate records created by joins.
• Review relationships and filter directions.
• Verify whether the SQL query aggregates data at a different level than the visual.
• Inspect NULL handling and data type conversions.

22.4 Native Query Is Slow

• Run the query in an approved SQL query tool to measure source behavior.
• Check the execution plan and database workload.
• Review filtering and join conditions.
• Reduce unnecessary output columns.
• Investigate whether query folding applies to subsequent Power Query transformations.

23. Practical Exercise: Connect SQL Server to Power BI

Objective: Connect Power BI Desktop to SQL Server and create a report using a custom SQL query.

Exercise Requirements

1. Obtain access to an approved SQL Server database.
2. Identify the server name, database name, and required credentials.
3. Connect through Get data and SQL Server database.
4. Choose Import mode for this exercise.
5. Enter a SELECT query in Advanced options.
6. Retrieve order records from the required date range.
7. Load the results into Power BI.
8. Check the data types and column names in Power Query.
9. Create a total sales measure and a monthly sales chart.
10. Refresh the report and verify the results.

Practice Query

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Amount
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01'
ORDER BY OrderDate;

Use this query only when the example table and columns exist in the database. Otherwise, adapt it to your actual schema.

24. Mini Project: Sales Analysis Using SQL Server and Power BI

Project Objective: Build a sales analysis report by retrieving, filtering, and summarizing relational data from SQL Server.

24.1 Project Dataset

Use a database with tables such as Customers, Orders, Products, and OrderDetails. Ensure that the relevant primary and foreign keys are defined or otherwise understood.

24.2 Project Tasks

1. Connect Power BI to SQL Server.
2. Retrieve customer and order data using SQL queries.
3. Filter orders by a specified reporting period.
4. Join customer records with order records.
5. Calculate total sales by customer and city.
6. Identify the top five customers by sales.
7. Load the query results into Power BI.
8. Create cards for total sales and order count.
9. Create a monthly sales trend chart.
10. Create a table showing customer-level sales.
11. Validate the report totals against the SQL results.
12. Document the connection method and query optimization decisions.

24.3 Expected Outcome

At the end of the project, you should have a working Power BI report connected to SQL Server, a set of understandable SQL queries, validated business metrics, and a clear explanation of how data moves from the database into the report.

25. Interview Questions and Answers

Q1. What is SQL Server?

Answer: SQL Server is a relational database management system from Microsoft. It stores structured data and supports SQL queries for retrieving, updating, and managing information.

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

Answer: Open Power BI Desktop, select Get data, choose SQL Server database, enter the server and optional database name, select a storage mode, authenticate, and choose the required tables or query results.

Q3. What is the difference between Import and DirectQuery?

Answer: Import loads data into the Power BI semantic model, while DirectQuery sends supported queries to the source as report interactions require. They differ in performance characteristics, data freshness, and source workload.

Q4. What is a native SQL query in Power BI?

Answer: A native SQL query is a SQL statement entered through a supported database connector to retrieve data directly from the source.

Q5. What is the purpose of the WHERE clause?

Answer: WHERE filters individual rows according to specified conditions.

Q6. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping, while HAVING filters groups after GROUP BY has been applied.

Q7. What is the difference between INNER JOIN and LEFT JOIN?

Answer: INNER JOIN returns matching records from both tables. LEFT JOIN returns every record from the left table and matching records from the right table.

Q8. What is query folding?

Answer: Query folding is the process through which Power Query translates supported transformation steps into source queries so the source database can perform some of the processing.

Q9. Why should you avoid SELECT * in production queries?

Answer: Selecting every column can retrieve unnecessary data and make queries less maintainable. Explicitly listing required columns improves clarity and may reduce data transfer.

Q10. What is the purpose of GROUP BY?

Answer: GROUP BY groups rows based on selected columns so aggregate functions can calculate values for each group.

Q11. How do you troubleshoot a failed SQL Server connection?

Answer: Verify the server and database names, network connectivity, authentication method, credentials, permissions, and encryption settings. Review the exact error message for further diagnosis.

Q12. How can SQL queries improve Power BI performance?

Answer: Properly designed queries can reduce unnecessary rows and columns, filter data at the source, and combine or aggregate data efficiently. Actual performance depends on query execution, the database, and the Power BI model.

Q13. What is the difference between a SQL view and a table?

Answer: A table stores data, while a conventional view exposes the result of a saved query. The performance of a view depends on its definition and the database execution plan.

Q14. Why is data type validation important?

Answer: Correct data types help ensure accurate filtering, joining, sorting, calculations, and date analysis in Power BI.

Q15. What is the purpose of a database index?

Answer: An index can help SQL Server locate and retrieve records more efficiently for suitable queries. Indexes must be designed according to actual workload and can introduce storage and write overhead.

26. Quick Revision Checklist

• Understand SQL Server databases, tables, columns, keys, and views.
• Know the connection requirements before opening Power BI.
• Connect using Get data and SQL Server database.
• Understand Import and DirectQuery storage modes.
• Write SELECT queries to retrieve specific columns.
• Use WHERE, AND, OR, IN, BETWEEN, and LIKE for filtering.
• Use ORDER BY to sort results.
• Use SUM, AVG, COUNT, MIN, and MAX to summarize values.
• Use GROUP BY and HAVING for grouped analysis.
• Use appropriate JOIN operations to combine related tables.
• Understand native SQL queries and query folding.
• Check data types, NULL values, and duplicate records.
• Follow database security and credential-management practices.
• Investigate query performance and connection errors systematically.
• Validate SQL results against Power BI report calculations.

27. Additional Learning Resources

• Microsoft Learn: SQL Server Connector for Power Query
• Microsoft Learn: Import Data Using Native Database Queries
• JustAcademy: Microsoft Power BI Training
• JustAcademy: Register for a Course Demo
• JustAcademy Official Website

28. Conclusion

Connecting Power BI to SQL Server and writing SQL queries are important skills for building reliable business intelligence solutions. SQL allows analysts to retrieve the right data, filter records, join related tables, and calculate summaries before using Power BI to model and visualize the results.

A successful workflow combines secure database connectivity, appropriate storage-mode selection, well-written SQL statements, efficient query execution, and accurate data modeling. Understanding the relationship between SQL, Power Query, and the Power BI semantic model helps developers build reports that are easier to maintain and more responsive.

To develop these skills further, explore Microsoft Power BI Training at JustAcademy and register for a course demo.