A fact is an entity that provides quantitative information about the business. We store these numeric entities into a fact table.

There are 3 types of Fact Tables in Data Warehouse.

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

Transaction Fact Table:

It stores the lowest grain of data. Each and every transaction that occurs in a day. Each row in the fact table uniquely identifies a day. Transaction Fact tables are very common in the warehouse world. One of the main goals of the data warehouse is to capture the lowest level of business detail that could answer business questions.

• What is the sales amount for store A for Product B?
• Show me a trend for Last Month?

The transaction fact table best fits in responding to most business requirements.

Features of Transaction Fact Table:

• Simple Structure
• Keeps most detailed Level
• Easy to aggregate
• Since grain is per day reporting for monthly data or weekly data can be easily done.
• Mostly additive fact

The transaction fact table will not answer every business question in the real world. In some business requirement when we need to analyze the current status of process transaction fact tables are less effective in responding to these requests.

Periodic Snapshot Fact Table:

In the health care industry when we create a warehouse for hospitals bed is an essential entity exact status of bed in the hospital is a basic requirement for better efficiency, we need to know the current status of all beds.

• How many beds are available for patients?
• How many are occupied?
• How many are in the cleaning process?

If you create a transaction fact table it wouldn't answer these questions for bed status.

we have to look for other types of fact tables which will provide detailed status of bed status at any point in the hospital process. We need a picture of all bed status at a moment and this requirement brings Periodic Snapshot Fact Table.

• Store Current State at a regular interval
• Explain the main state of entities at a particular instance of time.
• Time Interval - Day, Week, Month, Few hours.
• Best Case - Analyse business performance at a fixed interval

Common Examples:

Inventory Process, Hospital System

Accumulating Snapshot Fact Table:

When we need to describe what has happened over a period of time it is called Accumulating Snapshot Fact Table. For any process which has to begin and ending day time period, we can use the accumulating Snapshot Fact Table to show data.

We can take the example of online order.

We are ordering products from amazon and during the completion of our order, it goes to multiple steps. Each step has a fixed date as follows.

• Order Date
• Shipping Date
• Estimate Arrival Date
• Fulfillment Date

A row for each order is inserted into the accumulating snapshot fact table with multiple date fields. As the process moves from one milestone dates to other these date values along with location or status gets updated.

Accumulating Snapshot Fact Tables almost have all date fields that denote the phases of the process. Since many of these dates are not known we use undefined dates to fill these columns and later on come back to update the dates when data is available. This is something different from the transaction fact table and Periodic Snapshot Fact Table.

I hope it helps.