Set or Change the Preferred Connection Method for DirectQuery
When you create a model for use in DirectQuery mode, you must first configure the design environment to support use of DirectQuery. To do this, see Enable DirectQuery Design Mode (SSAS Tabular).
When you are ready to deploy the model, you must set some additional properties to enable users to access your model using one of the DirectQuery modes:
You must indicate whether queries against the model should use cached data or the relational data source. You can use a hybrid mode or DirectQuery only.
If any tables are partitioned, you must indicate which partition to use as the DirectQuery data source.
You must set impersonation options for users who will be accessing the relational data source.
This procedure describes how to set the preferred connection method for a DirectQuery model in the designer. It also describes how you can change this property in SQL Server Management Studio after the model has been deployed.
To set the preferred connection method for a DirectQuery model
In SQL Server Data Tools (SSDT), open the solution file for the DirectQuery model.
In Visual Studio, from the Project menu, select Properties.
In the Properties pane, change the property, DirectQueryMode, to one of the values that support DirectQuery usage:
InMemory with DirectQuery: If you use this option, the model is deployed but you must process the cache before you can run queries against the model.
DirectQuery with InMemory: If you use this option, the cache will be available for use by clients if it has already been processed. If you deploy the model with this setting and do not process the cache, some clients must get an error on trying to connect to the model.
DirectQuery only: If you use this option, the metadata is deployed but the model has no data in it. Clients that attempt to connect using in-memory mode will get an error, indicating that the model does not exist or has not been processed.
If there are errors, in Visual Studio, open the Error List and resolve any problems that would prevent the model from being deployed in DirectQuery mode.
To verify or change the preferred connection method for a DirectQuery model
In SQL Server Management Studio, connect to the instance where you deployed the DirectQuery model.
Right-click the model database, and select Properties.
In the Properties pane, change the property, DirectQueryMode, to one of these values:
InMemory with DirectQuery
DirectQuery with InMemory
Note that these properties are the same as the properties that you set on the project before deployment in Visual Studio. You can change the preferred connection mode for DirectQuery mode at any time, provided that you have configured the model to support DirectQuery usage.