OLAP Operations in the Multidimensional Data Model
In the multidimensional model, the records are organized into various dimensions, and each dimension includes multiple levels of abstraction described by concept hierarchies. This organization support users with the flexibility to view data from various perspectives. A number of OLAP data cube operation exist to demonstrate these different views, allowing interactive queries and search of the record at hand. Hence, OLAP supports a user-friendly environment for interactive data analysis.
Consider the OLAP operations which are to be performed on multidimensional data. The figure shows data cubes for sales of a shop. The cube contains the dimensions, location, and time and item, where the location is aggregated with regard to city values, time is aggregated with respect to quarters, and an item is aggregated with respect to item types.
Roll-Up
The roll-up operation (also known as drill-up or aggregation operation) performs aggregation on a data cube, by climbing down concept hierarchies, i.e., dimension reduction. Roll-up is like zooming-out on the data cubes. Figure shows the result of roll-up operations performed on the dimension location. The hierarchy for the location is defined as the Order Street, city, province, or state, country. The roll-up operation aggregates the data by ascending the location hierarchy from the level of the city to the level of the country.
When a roll-up is performed by dimensions reduction, one or more dimensions are removed from the cube. For example, consider a sales data cube having two dimensions, location and time. Roll-up may be performed by removing, the time dimensions, appearing in an aggregation of the total sales by location, relatively than by location and by time.
Example
Consider the following cubes illustrating temperature of certain days recorded weekly:
| Temperature | 64 | 65 | 68 | 69 | 70 | 71 | 72 | 75 | 80 | 81 | 83 | 85 |
| Week1 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| Week2 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 2 | 0 | 1 | 0 | 0 |
Consider that we want to set up levels (hot (80-85), mild (70-75), cool (64-69)) in temperature from the above cubes.
To do this, we have to group column and add up the value according to the concept hierarchies. This operation is known as a roll-up.
By doing this, we contain the following cube:
| Temperature | cool | mild | hot |
|---|---|---|---|
| Week1 | 2 | 1 | 1 |
| Week2 | 2 | 1 | 1 |
The roll-up operation groups the information by levels of temperature.
The following diagram illustrates how roll-up works.
Drill-Down
The drill-down operation (also called roll-down) is the reverse operation of roll-up. Drill-down is like zooming-in on the data cube. It navigates from less detailed record to more detailed data. Drill-down can be performed by either stepping down a concept hierarchy for a dimension or adding additional dimensions.
Figure shows a drill-down operation performed on the dimension time by stepping down a concept hierarchy which is defined as day, month, quarter, and year. Drill-down appears by descending the time hierarchy from the level of the quarter to a more detailed level of the month.
Because a drill-down adds more details to the given data, it can also be performed by adding a new dimension to a cube. For example, a drill-down on the central cubes of the figure can occur by introducing an additional dimension, such as a customer group.
Example
Drill-down adds more details to the given data
| Temperature | cool | mild | hot |
|---|---|---|---|
| Day 1 | 0 | 0 | 0 |
| Day 2 | 0 | 0 | 0 |
| Day 3 | 0 | 0 | 1 |
| Day 4 | 0 | 1 | 0 |
| Day 5 | 1 | 0 | 0 |
| Day 6 | 0 | 0 | 0 |
| Day 7 | 1 | 0 | 0 |
| Day 8 | 0 | 0 | 0 |
| Day 9 | 1 | 0 | 0 |
| Day 10 | 0 | 1 | 0 |
| Day 11 | 0 | 1 | 0 |
| Day 12 | 0 | 1 | 0 |
| Day 13 | 0 | 0 | 1 |
| Day 14 | 0 | 0 | 0 |
The following diagram illustrates how Drill-down works.
Slice
A slice is a subset of the cubes corresponding to a single value for one or more members of the dimension. For example, a slice operation is executed when the customer wants a selection on one dimension of a three-dimensional cube resulting in a two-dimensional site. So, the Slice operations perform a selection on one dimension of the given cube, thus resulting in a subcube.
For example, if we make the selection, temperature=cool we will obtain the following cube:
| Temperature | cool |
|---|---|
| Day 1 | 0 |
| Day 2 | 0 |
| Day 3 | 0 |
| Day 4 | 0 |
| Day 5 | 1 |
| Day 6 | 1 |
| Day 7 | 1 |
| Day 8 | 1 |
| Day 9 | 1 |
| Day 11 | 0 |
| Day 12 | 0 |
| Day 13 | 0 |
| Day 14 | 0 |
The following diagram illustrates how Slice works.
Here Slice is functioning for the dimensions “time” using the criterion time = “Q1”.
It will form a new sub-cubes by selecting one or more dimensions.
Dice
The dice operation describes a subcube by operating a selection on two or more dimension.
For example, Implement the selection (time = day 3 OR time = day 4) AND (temperature = cool OR temperature = hot) to the original cubes we get the following subcube (still two-dimensional)
| Temperature | cool | hot |
|---|---|---|
| Day 3 | 0 | 1 |
| Day 4 | 0 | 0 |
Consider the following diagram, which shows the dice operations.
The dice operation on the cubes based on the following selection criteria involves three dimensions.
- (location = “Toronto” or “Vancouver”)
- (time = “Q1” or “Q2”)
- (item =” Mobile” or “Modem”)
Pivot
The pivot operation is also called a rotation. Pivot is a visualization operations which rotates the data axes in view to provide an alternative presentation of the data. It may contain swapping the rows and columns or moving one of the row-dimensions into the column dimensions.
Consider the following diagram, which shows the pivot operation.
Other OLAP Operations
executes queries containing more than one fact table. The drill-through operations make use of relational SQL facilitates to drill through the bottom level of a data cubes down to its back-end relational tables.
Other OLAP operations may contain ranking the top-N or bottom-N elements in lists, as well as calculate moving average, growth rates, and interests, internal rates of returns, depreciation, currency conversions, and statistical tasks.
OLAP offers analytical modeling capabilities, containing a calculation engine for determining ratios, variance, etc. and for computing measures across various dimensions. It can generate summarization, aggregation, and hierarchies at each granularity level and at every dimensions intersection. OLAP also provide functional models for forecasting, trend analysis, and statistical analysis. In this context, the OLAP engine is a powerful data analysis tool.