ROLAP
Encyclopedia
ROLAP stands for Relational Online Analytical Processing.
ROLAP is an alternative to the MOLAP
(Multidimensional OLAP
) technology. While both ROLAP and MOLAP analytic tools are designed to allow analysis of data through the use of a multidimensional data model, ROLAP differs significantly in that it does not require the pre-computation and storage of information. Instead, ROLAP tools access the data in a relational database
and generate SQL
queries to calculate information at the appropriate level when an end user requests it. With ROLAP, it is possible to create additional database tables (summary tables or aggregations) which summarize the data at any desired combination of dimensions.
While ROLAP uses a relational database source, generally the database must be carefully designed for ROLAP use. A database which was designed for OLTP will not function well as a ROLAP database. Therefore, ROLAP still involves creating an additional copy of the data. However, since it is a database, a variety of technologies can be used to populate the database.
However, as with any survey there are a number of subtle issues that must be taken into account when interpreting the results.
tools now use a "Hybrid OLAP" (HOLAP
) approach, which allows the model designer to decide which portion of the data will be stored in MOLAP
and which portion in ROLAP.
, MicroStrategy
, Oracle
BI (the former Siebel Analytics) and Tableau Software. There is also an open source ROLAP server, Mondrian
.
ROLAP is an alternative to the MOLAP
MOLAP
MOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
(Multidimensional OLAP
OLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
) technology. While both ROLAP and MOLAP analytic tools are designed to allow analysis of data through the use of a multidimensional data model, ROLAP differs significantly in that it does not require the pre-computation and storage of information. Instead, ROLAP tools access the data in a relational database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
and generate SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
queries to calculate information at the appropriate level when an end user requests it. With ROLAP, it is possible to create additional database tables (summary tables or aggregations) which summarize the data at any desired combination of dimensions.
While ROLAP uses a relational database source, generally the database must be carefully designed for ROLAP use. A database which was designed for OLTP will not function well as a ROLAP database. Therefore, ROLAP still involves creating an additional copy of the data. However, since it is a database, a variety of technologies can be used to populate the database.
ROLAP vs. MOLAP
The discussion of the advantages and disadvantages of ROLAP below, focus on those things that are true of the most widely used ROLAP and MOLAP tools available today. In some cases there will be tools which are exceptions to any generalization made.Advantages of ROLAP
- ROLAP is considered to be more scalable in handling large data volumes, especially models with dimensionsDimension (data warehouse)In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric...
with very high cardinality (i.e. millions of members).
- With a variety of data loading tools available, and the ability to fine tune the ETLExtract, transform, loadExtract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...
code to the particular data model, load times are generally much shorter than with the automated MOLAPMOLAPMOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
loads.
- The data is stored in a standard relational databaseRelational databaseA relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
and can be accessed by any SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
reporting tool (the tool does not have to be an OLAPOLAPIn computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
tool).
- ROLAP tools are better at handling non-aggregatable facts (e.g. textual descriptions). MOLAP tools tend to suffer from slow performance when querying these elements.
- By decouplingDecouplingThe term "decoupling" is used in many different contexts.-Economic growth without environmental damage:In economic and environmental fields, decoupling is becoming increasingly used in the context of economic production and environmental quality. When used in this way, it refers to the ability of...
the data storage from the multi-dimensional model, it is possible to successfully model data that would not otherwise fit into a strict dimensional model.
- The ROLAP approach can leverage databaseDatabaseA database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
authorization controls such as row-level security, whereby the query results are filtered depending on preset criteria applied, for example, to a given user or group of users (SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
WHERE clause).
Disadvantages of ROLAP
- There is a consensus in the industry that ROLAP tools have slower performance than MOLAP tools. However, see the discussion below about ROLAP performance.
- The loading of aggregate tables must be managed by custom ETLExtract, transform, loadExtract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...
code. The ROLAP tools do not help with this task. This means additional development time and more code to support.
- When the step of creating aggregate tables is skipped, the query performance then suffers because the larger detailed tables must be queried. This can be partially remedied by adding additional aggregate tables, however it is still not practical to create aggregate tables for all combinations of dimensions/attributes.
- ROLAP relies on the general purpose database for querying and caching, and therefore several special techniques employed by MOLAPMOLAPMOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
tools are not available (such as special hierarchical indexing). However, modern ROLAP tools take advantage of latest improvements in SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
language such as CUBE and ROLLUP operators, DB2 Cube Views, as well as other SQL OLAP extensions. These SQL improvements can mitigate the benefits of the MOLAPMOLAPMOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
tools.
- Since ROLAP tools rely on SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
for all of the computations, they are not suitable when the model is heavy on calculations which don't translate well into SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
. Examples of such models include budgeting, allocations, financial reporting and other scenarios.
OLAP Survey
In the OLAP industry ROLAP is usually perceived as being able to scale for large data volumes, but suffering from slower query performance as opposed to MOLAP. The OLAP Survey, the largest independent survey across all major OLAP products, being conducted for 6 years (2001 to 2006) have consistently found that companies using ROLAP report slower performance than those using MOLAP even when the data volume were taken into consideration.However, as with any survey there are a number of subtle issues that must be taken into account when interpreting the results.
- The survey shows that ROLAP tools have 7 times more users than MOLAP tools within each company. Systems with more users will tend to suffer more performance problems at peak usage times.
- There is also a question about complexity of the model, measured both in number of dimensions and richness of calculations. The survey does not offer a good way to control for these variations in the data being analyzed.
Downside of flexibility
Some companies select ROLAP because they intend to re-use existing relational database tables -- these tables will frequently not be optimally designed for OLAP use. The superior flexibility of ROLAP tools allows this less than optimal design to work, but performance suffers. MOLAP tools in contrast would force the data to be re-loaded into an optimal OLAP design.Trends
The undesirable trade-off between additional ETL cost and slow query performance has ensured that most commercial OLAPOLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
tools now use a "Hybrid OLAP" (HOLAP
HOLAP
HOLAP is a combination of ROLAP and MOLAP which are other possible implementations of OLAP. HOLAP allows storing part of the data in a MOLAP store and another part of the data in a ROLAP store, allowing a tradeoff of the advantages of each...
) approach, which allows the model designer to decide which portion of the data will be stored in MOLAP
MOLAP
MOLAP stands for Multidimensional Online Analytical Processing.MOLAP is an alternative to the ROLAP technology...
and which portion in ROLAP.
Products
Examples of commercial products using ROLAP include Microsoft Analysis ServicesMicrosoft Analysis Services
Microsoft SQL Server Analysis Services is part of Microsoft SQL Server, a database management system. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and Analysis Services...
, MicroStrategy
MicroStrategy
MicroStrategy, Inc. , is a business intelligence software vendor. MicroStrategy's software enables leading organizations worldwide to analyze the vast amounts of data stored across their enterprises to make more strategic business decisions...
, Oracle
Oracle Corporation
Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...
BI (the former Siebel Analytics) and Tableau Software. There is also an open source ROLAP server, Mondrian
Mondrian OLAP server
Mondrian is an open source OLAP server,written in Java.It supports the MDX query languageand the XML for Analysis and interface specifications.It reads from SQL and other data sources...
.