What are pivot tables?

The pivot table element is a table element with fixed roles and types for its columns, suitable for processing multi-dimensional data. Grouping, sorting, and summarizing are performed. 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. No other column types exist in a pivot table. A pivot table has one type of row: fact rows. 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. There are usually many rows with identical dimension values in a column. The dimensions can be viewed as forming a hierarchy. For this reason dimension can also referred to a 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, and so on.

For example, consider a table with the dimension columns "Country" and "Region". After sorting the data, several rows starting with  {"Afghanistan","1 North",..} will be at the top, perhaps followed by some rows starting with {"Afghanistan","3 South",..} again followed by rows starting with {"Albania","1 North",..}. "Country" and "Region" form a hierarchy or tree where a country branch has sub branches for it’s regions. The innermost dimension is said to contain the "facts" or "values" (meaning 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 charts

The pivot table is a generalization the chart objects. As an example one can say that a  CATEGORYCHART is a PIVOTTABLE with two dimensions (The "categoryKey" and "key" attributes in the CATEGORYITEM element), one measure (the "value" attribute in the CATEGORYITEM element) on which a summarizing aggregation is performed for both dimensions. This table compares the different chart 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