How to use DirectQuery to run queries in SSAS Tabular against SQL Server tables
Part three of a three-part series of blogs

DirectQuery mode allows you to run queries in Analysis Services (Tabular) directly against the underlying SQL Server tables imported into the model. This blog explains what DirectQuery is, shows how to enable it and gives an idea of its pros and cons.

  1. DirectQuery within SSAS Tabular
  2. Enabling and using DirectQuery
  3. Deploying models using DirectQuery mode (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 22 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Deploying models using DirectQuery mode

If you try to deploy a model using DirectQuery mode without further change,, you'll get the following error message:

Can't deploy DirectQuery project

The error message you'll see.

This is because you need to change your deployment method first, as shown below.

Changing the deployment method

To allow deployment of your DirectQuery enabled model, change your project's properties:

Project properties

Right-click on your project and choose to change is properties.

Now change the Query Mode using the dropdown shown below:

Query Mode property

See below for what the 4 different options mean.

The options aren't exactly self-explanatory!  What is the difference between "Bread and butter" and "butter and bread"?

The 4 possible query modes for deployment

The possible query mode values that you can use are as follows:

Query Mode Notes
DirectQuery Tables in your model are not loaded in memory, and all DAX queries are converted into SQL queries. If you use this mode, no data is processed.
DirectQuery with In-Memory Tables in the deployed model will be processed and stored in xVelocity, but all DAX queries will be converted into SQL by default.
In-Memory Every table in your model is processed and stored in xVelocity.
In-Memory with DirectQuery Tables in the deployed model will be processed and stored in xVelocity, and all DAX queries will run against the xVelocity engine by default.

Switching query mode after deployment

The point of the two hybrid modes is that you can change the connection string for accessing the underlying data (and thus change the default data access mode) as follows:

What you want to do Text to add to connection string
To run DAX queries in memory when in DirectQuery with In-Memory mode DirectQueryMode= DirectQuery
To convert DAX queries to SQL to run against the underlying data source when in In-Memory with DirectQuery mode DirectQueryMode= InMemory

There are more considerations when using DirectQuery, such as security impersonation modes and partition processing options.  However, this blog is designed to explain what DirectQuery is (to give you an idea of whether you should be using it), rather than to give a detailed technical guide for how to use it.

  1. DirectQuery within SSAS Tabular
  2. Enabling and using DirectQuery
  3. Deploying models using DirectQuery mode (this blog)
This blog has 0 threads Add post