Translate

Thursday 10 October 2024

what is fact table in data were housing


Fact Table: The Heart of a Data Warehouse

In a dimensional data model, a fact table is the central component that stores quantitative measurements (facts) related to a business process. These facts are typically numerical values that can be aggregated or calculated.

Key Characteristics:

  • Measurements: Contains numerical data that can be analyzed and aggregated.

  • Relationships: Connected to dimension tables through foreign keys.

  • Grain: Defines the level of detail in the fact table. For example, a sales fact table might have a grain of "order" or "product."

  • Measures: The specific metrics being tracked, such as sales amount, quantity, profit, or cost.

Example: A Retail Sales Data Warehouse

A typical fact table in a retail sales data warehouse might look like this:





SalesID

ProductID

CustomerID

DateID

Quantity

Price

Total

1

101

1001

20231001

2

29.99

59.98

2

102

1002

20231002

1

19.95

19.95

...

...

...

...

...

...

...

In this example:

  • SalesID: A unique identifier for each sale.

  • ProductID: A foreign key referencing the Products dimension table.

  • CustomerID: A foreign key referencing the Customers dimension table.

  • DateID: A foreign key referencing the Time dimension table.

  • Quantity: The number of items sold.

  • Price: The unit price of the product.

  • Total: The total sales amount for the transaction.

Relationship with Dimension Tables:

The fact table is connected to dimension tables through foreign keys. This allows for flexible analysis and reporting across different dimensions. For example, you can analyze sales by product, customer, time, or any combination of these dimensions.

In summary, the fact table is the core component of a dimensional data warehouse, storing the quantitative data that you want to analyze. It provides the foundation for understanding business processes and making data-driven decisions.


vlr

Fact Tables: A Simplified Explanation with Examples

Imagine a pizza delivery company. They want to track their sales data to understand which pizzas are the most popular, how much profit they're making, and when customers are ordering the most.

Fact Table: Pizza Orders





OrderID

PizzaID

CustomerID

OrderTime

Quantity

Price

Total

1001

1

101

2023-10-01 18:30:00

2

12.99

25.98

1002

2

102

2023-10-02 12:15:00

1

9.99

9.99

1003

3

101

2023-10-03 20:45:00

3

15.99

47.97

...

...

...

...

...

...

...

Breakdown:

  • OrderID: A unique identifier for each pizza order.

  • PizzaID: A reference to the pizza being ordered (linked to a Pizza dimension table).

  • CustomerID: A reference to the customer placing the order (linked to a Customers dimension table).

  • OrderTime: The time the order was placed.

  • Quantity: The number of pizzas ordered.

  • Price: The price of each pizza.

  • Total: The total amount of the order.

Why a Fact Table?

  • Central Data Store: It stores the core measurements or facts about the business process (in this case, pizza orders).

  • Relationships: It connects to other tables (dimensions) to provide context.

  • Analysis: You can analyze this data to understand which pizzas are selling well, when peak order times are, and which customers are frequent buyers.

Think of a fact table as a basket that holds the main ingredients of your analysis. It contains the key metrics you want to measure, and it's connected to other tables (dimensions) that provide additional details about those metrics.

Would you like to explore the dimension tables that would be associated with this fact table?


vlr
vlr
vlr

No comments:

Post a Comment

Note: only a member of this blog may post a comment.