When you create Power BI reports, the viewers expect the report to be refreshed periodically and the data to be constantly updated. How? Manually updating the reports works, but Power Bi Direct Query offers a much better feature, scheduled refresh.

In this tutorial, you will learn how to take advantage of Power Bi Direct Query functionality by connecting a dataset stored in a server and running queries against your data.

Stay tuned and increase productivity while saving time in the process!

Prerequisites

This tutorial will be a hands-on demonstration. To follow along, be sure you have the following:

• Power Bi desktop - This tutorial uses Power Bi desktop version 2.109.1021.0.

• An SQL Server.

  Related: Getting Started With Microsoft SQL Server Standard Edition

• A code editor - This tutorial uses VS code version 1.71.

  Related: The Visual Studio Code Tutorial Worth Learning

• Microsoft On-premises Data Gateway.

  Related: Power BI Data Gateway : Connecting Different Data Sources

• SQL Server Management Studio (SSMS) - This tutorial uses SQL Server Management Studio 18.12.1.

  Related: Getting Started With SQL Server Management Studio (SSMS)

Creating an SQL Database to Manage with Power BI Direct Query

Power Bi Direct Query enables you to connect directly to a dataset and gives your project an option to upload live data to your project. But before making an SQL database and connection, you must know the server name and Data Source Name to acquire the correct details.

To create an SQL database, follow these steps:

1. Search for ODBC in your search bar, look for and click ODBC Data Source Administrator (64-bit) to open it.

   

   Launching ODBC Data Sources

2. Next, navigate to the System DSN tab in the ODBC Data Source Administrator window, and click Add to initiate adding a new data source.

   

   Initiating adding a new data source

3. Select the SQL Server driver from the list below, and click Finish to create an SQL Data Source.

   <aside> 💡 DirectQuery supports not every data source. MySQL is not supported, while SQL is.

   </aside>

   

   Selecting SQL Server driver

4. Now, name your data source (MssqlDataSource), select an SQL instance (POWERSERVER\POWERSERVER) installed in your machine, and click Finish.

   Take note of the SQL Server and Data Source Name, as you will need them for your Python connection string.

   

   Naming the new data source

5. Review the details of your data source, and click Test Data Source to test your data source.

   

   Testing the data source

   If the connection is good, you will get a TEST COMPLETED SUCCESSFULLY message, as shown below.

   

   Verifying testing the data source completed

Connecting to the SQL Server

Now that you have created a Data Source for your SQL server, you will use that Data source to create a connection string for your Python code.