Dimensions, elements, hierarchies, and cubes

A dimension is made up of a group of similar items. These items are called elements. A dimension can be used as a list of items that make up columns or rows in a report. For example, the Region dimension contains the elements Germany, France, Italy, and Spain. The Account dimension contains the elements Revenues, Units, Variable Costs, and Gross Margin. The Product dimension is a list of all products. Dimensions contain no values, they are indices to the values contained in the cubes.

OLAP dimensions do not need to be flat lists, they can also be hierarchical, defined by parent-child relations. Elements without children are called base elements. They are used to point to data that is physically stored in the database. The parent-child relations define a set of automatic data aggregations. Numerical data related to dimension elements that are not base elements are calculated on the fly and never stored in the database.

Relationships between hierarchy elements can also be defined mathematically. For example:

Central Europe = Germany + France

Or:

Southern Europe = Spain + Italy

Here Central Europe and Southern Europe are not base elements, but aggregated elements.

The lowest level in a hierarchy includes only base elements. Each level represents a vertical position in the hierarchy. Levels group the data for aggregation. Each level above the lowest level represents the aggregated total of the levels below it

A single dimension can have multiple alternate hierarchies. Each alternate hierarchy has a unique name within the dimension. Base elements of the dimension are available to all alternate hierarchies. This means a base element can be used in one or more alternate hierarchies.

Cubes are the components of an OLAP database that contain data. A cube is a collection of dimensions assembled to create a matrix. An example would be a three-dimensional cube that contains the Month, Region, and Account dimensions.

By specifying the elements for a cell, that cell can be located and its value retrieved from the cube or a value written to this location in the cube.

In the diagram, the marked cube cell is specified by the elements Jan, Germany, and Units.

You can think of cells in a multidimensional cube in the same way as cells in a spreadsheet. The most apparent differences are that OLAP uses meaningful names to designate these cells and you are not limited to two dimensions.

Cube cells can also store text rather than numbers. For that there is a third type of dimension element: the string element. The dimension containing the string element must also be defined as the last dimension in the cube. Then the cells in this cube that have the string element as coordinate are text cells.

In the example, the Account dimension is defined as the last dimension in the cube. In case the element Units would be a string element, then the marked cell would be text cell.

If the dimension containing the string element is not the last dimension in the cube, then the string element is treated for this cube as if it would be a base element. In the example, if only Jan would be a string element, then the marked cell would still be a numeric cell.