In the context of data warehousing and business intelligence, there are three main types of fact tables based on the nature of the data they store and the business processes they represent.
Transactional Fact Tables
Transactional fact tables serve as the bedrock of data warehousing, capturing granular details of individual business transactions at the finest level of granularity. They meticulously record specific events such as sales, orders, or interactions, providing a comprehensive and unambiguous record of business activities. Analyzing data at this transactional level allows for a nuanced understanding of operational intricacies, making these fact tables essential for detailed business intelligence and decision-making processes.
Nature: Transactional fact tables store detailed, atomic-level data about specific business events or transactions.
Granularity: The granularity of these fact tables is typically at the lowest level of detail, capturing individual transactions.
Example: In a retail environment, a transactional fact table might store details about each item sold in every transaction, including information such as product ID, quantity sold, price, and transaction timestamp.
Use Cases: Transactional fact tables are suitable for scenarios where a high level of detail is necessary, and analysis needs to be performed at the transaction level.
Periodic Snapshot Fact Tables
Periodic snapshot fact tables offer a strategic lens into business performance by aggregating data at predefined intervals, providing a summarized view of key metrics over time. These fact tables condense detailed information into manageable periodic snapshots, enabling organizations to track trends, identify patterns, and make informed decisions based on historical performance. By capturing data at regular intervals—whether daily, weekly, or monthly—periodic snapshot fact tables facilitate a comprehensive analysis of business activities, supporting strategic planning and trend analysis.
Nature: Periodic snapshot fact tables capture aggregated data at specific, predefined intervals or periods of time.
Granularity: The granularity is typically coarser than that of transactional fact tables, as data is aggregated over time periods (e.g., daily, weekly, monthly).
Example: In a sales environment, a periodic snapshot fact table might store aggregated sales data for each product at the end of each month, including total sales, units sold, and average price.
Use Cases: Periodic snapshot fact tables are useful for monitoring trends and changes over time, especially when the focus is on periodic summaries.
Accumulating Snapshot Fact Tables
Accumulating snapshot fact tables are the dynamic storytellers of business processes, recording key milestones and stages in a workflow to illuminate the progression of activities over time. Unlike traditional fact tables, accumulating snapshots uniquely capture both transactional details and aggregated metrics, providing a holistic view of critical events in a business process. These fact tables are instrumental in monitoring and analyzing the lifecycle of processes, facilitating a nuanced understanding of how activities evolve and enabling strategic decision-making based on comprehensive process insights.
Nature: Accumulating snapshot fact tables are designed to track changes in a process or workflow over time.
Granularity: These tables often include both transactional and aggregated data, capturing key events or milestones in a business process.
Example: In a manufacturing process, an accumulating snapshot fact table might track the progress of a product through various stages such as order received, manufacturing started, quality check completed, and product shipped.
Use Cases: Accumulating snapshot fact tables are valuable for analyzing and monitoring the progression of specific processes or workflows, providing a comprehensive view of how data changes over time.
Factless Fact Tables
Factless fact tables capture events or transactions without storing numerical measures, emphasizing the relationships between dimensions. These tables, devoid of quantitative data, are commonly used to represent events like appointments, actions, or statuses, providing a valuable tool for tracking and analyzing occurrences across various dimensions.
Nature: Factless events capture business activities without relying on numerical measures, emphasizing the interconnections and contextual relationships among different dimensions.
Granularity: The granularity of factless events is context-dependent. For instance, in scenarios like tracking a binary state such as „promotion activation,“ the granularity is low, capturing specific but limited details. The level of granularity varies based on the nature of the events and the business requirements they address.
Example: Imagine a „State Change“ event with dimensions like change ID, category, date, and location, representing transitions between different states. This provides a granular representation of state changes without the need for specific quantitative values.
Use Case: Factless events prove valuable for analyzing the impact of state changes, tracking shifts in visibility, and understanding patterns and relationships within the data. This approach allows for a nuanced exploration without being constrained by numerical values or business-specific contexts.
Aggregate Fact Tables
These tables store summarized values derived from more detailed fact tables, providing a faster and more efficient way to respond to common analytical queries. Strategically creating aggregate fact tables, could be an option for transparent and flexible modelling approaches.
Nature: Storing pre-computed, summarized values derived from more detailed fact tables.
Granularity: The granularity depents on parent fact table and the level of aggregation.
Example: For instance, summarizing the total quantity sold and sales amount for each product category on a monthly basis.
Use Case: Performance issues or very useful as a symantic layer to adjust granularity for further modeling or analytics. See example at ‚consolidated fact tables, different granularity‘.
Consolidated Fact Tables
Combining facts from various processes into a unified fact table could be an option, to make development and, or analysis more transparent. Performance could be a benefit, but opposing effects must also be taken into account.
Nature: Combined fact tables unify data from various fact tables.
Granularity: The granularity is dependent by the fact table, with the lowest granularity. Further and most important, all facts needs to be expressed in the same granularity.
Example: Different granularity; for instance, merging actual sales data with sales forecasts into a single fact table But it’s to be expected that forecast (low) and fact (high) wouldn’t have the same granularity. Therefore adjusting that granularity, needs aggregation layer. Same granularity; for instance, merging similar business processes from separate fact tables together, like order, invoice, returns.
Use Case: Combining similar facts like order, invoice, return into a combined fact table could be an option to reduce cardinality if they use entirely or predominantly the same dimension attributes. Especially for attributes with extrem high cardinality, like order number, that could be a real advantage if the storage technology provides column compression.
Usecase Stock Data
These different types of fact tables allow for flexibility in modeling and analyzing various aspects of business operations. The choice of which type to use depends on the specific requirements of the business and the type of analysis that needs to be performed.
Even if Snap Shot Fact Tables typically contain aggregated data, they aren’t necessarily smaller in data volume, then an equivalent Transactional Fact Table would be.
For example stock data, with a lot of transactions but not every day for each item. This data could be efficiantly compressed and stored, due to it’s low cardinality in a Transacitonal Fact Table (if your storage engine provides compression). But it’s likely that a lot of transactions aren’t necessary to calculate the daily stock value, e.g. single events each time the item is moved and tracked within a warehouse (good receipt, storage, goods issue). That could lead to poor query performance and complex business logic, to calucalte something like the current stock value. If there’s no need to provide granularity for „technical“ stock movents, it could be a good approach to aggregate and provide these data in a Periodic Snapshot Fact Table.
