Fact Table

A fact table is a central table in a star schema or snowflake schema of a data warehouse. It is used in online analytical processing (OLAP) systems to store quantitative data, called facts, about a business process or activity. Fact tables typically contain numerical measures or metrics, along with foreign keys that reference dimension tables. Here are the key characteristics and components of a fact table:

 

1. Quantitative Data:

   • A fact table stores quantitative data, also known as facts or measures, related to a specific business process or activity. These facts represent the metrics or performance indicators that are of interest for analysis and decision-making.

2. Numeric Measures:

   • Fact tables primarily contain numeric measures such as sales revenue, quantity sold, profit margin, cost, units produced, or any other measurable quantity relevant to the business domain.

3. Foreign Keys:

   • Fact tables include foreign keys that establish relationships with dimension tables. These foreign keys serve as references to the primary keys in dimension tables and enable analysts to perform multidimensional analysis by linking facts with descriptive attributes stored in dimensions.

4. Granularity:

   • Fact tables are designed to capture data at a specific level of granularity or detail. The granularity determines the level of detail at which facts are aggregated and analyzed. For example, a fact table may store sales data at the daily, weekly, monthly, or quarterly level.

5. Additive Measures:

   • Facts in a fact table are often additive, meaning they can be aggregated or summed up across different dimensions. For example, sales revenue can be aggregated across products, regions, or time periods to derive total sales figures.

6. Non-additive Measures:

   • In some cases, fact tables may also include non-additive measures, such as averages, ratios, or percentages. These measures may require special handling during aggregation to ensure accurate analysis.

7. Surrogate Keys:

   • Fact tables often include surrogate keys, which are artificial keys generated to uniquely identify each row in the table. Surrogate keys are used in place of natural keys for efficiency and consistency in data warehousing environments.

8. Types of Fact Tables:

   • There are different types of fact tables based on the nature of the business process they represent, including:
     • Transactional Fact Tables: Capture individual business transactions or events, such as sales orders, purchases, or shipments.
     • Periodic Snapshot Fact Tables: Store data captured at regular intervals, such as daily, weekly, or monthly summaries of business activities.
     • Accumulating Snapshot Fact Tables: Track the progress or lifecycle of a business process over time, typically by recording key milestones or events.

 

In summary, a fact table serves as a central repository for storing quantitative data related to business processes in a data warehouse environment. It enables multidimensional analysis and reporting by linking facts with descriptive attributes stored in dimension tables.

 

Thank you,