In this article we will look at some basic concepts related to OLAP (On-Line Analytical Processing). OLAP is a user interface concept that provides the ability to get significant insights from data, allowing you to analyze it deeply from multiple angles. The basic functions of OLAP are multidimensional visualization of data; Exploration; Rotation; and Multiple viewing modes.
OLAP and Data Warehouse are meant to work together, while DW stores information efficiently, OLAP must retrieve it just as efficiently, but very quickly. The two technologies complement each other, to the point that a Data Warehouse, in order to be successful, must take into account what you want to present in the OLAP interface already from its conception.
Databases configured for OLAP utilize a multidimensional data model, enabling complex ad hoc and analytical queries with fast execution time. They borrow aspects from navigational databases, hierarchical databases, and relational databases.
OLAP is a user interface and not a form of data storage, but it uses storage to present information. Its storage methods are:
- ROLAP (Relational OLAP): Data is stored relationally.
- MOLAP (Multidimensional OLAP): Data is stored in a multidimensional way.
- HOLAP (Hybrid OLAP): A combination of ROLAP and MOLAP methods.
- DOLAP (OLAP Desktop): The multidimensional dataset must be created on the server and transferred to the desktop. It allows portability to OLAP users who do not have direct access to the server.
The most common data storage methods used by OLAP systems are ROLAP and MOLAP, being the database technology the only difference between. ROLAP uses Relational DataBase Management System (RDBMS) technology, in which data is stored in a series of tables and columns, while MOLAP uses MDDB (MultiDimensional Database) technology, where data is stored in multidimensional arrays.
Both provide a solid basis for analysis and have both advantages and disadvantages. To choose between the two methods, the requirements and scope of the application to be developed must be taken into account.
ROLAP is more suitable for DATA WAREHOUSE due to the large volume of data, the need for a greater number of functions and several business rules to be applied.
Relational online analytical processing (ROLAP) is a type of online analytical processing (OLAP) that analyzes data using multidimensional data models. The difference between ROLAP and other OLAPs is that it accesses data stored in a relational database rather than a multidimensional database, which is most commonly used in other OLAPs. It can also generate SQL queries to perform calculations when an end user wants to do so.
MOLAP is more suitable for DATA MARTS, where the data are more specific and the application will be directed at the analysis with limited dimensionality and little data detail.
To make a basic comparison between the two methods, the most important rules are query performance and load performance.
Regarding consultation performance, MOLAP provides a quick response to virtually any query, as all possible combinations and summaries are pre-generated in the multidimensional model.
ROLAP responds to queries in the same way as applications RDBMSs, where the response speed depends on the desired information, as most processing is done at runtime as pre-calculated and summarized data often does not meet all user requests requirements.
Regarding loading performance, MOLAP needs a long period of time to execute the data load; this load is rarely daily due to the large volume of information to be updated to enable a quick return to OLAP interface queries.
ROLAP enables faster loading due to its table and column structure, which is less complex compared to the array structure used by MOLAP. Another important factor regarding loading speed is the smaller number of pre-calculated and summarized information.
ROLAP can handle large data volumes. Although using a relational database requires more processing time, it can be accessed by any SQL tool and does not have to be a specific tool for OLAPs. Compared to multidimensional online analytical processing (MOLAP), ROLAP tools are much better at tracking non-aggregated facts such as textual descriptions.
In terms of disadvantages, ROLAP performance depends on the data size; it can be slow when the data being processed is large, and fast otherwise. While any SQL tool can access ROLAP, it is limited by these tools because SQL statements do not meet all users’ needs, especially when performing complex calculations.
Learning a SQL Server can help you focus on OLAP (Online Analytical Processing) technology as a tool to implement BI in companies through solutions provided by Microsoft. The BI (Business Intelligence) area is one of the few areas that encompasses concepts of computing, database, statistics, business and analysis. Currently, the BI area is essential to assist the decision process in almost any company that needs data to understand the choices and alternatives available before effectively making a decision.
In conclusion, we can´t affirm that one is better than the other, and that the tendency will be to use the HOLAP method (mentioned above), in which it is possible to use the advantages of both models in the same architecture.
