Measures define a measurement attribute and
are used in fact tables. You can calculate measures by mapping them
directly to a numerical value in a column or attribute. An aggregation
function summarizes the value of the measure for dimensional analysis.
Measures become meaningful within the context of a set of dimensions.
For example, a revenue of 300 is not meaningful by itself. When you
put a revenue measure in the context of dimensions, such as Region
and Time, the measure becomes meaningful: the revenue for New York
in January is 300. Common examples of measures are Revenue, Cost,
and Profit.
A measure is defined by an aggregation list. If a measure has more
than one aggregation, the aggregation functions are performed in the
order that they are listed, with each subsequent aggregation taking
the previous aggregation's result as its input.
Each aggregation specifies a function that is applied to a corresponding
list of dimensions. The aggregation function can be any aggregation
function that is supported by the underlying database. The workbench
supports the following aggregation functions:
- AVG
- CORRELATION
- COUNT
- COUNT_BIG
- COVARIANCE
- MAX
- MIN
- STTDEV
- SUM
- VARIANCE
The measure object can only aggregate each dimension once. A
measure must have one aggregation with an empty list of dimensions,
and any other aggregations must each have an explicit list of dimensions.
The aggregation for an empty list of dimensions is applied to all
dimensions in the cube model that are not specifically being used
by another aggregation.
If the measure has an aggregation function, such as CORRELATION,
that requires two or more parameters, the measure will have two or
more SQL expressions.
Measures also have a data type that is based on SQL data types.
The workbench automatically determines the data type of a measure.
The measures in a fact table can be one of the following types:
- Additive
- Additive measures are measures that can be aggregated
across all of the dimensions in the fact table, and are the most common
type of measure. Additive measures are used across several dimensions
for summation purposes.
Since dimensional modeling involves hierarchies
in dimensions, aggregation of information over different members in
the hierarchy is a key element in the usefulness of the model. Since
aggregation is an additive process, use additive measures as much
as possible.
- Semi-additive
- Semi-additive measures can be aggregated across some
dimensions, but not all dimensions. For example, measures such as
head counts and inventory are considered semi-additive.
- Non-additive
- Non-additive measures are measures that cannot be aggregated across
any of the dimensions. These measures cannot be logically aggregated
between records or fact rows. Non-additive measures are usually the
result of ratios or other mathematical calculations. The only calculation
that can be made for such a measure is to get a count of the number
of rows of such measures.