DirectQuery Mode (SSAS Tabular)
Analysis Services lets you access data from a Tabular model by retrieving data and aggregates directly from a relational database system in DirectQuery mode. You can configure DirectQuery using Management Studio for a deployed database, or in SQL Server Data Tools (SSDT) when designing the model.
How you work with DirectQuery will vary depending on the compatibility mode of the model. For the new 1200 compatibility level introduced in SQL Server 2016, you have more control over data in design mode, with the ability to use no data, a sample data set (based on a user-defined query), or full data. Older compatibility level models at 1100 or 1103 use the DirectQuery mode from previous versions of Analysis Services.
You can determine the compatibility level of model by looking at the model properties list. Optionally, you can upgrade a model to 1200 compatibility level in SQL Server Data Tools by changing its CompatibilityLevel property. Remember that upgrade is irreversible so be sure to backup your model first.
To switch to DirectQuery mode, set the DirectQuery mode to On. For more information, see Enable DirectQuery Design Mode (SSAS Tabular).
Sections in this topic:
By default, Tabular models use an in-memory cache to store and query data. When Tabular models use data that resides in memory, even complex queries can be incredibly fast. However, there are some drawbacks to using cached data. Namely, large data sets can exceed available memory, and data freshness requirements can be difficult if not impossible to achieve on a regular processing schedule.
DirectQuery overcomes these limitations while also leveraging RDBMS features that can make query execution more efficient. With DirectQuery:
Data is guaranteed to be up-to-date, and there is no extra management overhead of having to maintain a separate copy of the data. Changes to the underlying source data can be immediately reflected in queries against the data model.
Data sets can be larger than the memory capacity of an Analysis Services server.
DirectQuery can take advantage of provider-side query acceleration, such as that provided by xVelocity memory optimized column indexes.
Security can be enforced by the back-end database , using row-level security.
If the model contains complex formulas that might require multiple queries, Analysis Services can perform optimization to ensure that the query plan for the query executed against the back-end database will be as efficient as possible.
DirectQuery mode uses data that is stored in a relational database. Any DAX or MDX queries on the data are translated by Analysis Services into equivalent SQL statements issued against the RDBMS.
After you enable DirectQuery mode, processing operations become largely unnecessary. Depending on the state of the model, you might need to run Import to gather table metadata, but otherwise importing or processing data on a DirectQuery model returns zero rows from the external data source.
By default, a DirectQuery model is empty of data. This is the expected behavior. In SQL Server 2016, there is no cached data unless you explicitly add it later via a sample data view. If you switched from in-memory to DirectQuery, any data that previously existed in the model is flushed from cache, leaving just the metadata behind.
Working without data can be a challenge. You can mitigate this by specifying a sample data view that returns a small data subset. A sample data view is based on SQL query that you write. Key advantages of using sample data views is that can create multiple queries that return results for different scenarios. For example, you might create different queries filtered by year, region, or product category.
Sample data is used in Analyze in Excel to validate DirectQuery is working properly when a PivotTable that you build contains the data you expect.
As soon as you change the design environment to enable DirectQuery mode, the data sources for the workspace database are validated to ensure that they come from a single relational data source (SQL Server, Oracle, or Teradata). Data from other sources, including copy-pasted data, is not allowed in DirectQuery models.
If you intend to use the model in DirectQuery mode, you must ensure that all the data you need for reporting is stored in the specified database. If the data you need for modeling is not available in that source, consider use of Integration Services or other data warehousing tools to import the data into a database that serves as the DirectQuery data source.
Excel uses MDX to retrieve data from an Analysis Services database In previous releases, DirectQuery models didn't accept MDX queries from Excel, but in SQL Server 2016, you can now use Excel to connect to Tabular models in DirectQuery mode, if the model is at compatibility level 1200.
During model authoring, you specify the permissions that are used to retrieve the source data. This will often be your own credentials, or an account used for development. However, when you switch the model to use DirectQuery mode, the security context is more complex:
Consider whether users have the necessary level of access to the data in the relational data store.
Users who view the same model or report might see different data, depending on the user’s security context.
If your report model requires security, you have two options: you can either use Analysis Services roles, or you can set row-level permissions on the data source. Security in the relational data source is used to control access to tables, and column-level security is not supported. Therefore, if users in one region do not have permission to view sales figures from different regions, a report that includes a measure based on the Sales table would return blanks or an error.
Additional Security Considerations Specific to DirectQuery Models at 1100 or 1103
If the model uses cached data, the cache is secured using the Analysis Services security model (roles). The cache might contain data that the model designer is privileged to see but the user is not. Model and report designers should either clear the cache, or secure this data by controlling access via roles.
A model that answers queries from the cache cannot impersonate the current user when connecting to the data source. If you want to impersonate the current user when connecting to the data source, you must use DirectQuery mode.
The impersonation settings property specifies the credentials used when you are connecting to a model using DirectQuery, either for a DirectQuery only model or for a hybrid model answering queries using DirectQuery. The property has the following values:
Uses the credentials specified in the import wizard to connect to the data source. This can be a specific Windows user or the service account.
Uses the credentials of the current user to connect to the data source.
For information on how to set these properties, see DirectQuery for Tabular 1100 or 1103 models (SSAS Tabular).
DirectQuery is significantly improved in this release, but still has a few restrictions. Before switching modes, determine whether the advantages of query execution on the backend server outweigh any reduction in functionality when data processing is offloaded to an external RDBMS.
If you change the mode of an existing model in SQL Server Data Tools, the model designer will notify you of any features in your model that are incompatible with DirectQuery mode.
The following list summarizes the main feature limitations to keep in mind:
Data sources: Recall from the previous section that DirectQuery models can only use data from a single relational database of the following types: SQL Server, Oracle, Teradata. See Data Sources Supported (SSAS Tabular) for version information.
When DirectQuery mode is enabled, you are limited to using the current data source in the model designer.
Calculated tables: Calculated tables are not supported in DirectQuery models, but calculated columns are. If you try to convert a tabular model that contains a calculated table, an error will occur stating that the model cannot contain pasted data.
Analysis Services converts all DAX formulas and measure definitions into SQL statements when querying a DirectQuery model. Any formula containing elements that cannot be converted into SQL syntax will trigger validation errors on the model.
This restriction is mostly limited to functions. For measures, DAX formulas are converted to set-based operations against the relational data store. This means that all measures created implicitly are supported.
Functions that are not supported or that behave inconsistently across platforms include time intelligence functions and statistical functions.
When a validation error occurs, you must substitute a different function, or workaround it by using derived columns in the data source. Any functions that are off limits will be reported when you switch to DirectQuery mode in the designer.
For a complete list of compatibility issues, including functions that might return different results when the model is deployed to real-time, see DAX Formula Compatibility in DirectQuery Mode (SQL Server Analysis Services).
Some formulas in the model might validate when you switch the model to DirectQuery mode, but return different results when executed against the cache vs. the relational data store. This is because calculations against the cache use the semantics of the in-memory analytics (engine, which contains many features meant to emulate the behavior of Excel, whereas queries against data stored in the relational data store necessarily use the semantics of SQL Server.
No relative object names. All object names must be fully qualified.
No session-scope MDX statements (named sets, calculated members, calculated cells, visual totals, default members, and so forth), but you can use query-scope constructs, such as the 'WITH' clause.
No tuples with members from different levels in MDX subselect clauses.
No user-defined hierarchies.
No drillthrough support.
No native SQL queries (normally, Analysis Services supports a T-SQL subset, but not for DirectQuery models).
Formula consistency: In certain known cases, the same formula can return different results in a cached or hybrid model compared to a DirectQuery model that uses only the relational data store. These differences are a consequence of the semantic differences between the in-memory analytics engine and SQL Server.
Change the design-time environment to create a DirectQuery model.
Describes how to change the DirectQuery partition.
Describes how partitions are used in models configured for DirectQuery mode.
Describes how to set or change the connection method for models configured for DirectQuery.
Describes DirectQuery deployment for Tabular models at the 1200 compatibility level
Describes DirectQuery deployment for Tabular models at 1100 or 1103 compatibility levels.