Free SQL Help for Devs, DBAs, and the Curious

Power BI Direct Query is a feature that provides a live connection to your data source, allowing you to create interactive reports and dashboards that always reflect the current state of the underlying data. This feature is especially valuable when dealing with large data volumes where importing all data into Power BI is not practical or when real-time reporting is required. In this blog post, we will be exploring how to utilize Power BI Direct Query with different SQL Server versions, MySQL 5.7, DB2 11.1, and Azure SQL.

To begin with, SQL Server 2016 and 2017, both support Power BI Direct Query. When you establish a Direct Query connection in Power BI Desktop, you simply need to choose SQL Server as your data source and provide the server name and database name. After this, you can write your SQL statement to retrieve the data. Please note that Direct Query does not support all SQL Server functions. It's advisable to refer to the Power BI documentation for a list of supported functions.

SELECT * FROM Sales.Orders WHERE OrderDate >= '2018-01-01'

For MySQL 5.7, you can also use Power BI Direct Query. However, you need to install the MySQL Connector/Net 6.9.9 or later and the MySQL for Visual Studio 1.2.7 or later before you can establish the connection. The SQL syntax for MySQL is slightly different from SQL Server, so you need to adjust your SQL statement accordingly.

SELECT * FROM sales.orders WHERE order_date >= '2018-01-01'

DB2 11.1 is another popular database system that supports Power BI Direct Query. You need to install the IBM Data Server Client or the IBM Data Server Runtime Client before you can connect to DB2. DB2 uses a different SQL dialect, so you need to adjust your SQL statement to match DB2 syntax.

SELECT * FROM SALES.ORDERS WHERE ORDERDATE >= '2018-01-01'

Last but not least, Azure SQL also supports Power BI Direct Query. The connection process is similar to SQL Server, but you need to provide the Azure SQL server name in the format of <servername>.database.windows.net. Azure SQL uses T-SQL, so the SQL syntax is the same as SQL Server.

SELECT * FROM Sales.Orders WHERE OrderDate >= '2018-01-01'

In conclusion, Power BI Direct Query provides a powerful way to create real-time reports with large data volumes. It supports various database systems including SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL. The syntax of SQL statement varies between different database systems, so you need to adjust your SQL statement to match the specific SQL dialect of your data source. As always, you should check the Power BI documentation for the most up-to-date and comprehensive information about Direct Query capabilities and limitations.