Analysis Services ROLAP for SQL Server Data Warehouses

SQL Server Technical Case Study

Writers: Denny Lee, Kay Unkroth

Investigators: Denny Lee, Thomas Kejser

Contributors: James Podgorski, Richard Tkachuk, Mosha Pasumansky, Akshai Mirchandani, Edward Melomed, Jonathan Ehren, Mark Tameling, Mukesh Kumar

Technical Reviewers: Akshai Mirchandani, Kevin Cox, Nicolae Mogoreanu

Published: July 2010

Applies to: Microsoft® SQL Server® 2008 and SQL Server 2008 R2

Summary: This technical case study describes how the SQL Server® Customer Advisory Team (SQLCAT), in collaboration with SQL Server developers, tested and optimized a large Online Analytical Processing (OLAP) solution based on SQL Server 2008 Analysis Services by using the Relational OLAP (ROLAP) storage mode. The study examines ROLAP system requirements and usage scenarios, highlights advantages and disadvantages of ROLAP in comparison with Multidimensional OLAP (MOLAP), and evaluates various ROLAP-related data warehouse (DW) optimization techniques regarding their effectiveness and limitations.

This case study is for data warehouse architects, database administrators, and storage engineers, and assumes the audience is already familiar with the concepts of large-scale data warehouse designs for servers, storage subsystems, and databases. A high-level understanding of Analysis Services optimization techniques for cube processing and query performance is also helpful. Detailed information is available in the SQL Server 2008 Analysis Services Performance Guide at https://go.microsoft.com/fwlink/?LinkId=165486.

Continue reading: Analysis Services ROLAP for SQL Server Data Warehouses