OLAP is a widely spread technology belonging to Business Intelligence processes developed to coordinate and analyze vast amounts of data. OLAP databases are stored in the form of multidimensional cubes where each cube comprises the data supposed relevant by a cube administrator. Through certain OLAP operations, a user is able to obtain a specified view of the cube and extract requisite information from it. So this way it’s possible to get a necessary Pivot Table and Pivot Chart report.
General OLAP operations involve Drill-up, Drill-down, Pivot, and Slice-and-Dice. Here we’d like to expand the list and look through all possible OLAP operations for data mining including slicing and dicing in OLAP with examples.
Drill-up is an operation to gather data from the cube either by ascending a concept hierarchy for a dimension or by dimension reduction in order to receive measures at a less detailed granularity. So that to see a broader perspective in compliance with the concept hierarchy a user has to group columns and unite the values. As there are fewer specifics, one or more dimensions from the data cube will be deleted, when this OLAP operation is run.
Here’s a typical example of a Drill-up operation:
Drill-down is an operation opposite to Drill-up. It is carried out either by descending a concept hierarchy for a dimension or by adding a new dimension. It lets a user deploy highly detailed data from a less detailed cube. Consequently, when the operation is run, one or more dimensions from the data cube must be appended to provide more information elements.
Have a look at the example of a Drill-down operation in use:
The operation of Slice takes one specific dimension from a cube given and represents a new sub-cube which provides information from another point of view. It can create a new sub-cube by choosing one or more dimensions. The use of Slice implies the specified granularity level of the dimension.
Performing Slice operation will look the following way:
Dice emphasizes two or more dimensions from a cube given and suggests a new sub-cube, as well as Slice operation does. In order to locate a single value for a cube, it includes adding values for each dimension.
The diagram below shows how Dice operation works:
This OLAP operation rotates the axes of a cube to provide an alternative view of the data cube. Pivot clusters the data with other dimensions which helps analyze the performance of a company or enterprise.
Here’s an example of Pivot in operation:
The operation of Scoping restrains the presentation of the database objects to a specified subset. It will let users receive and update certain data values which they want. If there is a huge amount of data and a user needs to constrain the access of information to a specified subset Scoping is mostly conducive.
Screening is conducted to limit the set of data extracted.
Drill across reconciles cells from several data cubes which share the same scheme.
Drill through enables to navigate from data at the lower level in a cube to data in the operational systems whence the cube was ejected. The operation is usually exploited to identify the cause of outlier values in a data cube.
Sort brings the cube back where the members of a dimension were sorted.
Thanks to this OLAP operation one is able to add new measures to a cube.
In contrast to Add Measure, it’s also possible to get rid of a measure from a data cube if it's not necessary.
Due to an opportunity of Union, you can unite a number of cubes which have the same scheme but separate instances.
Difference eliminates the cells in a cube which are owned by another one. These two cubes must possess the same scheme.
As a final point, its a must to point out that OLAP system contains all historical processing of information which you’ll be able to see in a summarized and multidimensional view drawing on the operations described above. Through them, the data will turn out flexible and user-friendly to analyze.