Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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


Leave a comment on the original post [, opens in a new window]

Loading comments...