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 (this blog)
- Deploying models using DirectQuery mode
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:
Right-click on your model and choose to change its properties.
Now just change DirectQuery Mode to On!
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:
Click on Details to see a bit more about the problem.
Choose to display the Error List (here's one way to do this):
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:
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:
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:
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.