OLAP (online analytical processing) is computer processing that enables a user to easily and selectively extract and view data from different points of view. For example, a user can request that data be analyzed to display a spreadsheet showing all of a company’s beach ball products sold in Florida in the month of July, compare revenue figures with those for the same products in September, and then see a comparison of other product sales in Florida in the same time period. To facilitate this kind of analysis, OLAP data is stored in a multidimensional database. Whereas a relational database can be thought of as two-dimensional, a multidimensional database considers each data attribute (such as product, geographic sales region, and time period) as a separate “dimension.” OLAP software can locate the intersection of dimensions (all products sold in the Eastern region above a certain price during a certain time period) and display them. Attributes such as time periods can be broken down into sub attributes.
OLAP can be used for data mining or the discovery of previously undiscerned relationships between data items. An OLAP database does not need to be as large as a data warehouse, since not all transactional data is needed for trend analysis. Using Open Database Connectivity (ODBC), data can be imported from existing relational databases to create a multidimensional database for OLAP.
Two leading OLAP products are Hyperion Solution’s Essbase and Oracle’s Express Server. OLAP products are typically designed for multiple-user environments, with the cost of the software based on the number of users.
OLAP provides a user-friendly environment for interactive data analysis. A number of OLAP data cube operations exist to materialize different views of data, allowing interactive querying and analysis of the data.
The most popular end user operations on dimensional data are:
The roll-up operation (also called drill-up or aggregation operation) performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by climbing down a concept hierarchy, i.e. dimension reduction.
The roll down operation (also called drill down) is the reverse of roll up. It navigates from less detailed data to more detailed data. It can be realized by either stepping down a concept hierarchy for a dimension or introducing additional dimensions.
Slice performs a selection on one dimension of the given cube, thus resulting in a subcube.
The dice operation defines a subcube by performing a selection on two or more dimensions.
Pivot otheriwise known as Rotate changes the dimensional orientation of the cube, i.e. rotates the data axes to view the data from different perspectives. Pivot groups data with different dimensions.
Other OLAP operations
Some more OLAP operations include:
SCOPING: Restricting the view of database objects to a specified subset is called scoping. Scoping will allow users to recieve and update some data values they wish to recieve and update.
SCREENING: Screening is performed against the data or members of a dimension in order to restrict the set of data retrieved.
DRILL ACROSS: Accesses more than one fact table that is linked by common dimensions. COmbiens cubes that share one or more dimensions.
DRILL THROUGH: Drill down to the bottom level of a data cube down to its back end relational tables.
Download this free guide