Configure In-Memory or DirectQuery Access for a Tabular Model Database
This topic explains how to change the data access properties of a Tabular model that has already been deployed, to enable its use in DirectQuery mode, where queries are executed against a backend relational data source rather than cached data residing in-memory. In SQL Server 2016, steps for DirectQuery configuration differ based on the model's compatibility level. Below you'll find steps that work for all compatibility levels.
This topic assumes that you have created and validated your model, and only need to enable DirectQuery access and update connection strings from a client such as Excel or Power View. For more end-to-end guidance, see DirectQuery for Tabular 1200 models and DirectQuery for Tabular 1100 or 1103 models (SSAS Tabular).
We recommend using SQL Server Data Tools to switch data storage modes because you can review any validation errors that occur. Although you an use SQL Server Management Studio as described in this article, validation errors are not reported.
Enabling the use of Direct Query mode on a tabular model is a multistep process. You must:
Ensure that the model does not have features which might cause validation errors in DirectQuery mode, and then change the data storage mode on the model from in-memory to DirectQuery.
A list of feature restrictions is documented in DirectQuery Mode (SSAS Tabular).
Edit the connection string on the deployed database to support DirectQuery mode.
Confirm that DirectQuery mode is operational through query execution.
When you change a Tabular model to DirectQuery mode, the new data storage mode takes effect immediately.
In Management Studio, connect to the instance that has the Tabular model.
In Object Explorer, right-click the name of the project, and select Properties.
For Tabular 1200 Models
In Database Properties, select Model.
In Default Mode, choose DirectQuery.
Default Mode properties consist of the following:
Retrieves data from a backend data source and stores it on disk, then loads the data in its entirety into memory for very fast table scans and queries. This is the most common mode for tabular models, and it is the only mode for certain (non-relational) data sources. This mode has nothing to do with DirectQuery.
Queries are executed against a backend relational database, using the data source connection defined for the model. Queries to the model are converted to native database queries and redirected to the data source. When you process a model set to DirectQuery mode, only metadata is compiled and deployed. The data itself is external to the model, residing in the database files of the operative data source.
Choose this value to enable DirectQuery.
(not valid at the model level). This value is operational at the individual partition level. It causes the partition to use whatever setting the model uses for its default mode. For example, if the model uses Import, a partition will also use Import.
This property is intrinsic to Tabular 1200 models so it's visible, but it's not supported in this scenario. It is only used internally, for Tabular models that run in a Power BI environment.
For Tabular 1100 and 1103 Models
In Database Properties, on the Database tab, set DirectQueryMode to one of these values:
Queries use the relational data source only.
Queries use the relational data source by default, unless otherwise specified in the connection string from the client. This is a hybrid mode where partitions are individually configured to use in-memory or DirectQuery.
Queries use the cache by default, unless otherwise specified in the connection string from the client. This is a hybrid mode where partitions are individually configured to use in-memory or DirectQuery.
For Tabular 1100 or 1103 Models Using Hybrid Data Storage
For Tabular models at the 1100 or 1103 compatibility level, you can configure DirectQuery to use a combination of in-memory and disk-based access to backend data sources. This is often referred to as a hybrid mode because both modes are used in different contexts. If you deploy the model in a hybrid mode, the cache is still available and can be used for queries. A hybrid mode provides you with many options:
When both the cache and the relational data source are available, you can set the preferred connection method, but ultimately the client controls which source is used, using the DirectQueryMode connection string property.
You can also configure partitions on the cache in such a way that the primary partition used for DirectQuery mode is never processed and must always reference the relational source. There are many ways to use partitions to optimize the model design and reporting experience. For more information, see Partitions and DirectQuery Mode (SSAS Tabular).
After the model has been deployed, 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 only after thoroughly testing any reports or queries that use the model. For more information, see Set or Change the Preferred Connection Method for DirectQuery.
Switching from in-memory to DirectQuery access can sometimes change the security context of the data source connection. When changing the data access mode, review impersonation and connection string properties to verify the login is valid for ongoing connections to the backend database.
In Object Explorer, expand Connections and double-click a connection to view its properties.
For DirectQuery models, there should only be one connection defined for the database, and the data source must be relational, and of a supported database type. See Data Sources Supported (SSAS Tabular).
Connection string should specify the server, database name, and the authentication method used in DirectQuery operations. If you're using SQL Server authentication, you can specify the database login here.
Impersonation Info is used for Windows authentication. Options that are valid for Tabular models in DirectQuery mode include the following:
Use the service account. You can choose this option if the Analysis Services service account has read permissions on the relational database.
Use a specific user name and password. Specify a Windows user account that has read permissions on the relational database.
Note that these credentials are used only for answering queries against the relational data store; they are not the same as the credentials used for processing the cache of a hybrid model.
Impersonation cannot be used when the model is used within memory only. The setting ImpersonateCurrentUser, is invalid unless the model is using DirectQuery mode.
Start a trace using either SQL Server Profiler or xEvents in Management Studio, connected to the relational database on SQL Server.
If you are using Oracle or Teradata, use the tracing tools for those database platforms.
In Management Studio, enter and then execute a simple MDX query, such as select <some measure> on 0 from model..
In the trace, you should see evidence of query execution.