Roll-up (Drill-up): The roll-up operation performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by dimension reduction.
• Performing roll-up using climbing up a concept hierarchy :
o Consider a hierarchy defined as the total order “street<city <province="" or="" state="" <country.”="" <="" p="">
o Rather than grouping the data by city, the resulting cube groups the data by country.
• Performing roll-up using dimension reduction:
o One or more dimensions are removed from the given cube.
o Consider a sales data cube containing only the two dimensions location and time.
o Roll-up may be performed by removing the time dimension, resulting in an aggregation of the total sales by location, rather than by both location and by time.
Drill-down: Drill-down is the reverse of roll-up. It navigates from less detailed data to more detailed data. Drill-down can be realized by either stepping down a concept hierarchy for a dimension or introducing additional dimensions.
• Performing a drill-down operation using stepping down a concept hierarchy
o Consider time defined as “day <month <quarter="" <year.”="" <="" p="">
o Drill-down occurs by descending the time hierarchy from the level of quarter to the more detailed level of month.
• Performing a drill-down operation by adding new dimensions to a cube
o Consider the central cube of the figure
o A drill-down on can occur by introducing an additional dimension, such as customer group.
Slice: The slice operation performs a selection on one dimension of the given cube, resulting in a sub cube.
o The figure shows a slice operation where the sales data are selected from the central cube for the dimension ‘time’ using the criterion ‘time= “Q1” ’.
Dice: The dice operation defines a sub cube by performing a selection on two or more dimensions.
o The figure shows a dice operation on the central cube based on the following selection criteria that involve three dimensions: (location = “Toronto” or “Vancouver”) and (time = “Q1” or “Q2”) and (item = “home entertainment” or “computer”).
Pivot (rotate): Pivot is a visualization operation that rotates the data axes in view in order to provide an alternative presentation of the data.
o The figure shows a pivot operation where the item and location axes in a 2-D slice are rotated.
o Other examples include rotating the axes in a 3-D cube, or transforming a 3-D cube into a series of 2-D planes.
Other OLAP operations( extra points for reference)
• Drill-across operationexecutes queries involving more than one fact table.
• Drill-through operation uses relational SQL facilities to drill through the bottom level of a data cube down to its back-end relational tables.
• ranking the top N or bottom N items in lists, as well as computing moving averages, growth rates, interests, internal rates of return, depreciation, currency conversions, and statistical functions.
• OLAP offers analytical modeling capabilities, including a calculation engine for deriving ratios, variance, and so on, and for computing measures across multiple dimensions.
• It can generate summarizations, aggregations, and hierarchies at each granularity level and at every dimension intersection.
• OLAP also supports functional models for forecasting, trend analysis, and statistical analysis. In this context, an OLAP engine is a powerful data analysis tool.
APPLICATION OF OLAP:
OLAP is widely used in several realms of data management. Some of these applications include: -
- Financial Applications
• Activity-based costing (resource allocation)
- Marketing/Sales Applications
• Market Research Analysis
• Sales Forecasting
• Promotions Analysis
• Customer Analyses
• Market/Customer Segmentation
- Business modeling
• Simulating business behavior
• Extensive, real-time decision support system for managers.