Connecting to SQL in Power BI

Part 1: The Connection Process

Connecting to a SQL database is as straightforward as connecting to a file, with a few extra—but very important—options. The process starts in the familiar Get Data menu.

Once you select this, Power BI will open a dialog box asking for a few key pieces of information. Let's watch a video that walks through this entire process from start to finish.

As you saw in the video, the main steps are:

1. Specify Server: Provide the name of the SQL Server instance.
2. Specify Database (Optional): You can target a specific database on the server from the start.
3. Choose Connectivity Mode: Select either Import or DirectQuery.
4. Authenticate: Provide credentials to prove you have access.
5. Navigate and Select: Choose the tables or views you want to load.

The most critical new concept here is the Data Connectivity Mode.

• Import Mode: Power BI copies the data from the SQL database and stores a snapshot of it inside your .pbix file. This is exactly how the Excel and CSV connections worked. Reports are fast because the data is held in memory, but the data is only as fresh as your last refresh.
• DirectQuery Mode: Power BI does not copy the data. Instead, it connects directly to the SQL database. Every time you interact with a visual, Power BI sends a query to the database to fetch the live data. This ensures your report is always up-to-date, but it can be slower as it depends on the performance of the source database.

We will explore the trade-offs between these two modes in much greater detail in our next lesson. For now, it's enough to understand the fundamental difference.

Part 2: Leveraging Your SQL Skills

While importing entire tables is useful, it's often inefficient. You might only need a few columns from a very wide table, or you may need to join several tables together at the source. Since you already know SQL, you can use it to your advantage.

Power BI's SQL Server connector has an Advanced options section where you can write your own T-SQL SELECT statement. This is a powerful feature that allows you to perform data shaping and reduction before the data even enters Power BI. This is considered a best practice as it pushes the transformation workload back to the server, which is optimized for that task.

The following video demonstrates this feature very clearly.

Let's imagine you are working with a motorsport finance dataset. A table named TeamExpenses might have 50 columns, but for your current analysis, you only need TeamName, Season, ExpenseCategory, and Amount. Instead of importing all 50 columns and removing 46 of them in Power BI, you could simply write the following query in the SQL statement box:

SELECT
    TeamName,
    Season,
    ExpenseCategory,
    Amount
FROM
    dbo.TeamExpenses
WHERE
    Season = 2023;

This approach is not only cleaner but also significantly improves the performance of your Power BI report by reducing the amount of data that needs to be imported and processed.

For a more detailed written guide on this topic, the following article provides another excellent example of using a SELECT statement to retrieve data.

Part 3: Connection Best Practices

Connecting to enterprise databases involves considerations beyond just getting the data in, such as security and performance. It's helpful to be aware of these from the start.

Conclusion

Well done! You have now expanded your data connection skills from simple flat files to enterprise-level databases. You've also seen how your existing SQL knowledge can be a significant asset in building efficient and high-performing Power BI reports.

Key Takeaways:

• You can connect to a SQL Server database using the Get Data menu in Power BI.
• The Import mode copies data into Power BI for fast performance, while DirectQuery mode connects to the live data at the source.
• You can leverage your SQL skills by writing a custom SELECT statement in the "Advanced options" to pre-filter and shape data, which is a key performance optimization technique.

Preview of the Next Lesson:
In this lesson, we introduced the Import and DirectQuery modes. In our next session, we will perform a deep dive into this topic. You will learn the specific pros and cons of each mode, the limitations of DirectQuery, and how to make a strategic decision about which one is right for your project's specific requirements.