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 with 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.