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.

  1. DirectQuery within SSAS Tabular (this blog)
  2. Enabling and using DirectQuery
  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.

DirectQuery within SSAS Tabular

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!

How normal queries work in SSAS Tabular (and limitations thereof)

Let's begin with how any normal query runs in Analysis Services:

Normal query steps

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:

Problem Details
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.

 

An alternative approach: using DirectQuery

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:

How DirectQuery works

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".

Limitations of DirectQuery

 If you plan to use DirectQuery mode, you should be aware of its limitations:

Limitation Notes
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. 

  1. DirectQuery within SSAS Tabular (this blog)
  2. Enabling and using DirectQuery
  3. Deploying models using DirectQuery mode
This blog has 0 threads Add post