Measure (data warehouse)
In the context of data warehousing and business intelligence, a measure is a quantitative attribute or numeric value that represents a measurable aspect of business performance. Measures are the primary focus of analysis and reporting in a data warehouse. They are typically aggregated and sliced across dimensions to provide insights into key performance indicators (KPIs) and trends.
Measures are stored in fact tables, which are central tables in a star schema or snowflake schema. Each fact table row typically represents an event or observation and contains one or more measures along with foreign keys referencing dimension tables.
Common characteristics of measures include:
- Numeric Data Type: Measures are typically stored as numeric data types like integers, decimals, or floating-point numbers to allow for mathematical operations like summing, averaging, counting, and calculating other aggregates.
- Additivity: A key property of a measure is its degree of additivity. Measures can be additive, semi-additive, or non-additive.
- Additive measures can be summed across all dimensions (e.g., Sales Amount).
- Semi-additive measures can be summed across some dimensions but not others (e.g., Inventory Level, which can be summed across products but not across time).
- Non-additive measures cannot be meaningfully summed across any dimension (e.g., Profit Margin).
- Grain: The grain of a measure determines the level of detail at which the measure is recorded. For example, sales data might be recorded at the individual transaction level, daily level, or monthly level.
- Aggregation Functions: Measures are typically aggregated using functions like SUM, AVG, MIN, MAX, COUNT, and others. The choice of aggregation function depends on the nature of the measure and the business question being asked.
Examples of common measures include:
- Sales Revenue
- Units Sold
- Cost of Goods Sold
- Profit
- Website Visits
- Number of Customers
- Inventory Level
- Employee Count
The proper identification and definition of measures are crucial for the effective design and use of a data warehouse. Well-defined measures enable accurate reporting, insightful analysis, and informed decision-making.