Скачать или смотреть What is a Fact Table in Data Warehousing ?

what is a fact table in data warehousing
In data warehousing, a fact table is a central component that stores quantitative data for analysis and reporting. Here's a closer look at what a fact table is and its role in data warehousing:

Key Features of a Fact Table:
Quantitative Data (Facts):

Fact tables store numerical data, often referred to as measures, that can be aggregated and analyzed. Examples include sales amount, units sold, and revenue.

Foreign Keys:

A fact table includes foreign keys that link to dimension tables, providing the context for the facts. These keys are used to join the fact table with dimension tables in queries.

Granularity:

The granularity of a fact table refers to the level of detail represented by each row. For example, the grain could be individual transactions, daily summaries, or monthly summaries.

Additivity:

Facts can be additive (can be summed across all dimensions), semi-additive (can be summed across some dimensions), or non-additive (cannot be summed).

Example of a Fact Table:
Consider a retail business analyzing its sales data. A typical fact table might look like this:

Date ProductID StoreID SalesAmount UnitsSold
2024-12-01 101 1 5000 250
2024-12-01 102 2 3000 150
In this example:

SalesAmount and UnitsSold are the facts.

Date, ProductID, and StoreID are foreign keys linking to dimension tables for date, product, and store, respectively.

Types of Fact Tables:
Transactional Fact Table:

Stores data for individual transactions. Each row represents a single event or transaction.

Snapshot Fact Table:

Captures data at regular intervals (e.g., daily, monthly) to provide a snapshot of performance metrics over time.

Accumulating Snapshot Fact Table:

Tracks the progress of processes that have a definite beginning and end, such as order fulfillment. It updates records as events occur.

Purpose of Fact Tables:
Analysis: Fact tables allow for the analysis of business metrics over various dimensions, such as time, product, and location.

Aggregation: They enable the aggregation of data to generate reports and insights.

Decision Making: Fact tables provide critical data that supports strategic business decisions.

In summary, a fact table is a core element of a data warehouse that stores quantitative data and facilitates detailed and meaningful business analysis.