Fact Tables

Submitted by wolf on

Fact Table


In simple terms, Facts store Events, Countable attributes and Dates. They are linked to dimensions to get the who, what, and where of a fact. The Natural Key (NK) of a fact is derived from the combination of the dimension keys. The NK of a fact is also referred to as the Grain of a Fact. Data changes are observed for a given NK. Most all fact tables link to a Date dimension to record the moment in time the event occurred.

There are different kind of Fact Tables. Each with a specific purpose and set of control attributes.

Standard Fact tables store Measures, Events and Countable attributes. Links to dimensions limit the redundand attributes. Updates are made to existing records within the table. ;New data is stored in by inserting a new row into the table.

Insert Only Fact table - All changes to the fact table are treated as inserts only. The most recent record depicts the most recent values for a given Natural Key and Max(Create Date/Time).

Snapshot Period Fact tables store Snapshots Periods are typically: Daily for intraday transactions, Weekly, Monthly, Quarterly and Yearly. Each period is retained for later analysis and comparisons.

Snapshot Point in Time Fact tables - Snapshots Point In Time selections are typically: End of Day, End of Week, End of Month, End of Quarter or End of Year. Changes to the Fact record are updated within the Snapshot. The time period becomes part of the grain. The data stores usually represents only one time period, for example the End of Week fact would only contain one weeks worth of data.

Aggregate Fact tables are used to summarized fact data, eliminating / reducing the number of keys in the overall grain.  The source is usually a detailed Fact table and has a Date/Time dimension component. Used for summarizing detailed fact data:  Count, Sum, Min, Max, Avg 


Limited A2B Data control attributes are added to record last update information.

For example: Sales, Employment History, Heart Rate measurements, Credit Card Transactions

           Employee Department History Fact

Data Change Observation

New NK combinations will generate a new record.  Existing NK combinations will update the record with most recent values. If the NK combination no longer exists in the source system, the row is marked as deleted. No history of data changes is retained.

The New Record will have attributes set to the following values:
          Delete Row = 'N'
          Insert DateTime = Processed Date/Time
          Update DateTime = Processed Date/Time

The Updated Record will have the following attribute values set:
          Update DateTime = Processed Date/Time

The Deleted Record will have the following attribute values set:
          Delete Row = 'Y'
          Update DateTime = Processed Date/Time

Primary Key

Primary Key is assigned for the unique combination of Natural Key of the Driver Table and Source System Indicator. Since the Natural keys may be the same across source systems, adding the Source System Indicator will make it unique.