How to use DirectQuery to run queries in SSAS Tabular against SQL Server tables
Part two 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 (this blog)
  3. Deploying models using DirectQuery mode

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.

Enabling and using DirectQuery

The best time to turn DirectQuery mode on for a model is when you first create it, as this ensures that you won't have violated any of the limitations listed in the previous part of this blog.

Enabling DirectQuery mode

To do this, first choose to change your model's properties:

Changing model properties

Right-click on your model and choose to change its properties.

Now just change DirectQuery Mode to On!

DirectQuery Mode property

Change this property. The property description at the bottom explains that this will fetch query results from the underlying data source, and not from the model's data.


You may well now get an error message, which you should handle as shown below.

Managing errors when enabling DirectQuery mode

When you choose to enable DirectQuery mode, you may well see an error message:

Error message

Click on Details to see a bit more about the problem.


Choose to display the Error List (here's one way to do this):

Showing error list

If the error list window isn't already visible, choose this menu option to display it.


It's likely that your errors will be either that you have calculated columns in your model, or that your model includes tables not in SQL Server format:

Error messages

My 3 error messages (although actually they're all saying the same thing - that I have a calculated column called MonthSortOrder in my Calendar table).

You'll need to fix these before you can enable DirectQuery mode.

Working in DirectQuery mode

You'll know you're working in DirectQuery mode, because some things look so different.  You can't create calculated columns:

No add column

There is no Add Column on the right, because you can't have calculated columns.


The import from data source dialog box is a bit limited too:

Importing from data source

There's only one choice available!


Now I've shown how to successfully enable DirectQuery mode, I'll finish this blog by showing how to deploy projects using this mode.

This blog has 0 threads Add post