562 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 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.
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.
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.
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.
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.
|Parts of this blog|
25 Aytoun Street