When working with the Tabular model in SQL Server Analysis Services (SSAS), you have several options for the “Query Mode” during deployment. Each mode has its own benefits and drawbacks, and it’s important to understand them in order to make the right choice for your project.

In-Memory Mode

The default mode in SSAS is In-Memory. In this mode, the data in the tabular model is processed and compressed using the xVelocity in-memory analytics engine. This engine is optimized for high-performance analysis and exploration of data, providing fast query times for aggregation queries. However, there are some drawbacks to consider:

• The data is not updated automatically when the source data changes, so the model needs to be processed to refresh the data.
• When the computer hosting the model is turned off, the cache is saved to disk and must be reopened when the model is loaded, which can be time-consuming.
• If you have a large amount of fact data, the server will require a significant amount of memory.

DirectQuery Mode

DirectQuery mode uses relational data stored in a SQL Server database. It allows users to retrieve data directly from the SQL Server data source in real-time. Any DAX queries on the data are translated by Analysis Services into equivalent SQL statements against the specified relational data source. DirectQuery mode offers several benefits:

• It is possible to have a model over data sets that are too large to fit in memory on the Analysis Services server.
• The data is guaranteed to be up-to-date, and there is no extra management overhead of maintaining a separate copy of the data.
• It can take advantage of provider-side query acceleration and row-level security provided by the backend database.
• Metadata discovery operations are faster, as data for a DirectQuery enabled model does not need to be loaded into memory.

However, there are some design considerations to keep in mind when using DirectQuery mode:

• During the design phase, you may need to use the Preview or Filter function to load subset data into your project.
• MDX queries are not supported for a model in DirectQuery mode. Only Power View and SSRS can be used to consume it.
• Calculated columns and some DAX functions are not supported, so you may need to use SQL View or other techniques for that purpose.
• DirectQuery enabled models cannot be created in PowerPivot; only models deployed to a standalone Analysis Services instance can use DirectQuery.

In-Memory with DirectQuery and DirectQuery with In-Memory Modes

These modes are hybrid options that provide more flexibility. In In-Memory with DirectQuery mode, queries are answered using the In-Memory mode by default, but the client can choose to use the DirectQuery mode. In DirectQuery with In-Memory mode, queries are answered using the DirectQuery mode by default, but the client can choose to use the In-Memory mode. These hybrid modes allow you to serve clients that issue both MDX and DAX queries from the same model.

After deploying the model, you can change the preferred connection method. For example, you might use a hybrid mode for testing and switch the model over to DirectQuery only mode after thoroughly testing any reports or queries that use the model.

It’s important to note that queries on a model deployed in DirectQuery mode can return different results than when the same model is deployed in-memory. This is because data is fetched directly from a relational data store and aggregations required by formulas are performed using the relevant relational engine. Differences in how certain relational data stores handle numeric values, dates, nulls, etc., can impact query results.

In conclusion, when data modeling in SQL Server, the in-memory cache is generally the preferred option for the tabular model. However, DirectQuery mode is a better choice when using Power View and when your scenario requires the benefits offered by DirectQuery.