In the previous article, we learned about “Data Warehouse Schema: Star and Snowflake”. In today’s article, we will cover different types of OLAP Operations In Data Warehousing.
In the multidimensional data model, the data is organized in the form of data cubes. The data cube represents data in multiple dimensions. The dimensions contain multiple levels of abstraction which can be viewed from different perspectives. These views are supported by different OLAP operations which allow data querying and analysis in an interactive way.
Let us take a look at different OLAP operations for multidimensional data:
Roll up
The roll-up operation
performs aggregation on the data by moving up the concept hierarchy. This
process is also called a drill up operation. The roll-up operation happens with
a reduction in dimensions. For example, if the concept hierarchy-“city <
state < country”, the roll-up operation will aggregate the data from city to
level state and level country. When this operation is applied to the data cube
will show grouping according to the state instead of the city. The
dimensionality reduction is done by removing the dimension from the data cube.
Another example of roll-up is when total sales are viewed by city and month. By
performing roll up on it, the dimension month can be removed and aggregation of
total sales can be viewed on the city rather than both the city and month.
The first cube shows the sale according to cities for a month in June and July. When roll up is applied the city Vancouver and Toronto are grouped to Canada.
Roll up is performed in the city. Aggregation of the city happens to the country.
Drill Down:
By drill down, it moves from aggregated level to a more detailed level. The less detailed data is now shown as more detailed data. Drill down is performed by moving downwards in the concept hierarchy or by adding some dimensions. For example: the concept hierarchy: location<city<state<country. Drill down is performed from the country level to state level to city level.
The drill-down operation can also be performed by adding a new dimension. For example, to examine the total sales, new dimension branch_name can be added to get a more detailed view of total sales.
In this example, the country dimension is drilled down to the city level for detailed information.
The first cube shows the sales of AC and refrigerator for city Canada. The drill-down is performed over the country to city level. The second cube shows total sales city wise for cities Vancouver and Toronto.
Drill Down is done over the country to move from Country to City
Slice and Dice Operation:
Slice: Slice operation is performed by selecting one dimension from the given data cube. This will lead to a subcube. An example of slice operation would be selecting a single dimension for example month to observe the sales. The resultant cube by performing slice operation on the data cube for a single dimension month= June for all cities would be:
| Vancouver | ||
| Toronto | ||
| New Delhi | ||
| Chandigarh | 300 | 500 |
| AC | Refrigerator |
Dice: A dice operation forms a subcube by selecting two or more dimensions. An example of dice operation would be selecting three dimensions month and City and product to analyze total sales. The resultant cube for Month= June and July, City= Delhi and Chandigarh and Product= AC, would be:
Pivot:
Pivot is a rotation of data cube along the axes to get the view of data presentation on the alternative site. Pivoting is also called rotation. The pivot operation can be applied to both 2D and 3D planes. In 2D planes the axes are changed will in 3D plane, pivoting leads to rotation of axes or transformation of 3D to multiple 2D figures. Below example shows pivoting on a 2 D:
| Vancouver | ||
| Toronto | ||
| New Delhi | ||
| Chandigarh | 300 | 500 |
| AC | Refrigerator |
Pivoting is done
| AC | 300 | |||
| Refrigerator | 500 | |||
| Toronto | Vancouver | New Delhi | Chandigarh | |
Drill Across:
Drill across operation performs queries on more than one fact table.
- Drill Through
- Other OLAP operations
Drill through applies SQL operations to drill down to its back relation database table.
Other OLAP operations may include ranking the items, computing average, finding out depreciation, converting the currency or performing some statistical operations.
Functions of OLAP:
- OLAP operations can perform summarization, aggregation at different levels of granularity.
- It can generate hierarchies at the dimension level
- OLAP has models for prediction, analysis of trends and patterns and well as statistical analysis.
- OLAP has analytical capabilities for calculation, ratios and variance derivation across dimensions.
- OLAP operations are done for business operations and handling a huge amount of data.
How to query a multidimensional database:
A starnet model is used to query a multidimensional database. In this model, there exists a central point from where the radial linearise. The radial lines represent the concept hierarchy of each dimension. The abstraction level in a hierarchy is called a footprint. The various OLAP operation discussed above can be applied using this model.
A starnet model is shown as below.
We can apply different OLAP operations such as:
Rollup: Roll up is applied to location from street to country.
Drill Down: Drill down applied on Customer from a group of the customer to name of the customer.
Generalization: Moving up the hierarchy. Generalizing day of time dimension with year.
Specialization: Moving down the hierarchy. Moving from the year of the time dimension to the day.
⇓ Subscribe Us ⇓
If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:
Happy Testing!!!
