Deployment Options for BISM Project

 

There are two Query Mode options you could use when you deploy the BISM project to the AS Tabular Model server.

• In-Memory
• DirectQuery

By default, data in tabular model is processed, compressed using the xVelocity in-memory analytics engine (VertiPaq).  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.

However, there are some drawbacks:

• Data is not updated when the source data changes. Model needs to be processed to refresh the data.
• When you turn off the computer hosting the model, the cache is saved to disk and must be reopened when you load the model.
• The save and load operations can be time-consuming.
• Need lots memory to hold the large fact data

On the other hand, DirectQuery mode uses data that is stored in a SQL Server database. It lets users retrieve data directly from a SQL Server data source in  real time.  This mode also lets you create models and build reports for large data sets that cannot reside in memory.

Here are additional benefits:

• The data is guaranteed to be up-to-date.
• Use the advantage of provider-side query acceleration, i.e. SQL 2012 column indexes.
• Could use row-level security provided by the backend  database.
• Analysis Services can perform optimization to ensure the query plan against the backend database will be as efficient as possible.

There are some design considerations if you are planning to use DirectQuery mode:

• During design phase, you may need to use Preview or Filter function to load subset data into your project.
• MDX queries are not supported for a model in DirectQuery mode. You cannot use PPS or other clients that only issue MDX queries to consume it.
• Currently this model only supports one data connection. You cannot query two or more SQL Servers from a DirectQuery enabled model.
• Calculated columns and some DAX functions are not supported. You may need to use SQL View or other technique for that purpose.

DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, see DirectQuery Mode (SSAS Tabular).

 

Reference

Formula Compatibility in DirectQuery Mode

DirectQuery Mode (SSAS Tabular)

White Paper: Using DirectQuery in the Tabular BI Semantic Model