Multidimensional online analytical processing (MOLAP) is the basic type of online analytical processing (OLAP) that businesses use to analyze data more quickly and efficiently than they can achieve with other technologies. Using MOLAP, businesses can improve their understanding of customers, uncover trends and make timelier, better-informed decisions. MOLAP and other forms of OLAP reflect the software industry's recognition that business analysis requires different technology characteristics than transactional applications such as order processing.

This article describes how MOLAP works, how it compares to relational online analytical processing (ROLAP), and discusses key considerations for businesses planning to implement MOLAP technology.

What Is MOLAP?

In MOLAP systems, database professionals capture snapshot copies of the business's data, typically from data warehouses, and store this data separately in multidimensional arrays known as "data cubes." The data stored in these data cubes is aggregated, summarized, organized and processed in predefined ways that aim to better optimize the data for the types of questions business managers and other users are likely to ask. Modern MOLAP systems store data in memory, which allows for even faster querying.

Using this approach, database administrators (DBAs) can optimize storage, indexing and caching, and handle a great deal of calculation in advance, so that the MOLAP system responds very quickly to users' queries. MOLAP was the first widely used approach to OLAP and, while other approaches now exist, some discussions of OLAP still focus almost entirely on the MOLAP model.

Key Takeaways

  • MOLAP promotes better-informed business decisions through analysis of data an organization already has generated.
  • MOLAP has applications throughout the organization — in sales, marketing, finance, merchandising, manufacturing.
  • In MOLAP systems, data stored in data warehouses is copied into multidimensional data cubes optimized for fast querying based on the types of questions an organization's analysts ask most often.
  • MOLAP contrasts with ROLAP, where multidimensional views are created dynamically when needed — potentially offering greater flexibility but slower performance. Hybrid "HOLAP" systems attempt to combine the best of both approaches.
  • As MOLAP, ROLAP and HOLAP technologies have matured, some traditional distinctions between them have begun to blur.

MOLAP Explained

MOLAP systems accelerate and enhance business analysis by copying relevant portions of a company's data from data warehouses to multidimensional data cubes that have already been optimized for exploratory tasks. "Multidimensional" is the critical word in that sentence, as opposed to the two-dimensional data tables in which information is stored in traditional databases. The three dimensions of a single data cube make for faster searches and easier data extraction. And nesting MOLAP cubes within one another lets business analysts explore the data more deeply and faster, allowing them to rapidly broaden or narrow their searches.

All this is accomplished through four primary MOLAP data analysis functions:

  • Drilling down into more detailed data. For example, seeing which SKU delivered the highest growth in a product family, or going even more deeply to see how that growth differed by region or even individual stores within a region.
  • Rolling up into higher-level summary data — the opposite of the drill-down example, e.g., understanding performance by region instead of by state or county.
  • Slice-and-dice to segment data. For example, exploring sales by product, month and store at the same time.
  • Pivot to rotate the view of a selected data set so a business leader can view it from a different perspective. An example: viewing a product's sales during a given year by location, and then pivoting to view its overall sales over several years.

By storing both source data and core calculations in multidimensional cubes ahead of time — often in a different area of the same data warehouse from which the data was originally extracted — MOLAP systems can respond quickly to common queries. But early MOLAP implementations often faced scalability and flexibility challenges.

MOLAP Architecture

A typical MOLAP system contains three conceptual components: a database server, MOLAP server and presentation tool that accepts analysts' queries and delivers the results. These work together in the following way:

Database server.

In a traditional three-tier MOLAP architecture, the database server is the first tier. It serves as the repository for data that is extracted from multiple sources and then cleaned and integrated.

MOLAP server.

The middle tier of a MOLAP system is the MOLAP server, which retrieves data from the data warehouse (typically via SQL) and loads it into multidimensional data cubes optimized for the data requests business managers and analysts are expected to make. The MOLAP server also responds to those user queries, sending results to the presentation layer/user interface.

Presentation, or front-end, tool.

Working through this tool, managers, analysts and other business professionals explore the information stored in data cubes in the MOLAP server. They create requests, typically by using point-and-click tools. Their requests are sent to the MOLAP server for processing, often using a data manipulation language created specifically to work with multidimensional data cubes and servers, such as MDX.

