One of the most compelling front-end applications for OLAP is a PC spreadsheet program. Below is the list of some popular operations that are supported by the multidimensional spreadsheet applications.
- Takes the current aggregation level of fact values and does a further aggregation on one or more of the dimensions.
- Equivalent to doing GROUP BY to this dimension by using attribute hierarchy.
- Decreases a number of dimensions - removes row headers.
SELECT [attribute list], SUM [attribute names]
FROM [table list]
WHERE [condition list]
GROUP BY [grouping list];
- Opposite of roll-up.
- Summarizes data at a lower level of a dimension hierarchy, thereby viewing data in a more specialized level within a dimension.
- Increases a number of dimensions - adds new headers
- Performs a selection on one dimension of the given cube, resulting in a sub-cube.
- Reduces the dimensionality of the cubes.
- Sets one or more dimensions to specific values and keeps a subset of dimensions for selected values.
- Define a sub-cube by performing a selection of one or more dimensions.
- Refers to range select condition on one dimension, or to select condition on more than one dimension.
- Reduces the number of member values of one or more dimensions.
Pivot (or rotate)
- Rotates the data axis to view the data from different perspectives.
- Groups data with different dimensions.
- Accesses more than one fact table that is linked by common dimensions.
- Combines cubes that share one or more dimensions.
- Drill down to the bottom level of a data cube down to its back-end relational tables.
- Spreadsheet style row/column aggregates.