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
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 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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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 |
---|
|
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.