Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 111 of 212

99 C H A P T E R 6 | More analytics Accessing more data sources with DirectQuery One of the benefits of using tabular models in Analysis Services is the ability to use data from a variety of data sources, both relational and nonrelational. Although prior versions of SQL Server support a quite extensive list of data sources, not all of those sources are available to use with DirectQuery, the feature in tabular models that retrieves data from the data source when a query is run instead of importing data into memory in advance of querying. Having live access to more data sources means that users can get answers to questions more quickly, and you have less administrative overhead to maintain in your analytic infrastructure. In previous versions of SQL Server, you are limited to using SQL Server 2005 or later for a model in DirectQuery mode. In SQL Server 2016, the list of data sources supported for DirectQuery now includes the following: SQL Server 2008 or later Azure SQL Database Analytics Platform System (formerly Parallel Data Warehouse) Oracle 9i, 10g, 11g, and 12g Teradata V2R6, V2 When should you use DirectQuery? Tabular models can compress and cache large volumes of data in memory for high-performance queries. DirectQuery might be a better option in some cases, but only if you are using a single data source. In general, you should use DirectQuery if any of the following situations apply: your users require real-time access to data, the volume of data is larger than the memory available to Analysis Services, or you prefer to rely on row-level security in the database engine. Using DirectQuery can potentially have an adverse impact on query performance. If your source is SQL Server 2012 or later, you should consider implementing columnstore indexes so that DirectQuery can take advantage of query optimization provided by the database engine. Even if you create a tabular model in in-memory mode, you can always switch to DirectQuery mode at any time. If you do this, any data previously stored in the cache is flushed, but the metadata is retained. There are some drawbacks to using DirectQuery mode that you should consider before choosing it for your model. First, you cannot create calculated columns or calculated tables in the model, nor can you add a pasted table. An alternative is to use corresponding logic to create a derived column or a view in the underlying source. Second, because Analysis Services translates the DAX formulas and measures of your model into SQL statements, you might encounter errors or inconsistent behavior for some DAX functions that do not have a counterpart in SQL, such as time-intelligence functions or some statistical functions. In that case, you might be able to create a derived column in the source. You can see a list of functions that are not supported in DirectQuery mode at To learn more about DirectQuery mode in general, see us/library/hh230898.aspx. Modeling with a DirectQuery source During the tabular modeling process, you import data from data sources into the design environment, unless your model is configured in DirectQuery mode. A new element in this process in SQL Server 2016 is that you can specify whether to create a model by using all the data (which is the only option

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016