SQL Server Analysis Services Tabular Model

Tabular models are in-memory databases in Analysis Services. They use the xVelocity in-memory analytics engine (VertiPaq) to deliver fast access of data from client tools (Excel, PowerView)

Tabular models support data access through two modes: Cached mode and DirectQuery mode. For cached mode use the In-Memory query mode and for passthrough use the DirectQuery query mode

The In-Memory query mode requires processing to update data while the DirectQuery mode retrieves results from the data source

Limitations of DirectQuery mode

• Model can be queried only by DAX Queries
• Permissions must be defined in the relational database
• Calculated columns are not supported
• DirectQuery models do support the use of DAX formulas for use in measures, which are converted to set-based operations against the relational data store. All measures that you create by using implicit measures are supported.
• Can only be based on Single SQL Server relational database

Tabular model in SSAS offers 4 deployment options

• DirectQuery
• DirectQuery with In-Memory
• In-Memory
• In-Memory with DirectQuery

In-Memory: This is the default uses the xVelocity engine. This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data. It provides fast query times for aggregation queries

DirectQuery: This mode uses relational data that is stored in a SQL Server database (it is similar to the ROLAP mode in a multidimensional model). It lets users retrieve data directly from a SQL Server data source in real-time.

In-Memory with DirectQuery: This is a hybrid mode. By default, queries should be answered by using the In-Memory mode, however, the connection string from the client can instead choose to use the DirectQuery mode.

DirectQuery with In-Memory: This is a hybrid mode. By default, queries should be answered by using the DirectQuery mode, however, the connection string from the client can instead choose to use the In-Memory mode.