SSAS Tabular Model – Query Modes

One of the most compelling features of the new SSAS Tabular Model is the ability to set the model’s Query Mode to In-memory (aka Vertipaq), DirectQuery or both!

QueryMode can be set to one of the following:

  • DirectQuery – This setting specifies all queries to the model should use the relational data source only.
  • DirectQuery with In-Memory – This setting specifies, by default, queries should be answered by using the relational source, unless otherwise specified in the connection string from the client.
  • In-Memory – This setting specifies queries should be answered by using the cache only.
  • In-Memory with DirectQuery – This setting specifies, by default. queries should be answered by using the cache, unless otherwise specified in the connection string from the client.

The hybrid modes give the best of both In-memory and DirectQuery but there are a few gotchas. One of the hybrid or DirectQuery modes must be set in 2 places.

Configuration: Set Query Mode at Project level:

image

Next you can edit the table partitions if you don’t like the defaults:

image

Some DAX functions and calculated columns are not yet supported in DirectQuery Mode. If You try to deploy/process the model you will get a errors explaining which objects need to be removed.

image

In the above example  the calculated column needs to be deleted before DirectQuery Mode can be enabled.

Summary:

Configuring Tabular model to use one of the Hybrid Query Modes will allow it to scale past the memory limitation of the server and when combined with Columnstore indexes in SQL server 2012 provides a compelling reason to use a Tabular Model for enterprise BI solutions. Although there are currently some limitations I would expect them to be resolved in the future.

Additionally making Columnstore indexes available in the next version of Microsoft’s Parallel Data Warehouse would be a huge performance boost to an already very powerful appliance. The next logical step would be to simply put a Tabular Model on top of it. All this is just dream right now but won’t it be nice!

About these ads

Tags: ,

4 Responses to “SSAS Tabular Model – Query Modes”

  1. SQL Server “Denali”: Details on the next version of SSAS | James Serra's Blog Says:

    [...] SSAS Tabular Model – Query Modes Share this:EmailPrintFacebookShareDiggRedditStumbleUpon [...]

  2. Cathy Dumas Says:

    You don’t need to manually configure a DirectQuery partition. SSDT automatically sets the first partition in each table to be the DirectQuery partition. If you only have one partition per table (as you would for a DirectQuery only model) then changing the DirectQuery partition is not necessary.

  3. Tabular query modes: DirectQuery vs In-Memory | James Serra's Blog Says:

    [...] SSAS Tabular Model – Query Modes [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: