actual process of working on data in SSAS-Tabular Model with in-memory and directquyer mode

Question

Hello There,

I have been stuck understanding the difference between in-memory mode and directquery mode in SSAS tabular mode.

This happened when I tried to understand what actually happens when building the models in tabular mode?

In building the models, when and why would data be stored in-memory cache and

if queried, what are the objectives of querying? Querying to test the models built?

I have these questions put to myself in trying to understand the DirectQuery documentation (the link of the documentation is attached to this post)

Would anyone please help me get the needed clarity?

Thank you for giving your valuable time

Answer 1

Hi @ArunRaaman ,

Below information is quote from a book SQL Server 2008 Analysis Services Step by Step:

Analysis Services can use natural hierarchies two ways to improve query performance . First, when you execute a query, Analysis Services checks to see if the needed aggregate values have been stored in memory cache . If not, Analysis Services will look in memory for the values of descendants that can be aggregated . Even if you don’t create aggregations, you should still create attribute relationships so that Analysis Services can take advantage of descendant values stored in memory cache . Second, Analysis Services can use natural hierar-chies to design aggregations that are stored on disk. Retrieving an aggregate value from disk (or retrieving and aggregating the aggregate values of members of a lower level of a natural hierarchy) is faster than retrieving and aggregating the lowest level of detail from disk.

The data that Analysis Services needs to respond to a query can exist in one of three storage locations, also called caches. Analysis Services can respond to a query most quickly if the data exists in memory . If the data is not in memory, Analysis Services can use the second-quickest option and retrieve the data from MOLAP disk storage . If the data is not available from memory or MOLAP storage, the slowest option is to retrieve the data from ROLAP storage—that is, Analysis Services has to query the source relational database.

Best regards,
Carrin

---

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 2

Hi @ArunRaaman ,

Welcome to Microsoft Q&A!

Please read this blog that can help you to understand In-Memory Mode and DirectQuery Mode in Tabular model.

In-Memory Mode (aka Imported Mode). Stores the data in the in-memory model, so all queries are satisfied by the data imported into the Tabular model's storage engine. This requires the model to be processed for updated data to become available for reporting. This mode is conceptually analogous to MOLAP in SSAS multidimensional models (though the SSAS architecture differs significantly).
DirectQuery Mode. Leaves the data in the source and sends the queries to the underlying database. In this case there's no processing and SSAS serves as a semantic model to improve the user experience. This mode is conceptually analogous to ROLAP in SSAS multidimensional models (though there are architectural / implementation differences between DirectQuery and ROLAP).

Best regards,
Carrin

---

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.