About
In a dimensional model, a measure is a quantitative attribute of a fact (in a fact table) that is not a foreign key that creates a relationship to a dimension.
A measure permits to quantify. A calculated and aggregated measure is called a (performance) metrics.
A measure is also known as performance measurement (measurement)
The different types of measurement are categorized into four groups.
As dimensional modeling is the representation of a process, a measure is a quantitative information of an event and is then coupled to the time (dimension) forming a time serie
We cannot improve what we cannot measure, so measure we must.
Articles Related
Properties
Data Type
Every measure has a numeric data type.
Even if a boolean may be represented by the pair 0/1, it's a descriptif attribute. See Dimensional Data Modeling - A boolean is not a numerical measure but a descriptif attribute.
Unit
Each measure must have its unit of measurement. If it is a monetary measure, it may have a currency field and if it is a unit measure it may have a field to explain the kind of units used e.g. centimeters, litres, cubic metres etc.
Additivity
Additivity is crucial because datawarehouse application almost never retrieve a single fact row. The most useful facts are numeric and additive, such as sales amount.
Kimball (1996) defines three levels of additivity.
Fully Additive
A Measure fully additive can be aggregated across all dimensions.
Semi-Additive
Semi-Additive facts can be added only among some of the dimension.
Non-additive
Non Additive measures simply can't be added at all. The measures are only valid for a combination of dimension level and are therefore level-based measure.
Example:
- Stock
- and in general all calculations on one specific intersection of the dimension.
A trend for instance is only valid for one month. You can have a positive trend on one month but a negative one on one week.
Validity
Measures that are valid for:
- all level are called aggregate measure
- a combination of level: level-based measure
Aggregate
An aggregate measure is the result of a summary on a measure that can be calculated for every level (ie through a classic group by)
The result of an aggregate measure is called a metrics.
Level-based
A level-based measure is a column whose values are valid only for a specific combination of level.
A level-based measure may be:
- measured - all non additive measure such as stock, …
- defined - for example, quota, goals are only valid for a combination of level (ie by department and by month)
Calculated Measure
A calculated measure should be store physically in the database because :
- The cost of a user incorrectly representing gross profit for instance overwhelms the minor incremental storage cost.
- Storing it also ensures that all users and reporting applications refer to profit consistently.
Likewise, some organization want to perform the calculation in the query tool. This works if all users access the data using a common tool (which is seldom the case in our experience).
Conforming Measure
Conforming Measure means making agreements on common business metrics such as key performance indicators (KPIs) across separated databases so that these numbers can be compared mathematically for calculating differences and ratios.
Null keys
referential integrity is violated if we put a null in a fact table declared as a foreign key to a dimension table. In addition to the referential integrity alarms, null keys are the source of great confusion to the users because they can't join on null keys.
Avoid null keys in the fact table.
A proper design includes a row in the corresponding dimension table to identify that the dimension is not applicable to the measurement.