SSAS - Query mode - Hybrid in 2019

Question

Hi, I'm currently working on a tabular model project and I've been reading about the two modes for query mode, Direct Query and In-Memory.

In the 2012 version for VS there's a option to deploy the solution in Hybrid mode (compatibility 1100 i think), but since the 1103 version there's no longer available. I'm working right now with the latest version for SQL Server, (2019) and VS 2019. My team is currently working for having the solution deployed to the In-Memory option, we have almost 124GB of RAM and we calculated that is enough for the DW.

My concern is about the RAM available in the future and if is possible to have partitions working with both modes (like hybrid mode in 2012), for example partitions for previous years working with InMemory and the current partition (2020 right now) with DirectQuery, is that possible??, that would mean that i would only have to refresh the older partitions once a year or once a month (depending on the requirements), or viceversa, the older partitions in DirectQuery and the current partition with InMemory.

Right now we don't need Real Time acces for this solution.

Thanks in advance!.

Answer 1

Hi Lukas

Thanks for the answer, so that would mean that i must deploy the project under DirectQuery mode and those partitions with the "sample" prefix would be housed in the RAM, I'm going to make some test and see how that goes for the reporting and the power bi, and the refreshing of course.

Can i ask another thing related to this topic, when i have the solution deployed, in the SSMS, within the Model properties i can see that there is an option "Default Mode", that has three options, Direct Query, Import, and Dual, do i need to change to Dual??

I'm still currently reading and working this out with some testing.

Regards,

Answer 2

Hi,

I am sorry, I made a mistake above.

As I did some research later , the sample partition is something other to optimize the performance of DirectQuery. It is not used to mix DirectQuery with In-memory mode. Adding sample data to a DirectQuery model project. I've mistaken it with the older hybrid mode partitioning methods.
Sorry for this misguidance.

Since SSAS 2017 tabular model is not support mixed mode partitions. The whole model can operate in DirectQuery or In-Memory. It is suggested by other developer for this scene we'd better have two databases. Check this thread: SSAS Tabular QueryMode Confusion. Why Hybrid mode is useful?

If you really want hybrid, I suggest you submit the requirement at https://feedback.azure.com/forums/908035-sql-server.
If the requirement mentioned by customers for many times, the product team may consider to add this feature in the next SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The whole hybrid mode feature was only ever a design time option. You have never been able to deploy a mixture of Direct Query and In-Memory partitions.

The idea behind this feature was that if you had a large direct query model and it was slow to develop against you could do development against a local in-memory version then deploy to a direct query model in production. Or if your in-memory model was too large or took too long to process you could develop using a direct query version then deploy to in-memory in production. But either way the whole model was always in a single mode. There is not currently and has never been an option to have different partitions in different direct query vs in-memory modes.

Power BI has an aggregations feature where you can have an direct query base table and then have an in-memory aggregation table which can be used to answer queries and I think this can possibly be filtered, but it's a Power BI only feature at the moment and is not available in SSAS or Azure AS.

If you have enough RAM and don't need realtime then you are best to keep everything in-memory