Dimensional Data Modeling - Measure

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.

Properties

Data Type

Every measure has a numeric data type.

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:

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.

Documentation / Reference

Powered by ComboStrap