Categories

# Dimensional Modelling – Facts

photo credit: Felipe Morin

I am often asked by my colleagues to explain dimensional modelling concepts to them. I will try to capture come on the concepts here. I am going to focus on Facts.

## Accumulating Fact Table

An accumulating fact table is where all of the dimensional attributes are not available at the time of creation and the dimensions that are linked to a fact table change over time. The most common implementation of this is in the recording of dates against facts.

Take a “Sales” fact, typical dates you may be intersted in when tracking an individual sale is maybe, order_date, ship_date, delivery_date and payment_date. These would not all be available when the fact is first created. Over time the fact record would accumulate more relationships with the dimensions as the relevant date milestones were passed for the sale.

## Factless Facts

A factless fact is where the fact does not store an actual numerical measure, the mere existance of a fact record indicates that an event has happened that you wish to track.

The classic example of this would be an “Attendance” fact. If you had dimensions to record date, scheduled_course, instructor and delegate then you could create a fact table that held the permutations of these dimensions. From this you could evaluate the number of courses you run, the number of delegates, the number of courses by instructor etc.

I would never simply leave a factess fact as a bare collection of foreign key columns I would always add a dummy measure column which would be set to 1 which you would then sum.

## Transaction Grain

This is the most common type of fact. You would declare the grain of the fact, ie the level of detail and then this is what would be stored. For example you may have a sales_order fact, every time a new sales order a new row would be created in the fact table. alternatively you may have a “monthly_sales” fact. At the end of every month you would aggregate up all the sales that happened in that month and record the single total value.

## Snapshot Facts

The snapshot fact contains a reflection of the state of an entity at a given point in time. A classic example of this would be a “daily_balance” fact in a banking system. This would, on a daily basis record the balance of each account, it would NOT list the individual transactions that happened on the account.

A fully additive fact is one where the measures can be aggregated.

For example our Sales fact above would be fully additive as you can aggregate the sales amout over time, by product, by region or by salesman and still get the correct answer.