OLAP (online analytical processing) is an analysis technique with functionalities such as summarization. consolidation, and aggregation as well as the ability to view information from a different angle. This system manages a large amount of historical data, and stores and manages information at different levels of granularity. These features make the data easier to use and in informed decision-making. An OLA P system adopts either a star and snowflake model and a subject-oriented database design. It also deals with information that originates from a different organization, item-using information from any data store. Because of their huge volume, OLAP data are stored on multiple storage media.
The basic operation of OLAP is given below. Each of the operations described below is illustrated in fie: 2.9. At the center of the figure is a data cube for All Electronic, sales. The cube contains the dimensions location, time, and item, where location is aggregated with respect to city values, time is aggregated with respect to quarters. and the item is aggregated with respect to item types.
The roll-up operation (also called the drill-up operation by some vendors) performs aggregation on a data cube. either by climbing up a concept hierarchy for a dimension or by dimension reduction. Fig. 2.9 shows the result of a roll-up operation performed on the central cube by climbing up the concept hierarchy for location. This hierarchy was defined as the total order “street < city < province_orstate < country”. The roll-up operation shown aggregates the data by ascending the location hierarchy from the level of the city, to the level of the country In other words, rather than grouping the data by city, the resulting cube groups the data by country.
When roll-up is performed by dimension reduction, one or more dimensions are removed from the given cube.
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. Fig. 2.9 shows the result of a drill-down: operation performed on the central cube by step, down a concept hierarchy for the time defined as “day <month < quarter < year”. Drill-down occurs by defending, the time hierarchy from the level of a quarter to a more detailed level of the month. The resulting data cube details the total sales per month rather than summarizing, them by quarter.
Slice and Dice
The slice operation performs a selection on one dimension of the given cube, resulting in a subcube. Fig. 2.9 shows ‘a slice operation where the sales data are selected from the central cube for the dimension time using the criterion time = “Q1”. The dice operation defines subcube by performing a selection on two or more dimensions. Fig. 2.9 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 (also called rotate) is a visualization operation that rotates the data axes in view in order to provide an alternative presentation of the data. Fig. 2.9 shows a pivot operation where item and lOcation axes in a 2-D slice are rotated.
Other OLAP Operation –
Some OLAP systems offer additional drilling operations. For example, drill-across executes queries involving (i.e., across) more than one fact table. The drill-thrOugh operation uses relational SQL facilities to drill through the bottom level of a data cube down to its back-end relational tables.
Other OLAP operations may include 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.