A Case for Microsoft ROLAP

With Microsoft Analysis Services being one of the most scalable multi-dimensional OLAP tools on the market, the relational OLAP storage method has traditionally not been given much consideration.  Is there a reason to consider, or reconsider, Microsoft’s ROLAP architecture given the current technology landscape?  Thorogood has extensive experience designing Business Intelligence solutions with Microsoft’s multi-dimensional technology and puts the storage options into perspective.

What is OLAP?

Relational databases are very good at storing enterprise data and providing operational reporting but business scenarios are often best modeled in a multi-dimensional tool. 

Online Analytical Processing (OLAP) tools provide the engine that allows complex business calculations to be expressed simply and evaluated quickly, whilst enabling business users to navigate through their data in an intuitive, hierarchical fashion.

OLAP can be implemented directly through the Relational engine (ROLAP) or more usually cached through a proprietary multi-dimensional engine (MOLAP).   Because MOLAP engines are optimized for an OLAP workload, they are usually faster to query and more flexible than their ROLAP counterparts.

So is there a case for ROLAP?

Data is more valuable when it is timely and ROLAP can help get data to users faster.  Overnight processing windows are not always available in systems that need intra-day loads or global systems that need to be available 24/7.

A ROLAP implementation eliminates the additional step required to process data into a multi-dimensional database.  This has two immediate benefits:

    • It reduces the overall latency of data and any associated downtime window.
    • It increases use of the underlying database platform maximizes IT’s investment in the relational technology.

Historically, Microsoft’s ROLAP implementation was seen as being unable to compete with the performance of an equivalent solution delivered using MOLAP storage and so was invariably overlooked by Microsoft Analysis Services designers. 

However, with significant advances in relational database performance, the number of scenarios where a ROLAP solution can perform comparably to MOLAP is increasing. With a good design, ROLAP can leverage the constantly improving relational database technologies including the use of columnar indexes and massively parallel processing (MPP) to provide business value at speed and scale.                                           

Things to consider

Thorogood drives the design of solutions based on adding value to the business.  When considering the use of ROLAP in the organization, here are some points to consider:

    • Is there business value to having OLAP in the architecture?
    • How timely does the information need to be & when does it need to be available?
    • How much information needs to be available & how often does the data change?

How to design your solution

Thorogood has in-depth knowledge of how the Analysis Services engine operates in MOLAP and ROLAP storage modes and experience of implementing these solutions on an enterprise scale.  The optimization of the ROLAP storage engine is based on a number of factors which can vary depending on the underlying relational platform:

    • Horizontal or vertical fact table design
    • Star schema or snowflake schema design
    • Relational and OLAP partitioning
    • Aggregation strategy
    • Complex modeling scenarios
    • Analysis Services query optimization
    • Relational database physical optimization
    • Product version and patching
    • Instance settings and data source configuration

Don't limit yourself to just one storage mode

An OLAP solution doesn’t need to be exclusively implemented using a single storage mode.  Based on business requirements and service level expectations it can be a valuable proposition to implement both ROLAP and MOLAP storage methods within the same Analysis Services cube.  

A partition strategy can be used to combine multiple storage modes into a seamless OLAP environment that meets the functional requirements and performance expectations of the user community whilst driving down latency and the need to copy data.  For example, historic data can be cached into MOLAP while leaving the latest period in ROLAP for real time updates or alternatively large volumes of historical data can be left in the relational source while more frequently queried data is stored in MOLAP.

About Thorogood

Thorogood provides world-class Business Intelligence solutions, developed and delivered with expert human insight. It has been building leading-edge applications for some of the world’s most demanding customers for more than 20 years.

Want more information

For more information on Thorogood’s Business Intelligence solutions and specific information on considering ROLAP at your company, please reach out to jeffrey.burke@thorogood.com.

open as pdf

Share this page