Measures

Each measure defines an operation that can be performed on data in the fact table. For example, SalePrice = Sum(sale), CostPrice = Sum(cost) Measure expressions then represent either one measure, or a series of measures related by arithmetic operations. For example: Profit = SalePrice - CostPrice.

Measures

The Measures panel allows you to add, edit or remove measures. Each measure must have a unique name. The measure dialog allows you to choose a field from the fact table and an operation, such as Sum, Average, Max etc. to apply to that field. Note that numeric operations such as Sum and Average can only be applied to numeric fields, whereas Count, Max etc. can be applied regardless of the field type.

Measure Expressions

The Measure Expressions panel allows you to add, edit or remove measure expressions. It is these measure expressions that you see listed on the left side of the Cube.

The simplest expression is just a single measure. To do this, click the Add button and give your measure expression a name. Then in the Expression field enter the name of a measure in square brackets, eg. [Sales]. The square brackets are optional here, but are recommended for all measure references. They are essential for measures where the name includes spaces, or special characters such as -, + etc. which would otherwise be treated as arithmetic operations.

More complex expressions can include mathematical operations such as +,-,*,/ to relate several measures together. Using the example introduced above, the expression string would be [SalePrice]-[CostPrice].

It is also possible to construct more complex expressions using an extension of the measure syntax. Complex measures support the following prefixes:

[ParentCol:SalePrice]
[ParentRow:SalePrice]
[RootCol:SalePrice]
[RootRow:SalePrice]
[PreviousCol:Dim:SalePrice]
[PreviousRow:Dim:SalePrice]
where SalePrice is the name of the measure and Dim is any dimension used higher in the cube (explained below).

To understand how these work, you need to think of how a cube is constructed. A cube consists of two trees. One for each dimension. Every cell references two tree nodes - one from the row tree and one from the column tree. These nodes will be leaf nodes, or parent nodes depending on whether we are looking at totals.

Each tree node defines a set of records. Each cell is therefore the intersection of two sets of records. With simple measures you can perform actions on the selected set of records - eg. Sum, Average, Max, but you can't refer to other cells. With complex measures you can "walk" the tree and compare your values with those of other cells - eg. what is the difference in sales this month and last.