Translate

Thursday, 10 October 2024

Facts and dimensions in dimensional modeling

Facts in dimensional modeling are the quantitative measurements or metrics that you want to analyze. They represent the core business processes or events that you're tracking.  

Key Characteristics:

  • Numerical Values: Facts are typically numerical values, such as sales, revenue, quantity, profit, or cost.

  • Measurements: They measure specific aspects of a business process.

  • Relationships: Connected to dimension tables through foreign keys.

Example:

In a retail sales data warehouse, the Sales fact table might include the following facts:

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, the facts are:

  • Quantity: The number of items sold.

  • Price: The unit price of the product.

  • Total: The total sales amount.

Relationship with Dimension Tables:

The fact table is connected to dimension tables through foreign keys. For example, the ProductID, CustomerID, and DateID in the fact table reference the corresponding dimensions. This allows you to analyze the facts along different dimensions (e.g., sales by product, customer, or time).

In summary, facts are the core data elements that you want to analyze in a dimensional data warehouse. They provide the measurements and metrics that drive your business insights.

Dimensions in dimensional modeling are the descriptive attributes that provide context for the facts stored in a fact table. They define the different perspectives or angles from which you can analyze the data.

Key Characteristics:

  • Descriptive Data: Contain attributes that describe the dimensions of the data.

  • Hierarchies: Often have hierarchical structures to allow for drilling down or rolling up to different levels of detail.

  • Relationships: Connected to the fact table through foreign keys.

Example:

In a retail sales data warehouse, common dimensions might include:

  • Time: Year, quarter, month, day, hour.

  • Products: Product category, subcategory, brand, model.

  • Customers: Customer ID, name, region, city.

  • Geography: Country, state, city, region.

Purpose:

Dimensions provide a framework for analyzing data from different perspectives. For example, you can analyze sales data by product category, customer region, or time period.

Types of Dimensions:

  • Conforming Dimensions: Dimensions that are shared across multiple fact tables, ensuring consistency and enabling cross-dimensional analysis.

  • Degenerate Dimensions: Dimensions that are included in the fact table as attributes, rather than being stored in a separate dimension table.

In summary, dimensions are the building blocks of a dimensional data model, providing the context and structure for analyzing data effectively.

Would you like to know more about specific types of dimensions or how to design effective dimension tables?

vlrvlr

No comments:

Post a Comment

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