Hiprup

What is the difference between a dimension and a measure in analytics?

Dimensions describe; measures quantify. Dimensions answer by what (region, product, month) and measures answer how much (revenue, units, customers). This split is the foundation of dimensional modelling and of every BI tool's field list.

  • Dimensions are descriptive and categorical — region, channel, product, customer segment and date; typically text or dates, relatively low cardinality, and used to filter, group and label.

  • Measures are numeric and aggregated — revenue, quantity, cost and order count; they are meaningless on a single row in a report and only become useful once summed, averaged or counted.

  • The SQL test — whatever sits in GROUP BY is a dimension and whatever sits inside SUM, COUNT or AVG is a measure, which resolves almost every ambiguous case.

  • Not every number is a measure — a postcode, a store ID or a year are numeric but never summed; they are dimensions, so ask whether adding the values would mean anything.

  • Additivity matters — revenue is fully additive across all dimensions, inventory balance is semi-additive (never summed across time), and ratios and distinct counts are non-additive and must be recomputed at every level.

Key terms: dimension, measure, fact table, star schema, GROUP BY, cardinality, additive, semi-additive, non-additive

-- Dimensions = what you slice BY.  Measures = what you aggregate.
SELECT
    -- DIMENSIONS: descriptive, used to group and label
    c.region,
    c.channel,
    DATE_TRUNC('month', f.order_date)  AS order_month,

    -- MEASURES: numeric, only meaningful once aggregated
    SUM(f.net_amount)                  AS revenue,          -- fully additive
    COUNT(*)                           AS order_count,      -- fully additive
    COUNT(DISTINCT f.customer_id)      AS customers,        -- NON-additive

    -- RATIO: recompute from numerator and denominator, never sum or average a rate
    SUM(f.net_amount) / NULLIF(COUNT(DISTINCT f.customer_id), 0) AS revenue_per_customer
FROM       fact_orders  f
JOIN       dim_customer c ON c.customer_id = f.customer_id
GROUP  BY  c.region, c.channel, DATE_TRUNC('month', f.order_date);
-- The test: everything in GROUP BY is a dimension, everything in an aggregate is a measure.


-- Numeric but NOT a measure -- summing these is meaningless
SELECT store_id,          -- an identifier: group by it, never SUM it
       postal_code,       -- a label that happens to be digits
       SUM(net_amount) AS revenue
FROM   fact_orders
GROUP  BY store_id, postal_code;


-- Why distinct counts and ratios cannot be rolled up:
--   region  customers  revenue
--   North         800    40000
--   South         700    35000
--   TOTAL       1300 (not 1500) -- 200 customers bought in BOTH regions
-- Company-wide customers must be recomputed over all rows, not added from the region rows.

The first query is the definition made executable: the three fields in GROUP BY are dimensions and the four aggregated expressions are measures, which is exactly the test to fall back on when a field is ambiguous. It also grades the measures by additivity — revenue and order count sum cleanly across any dimension, while the distinct customer count does not, and revenue_per_customer is a ratio computed from its two parts rather than aggregated.

The second query shows the trap that catches people who classify by data type: store_id and postal_code are numeric but adding them would be nonsense, so they are dimensions. The closing comment block gives the concrete arithmetic behind non-additivity — 800 plus 700 customers does not give 1300 company-wide because 200 people bought in both regions, so the total has to be recalculated across all rows rather than summed from the sub-totals, and the same reasoning applies to every rate and percentage.

Give the definition in one line, then win the question with the GROUP BY test and a numeric-but-not-a-measure example such as a postcode — that pair shows practical understanding rather than memorised terminology. The strongest closing point is additivity, especially the rule that ratios and distinct counts must be recomputed at each level and never summed, because that is the bug interviewers have all seen in real dashboards.

Expect a follow-up asking where each lives in a star schema (dimensions in dimension tables, measures in the fact table) and possibly "can a field be both?" — yes, price can be a measure to average and a dimension to band into price ranges.