How To Connect Azure SQL Data With Power BI Via Direct Query

In simple words, Power BI connects data directly from the source repository via designed queries or APIs, known as DirectQuery.

Here I am going to talk about, how to connect Azure SQL Data with Power BI via Direct Query.

Let's talk about the best practice to use Import Data Vs Direct Query,

• Import data to Power BI takes advantage of the high performance and provides a highly interactive featured experience. Data become local to Power BI.
• Direct Query where data is changing frequently and reports must reflect the latest data, DirectQuery may be the best fit. Import data can't be met with the objective.

There are some scenarios when the direct query is useful.

	Direct Query	Import Data
Data Changes	Whenever there is a demand to show the latest data where continuous changes occur very frequently. for example, the Covid report which shows data from WHO. DirectQuery open or refresh a report or dashboard always shows the latest data in the source	There are limits on the data volumes. Import data with scheduled refresh might not meet those needs.
Large Volume of Data	DirectQuery requires no large transfer of data because it's queried in place.	If the data is very large, it wouldn't be feasible to import it all.

Step 1

Browse PowerBI desktop. Select Get Data -> SQL Server Database,

Step 2 - Add SQL Server database credential and Direct Query.

In this example, I have taken the Azure SQL server to connect via a direct query to Power BI.

1. Add Azure SQL Server Name
2. Add Database Name
3. Select Direct Query Option
4. Add SQL Statement as query

SELECT [EmployeeName]
 ,[EmployeeCompetency]
 ,[Location]
 ,[Langauge]
 ,[RegisteredforCourse]
 ,[CourseName]
 ,[RegisteredDate]
 ,[CompletedDate]
 ,[Category]
 ,[CompletionStatus]
 FROM [dbo].[CourseCompletionDetail] where CompletionStatus ='Yes'

Step 3 - Enter Credentials -> Select Database and Click Save to continue

In this example, I used SQL Server credentials, We have multiple options to login i.e. Window Credentials, Database Credentials, Microsoft Account Credentials.

Step 4 - Query View

Direct query view has only two options i.e. dashboard and query view. There is no table view.

Step 4 - Power BI Dashboard

Advantage of Direct Query, PowerBI dashboard will reflect all changed data with every refresh whereas Import data need a scheduler to update the dataset after the defined interval.

More reference details can be found here.

Hope you enjoyed and learned something new in the article, stay tuned for more tips and tricks with PowerBI.