DirectQuery

Many of you have likely been trying to envision how you may implement these data imports in your environment. You may ask yourself questions such as the following:

• If data imported into Power BI uses an in-memory technology, did my company provide me a machine that has enough memory to handle this?
• Am I really going to import my source table with tens of billions of rows into memory?
• How do I handle a requirement of displaying results in real time from the source?

These are all excellent questions that would have many negative answers if the only way to connect to your data was by importing your source into Power BI. Fortunately, there is another way. Using DirectQuery, Power BI allows you to connect directly to a data source so that no data is imported or copied into the Power BI Desktop.

Why is this a good thing? Consider the questions that were asked at the beginning of this section. Since no data is imported to the Power BI Desktop, that means it is less important how powerful your personal laptop is because all query results are now processed on the source server instead of your laptop. It also means that there is no need to refresh the results in Power BI because any reports you design are always pointing to a live version of the data source. That's a huge benefit!

Enabling this feature can be done by simply selecting DirectQuery during the configuration of a data source. The following screenshot shows a connection to an SQL Server database with the DirectQuery option selected:

Earlier in this chapter, the Data Gateway application was mentioned as a requirement to schedule data refreshes for sources that used the import option. This same application is also needed with DirectQuery if your data is an on-premises source. Even though there is no scheduled data refresh, the Data Gateway is still required to push on-premises data into the cloud. Again, this will be discussed in more depth in Chapter 7, Using a Cloud Deployment with the Power BI Service.

Limitations

So, if DirectQuery is so great, why not choose it every time? Well, with every great feature you will also find limitations. The first glaring limitation is that not all data sources support DirectQuery. As of the time this book was written, the following data sources support DirectQuery in Power BI:

• Amazon Redshift
• Azure HDInsight Spark
• Azure SQL Database
• Azure SQL Data Warehouse
• Google BigQuery
• IBM Netezza
• Impala (Version 2.x)
• Oracle Database (Version 12 and above)
• SAP Business Warehouse Application Server
• SAP Business Warehouse Message Server
• SAP HANA
• Snowflake
• Spark (Version 0.9 and above)
• SQL Server
• Teradata Database
• Vertica

Depending on the data source you choose, there is a chance of slower query performance when using DirectQuery compared to the default data import option. Keep in mind that when the import option is selected it leverages a highly sophisticated in-memory storage engine. When selecting DirectQuery, performance will depend on the source type you have chosen from the list above.

Another limitation worth noting is that not all Power BI features are supported when you choose DirectQuery. For example, depending on the selected source, some the Power Query Editor features are disabled and could result in the following message: This step results in a query that is not supported in DirectQuery mode. Another example is that some DAX functions are unavailable when using DirectQuery. For instance, several Time Intelligence functions such as TotalYTD would generate the following type error when using DirectQuery:

The reason for this limitation is because DirectQuery automatically attempts to convert DAX functions such as this one to a query in the data source's native language. So, if the source of this solution was SQL Server, then Power BI would attempt to convert this DAX function into a comparable T-SQL script. Once Power BI realizes the DAX function used is not compatible with the source, the error is generated.

You can turn on functions that DirectQuery blocks by going to File | Options and settings | Options | DirectQuery | Allow unstricted measures in DirectQuery Mode. When this option is selected, any DAX expressions that are valid for a measure can be used. However, you should know that selecting this can result in very slow query performance when these blocked functions are used.