560 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 one 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 explains what DirectQuery is, and why and how you might want to use it within Analysis Services.
Disclaimer: although I understand what DirectQuery is and does, I can't see the point of it. You should bear this in mind while you read this blog!
Let's begin with how any normal query runs in Analysis Services:
It's a simplification, but this diagram shows that DAX queries don't run against the underlying SQL Server database tables (indeed, that's kind of the point of Analysis Services).
This approach has two possible limitations:
|No real-time reporting||When data in the underlying SQL Server tables changes, these tables must be reprocessed before DAX can query them from the SSAS database. It's thus impossible to get real-time data using DAX for normal tabular models.|
|Possible memory limitations||Because xVelocity relies on storing all data in-memory, if your computer is short of memory you may run into paging issues and queries may start running slowly.|
Using DirectQuery you still have an intermediate model, but DAX queries that you write are translated into SQL and run directly against the underlying SQL Server tables:
Thus any DAX query uses the tabular model for its logic, but always goes back to the original data source for its data.
You may now be thinking, "What's the point of having the intermediate model? Why not run SQL directly against the underlying tables?". Unless you want to have a business intelligence semantic model (BISM) sitting between you and your database, the probable answers are "Not much" and "Indeed".
If you plan to use DirectQuery mode, you should be aware of its limitations:
|No MDX||You can only use DirectQuery mode with DAX (MDX isn't supported).|
|Missing DAX functions||You can see a list of the DAX functions which work differently (or not at all) for DirectQuery here (they're mostly date functions).|
|No calculated columns||You can't include calculated columns in a model if you're using DirectQuery (Analysis Services will stop you enabling DirectQuery mode if you have included calculated columns in your model).|
|SQL Server only||Because DirectQuery must compose SQL queries against the underlying data tables, your model must be based solely on SQL Server database tables.|
OK, now that I've explained what DirectQuery is (and the pros and cons of using it), it's time to see how to enable DirectQuery mode.
|Parts of this blog|
25 Aytoun Street