Facts and Dimensions

Introduciton

Facts in a database context are the measurable and numeric data and the foundation for key performance indicators or business metrics. They provide the quantitative details that businesses analyze to gain insights into their operations. Dimensions, on the other hand, are descriptive attributes or categories that provide context to the facts. They offer a way to categorize and filter the data in the fact table.

In a dimensional model, such as a star schema, facts are stored in the central fact table, while dimensions are stored in separate dimension tables. Together, facts and dimensions form the foundation for effective data warehousing and business intelligence, enabling users to conduct meaningful analyses and gain a comprehensive understanding of their business data.

Fact Tables

A fact table serves as a repository for quantitative data, commonly referred to as measures or metrics, that represent the essential business facts. Key considerations and guidelines for defining fact tables:

Granularity:
Fact tables should be defined at the finest level of detail, representing the lowest level of information that is of interest for analysis. This is known as the granularity of the fact table.

Additive Measures:
Fact tables should primarily contain additive measures. Additive measures are those that can be summed up across all dimensions. For example, sales revenue is an additive measure because you can sum it across various dimensions like time, geography and product.

Numeric Values:
Fact tables should primarily store numeric values, representing measurable quantities, like sales revenue, quantity sold and profit.

Foreign Keys to Dimension Tables:
Fact tables include foreign keys that establish relationships with dimension tables. These foreign keys link the fact table to the descriptive attributes stored in dimension tables. These relationships enable users to analyze and filter data by various dimensions.

Date and Time Stamps:
Fact tables often include date and time stamps to capture when the business events or transactions occurred. This supports time-based analysis, such as trend analysis or comparisons across different time periods.

Surrogate Keys:
It is common to use surrogate keys in fact tables. Surrogate keys are system-generated keys that uniquely identify each row in the fact table. These keys provide a stable reference point for relationships with dimension tables.

Degenerate Dimensions:
Fact tables may include degenerate dimensions, which are attributes that would normally be part of a dimension but are instead included in the fact table due to their simplicity or high cardinality. An example is an invoice number.

Snapshot Fact Tables vs. Transaction Fact Tables:
Fact tables can be categorized into snapshot and transaction types. Snapshot fact tables capture a snapshot of business metrics at a specific point in time, while transaction fact tables record individual business events or transactions.

Sparse Data:
Fact tables are often sparse, meaning that not every combination of dimension keys has a corresponding fact record. This is typical in scenarios where certain measures are not applicable for every combination of dimensions. For example, consider a fact table that tracks sales data with dimensions for time, product, and geography. If there are no sales for a particular product in a specific month and region, there may be no entry in the fact table for that combination of dimension keys. This is an example of sparse data. Managing sparse data efficiently is important for optimizing storage, improving query performance, and ensuring that analysis tools can navigate the data effectively. (Practice example: reduced history in fact table without limiting the dimensions as well.)

Following these guidelines or concepts, is a good starting point to create fact tables that are well-structured and optimized for analytical queries, supporting flexible and intuitive analysis of business data in a dimensional data warehouse environment.

Dimension Tables

Dimension tables in a database contain descriptive attributes that provide context to the quantitative data stored in the fact table(s). These attributes categorize and organize the facts, offering a way to analyze and filter data along different axes. In a star schema, dimension tables surround the central fact table(s), forming a star-like structure. Each dimension table typically corresponds to a specific aspect of business, such as time, geography or product and holds the descriptive details related to that aspect. The relationship between the fact table and dimension tables is established through foreign key links, allowing users to navigate and analyze data across various dimensions in an efficient and intuitive manner for business intelligence purposes. Key considerations and guidelines for defining dimension tables:

Surrogate Keys:
It’s recommended using surrogate keys in dimension tables. Surrogate keys are system-generated, unique identifiers that provide stability and consistency, even if the source system keys change.

Descriptive Attributes:
Dimension tables should include descriptive attributes that help categorize and describe the business entities represented in the fact table. Examples of dimension attributes are product names customer names or geographic locations.

Hierarchies:
Dimension tables may include hierarchies to represent relationships between different levels of granularity within a dimension. For example, a time dimension might have hierarchies such as year, quarter, month and day.

Slowly Changing Dimensions (SCD):
The concept of Slowly Changing Dimensions (SCD), recognizing that dimension attributes may change over time. Dimension tables could accommodate different SCD types, such as Type 1 (overwrite), Type 2 (add a new record), and Type 3 (add a new attribute). E.g. Change the name of a brand like Raiders and Twix, or a customer has a new address.

Data Quality:
Ensuring data quality in dimension tables is emphasized. Validating and cleansing dimension data to maintain accuracy and consistency in reporting and analysis in mandatory.

Denormalization:
Dimension tables are denormalized to some extent to simplify queries and improve performance. Redundant information is allowed to avoid complex joins when querying the data.

Integrity Constraints:
Integrity constraints refer to rules or conditions that are enforced to maintain the accuracy, consistency, and reliability of the data. These constraints help ensure that the data in dimension tables adheres to the specified business rules and relationships. Here are some common integrity constraints applied to dimension tables:

• Primary Key Constraint: The primary key constraint ensures that each record in the dimension table is uniquely identified. It typically involves a unique identifier, often referred to as a surrogate key, which ensures that there are no duplicate records.
• Foreign Key Constraint: Foreign key constraints establish and enforce relationships between the dimension table and the fact table. The foreign key in the fact table links to the primary key in the dimension table, maintaining referential integrity.
• Domain Integrity: Domain integrity constraints define the permissible values for columns in the dimension table. For example, if a dimension represents product categories, the domain integrity constraint ensures that only valid categories are entered.
• Not Null Constraint: This constraint ensures that certain attributes in the dimension table, which are essential for analysis and reporting, cannot have a null (missing) value. It helps prevent incomplete or inconsistent data.
• Unique Constraint: The unique constraint ensures that specific columns or combinations of columns in the dimension table have unique values. This can be used to enforce uniqueness on natural keys or other business-specific attributes.
• Check Constraint: Check constraints define conditions that the data in the dimension table must meet. For instance, a check constraint might ensure that the start date of a dimension record is earlier than the end date.

Defaults and Unknowns:
It can also be useful to handle default values and unknowns in dimension tables to account for missing or unspecified information. This could imporve developing speed and improve flexibility.

Following these guidelines or concepts, is a good starting point to create dimension tables that are well-structured, stable and flexible entities that provide valuable context for analyzing business metrics within a dimensional data warehouse, particularly in the context of a star schema.