Different Types of Facts and Fact Tables in Data warehouse Design


In the previous post

Different Types of Dimensions used in Data warehouse Design.

we have discussed most commonly used types of Dimensions in Data warehouse Design.

And as promised in this post I will be discussing most commonly used types of Facts and Types of Fact tables.

But before going into Fact and Fact Tables lets see what  “Rapidly Changing Dimensions” is and how we handle that from the last blog post.

Rapidly changing Dimensions are dimensions where attributes are changing more often than normal.

i.e Customer Age,Rating.

To handle these types of attributes,We can create mini Dimensions which have only attribute that is changing rapidly.

Shown bellow we have split age attribute as new dimension ,This newly created Dimension called Rapidly Changing dimension.

Original Dimensions

 

Rapidly changing DimensionsUpdate Original Dimension

 

 

 

 

Now lets talk through Facts and Fact Tables

What are the Facts in Data warehouse?

Facts are the values or measurements which represent a business process at the same grain level.

i.e. Sales price of a particular product is the fact about product.

In simple terms,Facts are the Numbers or values which can be sliced and diced with various dimensions.

Types of Facts

There are three types of facts:

  1. Additive Facts
  2. Non-additive Facts
  3. Semi-Additive Facts

We will look in to these Fact types with examples.

1. Additive Facts

Additive Facts are the Facts that can be summed up through all dimensions associated in data warehouse.

I.e. Sales Price of a Product.

AdditiveFacts

2.Non Additive Facts

Non Additive Facts can not be summed for any dimensions related to Fact table in Data warehouse.

Some facts are completely non additive such as ratios,% of Profit, Profit margin etc.

Usually,These  types of facts or measures derived in OLAP (CUBE) layer of solution as Calculations.

Non Additive Facts

 

In Above diagram Net Profit Margin is Non Additive Fact which can not be summed by  Customer or Date.

i.e. if on the 1st net profit margin is 10% for Customer A and on 2nd it is 25% for same customer ,We can not Summarize it as 35% for Customer A for both days.

3.Semi-Additive Facts

These are the facts which can be summed up against all the dimensions in Data warehouse except Time dimensions.

It is very import type of facts.

Classic example of it is Account balance in Fact table.

 

Semi-Additive

 

In Above example Balance amount can not be aggregated over the time.

i.e. for A customer Balance on 1st of June is £100,in 2nd June is £150. so Balance for A customer on 2nd June can not be done as £100+£150=£250.

Now we will go into different types of Fact Tables used in data warehouse design.

What are the Fact Tables?

Fact Tables are the most import part of Data warehouse.They mainly contains Facts about the business process.

I.e. Sales price of the product which is the part of Sales (business process).

Types of Fact Tables

Most of the fact tables fall in one of the following types of fact tables.In a Data warehouse project,We may need to use  either one ,two or all of them.Selections of the Fact tables in Data warehouse depends on the Business needs.

Each fact tables addresses specific requirements presented by Business.

  1. Transaction Fact Tables
  2. Accumulating Snapshot Fact Tables
  3. Periodic Snapshot Fact Tables

Lets walk through each of them one by one.

1.Transaction Fact Tables

  • These are most commonly used fact tables.
  • Each row in this table represent a specific event in business process.
  • They  contain more foreign keys than other types as they maintain relationship to all possible dimensions.
  • They  get heavily sliced and diced to get answers by business users.
  • Facts in these type of tables are mostly Additive in nature.

I.e. Sales Fact table shown below,which contains single row for every sales orders.it also have foreign key relationship with most of the dimensions i.e. Customer,Date,Order No,Sales amount etc.

2015_06_23_20_04_45_Tx.png_Windows_Photo_Viewer

2. Accumulating Snapshot Fact Tables

This type of Fact table will represent entire life cycle  of a business process  from the beginning to end of the process (i.e. sales Order Processing,Claim Processing).

Each record in this type of table represent one entity of the respective business process and then this record will be getting updated every time as per the current status of the entity.

Lets take an example of the Sales order, a typical Sales order going through following phases.

  • Order generated
  • Picking order
  • Packing order
  • Shipping order

In fact table we will have one record for one order and this record getting update based on the status of the order when Data warehouse processing taking places

Following diagram will give overview how Fact table holds information for a typical Sales Order and you can see when order is shipped all respective columns getting updated accordingly.

Order Placed

 

Order Picked

Order packed

 

Order shipped

3.Periodic Snapshot Fact Tables

This table represent Snapshot of a business process for specific period of time.In this Fact table grain may not be at the business process level.It summarize the activity for a span of time, it can be month,year or week.

In the following figure we can see Sales and Discount for customers at Monthly period.

This table will update every time data warehouse getting processed.

Periodic SnapShot

This table will give information at the Monthly period.

These are the mainly used Fact tables in Data warehouse design.

Hope this will be useful and informative post.

In next post,I will discuss How we can use Project Parameters in  BIML (Business Intelligence Markup Language)?

As always please do share your thoughts and comments !!!!

Happy Learning

SqlBI