Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Microsoft Business Intelligence and Data Warehousing

Garrett Edmondson, Independent Consultant MCITP & MCTS: 10+ years experience with BI stack.

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!


Comments

Leave a comment on the original post [garrettedmondson.wordpress.com, opens in a new window]

Loading comments...