OLAP technology has continued to develop, a good indicator of its broad applicability in the software solution market. And though newer doesn’t always mean improved, our opinion is that the most recent OLAP technologies are faster and (generally) better than their predecessors. These recent OLAP advances include aspects of in-memory OLAP combined with hybrid systems that couple the benefits of multidimensional modeling with the steadfastness of a relational database.
Which OLAP is best for your business?
When a company commits to purchasing an OLAP-based BI system, it’s essential that the system meets present and potential future needs. With the wide variety of OLAP technologies available, it has become critical to know the differences between the main types, MOLAP, ROLAP, HOLAP–and a new entrant, HTAP. While there are other versions of OLAP, with this post we have tried to help make the decision-making process a bit easier, by providing descriptions of each type, along with their advantages and drawbacks.
MOLAP: Multi-dimensional OLAP
Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats (one example is PowerOLAP’s .olp file). MOLAP products can be compatible with Excel, which can make interacting with the data very easy to learn.
Advantages:
- Excellent performance: MOLAP cubes are built for fast data retrieval, and are optimal for slicing and dicing operations.
- Can perform complex calculations quickly: often calculation logic can be handled by users (meaning, no relational database programming skills needed), and the main reason for MOLAP is precisely to speed up calculations in a multidimensional environment optimized for fast data calculation.
Disadvantages:
- Sometimes limited in the amount of data it can handle: because all calculations are performed when the cube is built, it might not be possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible, but only summary-level information will be included in the cube itself.
- MOLAP products are typically proprietary systems.
- Relevant data must be transferred from relational tables, which can be cumbersome and, by definition, redundant.
ROLAP: Relational OLAP
ROLAP products access a relational database by using SQL (structured query language), which is the standard language that is used to define and manipulate data in an RDBMS. Subsequent processing may occur in the RDBMS or within a mid-tier server, which accepts requests from clients, translates them into SQL statements, and passes them on to the RDBMS.
Advantages:
- No data limitation, can handle large amounts of data
- Can access use functionality of inherited relational databases
Disadvantages:
- Performance can be slow because of large size of data sets
- Can be limited to SQL functions, which can be inflexible
- Data may need to be reformatted for end-users
HOLAP: Hybrid OLAP
The merger of the best features of MOLAP and ROLAP allowing for fast calculations from RDBMS by using pre-calculated cubes. (New HTAP systems–see further below–may be considered HOLAP products, though they function differently from previous products that people may recognize as HOLAP.)
Advantages:
- Has the best features of both MOLAP and ROLAP: scalability, flexibility, and speed
- Uses RDBMS SQL functionality
- Can “drill-down” from a cube to a relational table
- Fast to use because of pre-calculated cubes
Disadvantages:
- Has the limitations of both MOLAP and ROLAP: as it is fast, it may not be as fast as pure MOLAP, and as it is scalable, it may not be as scalable as pure ROLAP.
What’s New in the Market?
Hybrid Transaction / Analytical Processing (HTAP)
Gartner coined the term HTAP in a paper in early 2014 to describe new in-memory data systems that do both online transaction processing (OLTP) and online analytical processing (OLAP). HTAP represents a new way to tie data together in a way that hasn’t been possible before. Combining analytical engine capabilities with relational data tables is a the root of HTAP, and we at OLAP.com think it is the way data will be managed in the future.
Advantages:
- The technology is sited in the relational database
- Powerful, often distributed, processing–which means it is fast
- No more data replication
- New transactional information becomes part of an analytical model as fast as technologically possible
- Unites the relational data tables with the models that are used for decision making by business leaders
Disadvantages:
- Change in existing architectures can be disruptive
- New technologies and accompanying skills may have to be learned
For an example of an HTAP product, check out Olation® from PARIS Tech, the sponsor of OLAP.com. Olation can be categorized as an HTAP product — even the name Olation implies the combination of “OLAP” and “relational” technologies.