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
Posted in Uncategorized

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Revolutions

Tomaz doing BI and DEV with SQL Server and R

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

DB NewsFeed

Matan Yungman's SQL Server blog

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Clocksmith Games

We make games we love to play

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R

Search Msdn

Tomaz doing BI and DEV with SQL Server and R

R-bloggers

Tomaz doing BI and DEV with SQL Server and R

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by (750) R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and .net in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

William Durkin

William Durkin a blog on SQL Server, Replication, Performance Tuning and whatever else.

$hell Your Experience !!!

As aventuras de um DBA usando o Poder do $hell

%d bloggers like this: