This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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:

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:

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.

Parts of this blog
DirectQuery within SSAS Tabular (this blog) Enabling and using DirectQuery Deploying models using DirectQuery mode

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC