What are pivot tables?

A pivot table is a table element with fixed roles and types for its columns, suitable for processing and aggregating multi-dimensional data.

Grouping, sorting, and summarizing can be performed on a pivot table. The results can be displayed in different ways.

A pivot table has two types of columns: dimensions and measures. A column is either a dimension or a measure. A pivot table has one type of row, a fact row. The values in the cells of a row are either dimension values or measures, depending on the column type.

Data is sorted by the dimension values. A column usually has many rows with identical dimension values. The dimensions can be seen as forming a hierarchy. For this reason, dimension, can also called hierarchies.

A measure is aggregated. If the measure is numeric, the aggregation could be an average of the measure values, the sum of the measure values, the maximum or minimum of the measure values, or another mathematical function.

For example, a table has the dimension columns "Country" and "Region". After sorting the data, several rows starting with  {"Afghanistan","1 North",..} are at the top, followed by rows starting with {"Afghanistan","3 South",..}, followed by rows starting with {"Albania","1 North",..}. "Country" and "Region" form a hierarchy or tree where a country branch has sub branches for its regions. The innermost dimension contains the "facts" or "values" (the measure columns from the fact rows). In a tree representation, the leaves of the tree are records containing the values for the measure columns.

Relationship to business graphs

The pivot table is a generalization of the business graph objects. For example, a CATEGORYCHART is a PIVOTTABLE with two dimensions (The "categoryKey" and "key" attributes in the CATEGORYITEM element) and one measure (the "value" attribute in the CATEGORYITEM element) on which an aggregation is performed for both dimensions. The following table compares the business graph objects to the pivot table.
Table 1. Comparing chart objects and pivot tables
Element type Number of dimensions Number of measures Number of aggregation groups Aggregation functions Sorting options
MAPCHART One (specified by the key attribute) One (specified by the value attribute) One (values with the same key value are summarized) Summarizing By key, value, and input order
CATEGORY CHART Two (specified by the key and categoryKey attributes) One (specified by the value attribute) One (values with the same key + categoryKey value combination are summarized) Summarizing By keys, value, and input order
XYCHART None Two (specified by the x and y attributes) None None None
PIVOTTABLE N (specified by HIERARCHY elements) N (specified by MEASURE elements) N (Aggregation can be performed on all dimensions) Summarizing and others (such as count, average, maximum, minimum, and so on) Input order and any combination of measures