Everything about Facts!

Measures are the essence of a FACT table, they are Numeric and can be Aggregated storing values that are of business interests. Each row present in a fact table corresponds to a measurement event.

The data on each row is at a specific level of detail, referred to as the grain, such as one row per product sold on a sales transaction and all the measurement rows must be at the same grain.

Measures defined above can be of the following types-

ADDITIVE: Facts/Measures that can be summarized along all dimensions. Eg Sales

SEMI-ADDITIVE: Facts/Measures that can be added across some but not all the dimensions. Eg Account Balance which cannot be summarized across time dimension.

NON-ADDITIVE: These are typically a calculated measure and cannot be summarized across the dimensions. Eg Unit Price

Fact tables store surrogate keys which are the business keys from the table that was used as the primary source for fact table. These surrogate keys ca together form a Composite Primary Key for the fact table.

Fact tables have the following column types:

• Foreign Keys
• Measures
• Lineage Columns(optional)
• Business Key columns from primary source tables (optional)

Fact tables tend to be deep in terms of the number of rows, but narrow in terms of the number of columns.

Implementing Fact Tables

Facts are always implemented after the Dimension tables as it is on the "many" side of the relationship with dimensions. If the facts tables are large they are then partitioned for easier maintenance and better performance.

**It is not necessary that all the foreign keys together uniquely identify each row of a fact table.