In the previous post
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.
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:
- 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.
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.
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 !!!!