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:
Next you can edit the table partitions if you don’t like the defaults:
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.
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!
