It is all about data !!! Data Science, Data analytics,Data warehouse : OLAP Concepts

OLAP Concepts

Hi Guys,

Happy day folks. This explanation is about OLAP cubes. I will try to put as simple as that so that everyone can understand better.

CUBE Concept

Let us assume a salesman selling these items for 3 days

Item	Day	Type	Price	Sales
Apple	April 1	Fruit	10	20
Apple	April 1	Fruit	10	60
Orange	April 1	Fruit	5	5
Apple	April 2	Fruit	5	25
Apple	April 2	Fruit	5	20
Orange	April 3	Fruit	3	30
Apple	April 3	Fruit	10	10

If someone asks you the below questions, what would you do ?

1. How many apples were sold on April 1 and April 2 ? How much was the total sales on April 1 and April 2

Traditional approach is to write couple of group by aggregation queries separately and provide the result

What if we store all pre calculated value in three dimensional fashions?

Just imagine the CUBE which is presented below exists already for all the reporting you require.

· Summary calculations are all summation of the corresponding rows and Columns – I have highlighted in green Color

· Such calculations helps the analysts and reporting groups to immediately look for the data

· Also since the data is already summarized, this is fast retrieval. If we try to do the same thing using SQL query especially with the large database with millions of rows. The performance is going to slow down a lot.

ROLL UP Concept

If you notice in our initial table, we have a field called Type à Assume Type can be Fruit or Vegetable

In the above Cube, Item is categorized by Apple, Orange etc. Let us assume you need a grouping based on TYPE ( Fruit or vegetable ) à This becomes Roll up. (i.e) rolling up products based on its TYPE.