BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- DirectQuery within SSAS Tabular
- Enabling and using DirectQuery
- 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:
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:
Right-click on your project and choose to change is properties.
Now change the Query Mode using the dropdown shown below:
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.
- DirectQuery within SSAS Tabular
- Enabling and using DirectQuery
- Deploying models using DirectQuery mode (this blog)