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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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:
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. |
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.