The basics of OLAP data modeling
The basics of OLAP data modeling are explained in this blog post.
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 modeling. This is where OLAP comes in.
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 permit 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 modeling 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.
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.
Dimensions are lists of related items used to organize your data in similar categories, such as products, time and/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 : example :
- organization 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 , … )
- exchange rates
- year – period
In practice dimension 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.
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 nonfinancial example: COA’s , KPI’s , FTE ‘s, Volumes , … .
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 KSU by packaging by product group ).
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.