ROLAP - now a viable option

The time for ROLAP has arrived:

Most operational data stores (ODS) and data warehouses (DW) reside on a rowstore relational database. It's a great tool for data organization, but rowstores and their related indexes don't mesh with the demanding needs of analytics, business intelligence, reporting and self-service oriented cubes. So it was not uncommon to push selected data down from the data warehouse into customized MOLAP cubes. MOLAP cubes offered business analysts great self-service data mobility, usually using a connected EXCEL pivot table, or more expensive tools such as Tableau.

Ideal for the analyst, but pushing data down from a data warehouse or operational data store to a MOLAP cube was lots of added work, risk and most importantly, loss of data timeliness. Once the data warehouse or ODS was updated, yet another process had to be started to update and process the MOLAP cube. Once a day updates were not uncommon for MOLAP cubes. Fast data it was not! And if you have reviewed the literature on large MOLAP cubes, you've found that they can get unwieldy.

MDX was another road block. Using a BI tool such as Excel, analysts were shielded from the sometimes complex MDX commands. But problems arose when analysts wanted to do custom queries against the MOLAP cube. Queries that would be far easier against a traditional data warehouse using SQL.

Columnar databases to the rescue.

With its release of SQL Server 2016, Microsoft has a full featured relational database that can be run as a columnar database. By simply upgrading to SQL Server 2016, and converting your existing rowstore indexes into a columnstore, your data warehouse or ODS can now support a ROLAP cube. Efficient and fast. And no more MOLAP processing.  Here at Realized Design, we have done some limited testing using ROLAP, and have been pleased with the results. For the relational backend, we have used both SQL Server 2016 and the specialized analytic/columnar database EXASOL. In both cases, ROLAP proved comparable to a MOLAP design. But without all the extra processing. And thus far more elegant. You can find a detailed analysis of our work so far with EXASOL here: EXASOL review at RealizedDesign.

But don't just take our word for it. Here are two additional links where individuals have successfully used ROLAP cubes against very large databases. And they have been please with the results.

In 2014, Karen Gulati did exploratory ROLAP work using SQL Servers new column store indexes. See: Harnessing the Power of both worlds.

Also in 2014, Hilmar Buchta did some work with SSAS ROLAP against an MSFT Parallel Data Warehouse  See:  Parallel Data Warehouse (PDW) and ROLAP - Hilmar Buchta. 

So, just when you thought SSAS multi-dimensional was dead, its back!

Next Steps:  Build an SSAS ROLAP Cube using EXASOL