The sophisticated technology behind modern EPM solutions is still a mystery to many. In this blog post, we’ll look at the definition of OLAP as well as an overview of the technology. We explain what lies behind OLAP, what cubes have to do with it and what makes the technology so powerful for modern planning, budgeting, and forecasting.
Background and Overview
The basis of any EPM solution is digitally available data and Online Analytical Processing (OLAP) organizes and visualizes data multidimensionally. With the major advancements in technology and the huge amount of data that is generated along with those advancements, nearly every business has digitally available data. Often, that data is still only available in hundreds and hundreds of Excel spreadsheets. More data, more spreadsheets.
Let us start with the cube. The geometric shape can be represented in a three-axis coordinate system – that is, in three dimensions. Most modern EPM solutions rely on multidimensional OLAP, also called MOLAP. The name derives from the process that accesses a multidimensional database as its operating foundation.
Let’s say your data set has figures for various products entered, including customers and time of purchase. In cube form, for example, the X-axis in the coordinate system represents the time dimension, the Z-axis represents the customer dimension, and the Y-axis represents your product dimension. The size of the cube is therefore determined by the existing data and is therefore dynamic from the ground up. Each combination of dimension data is assigned values, each of which represents a coordinate in the cube.
Here’s an example: In March 2020, customer C purchased product B. The result of this relationship are quantifiable values, such as the sales revenue or the amount purchased, which are stored, so to speak, at the coordinate that can now be exactly determined (data point). Using the MOLAP technology, you can use various functions to enter data into an underlying multidimensional database and then process that data.
Thanks to MOLAP, many more dimensions are possible, which quickly exceed average human capabilities for data visualization. To get an idea of how it works, the cube works really well as a model. Additional data dimensions such as region, distribution channel, stock, etc. are often used.
What’s so special about OLAP technology?
And we’re back to Excel sheets: Here we have our data on a row and column level, so it’s two-dimensional. This means that the best you can hope for are understanding two-dimensional relationships in your data. Both rows and columns must always be populated, even if this means, for example, that the same month, the same product, or the same customer, etc. is listed several times. As a result, the sheets quickly become large and cumbersome – especially when multiple people have to access them and make changes. This is similar to relational databases, in which a data set (row) is put into relation via attributes (columns) in a two-dimensional way.
With the multidimensional model described above using the cube, multiple entries of the same dimensions become redundant. Once created in the database, they point to the various target coordinates in the form of key figures to which they are linked. The multidimensional arrangement of the data has many advantages. Let’s take a look at the most important ones.
The advantages of OLAP at a glance
The multidimensional approach to data storage allows you to quickly create ad hoc reports, for example by slicing the cube. Following our example above, you might decide to visualize the sales of your entire product range across all customers for a specific year or month. In other words, a complete “slice” of data points with key figures is extracted, whose “thickness” is determined by a specified time period.
Similarly, “dicing,” i.e. the extraction of a smaller cube from the “main cube,” which contains all the data, is another way to parse your data. For example, you can limit the selection to individual products or product groups in a defined time period and for a select segment of customers.
By creating hierarchies, you can also perform what is commonly known as “drilldowns.” This allows you to obtain detailed values, for example, for individual products in a product group or for the detailed key figures of a single point of sales in your sales network.
In addition to the possibility of fast ad hoc reports, automation for standard reports is one of the key advantages of OLAP. Once correctly set up, the time required and the susceptibility to errors is reduced significantly. Planning processes also benefit because the relevant data can be entered directly into the multidimensional database by planners instead of having to collect and validate the data using endless manual Excel sheets.
Finance professionals and business planners using modern EPM solutions with OLAP technology enjoy a variety of benefits that help to optimize value creation across the organization. Learn more about the powerful technology behind Jedox.