30 Mar

Data Warehousing: Measures

Measure in a data warehouse are, to put it simply, because I’m all about simple these days, measures that you can add.  For instance, sales, is an additive measure.  But, of course, because we’re in data warehouse land it’s not that easy.

There are three types of measures.

Additive Measures

A measure that can be aggregated across all of its dimensions.  For instance, sales is additive by date, by customer, by location and so on, and so forth.

Semi-Additive Measures

This is a measure that can be aggregated by some dimensions but not by all dimensions.  The most common example is inventory.  You can add, or subtract, inventory over a time-period, so it’s additive in the sense that you can know your quantity sold for a time-period.  However, using that same time-period, would probably not yield an exact total of your inventory on-hand.

Non-Additive Measures

This is a measure that cannot be aggregated.  For instance, sales and cost data are additive in that they can be aggregated, separately,  to produce a gross profit percentage.  However, that gross profit percentage can not be aggregated further.  For instance, if you calculate gross profit by invoice, aggregating those gross profit numbers will not yield the gross profit percentage for the business.