Editing Online analytical processing (section)

===Relational OLAP (ROLAP)===
'''ROLAP''' works directly with relational databases and does not require pre-computation. The base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information. It depends on a specialized schema design. This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement. ROLAP tools do not use pre-calculated data cubes but instead pose the query to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question because the methodology is not limited to the contents of a cube. ROLAP also has the ability to drill down to the lowest level of detail in the database.

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.

==== Advantages of ROLAP ====
<!--Note to editors:
Please review the discussion page before making changes to the advantages or disadvantages. Thank you.
-->

* ROLAP is considered to be more scalable in handling large data volumes, especially models with [[Dimension (data warehouse)|dimensions]] with very high [[cardinality]] (i.e., millions of members).
* With a variety of data loading tools available, and the ability to fine-tune the [[extract, transform, load]] (ETL) code to the particular data model, load times are generally much shorter than with the automated [[#Multidimensional_OLAP_.28MOLAP.29|MOLAP]] loads.
* The data are stored in a standard [[relational database]] and can be accessed by any [[SQL]] reporting tool (the tool does not have to be an OLAP tool).
* ROLAP tools are better at handling ''non-aggregable facts'' (e.g., textual descriptions). [[#Multidimensional_OLAP_.28MOLAP.29|MOLAP]] tools tend to suffer from slow performance when querying these elements.
* By [[Decoupling (electronics)|decoupling]] 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 [[database]] 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 ([[SQL]] WHERE clause).

==== Disadvantages of ROLAP ====
<!--Note to editors:
Please review the discussion page before making changes to the advantages or disadvantages. Thank you.
-->

* 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 [[Extract, transform, load|ETL]] 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 [[MOLAP]] tools are not available (such as special hierarchical indexing). However, modern ROLAP tools take advantage of latest improvements in [[SQL]] 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 [[MOLAP]] tools.
* Since ROLAP tools rely on [[SQL]] for all of the computations, they are not suitable when the model is heavy on calculations which don't translate well into [[SQL]]. Examples of such models include budgeting, allocations, financial reporting and other scenarios.

==== Performance of ROLAP ====
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 [[#Multidimensional_OLAP_.28MOLAP.29|MOLAP]]. The {{usurped|1=[https://web.archive.org/web/20040602192842/http://www.olapreport.com/survey.htm 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 data volumes 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 [[#Multidimensional_OLAP_.28MOLAP.29|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. [[#Multidimensional_OLAP_.28MOLAP.29|MOLAP]] tools in contrast would force the data to be re-loaded into an optimal OLAP design.