Dimensional modelling is a powerful tool for organising and analysing data in data warehousing and business intelligence. At the heart of dimensional modelling are fact tables, which capture business processes’ measurable, quantitative aspects. This blog post will delve into the three fundamental types of fact tables: transactional, periodic snapshot, and accumulating snapshot. Let’s explore how each type contributes to effective data analysis and decision-making.

Transactional Fact Tables: Granular Insights

Transactional fact tables form the backbone of dimensional models, storing the most granular detail about business events. Each row in a transactional fact table represents a transaction, such as a sale, order, or product movement. These tables are characterised by their atomicity, additivity, and potential for large volumes of data.

Key features of transactional fact tables include:

1. Grain: Transactional fact tables represent the lowest level of detail in a dimensional model. Each row corresponds to a single transaction or event.

2. Measures: Fact tables contain numeric measures that quantify the business process or event. These measures are typically additive and can be aggregated across different dimensions.

3. Foreign Keys: Transactional fact tables have foreign keys that connect to the primary keys of the associated dimension tables, establishing relationships between facts and dimensions.

4. Atomicity: Transactional fact tables store the most detailed level of information available, with each row representing a unique combination of dimension values and measures for a specific transaction.

5. Sparsity: Not all combinations of dimension values may have corresponding facts, resulting in sparse fact tables.

6. Additivity: The measures in a transactional fact table are typically additive across all dimensions, allowing for easy aggregation and calculation of totals and subtotals.

7. Large Volume: Transactional fact tables can grow very large over time as they capture the details of individual transactions or events.

8. Query Performance: Due to their large size, querying transactional fact tables directly can be slow. Aggregations and pre-calculated summary tables are often used to store summarised data at higher levels of granularity for improved query performance.

Transactional fact tables enable users to drill down to specific transactions, perform complex analyses, and gain deep insights into business operations. Examples include sales fact tables (storing individual sales transactions), order fact tables (capturing details of customer orders), and inventory fact tables (recording product movement in and out of inventory).

Periodic Snapshot Fact Tables: Historical Perspectives

Periodic snapshot fact tables capture the state of a business process or entity at regular intervals, providing a historical view of how data has evolved. Each row represents a snapshot of the business at a specific time, such as daily, weekly, or monthly.

Key characteristics of periodic snapshot fact tables include:

1. Regular Intervals: Periodic snapshot fact tables store data at regular, predefined intervals, with each row representing the state of the business process or entity at a specific point in time.

2. Measures: Like transactional fact tables, periodic snapshot fact tables contain numeric measures. However, these measures represent the state or cumulative values at the specified interval rather than individual transactions.

3. Dimension Keys: Periodic snapshot fact tables have foreign keys that link to the associated dimension tables, identifying the specific entities or attributes related to the measures at each snapshot interval.

4. Slowly Changing Dimensions (SCDs): Periodic snapshot fact tables often deal with slowly changing dimensions, where the attributes of a dimension can change over time. The fact table captures the state of the dimensions at each snapshot interval.

5. Snapshot Date: Periodic snapshot fact tables include a snapshot date dimension or attribute that indicates the specific time when the snapshot was taken, allowing for tracking and analysing data across different periods.

6. Aggregation: Measures in periodic snapshot fact tables are typically aggregated or calculated values at the specified interval, such as total sales or average inventory level for each day.

7. Historical Analysis: Periodic snapshot fact tables enable historical and trend analysis over time by comparing measures across different snapshot intervals.

8. Slower Growth: Compared to transactional fact tables, periodic snapshot fact tables generally grow slower, determined by the number of snapshot intervals captured over time.

Periodic snapshot fact tables are valuable for financial reporting (capturing account balances, revenues, and expenses at regular intervals), inventory management (tracking inventory levels and stock positions at specific points in time), and customer analysis (storing customer metrics like customer count, lifetime value, or segmentation at regular intervals). Examples include daily sales snapshots, monthly account balance snapshots, and quarterly inventory snapshots.

Accumulating Snapshot Fact Tables: Tracking Progress

Accumulating snapshot fact tables are designed to track the progress and state of a business process with a defined start and end point. They capture the accumulation of measures and changes in dimensions throughout the process lifecycle.

Key features of accumulating snapshot fact tables include:

1. Process Tracking: Accumulating snapshot fact tables track the progress of a specific business process or event with a distinct beginning and end, such as order fulfilment, claim processing, or project lifecycle.

2. Multiple Date Dimensions: Unlike other fact tables, accumulating snapshot fact tables have multiple date dimensions representing key milestones or stages in the process.

3. Evolving Measures: The measures in an accumulating snapshot fact table evolve and accumulate throughout the process, capturing the state at different stages. They can include both additive and non-additive measures.

4. Changing Dimensions: Accumulating snapshot fact tables often involve dimensions that change for the process, such as the shipping carrier or delivery address in an order fulfilment process.

5. One Row per Process Instance: Each row in an accumulating snapshot fact table represents a unique instance of the business process, initially inserted when the process begins and updated as it progresses through different stages until completion.

6. Sparse Fact Table: Accumulating snapshot fact tables can be sparse, as not all milestone dates or dimensions will have values for every process instance if the process hasn’t reached all milestones or data is unavailable for all stages.

7. Analysis of Process Duration and Efficiency: Accumulating snapshot fact tables enables analysis of the duration and efficiency of the business process by examining the time differences between milestone dates and the values of measures at different stages.

Accumulating snapshot fact tables are commonly used in scenarios such as order fulfilment (tracking the progress of an order from placement to shipping and final delivery), claims processing (monitoring the stages of a claim, from submission to review, adjudication, and settlement), and project management (capturing the progress and status of a project, from initiation to planning, execution, and completion). Examples include order fulfilment fact tables, claims processing fact tables, and project lifecycle fact tables.

Conclusion

Transactional, periodic snapshot, and accumulating snapshot fact tables form the three pillars of dimensional modelling. Each type serves a distinct purpose in capturing, analysing, and understanding business processes. Transactional fact tables provide: Granular insights. Periodic snapshot fact tables offer historical perspectives. Accumulating snapshot fact tables track progress and performance.

By effectively leveraging these fact table types, organisations can gain valuable insights, identify trends, optimise processes, and make data-driven decisions. Understanding each fact table type’s characteristics and use cases is crucial for designing robust dimensional models that support business intelligence and analytics initiatives.

Embrace the power of dimensional modeling and unleash your data’s potential with transactional, periodic snapshot, and accumulating snapshot fact tables.