560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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 possible query mode values that you can use are as follows:
|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.|
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.
|Parts of this blog|
25 Aytoun Street