SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Comparing DirectQuery and ROLAP for real-time access

In the multidimensional model in SSAS, there is a storage mode called relational OLAP (ROLAP), which is the multidimensional solution for allowing real-time access to relational data sources.  In the tabular model in SSAS, the solution for real-time access is a query mode called DirectQuery, which uses a fundamentally different architecture and provides a different set of capabilities.  This blog post will explain the differences between the two.

DirectQuery has a dedicated engine for executing queries on the relational data source.  After a DAX query has been translated to SQL, SQL Server performs the computations required to return query results.  The DirectQuery engine can optimize SQL queries to reduce the number of queries issued, many times translating a DAX query to a single SQL query.

ROLAP does not have a dedicated engine for real-time queries.  Instead, ROLAP uses the same formula engine and storage engine as multidimensional OLAP (MOLAP) for answering queries.  When an MDX query is issued on a ROLAP-enabled multidimensional model, the formula engine computes the required data set and data is processed on the fly into an in-memory data cache.  The Analysis Services storage engine then computes the results from this in-memory cache and returns the results to the end users.

DirectQuery requires fewer machine resources compared with ROLAP, because data is not cached in memory.  Also, DirectQuery supports impersonating the current user’s credentials when connecting to SQL Server, which enables the SQL Server security model to be used.  ROLAP does not support impersonating the current user’s credentials.  Also, because queries on the relational data source are optimized, simple DAX queries on DirectQuery enabled models may perform better than similar MDX queries requiring relational data source access on ROLAP enabled models.

ROLAP provides support for data sources other than SQL Server, such as Oracle and Teradata.  Time intelligence functions are supported in ROLAP.  Neither of those features is supported for DirectQuery.  MOLAP and ROLAP results may be combined in response to a single MDX query, whereas DirectQuery does not support mixed real-time and cached data access.  MDX query results may be cached, so not all MDX queries issued to a ROLAP enabled model require a query to be issued on the relational data source.  ROLAP may perform better than DirectQuery in situations where query results are cached.

More info:

Using DirectQuery in the Tabular BI Semantic Model

Tabular query modes: DirectQuery vs In-Memory

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...