Analysis Services ROLAP for SQL Server Data Warehouses

You’re sh*&ting me. No way. Man, that sucks. rocks!

– Toby Lee Shavers, from XXX (2002), altered of course!

And before you start, yes, I’m starting to follow the schtick of quotes from fellow PowerPivot bloggers Rob Collie (@PowerPivotPro) and Kasper de Jonge (@Kjonge).  For more memorable movie quotes, check out


After about 14 months of testing, validation, analysis, and all sorts of hardware headaches and dancing, I am proud to say that I have finally published the Analysis Services ROLAP for SQL Server Data Warehouses whitepaper with my trusted partners-in-crime Thomas Kejser and Kay Unkroth.  Thomas for his technical prowess and Kay in his ability to translate a lot of complex concepts (and frankly, my floating in outer space) into something actually cohesive.

So what is the ROLAP paper really about?

What this paper is really about is that:

  1. Yes, ROLAP is a viable option for high performance SSAS
  2. But, it isn’t easy, there are disadvantages, and … did I mention that it isn’t necessarily easy?

But, if you want to work with high performance ROLAP – here is a technical case study on how to do it as well as the advantages and pitfalls of doing this.

As well, here’s the official summary:

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


To continue reading, click through to the whitepaper: Analysis Services ROLAP for SQL Server Data Warehouses




  1. Shelley Fu

    Hi, due to the business needs, I created ROLAP cubes with 4 dimensions. However, data is not updated in the real time, unless the cube is processed. Do you know what’s wrong?
    Thank you in advance for your help and assistance.


  2. Hi Shelley, that is a bit surprising – are you sure that the cube is correclty setup in ROLAP mode and perhaps it isn’t proactive caching?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s