4x4-rubik-s-cube-on-a-man-s-hand-1002693

The basics of OLAP data modeling

In this data driven world, an enormous amount of data is collected and stored on a daily basis. But why is it important to collect and store these huge amounts of data? Having piles of raw data can help your organization to make better analysis. The problem is, data in its original form doesn’t always make much sense. By structuring your collected raw data, you will be able to make more informed decisions. The process of structuring your raw data is called data modelling. 

This is where OLAP comes in.

Blog-the-basics-of-OLAP-data-modeling-768x432

Back to the basics of OLAP.

OLAP is an acronym for Online Analytical Processing. Before we start explaining the basics of OLAP, you might wonder why OLAP can be handy for your organization. Well, OLAP is a multi-dimensional database technology that permits to perform quick data analysis on many data records. This analysis will provide relevant information aimed at better decisions taking, storytelling and planning. In summary, OLAP is a software technology that allows organizations to perform multidimensional analysis of collected data. It provides the capability for complex calculations, trend analysis and data modelling with one goal: understanding your business better.

When learning about OLAP, there is no getting away from the terms dimensions, cubes, measures and hierarchies. Here are some definitions that will make it easier to understand their relevance.

  1. Cubes: OLAP tools use multidimensional database structures, called cubes. An OLAP cube, or a data cube, is a multidimensional data set that allows fast analysis of data, according to the multiple dimensions you set up. You can compare a cube with a multidimensional spreadsheet: you can collect data from users and store that data in a transparent way and calculate when needed. In order to form a cube you need dimensions.
  2. Dimensions: Dimensions are lists of related items used to organize your data in similar categories, such as products, time or regions. Dimensions are the basis for the data structure of an OLAP data cube. For example, the months and quarters may make up your year dimension. You can compare dimensions with the business parameters that you normally see in the rows and columns of a report. A model can consist in multiple dimensions such as:
    • organizational structure of the company
    • product structure
    • version (for simulations and final)
    • scenario (actual , budget , forecast , best case , worst case , …)
    • measure (Account list , FTE , Headcount , SKU , … )
    • currencies
    • exchange rates
    • year – period

    In practice, dimensions need to be limited to +/- 12 in order to remain workable for end users and calculation engine. Depending on technology used the dimension can be higher without impact on performance.

  3. Measures: Each cube must have at least one measure. But in reality, we see that cubes often contain multiple measures. An OLAP measure is a numeric value by which the dimensions are detailed or aggregated. It gives you the information about quantities you’re interested in. Do you have difficulties with defining you OLAP measures? Ask yourself the question ‘how much…?’ and your answer will be your OLAP measure. Measures can be financial or non-financial example: COA’s , KPI’s , FTE ‘s, Volumes , … .
  4. Hierarchies: Hierarchies are the subcategories of your dimensions. They have multiple levels and allow you to drill down or drill up your data. What is drilling, you may ask? Drilling allows you to analyze your data at different levels of granularity. (example : total volume , volume by product group, volume by packaging by product group, volume by SKU by packaging by product group ).

 

Conclusion: OLAP is a common technology behind many Business Intelligence and CPM applications and is still most relevant today. Using OLAP can help your organization with your analyses, forecasting and planning. In short, it should contribute to better decision-making and eventually lead to more profit.

Do you have question or need more information? Our expert is happy to help you with this! Contact Christiaan Van t’ klaphek or Stijn Hermans for more information.

Share This Post

More To Explore

Aexis is excited to participate in AI for Finance 2024, an essential gathering for finance professionals focusing on the latest AI innovations.

What is Corporate Performance Management (CPM)? Corporate Performance Management involves a set of tools and processes that help businesses plan, monitor, and

GemBud

Budgettering voor gemeentes volgens de BBC richtlijnen