What is OLAP Cube (In 10 minutes)?
Let’s comprehend what it is by differentiating the two words.
OLAP: As mentioned in the previous article, OLAP is a tool that is used within Data Warehousing to export data to BI tools or programming languages after de-normalising the data from OLTP too via ELT process.
Cube: Cube is a 3-dimensional structure (or sometimes even higher). A Metrix is mathematical or technical calculation of pair of numbers.
So, what OLAP Cube does is that it mixes the three together to show results which is an aggregation of a Metric based on a number of dimensions in a 3-dimensional form.
An OLAP Cube looks something like the image above. You can see that we have 3 dimensions month, movie name and city name with fact table in the fields.
An OLAP Cube is an aggregation of measure (number) based on the number of dimensions
We mentioned in the previous article that OLAP tool works best with Star Schema which is based on dimensions within the Data Warehouse. So, once we have a Star Schema only then we can create OLAP Cube.
4 Common OLAP Operations
The four common OLAP operations are “rollup, drill down, slice and dice”.
Rollup
Rollup is an extension of a Group By clause where values are aggregated on multiple axes. So, what it does is that it creates multiple grouping sets along the hierarchy of columns.
So, in the example above you have Countries in the vertical column headers. Thus, what rollup does is that groups all the cities within each country and gives the aggregation of a matric based on country level not city level.
Drill-down
Apposite to rollup, it goes down to lower level instead of going to higher level. So, instead of showing country it might show states/regions/districts of the city. If city is the lowest level in the geographic hierarchy then it will stay at city level.
Slice
Slicing is simply about reducing number of dimensions. If the cube is 3-dimensional than it become 2 dimensional. the 3rd dimension doesn’t disappear but it becomes a filter.
So, say we wanted to filter to just one specific month then we can use slicing. We can instead filter to just one movie “Avatar” like what was it’s performance throughout the month for each city and so on.
Dice
Now, in slicing we did filter on one dimension but in dice you can do filtering on multiple dimensions if not all of them. Also you can filter to multiple attributes unlike slicing. What it does is that it reduces the size of the data to create a sub-cube of the original cube. So, the dimensions do not reduce but the size of the data does.
In this example, we diced the cube by filtering to just two months and two movies for the city New York.