Cubes, dimensions and hierarchies are the essence of OLAP’s multidimensional navigation. By describing and representing information in this way, users can intuitively navigate a complex set of data. However, just describing the data model in a more intuitive way does very little to help deliver the information to the user more quickly.
A key principle of the OLAP is that users should obtain consistent response times for each data view they require. Since the information is collected at the level of detail only, the summary of the information is usually calculated in advance. These pre-calculated values are the basis of OLAP performance gains.
In the early days of OLAP technology, most companies assumed that the only solution for an OLAP application was a non-relational storage model. Later, other companies discovered that through the use of database structures (star and snowflake schemes), indexes and the storage of aggregates, relational database management systems (RDBMS) could be used for the OLAP.
These vendors called this relational OLAP technology (ROLAP). The first companies then adopted the term OLAP multidimensional (MOLAP), these concepts, MOLAP and ROLAP, are explained in more detail in the following paragraphs. MOLAP implementations typically perform better than ROLAP technology, but they have scalability issues. On the other hand, ROLAP implementations are more scalable and are often attractive to customers because they take advantage of investments in pre-existing relational database technologies.
The MOLAP architecture uses multidimensional databases to provide the analysis, its main premise is that the OLAP is better implemented by storing the data multidimensionally. On the contrary, the ROLAP architecture believes that OLAP capabilities are perfectly implemented on relational databases. A MOLAP system uses a multidimensional proprietary database, in which information is stored multidimensionally, to be visualized in several analysis dimensions.
The MOLAP system uses a two-tier architecture: the multidimensional databases and the analytical engine. The multidimensional database is in charge of the management, access and obtaining of the data.
The level of application is responsible for the execution of the OLAP requirements. The level of presentation is integrated with the application level and provides an interface through which end users view OLAP analysis. A client / server architecture allows multiple users to access the same multidimensional database.
The information coming from the operational systems is loaded in the MOLAP system, through a series of batch routines. Once the elementary data has been loaded in the multidimensional database (MDDB), a series of batch calculations are performed to calculate the aggregated data, through the business dimensions, filling in the MDDB structure.
After filling this structure, indexes and algorithms of hash tables are generated to improve the access times to the queries. Once the compilation process is over, the MDDB is ready for use. Users request reports through the interface, and the application logic of the MDDB obtains the data.
The MOLAP architecture requires intensive compilation calculations. Reads precompiled data, and has limited capabilities to create aggregations dynamically or find ratios that have not been pre-calculated and stored previously.
The ROLAP architecture accesses the data stored in a data warehouse to provide OLAP analysis. The premise of ROLAP systems is that OLAP capabilities are better supported against relational databases.
The ROLAP system uses a three-tier architecture. The relational database handles the data storage requirements, and the ROLAP engine provides the analytical functionality. The database level uses relational databases to manage, access and obtain the data. The application level is the engine that executes the multidimensional queries of the users.
The ROLAP engine is integrated with presentation levels, through which users perform OLAP analysis. After the data model for the data warehouse has been defined, the data is loaded from the operational system. Database routines are executed to add the data, if so required by the data models. The indexes are then created to optimize the access times to the queries.
End users execute their multidimensional analysis, through the ROLAP engine, which dynamically transforms their queries to SQL queries. These SQL queries are executed in the relational databases, and their results are related by cross-tables and multidimensional sets to return the results to the users.
The ROLAP architecture is capable of using pre-calculated data if they are available, or of dynamically generating the results from the elementary data if necessary. This architecture directly accesses data from the data warehouse, and supports access optimization techniques to speed up queries. These optimizations are, among others, partitioning of the data at the application level, support for denormalization and multiple joins.
A slightly more recent development has been the hybrid OLAP solution (HOLAP), which combines the ROLAP and MOLAP architectures to provide a solution with the best features of both: superior performance and great scalability. A type of HOLAP maintains the detail records (the largest volumes) in the relational database, while keeping the aggregations in a separate MOLAP store.
Hope this technical post has been useful for you and looking forward to your comments!