This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
DirectQuery within SSAS Tabular Enabling and using DirectQuery Deploying models using DirectQuery mode (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Deploying models using DirectQuery mode

Changing the deployment method

The 4 possible query modes for deployment

Switching query mode after deployment

Head office

London

Manchester