Transaction Fact

Transaction Fact Table
Periodic Snapshot Fact Table
Accumulating Fact Table

Transaction Fact Table

src: www.aroundbi.com

What is the GRAIN here?

by day by store by product

This is a Transaction Fact table. It captures the lowest amount of data.

It helps you to find

What are the sales of Store 1?

What is the sale of a particular product per day?

Simple structure.
Keeps most detailed level data.
Easy to aggregate.
Most additive fact.

Transaction	Periodic	Accumulating
Stores lowest grain of data.	Stores current state of data at a regular interval of time.	Stores intermediate steps that has happened over a period of time.
one row per Transaction	one row per time period	one row per entire lifetime of the event
Date dimension is lowest table	Date/time is regular interval of snapshot frequency	multiple data dimensions for intermediate steps
Easy to aggregate	minimal aggregation	not easy to aggregate
largest database size	Medium Database size	Smallest Database Size
Only Insert	Only Insert	Insert and Update
Most business requirement	business performance is reviewed at regular interval	the Business process has multiple stages