Nisal's Business Intelligence Blog

Learn, Grow and Share on Microsoft BI & Analytics Platform

Saturday, October 28, 2017

SSAS Tabular DirectQuery Mode VS In-memory Mode. Which Mode is Suites for Your Requirement?

When we decided to implement the solution using SSAS Tabular, one of the key consideration have to do is, whether are we going to do the implementation with DirectQuery mode or in-memory(Import) mode. Because the hardware requirements are entirely different for each modes. Should I go with DirectQuery mode or stay with In-memory mode the decision totally based on your business requirement. You will realize it at the end of this post. However the good thing is, you can toggle back and forth between DirectQuery and In-memory at any time.

How to Switch Between DirectQuery and In-memory Mode

You can switch between these modes during the development in SSDT and even after the deployment using SSMS.

In SSDT Environment
  1. Right click on the Model.bim
  2. Goto Properties 
  3. Select the Property DirectQuery Mode

In Management Studio,
  1. Select the Tabular database 
  2. Right click and go to properties
  3. Select model tab and change the property DirectQuery mode. 

What is In-memory mode

This is the default configuration on your tabular database unless you force to change it to direct query mode. Same as most of the In-memory databases out there, a tabular database in In-memory or Import mode all the data reside in-memory. 

Once you processed the model all the data imported into the memory in In-memory Mode


Once you imported the data you can see it in table tabs


This will give faster query performance and better calculation speed. End user can feel better query performance in this mode when compared to the direct query mode. All the hierarchies, calculated measures, calculated tables plus data are stored in-memory. Therefore, if you are planning to use this mode you will need relatively large size of memory in your Production environment. Precisely, you will need a RAM of two or three times of your estimated database size. You don't need to invest a large storage in this mode, because all the data in-memory. 

What is DirectQuery Mode  

In DirectQuery mode its hold only the structure of your model in the tabular database and when client tool issue a query its read data from under-line source which connected to build the tabular mode. It could be a SQL Server data warehouse, Oracle OLTP database or any type of source which currently support. If you have work with SSAS Multidimensional model this architecture slightly similar to ROLAP. 



Hence in DirectQuery mode data is not store in the model when you processing it you will see row counts as 0


So, in SSDT only structure of tables will be appear with no data. 


As a result of this mode directly connect to the source to return the result set, the end user gets up-to-date fresh data. So, no need to perform additional task to update the model. This mode is suite for solutions where the data volume is larger than the available memory size. Because you are connect to the back-end data source instead query in-memory. Further, special scenarios like row level security can be address through implement in this mode. 

There are few limitations as well in this mode. One of the major limitation is you will be only able to connect to one available data source in your model where as In-memory mode you can connect to multiple sources. Not all DAX functions support as same as in-memory in this mode. There for you have to be careful when you switch the existing implementation from In-memory to direct query. You can find more limitations and support data sources and this article

If you consider about hardware you don't really need to have a large size of memory to implement a model in this mode. While you can focus on storage and CPU in order to improve the performance.

Summary
In this article I've explained the main difference between In-memory and DirectQuery mode in SSAS Tabular databases. You can still switch between these two modes at anytime, but you need to be-careful about your existing implemented calculated measures, tables using DAX. Because all the DAX functions are not supported in DirectQuery mode. DirectQuery mode is relatively slow than In-memory mode.  You can go with DirectQuery mode if your data volume is relatively greater than the configured memory.
Reference: MSDN

3 comments:

  1. In Direct Query Mode there is Once You Import the data into the model there will not be tables appear in your model as in the article. However you can create Sample Partitions which is an aid for your development.

    ReplyDelete
  2. Really good information to show through this blog. I really appreciate you for all the valuable information that you are providing us through your blog. Power BI Online Training Bangalore

    ReplyDelete
  3. Hi Nisal,
    if we use import mode and something happens to the VM machine like restart service or the machine itself - what happens to the data in the Tabular DB? Will we need to process full all the data again? what kind of backups (WITH DATA) can we do? (ABF file?)

    thank you!

    ReplyDelete