While such MOLAP systems were originally deployed separately from other database technologies, MOLAP capabilities are now sometimes integrated into broader database platforms. Of note, OLAP systems, in general, are often part of a larger architecture in which they are paired with online transaction processing (OLTP) systems.

MOLAP Architecture Components

molap architecture components

MOLAP vs ROLAP

MOLAP is often compared to relational online analytical processing (ROLAP), another OLAP variant. While MOLAP organizes data into predefined OLAP cubes and is considered the fastest type of OLAP system, ROLAP works directly with data in a relational database — it reorganizes the data into cubes on the fly in response to specific queries. MOLAP is faster, easier to use, and lets business analysts sort data in more ways with less programming effort, while ROLAP can analyze larger data volumes than MOLAP. But ROLAP can slow performance of the underlying relational database — which could be a major issue if that database is also hosting a transactional application, like an ecommerce website.

A Comparison of MOLAP vs ROLAP


MOLAP ROLAP
Data is copied from a business"s data repository into data cubes within a multidimensional database. When underlying data is updated, it can take time before the data cubes are synced. Data remains in relational databases that are also used for other purposes (such as transactions). This eliminates data redundancy and update latency but could slow performance of the underlying database.
Precalculated data cubes have traditionally enabled faster queries in many OLAP applications. Uses SQL OLAP queries that are generally slower but may be more accessible to database professionals who are already familiar with SQL.
May not always scale to store sufficient data or handle enough dimensions for some large analytical applications. Can scale to support extremely large data sets stored in relational databases and to handle larger numbers of dimensions than MOLAP.
New query requirements may necessitate creation of new data cubes. New SQL queries can be built to meet new requirements, making ROLAP systems potentially more flexible. However, complex OLAP SQL queries must be written carefully to achieve acceptable performance.

More recently, a hybrid type of OLAP has emerged — called HOLAP — that organizes data into cubes but also works directly with relational data. It combines the speed of MOLAP with the scalability of ROLAP but is more complex to use than either of them. In addition, other MOLAP-ROLAP distinctions have blurred: For instance, while ROLAP was once required to drill through summary data into a detailed transaction, some MOLAP systems can do this now as well.

MOLAP Implementation

MOLAP deployments require careful upfront design and modeling. Organizations considering a MOLAP implementation must factor into their design planning a careful assessment of their business goals, the types of questions they expect to ask most often, the data sources available to them, and the capabilities and sophistication of their users. All of these may affect the organization's choice of solution. Among other issues, organizations should consider a MOLAP system's performance and flexibility, the ease of creating or changing OLAP data cubes, visual and other reporting capabilities, and the intuitiveness and power of the presentation/user interface tools.

As an organization designs its data cubes, it needs to clarify dimensions (data categories such as time or location), hierarchies (levels of subcategories that help users roll up or drill down on their data) and measures (assessments of outcomes such as revenue or costs). Typically, each cube will be linked to a specific area of analysis, but cubes can be linked so users can explore issues that go beyond narrow organizational silos.

Benefits of MOLAP

MOLAP is widely viewed as offering several key benefits. These relate to performance, efficiency and analytical operations, including:

  • Query speed.

    Traditionally, the main advantage of MOLAP systems has been speed. MOLAP is faster, first, because its architecture is built from the ground up to reflect the unique requirements of analytical processing in its handling of caching, indexing and storage, and, second, because MOLAP handles much of the calculation in advance. In essence, many answers are ready before analysts ask the questions.

  • Size.

    MOLAP data cubes eliminate redundancy, so data is compressed — requiring less expensive storage than corresponding data stored in a relational database.

  • Ability to work offline.

    Since it is a data cube that is being queried, not the relational database behind the data, some analytical work can be performed without a live connection to the relational database.

Disadvantages of MOLAP

