A Fact Table is a central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often denormalized. A fact table works with dimension tables and it holds the data to be analyzed and a dimension table stores data about the ways in which the data can be analyzed.
Thus, a fact table consists of two types of columns. The foreign keys column allows to join with dimension tables and the measure columns contain the data that is being analyzed.
Types of fact table:
I. Transaction Fact Tables:
The most fundamental view of the business operations is at the individual transaction or transaction line level. These fact tables represent an event that occurred at an instantaneous point in time. A row exists in the fact table for a given customer or product only if a transaction event occurred. Conversely, a given customer or product likely is linked to multiple rows in the fact table because hopefully the customer or product is involved in more than one transaction.
II. Periodic Snapshot Fact Tables:
Periodic snapshots are needed to see the cumulative performance of the business
at regular, predictable time intervals. Unlike the transaction fact table where a row
is loaded for each event occurrence, with the periodic snapshot, you take a picture
(hence the snapshot terminology) of the activity at the end of a day, week, or month,
then another picture at the end of the next period, and so on. The periodic snapshots
are stacked consecutively into the fact table. The periodic snapshot fact table
often is the only place to easily retrieve a regular, predictable view of longitudinal
performance trends.
III. Accumulating Snapshot Fact Tables:
This type of fact table is used to show the activity of a process that has a well-defined beginning and end. For example, the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated.
Accumulating snapshots almost always have multiple date stamps, representing the predictable major events or phases that take place during the course of a lifetime. Often there’s an additional date column that indicates when the snapshot row was last updated. Since many of these dates are not known when the fact row was first loaded, we must use surrogate date keys to handle undefined dates.
In sharp contrast to the other fact table types, we revisit accumulating snapshot fact table rows to update them. Unlike the periodic snapshot, where we hang onto the prior snapshot, the accumulating snapshot merely reflects the accumulated status and metrics. Sometimes accumulating and periodic snapshots work in conjunction with one another.