Measure types in fact table

Fact table holds the measures data for measuring the performance of your business. Your business might be sales, purchasing, inventory, logistics, banking, telephony data, and many more. Normally, one fact table represent a line of core business and it usually takes more fact tables to cover more complex business aspects (purchasing, selling, sales, etc.) Nevertheless, a fact table holds the necessary key metrics (based on business rules) for measuring the a line of business performance and setting up main and relevant key performance indexes (KPI).

Based on line of business, measures types in fact tables can be: fully-additive, semi-additive or non-additive.

Fully-additive (short: additive) fact table holds measures that are (and can be) grouped, summed through all the dimensions.

Semi-additive fact table holds measures that can be grouped, summed or aggregated through some dimensions but not all the dimensions (commonly: time dimension.

Non-additive fact table holds measures that can not be grouped, summed or aggregated in any aspect. These measures are normally derived and calculated measures, such as percentages, ratios, running sums  or any similar measures that are typical done on BI layer against the cube using MDX and not stored as a measure in fact table.

Be aware, a fact table can have all three types of measures (additive, semi-additive and non-additive) and therefore, BW designer must know the business in order to have measure type correctly defined.

Of course, all measures can be summed or grouped, but the outcome and results will be absurd. Usually – a rule of thumb – holds, that semi-additive or non-additive already hold some business rule, because both already have some logic applied to.

For better clarification, this is an example of customer purchases from a normal fact table.

Measure_types

All measures can be summed, but do you want to do this? NO! So clearly the only fully-additive measure is transaction_value, which can be summed and grouped along all dimensions (Date, CustomerID,…).

Semi-additive measure is credit_left, because it can not be grouped or summed along Date  (time) dimension, because each value of this measure is a snapshot from a certain time period. Imagine, grouping this measure, the more customer would be buying, the more credit he would have.

And non-additive is in this case a calculated measure Percent_of_ABC_products_bought, which can not be aggregated on none of the dimensions, but is represented for each line of fact table as it is.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s