There are three types of facts:
- Additive Facts
- Non-additive Facts
- 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.
- Transaction Fact Tables
- Accumulating Snapshot Fact Tables
- 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