Introducing DirectQuery Support for Tabular 1400

With the production release of SSDT 17.0, Tabular projects now support DirectQuery mode at the 1400 compatibility level, so you can tap into large data sets that exceed the available memory on the server and meet data freshness requirements that would otherwise be difficult if not impossible to achieve in Import mode. As with Tabular 1200 models, DirectQuery 1400-supported data sources include SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Oracle, and Teradata, as the following screenshot indicates, and you can only define a single data source per model. Available DAX functions are also limited, as documented in “DAX Formula Compatibility in DirectQuery Mode.” Another important restriction pertains to the M queries that you can create in DirectQuery mode.

Given that Analysis Services must transform all DAX and MDX client queries into source queries to send them to the source where the data resides, M transformations must be foldable. A foldable transformation is a transformation that the Mashup engine can translate (or fold) into the query dialect of the source, such as T-SQL for SQL Server or PL/SQL for Oracle. You can use the View Native Query option in the Query Builder dialog to verify that the transformation you create is foldable. If the option is available and can display a native query, the transformation meets the DirectQuery requirements (see the following screenshot).

On the other hand, if the option is unavailable and a warning is displayed, you must remove the problematic step because it does not meet the DirectQuery requirements. If you attempt to create a table based on an unsupported M query, SSDT Tabular will display an error message asking you to redefine the query or switch the model into Import mode, as the following screenshot illustrates.

The DirectQuery experience in SSDT Tabular is similar to Power BI Desktop, but there are some noteworthy differences. For example, in Power BI Desktop, you can switch individual connections into DirectQuery mode whereas SSDT Tabular enables DirectQuery only on a per-model basis, as the following screenshot illustrates with the Power BI Desktop dialog in the background and SSDT Tabular Solution Explorer and Properties window in the front. Mixing Import and DirectQuery mode data sources is not supported in a Tabular model because, in DirectQuery mode, a model can only have a single data source. Also, Power BI Desktop supports Live mode against Analysis Services, which Tabular models do not support.

Another issue worth mentioning is that there currently is no data preview for tables defined in the model. The preview in Query Editor works just fine, but when you apply the changes by clicking Import, the resulting table in the model remains empty because models in DirectQuery mode do not contain any data as all queries are directed to the source. Usually, you can work around this issue by adding a sample partition, as the article “Add sample data to a DirectQuery model in Design Mode” (/en-us/sql/analysis-services/tabular-models/add-sample-data-to-a-directquery-model-in-design-mode) describes, but sample partitions are not yet supported in 1400 mode. This will be completed in a future SSDT Tabular release.

Moreover, SSDT Tabular, running inside Visual Studio, requires 32-bit drivers, while the SSAS engine runs as a 64-bit process and requires the 64-bit versions. This is particularly an issue when connecting to Oracle. Make sure you install the drivers per the following requirements.

And that’s it for a quick introduction of DirectQuery support for Tabular 1400. Please take it for a test drive and send us your feedback and suggestions via ProBIToolsFeedback or SSASPrev at Microsoft.com. Or use any other available communication channels such as UserVoice or MSDN forums. You can influence the evolution of the Analysis Services connectivity stack to the benefit of all our customers.