Tuesday, November 29, 2016

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.

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.


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.




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.

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.







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.

This table will give information at the Monthly period.
These are the mainly used Fact tables in Data warehouse design.

No comments:

Post a Comment