What are the differences between OLAP, ROLAP, MOLAP and HOLAP SSAS cubes? They allow the implementation of data analysis systems. Also known as multidimensional databases, they allow a different approach to that of so-called relational or OLTP databases. These decision support systems provide an in-depth, aggregated and rapid view of the data.
Difference between a ROLAP, MOLAP and HOLAP SSAS cubes
But in practice, what is an OLAP database or cube? What is the difference between a ROLAP, MOLAP and HOLAP cube?
OLAP stands for On Line Analytical Processing.
MOLAP cubes stand for Multidimensional On Line Analytical Processing
Firstly, MOLAP is Multidimensional OLAP. The system stores the data in a cube which is a multidimensional database. In this way, the concept of relational data disappears. All possible and conceivable cross-references are pre-calculated. This is the main objective of this type of database.
Indeed, this pre-calculation allows for the restitution of data in an instantaneous manner. The data stored after the calculation allows a considerable saving of time during the restitution.
The disadvantages of MOLAP SSAS cubes
A big disadvantage is the important cost, as MOLAP often requires licences for multidimensional databases and costs for the development of the CUBES.
Advantages of MOLAP cubes
The response time is extremely fast because all the data is stored in a cube, i.e. the combinations are pre-calculated during the processing of the cube.
Multidimensional databases have their own query language, called MDX, which is the equivalent of SQL used for relational databases.
ROLAP stands for Relational On Line Analytical Processing
Secondly, ROLAP is relational OLAP. The data is obtained via relational tables and the joins that go with them. Therefore, the query created will be relatively complex, depending on the granularity of the project, and will therefore have a significant maintenance cost.
As the result is not stored, at each consultation, the query will have to be restarted. However, ROLAP systems often offer caching options to limit the number of queries on the data warehouse databases.
Some disadvantages of the ROLAP method
Response times are high as queries read results directly from relational tables. The databases are therefore used at each execution of the report, unless the cache is activated. In addition, queries are more complex, as aggregation and formulas are done on the fly.
The advantages of the ROLAP method
The cost is relatively low, as this method uses already available resources such as a SQL server. In addition, the data is always available without any processing to pre calculate and aggregate the data.
SSAS cubes in HOLAP mode for Hybrid Online Analytical Processing
Thirdly, HOLAP is a mixture of ROLAP and MOLAP, so HOLAP cubes are hybrid. MOLAP is used when you want to access aggregated data. To reach a higher level of detail, use ROLAP. For example, the data is stored and accessible via a multidimensional cube, but it is also retrieved via a reporting tool such as SSRS.
The user can have access to a report providing the data from a cube. And also to another detailed report, called operational, containing data from tables, this time relational.
This is the concept of drill through, it allows the user to see the finest detail from the source tables of the cube. It is the equivalent of double-clicking in an Excel pivot table.
The disadvantages of the HOLAP method
The Hybrid OLAP method is difficult to use in the case of high complexity reports. Or if they involve too many data cross-references.
Advantages of the HOLAP method
Possible with a lower financial investment than the MOLAP method because the development part is less important. In addition, the response time is relatively efficient. Most reports do not necessarily require a large number of data cross-references.
This article presents the fundamental differences between ROLAP, MOLAP and multidimensional HOLAP cubes. Technical and especially functional requirements determine the choice of cube type.
These SSAS tutorials on the official Microsoft website allow you to learn multidimensional modelling with simple practical cases.
The exercises use the AdventureWorksDW example database, which must be downloaded and installed beforehand.
This article introduces the SSAS ROLAP MOLAP and HOLAP Cubes and their differences, especially in the options available with Microsoft SSAS.
Be the first to comment