MOLAP's benefits may come with trade-offs that sometimes lead database professionals and analysts to choose a different OLAP solution. Traditionally, MOLAP's disadvantages have included:

  • Limited scalability and number of dimensions.

    Some MOLAP systems work best with a limited amount of data and may encounter significant performance deterioration as data volumes or the number of dimensions being analyzed grow.

  • Potentially less flexibility.

    MOLAP systems require careful design to reflect the types of queries that will be performed. Traditionally, additional professional design has been called for when new types of queries are needed, slowing business decision-making.

  • Redundant data and latency.

    With MOLAP, analysts are querying a processed copy of data stored in a separate set of cubes, rather than directly querying the original data. Accordingly, DBAs are required to manage two sets of data. This also introduces latency: Up-to-date information added to the relational database may not be available to the analyst. For some applications, this is no problem, but analytics increasingly demands real-time or near real-time information.

  • Language challenges.

    While relational databases nearly all use some dialect of the SQL language to execute queries. MOLAP cubes may require additional languages to retrieve data and perform calculations.

MOLAP Tools

In general, MOLAP tools are the complete set of architectural components (outlined above) that comprise a full MOLAP implementation, though business analysts who refer to MOLAP tools often mean the presentation layer that accepts their queries and returns results. A wide variety of MOLAP tools have been introduced, some of which can support both MOLAP and ROLAP. Oracle Essbase and Microsoft SQL Server Analysis Services are among the most prominent. Increasingly, MOLAP and other OLAP services are being delivered through the cloud, leveraging the cloud's inherent scalability and manageability.

Industries That Use MOLAP

MOLAP and other forms of OLAP are utilized everywhere companies need to analyze business performance, sales, customer relationships or trends — and aid them in exploring ways to ask new questions and uncover problems and opportunities that might not be immediately visible in transaction data. For example, in health care or insurance, MOLAP applications are used to identify and predict risks and outcomes. An ecommerce or bricks-and-mortar retailer might use MOLAP to assess the impact of pricing changes. A university seeking to expand enrollment might use MOLAP to evaluate where new learners are coming from, which programs are receiving the most inquiries and which types of inquiries lead to enrollments.

As these examples suggest, MOLAP can provide value to many parts of an organization, from sales and marketing and finance, to budgeting and forecasting, and to factory production planning and defect analysis.

Conclusion

MOLAP and other forms of online analytical processing help organizations gain more timely and useful information for decision-making. MOLAP systems typically consist of a database server, a MOLAP server and a presentation/user interaction front end. Traditionally, MOLAP has provided a speed advantage compared with the ROLAP alternative, which is considered slower but more flexible. More powerful underlying hardware and highly scalable cloud architectures have begun to blur these distinctions, and some hybrid solutions have emerged that split data between multidimensional and relational databases in an effort to balance analytical performance and flexibility.

Award Winning
Warehouse Management
Software

Free Product Tour

MOLAP FAQs

What are the differences among OLAP, ROLAP, MOLAP and HOLAP?

The term online analytical processing (OLAP) refers to any computing application intended for analysis rather than transactions, and comprises variants such as MOLAP, ROLAP and HOLAP. Multidimensional online analytical processing (MOLAP) is a version of OLAP where source data is maintained in a separate multidimensional database, using data cubes designed in advance and optimized for rapid analytical queries. ROLAP, in contrast, operates on standard relational database tables. Analysts still get a multidimensional view of their information, but it's created dynamically when they make a request. Hybrid online analytical processing (HOLAP) systems seek to combine the best features of MOLAP and HOLAP, attempting to optimize performance and responsiveness while reducing latency.

What are ROLAP and MOLAP in data warehouse environments?

ROLAP and MOLAP are both forms of online analytical processing (OLAP) that draw on centralized data repositories typically maintained in a data warehouse. MOLAP servers regularly query data warehouses and copy their data into data cubes, which the MOLAP system must then maintain and keep up to date. ROLAP systems directly query relational databases when a user request is made and create multidimensional views of the resulting data dynamically, so users can explore it.

What are the main advantages and disadvantages of a MOLAP cube?

MOLAP cubes are designed to deliver outstanding performance when responding to the types of queries a company's analysts are expected to present most often. However, if analysts need to ask fundamentally different questions, additional cubes may have to be developed and implemented, which can take time and database programming skill.