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.