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.
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.
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 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.
A Measure fully additive can be aggregated across all dimensions.
Semi-Additive facts can be added only among some of the dimension.
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:
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.
Measures that are valid for:
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.
A level-based measure is a column whose values are valid only for a specific combination of level.
A level-based measure may be:
A calculated measure should be store physically in the database because :
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 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.
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.