Why is OLAP required?

OLAP is a data structure that provides multidimensional cubes from data and provides useful information for decision makers. While designing an OLAP system, the most important factor is reporting requirements. Compared to OnLine Transactional Processing (OLTP), OLAP provides faster reports because it reshapes data in cube structures rather than the traditional relational structure based on tables.

As an example, assume that you want to create a report in a BI system for sales based on products and dates. You might need to get the sales amount per day, per month, or per year. You might also have categories of products. So, it will be helpful if you have a graph with two dimensions, Product as one dimension and Date as another dimension:

Having two dimensions helps to achieve each granular point and each conjunction of both dimensions easier and faster. For example, point A shows the total sales amount of ProductA in August 2012. Each point in the preceding graph is a measure, and each line shows a dimension attribute.

Now, assume that the report needs another dimension, Store. Also, the Store dimension might have its own attributes and hierarchies as well, for example, types of stores and branches of stores. So, you will need a graph with three axes as shown in the following diagram:

The structure shown in the previous diagram helps you to find the junction of the three dimensions in every granular level of the defined attributes. For example, the total sales amount of ProductA in August 2012 in a New Zealand store is a point of junction of three axes of each value represented together in the cube shown in the previous diagram. This structure is also called a cube. This is how the OLAP cube is named: the dimensions are axes of the cube and the measures are connection points of it.

In real-world requirements, you might need many more than three dimensions for some reports and analytics, so the real-world structure of cubes will have more than three dimensions, but they cannot be illustrated on paper so we cannot show you a sample of cube with eleven dimensions.

The OLAP tools are utilities that provide the ability to create an OLAP cube structure, and then data will be loaded into that structure. After loading data into that structure, which is called processing, information can be easily gathered from data by querying each point or conjunction of the cube axis.