Sql-server – SSAS Tabular QueryMode Confusion. Why Hybrid mode is useful

sql serversql server 2014ssastabular-model

I read couple of articles about QueryMode in Tabular. I finally noticed that hybrid modes (DirectQuery with In-Memory and In-Memory with DirectQuery) are NOT useful for my case. Actually they are not working the way I like.

We have huge Tabular databases and we use In-Memory for all partitions and concern is we run out of memory at some point.

My thought is, if I could use DirectQuery for old partitions and In-Memory for recent partitions (newer dates), this would help us to have fast performing reports for recent dates and keep data for recent dates in RAM and leave older data which are not accessed often to use DirectQuery.

Does my thought make sense? Is there any way we use Tabular databases this way?

Thanks for sharing your thoughts.

Best Answer

You are correct that currently (up through SSAS 2017 Tabular) is is not possible to have mixed mode partitions. The whole model can operate in DirectQuery or In-Memory. The DirectQueryMode connection strong property can specify whether SSAS should use DirectQuery or In-Memory for answering the current query. But that's not honestly too much better than just deploying the model to two different databases, one DirectQuery and one In-Memory.

There is a similar connection string DataView=Sample which has SSAS answer the query using a special cached partition marked as the sample partition. marking sample partition

But this is still not what you want because a particular query won't union results from DirectQuery partitions and sample In-Memory partitions.

I will try to update this answer if a future release of SSAS adds the feature you are wanting.

Related Question