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 and uses MDX queries. In the tabular model in SSAS, the solution for real-time access is a query mode called DirectQuery that uses DAX queries, 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.