Printed 2017/07/24 01:23AM

Tabular query modes: DirectQuery vs In-Memory


When using the Tabular model in SSAS, the deployment options screen offers four choices for “Query Mode”: DirectQuery, DirectQuery with In-Memory, In-Memory, In-Memory with DirectQuery.  Query Mode is used to specify the source from which query results are returned when you deploy the BISM project to the SSAS tabular model server.  Here is a description of each, with the benefits and drawbacks:

In-Memory: This is the default.  The data in the tabular model is processed and compressed using the xVelocity in-memory analytics engine (formerly called VertiPaq).  This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data.  It provides fast query times for aggregation queries.  However, there are some drawbacks:

DirectQuery: This mode uses relational data that is stored in a SQL Server database (it is similar to the ROLAP mode in a multidimensional model).  It lets users retrieve data directly from a SQL Server data source in real-time.  Any DAX queries on the data are translated by Analysis Services into equivalent SQL statements against the specified relational data source.  To create a model in DirectQuery mode, you must first change the design-time environment so that it supports the user of DirectQuery mode (see Enable DirectQuery Design Mode).  There are some additional benefits:

Be aware there are some design considerations if you are planning to use DirectQuery mode:

In-Memory with DirectQuery: This is a hybrid mode.  By default, queries should be answered by using the In-Memory mode, however, the connection string from the client can instead choose to use the DirectQuery mode.

DirectQuery with In-Memory: This is a hybrid mode.  By default, queries should be answered by using the DirectQuery mode, however, the connection string from the client can instead choose to use the In-Memory mode.

A hybrid mode provides you with many options:

Note that queries on a model deployed in DirectQuery mode can return different results than when the same model is deployed in-memory, because data is fetched directly from a relational data store and aggregations required by formulas are performed using the relevant relational engine, rather than using the xVelocity in-memory analytics engine for storage and calculation.  For example, there are differences in the way that certain relational data stores handle numeric values, dates, nulls, and so forth.

Also note that after a project is deployed you can change the preferred query data source for a DirectQuery model by changing a property in SSMS.  This is done by right clicking on the deployed database in SSMS and selecting properties and you will see a property called DirectQueryMode that is used to make the change.  See Set or Change the Preferred Connection Method for DirectQuery.

In the end, your preference when data modeling should be to use the in-memory cache for your tabular model.  DirectQuery is a better choice when you are using Power View and your scenario requires one or more of the benefits offered by DirectQuery.

More info:

Deployment Options for BISM Project

SSAS Tabular Model – Query Modes

Formula Compatibility in DirectQuery Mode

DirectQuery Mode (SSAS Tabular)

Difference between DirectQuery and In-memory mode in tabular mode analysis services

Upcoming DirectQuery vs Vertipaq Presentation (slides)

DirectQuery 101

Tabular Projects – DirectQueryMode and QueryMode

Using DirectQuery in the Tabular BI Semantic Model

Hybrid Mode in Tabular BI Semantic Model – Part 1, Part 2

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.