Fact Table

Definition: A fact table is a central table in a star schema of a data warehouse. It stores quantitative data for analysis and is often where transactional data is aggregated.

Fact Characteristics

Contains Measurements: Fact tables primarily hold numerical measurements, metrics, or key performance indicators (KPIs) of a business's operations. Examples include sales amounts, profit margins, and quantities sold. Foreign Keys to Dimension Tables: They have foreign keys that relate to primary keys in dimension tables. This establishes the relationship necessary for joins and aggregated queries. Granularity: The level of detail or granularity is crucial. Fine granularity means the data is detailed (e.g., individual sales transactions), whereas coarse granularity implies aggregated data (e.g., total sales per month). High Volume: Because they record transactional details, fact tables tend to have a large number of rows. Additive, Semi-Additive, and Non-Additive Facts: Additive facts can be summed across any dimension (sales revenue), semi-additive facts can be summed only in certain dimensions (account balances), and non-additive facts cannot be summed (ratios, percentages). Example: Consider a sales fact table in a retail business:

Facts: Sales Amount, Quantity Sold, Discount Given Foreign Keys: Product ID, Store ID, Date ID

Dimension Table

Definition: Dimension tables store descriptive attributes related to the business, providing context to the numerical data in fact tables.

Dimension Characteristics

Attributes: These tables contain textual or descriptive data (attributes) that help in filtering, grouping, and labeling data. For example, in a Customer dimension table, attributes might include customer name, gender, and location. Primary Key: Each dimension table has a primary key that uniquely identifies each record. This key is referenced by foreign keys in the fact table.

Denormalization: Dimension tables are typically denormalized to speed up query performance. This means they may have redundant data to avoid complex joins. Relatively Low Volume: Compared to fact tables, dimension tables usually have fewer rows. Slowly Changing Dimensions (SCD): This is a key concept referring to how the attributes in dimension tables are managed when they change over time. SCDs can be managed in various ways (e.g., maintaining historical data vs. updating to current). Example: A product dimension table might include the following:

Attributes: Product Name, Category, Brand, Supplier

Differences and Uses

Purpose:

Fact tables capture the metrics for analysis. Dimension tables provide descriptive context that explains the facts.

Structure:

Fact tables are generally highly normalized and focused on the efficiency of data storage of transactional data. Dimension tables are typically denormalized to enable quick retrieval of descriptive information.

Data Type:

Fact tables primarily deal with numeric data. Dimension tables handle textual/descriptive data.

Examples:

Fact table: Sales transaction table with revenue details. Dimension table: Customer details or product information.

Conclusion

In summary, both fact and dimension tables are integral to data warehousing and business intelligence frameworks. Fact tables are integral for storing quantifiable business metrics, while dimension tables provide the necessary descriptive context, enhancing the narrative around the data. Effective design of fact and dimension tables leads to a well-optimized, efficient, and insightful analytical data environment that aids in strategic decision-making.