Hubbry Logo
Fact tableFact tableMain
Open search
Fact table
Community hub
Fact table
logo
8 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
Fact table
Fact table
Fact table
View on Wikipedia
from Wikipedia
Example of a star schema; the central table is the fact table

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables. Where multiple fact tables are used, these are arranged as a fact constellation schema. A fact table typically has two types of columns: those that contain facts and those that are a foreign key to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables contain the content of the data warehouse and store different types of measures like additive, non-additive, and semi-additive measures.

Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a sales fact table might be stated as "sales volume by day by product by store". Each record in this fact table is therefore uniquely defined by a day, product, and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (a region contains many stores).

Example

[edit]

If the business process is sales, then the corresponding fact table will typically contain columns representing both raw facts and aggregations in rows such as:

  • $12,000, being "sales for New York store for 15-Jan-2005".
  • $34,000, being "sales for Los Angeles store for 15-Jan-2005"
  • $22,000, being "sales for New York store for 16-Jan-2005"
  • $21,000, being "average daily sales for Los Angeles Store for Jan-2005"
  • $65,000, being "average daily sales for Los Angeles Store for Feb-2005"
  • $33,000, being "average daily sales for Los Angeles Store for year 2005"

"Average daily sales" is a measurement that is stored in the fact table. The fact table also contains foreign keys from the dimension tables, where time series (e.g. dates) and other dimensions (e.g. store location, salesperson, product) are stored.

All foreign keys between fact and dimension tables should be surrogate keys, not reused keys from operational data.

Measure types

[edit]
  • Additive – measures that can be added across any dimension.
  • Non-additive – measures that cannot be added across any dimension.
  • Semi-additive – measures that can be added across some dimensions.

A fact table might contain either detail-level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

Special care must be taken when handling ratios and percentages. One good design rule[1] is to never store percentages or ratios in fact tables but only calculate these in the data access tool. Thus, only store the numerator and denominator in the fact table, which then can be aggregated, and the aggregated stored values can then be used for calculating the ratio or percentage in the data access tool.

In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called "factless fact tables", or "junction tables".

The factless fact tables may be used for modeling many-to-many relationships or for capturing timestamps of events.[1]

Types of fact tables

[edit]

There are four fundamental measurement events, which characterize all fact tables.[2]

Transactional
A transactional table is the most basic and fundamental. The grain associated with a transactional fact table is usually specified as "one row per line in a transaction", e.g., every line on a receipt. Typically a transactional fact table holds data of the most detailed level, causing it to have a great number of dimensions associated with it.
Periodic snapshots
The periodic snapshot, as the name implies, takes a "picture of the moment", where the moment could be any defined period of time, e.g. a performance summary of a salesman over the previous month. A periodic snapshot table is dependent on the transactional table, as it needs the detailed data held in the transactional fact table in order to deliver the chosen performance output.
Accumulating snapshots
This type of fact table is used to show the activity of a process that has a well-defined beginning and end, e.g., the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated. An accumulating snapshot table often has multiple date columns, each representing a milestone in the process. Therefore, it's important to have an entry in the associated date dimension that represents a placeholder for an unknown date, as many of the milestone dates are unknown at the time of the creation of the row.
Temporal snapshots
By applying temporal database theory and modeling techniques the temporal snapshot fact table [3] allows to have the equivalent of daily snapshots without really having daily snapshots. It introduces the concept of time Intervals into a fact table, allowing saving a lot of space, optimizing performances while allowing the end user to have the logical equivalent of the "picture of the moment" they are interested in.

Steps in designing a fact table

[edit]

Kimball’s dimensional design follows four steps:[4]

  1. Select the business process to model (e.g., order entry, claims processing).[4]
  2. Declare the grain – define exactly what a single fact-table row represents; different grains must not be mixed in one table.[5]
  3. Identify the dimensions that apply to each fact-table row.[4]
  4. Identify the facts (measures) that are true to the declared grain.[6]

After the grain is declared, choose an appropriate fact-table type (e.g., transaction, periodic snapshot, accumulating snapshot).[7] Measures in a fact table are commonly classified as:[8]

  • Additive – can be summed across all dimensions (e.g., sales amount).
  • Semi-additive – additive across some dimensions but not others (e.g., account balance not additive across time).
  • Non-additive – not summable across any dimension (e.g., ratios); typically computed in queries rather than stored.

Example (transaction grain, “one row per order line”): 

SELECT d.calendar_date, SUM(f.sales_amount) AS daily_sales
FROM fact_sales f
JOIN dim_date d    ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE p.category = 'Widgets'
GROUP BY d.calendar_date;

This aggregate remains valid because the measure is additive and the fact rows share a single declared grain.[5]

References

[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Fact table

View on Grokipedia
from Grokipedia
In data warehousing, a fact table is a central database table in a dimensional model that stores quantitative measures or facts associated with events or observations, such as quantities or amounts, to support , reporting, and . This structure forms the core of a , where the fact table is surrounded by tables containing descriptive attributes like customer details, product , or time periods, connected via foreign keys to enable slicing, , and aggregation of data. Pioneered by in his 1996 book The Data Warehouse Toolkit, dimensional modeling emphasizes fact tables as the foundation for bus architectures that integrate multiple business processes across an enterprise.

Structure of a Fact Table

A typical fact table includes:
  • Foreign keys: Surrogate keys linking to tables, which define the or level of detail (e.g., per transaction line or daily summary).
  • Measures: Numeric columns representing business metrics, often additive (e.g., total sales revenue that can be summed), though semi-additive (e.g., account balances not summed over time) or non-additive (e.g., ratios like percentages) measures may also appear.
  • Degenerate dimensions: Optional non-key attributes like order numbers that provide context without a separate table.
  • Audit attributes: Fields for tracking , such as creation dates, though these are not core to analysis.
Fact tables generally lack a and are designed to be wide and sparse, with billions of rows in large-scale implementations, prefixed conventionally as f_ or Fact_ for clarity.

Types of Fact Tables

Kimball's framework identifies three primary types of fact tables, each suited to different analytical needs:
  • Transaction fact tables: Capture atomic-level events, such as individual lines, at the finest grain for detailed querying.
  • Periodic snapshot fact tables: at fixed intervals (e.g., daily levels), reducing storage while preserving trends over time.
  • Accumulating snapshot fact tables: Track the cumulative progress of semi-durable processes, like stages, updating as milestones are reached.
These types ensure flexibility in modeling volatile or stable metrics, with transaction tables being the most common due to their .

Overview

Definition and purpose

A is a central component in the or of a , serving as the primary repository for quantitative facts or measures that are associated with descriptive . These facts typically represent numeric measurements derived from processes, such as or quantities, and are linked to dimension tables through foreign keys to provide context for . The primary purpose of a fact table is to facilitate efficient querying and aggregation of metrics across multiple dimensions, enabling analysts to perform complex analytical operations like summing totals or calculating averages without excessive joins or computations. By storing pre-integrated data in a denormalized format optimized for read-heavy workloads, fact tables support in data warehousing environments, contrasting sharply with the normalized structures of (OLTP) databases that prioritize transactional integrity over analytical speed. This concept originated in Kimball's methodology during the 1990s, as detailed in his seminal 1996 book The Data Warehouse Toolkit, which emphasized a practical approach to building data warehouses for rather than rigid normalization. In terms of basic structure, a fact table generally consists of numeric measure columns alongside foreign keys referencing dimension tables, deliberately avoiding descriptive attributes beyond those keys to maintain focus on metrics and ensure query performance.

Role in dimensional modeling

In dimensional modeling, the fact table serves as the central component of a , positioned at the core and surrounded by multiple denormalized dimension tables that provide contextual attributes such as time, product, customer, and location. These dimension tables connect to the fact table through foreign keys, forming simple one-to-many relationships that enable straightforward joins during analytical queries. This structure assumes dimension tables deliver the necessary descriptive context to interpret the quantitative measures stored in the fact table, facilitating without complex normalization. Unlike operational databases designed for (OLTP), which prioritize frequent updates, inserts, and deletes in normalized schemas to ensure during real-time transactions, fact tables in are optimized for (OLAP) environments. They support read-heavy workloads focused on aggregation and reporting, accommodating large-scale historical data loads rather than supporting transactional consistency. This shift allows fact tables to handle denormalized data efficiently, reducing join complexity compared to OLTP systems. The integration of fact tables in star schemas yields significant benefits for applications, including the ability to perform slicing, , and drill-down operations that allow users to explore data across various perspectives, such as filtering by product category or aggregating by time period. in this model enhances query performance by minimizing the number of table joins required for common aggregations, making it particularly effective in OLAP tools for rapid insight generation. Overall, this architecture promotes intuitive data navigation and scalability in analytical environments.

Components

Measures and facts

In dimensional modeling, measures represent the quantitative values captured from business process events, such as sales revenue, order quantities, or inventory levels, which form the core analytical content of a fact table. These measures enable organizations to perform calculations and derive insights from operational data. The terms "facts" and "measures" are often used interchangeably in data warehousing literature, though facts typically refer to the underlying raw event data that generates the numeric measures. For instance, a transaction event produces facts like the dollar amount and unit count, which are stored as measures for aggregation and analysis. Measures are generally stored in fact tables as numeric data types, such as integers for counts or decimals for monetary values, to optimize query performance and storage efficiency. Contextual details like units (e.g., kilograms) or currencies (e.g., USD) may accompany these values, either embedded in the measure column or referenced via associated attributes, ensuring accurate interpretation during reporting. When certain descriptive elements lack sufficient attributes to warrant a full table, they are incorporated directly into the fact table as degenerate dimensions, such as transaction identifiers like order numbers or IDs. These text-based fields serve as natural keys without requiring joins to separate tables, simplifying the schema while preserving traceability. To promote consistency across an enterprise , conformed facts are standardized measures that can be reused in multiple fact tables, ensuring identical definitions and calculations for metrics like or costs regardless of the . This approach facilitates integrated analytics by aligning facts from disparate sources, such as sales and inventory systems.

Dimensions and keys

In , fact tables connect to dimension tables through foreign keys, which are columns in the fact table that reference the primary keys of tables, thereby enforcing and enabling the integration of descriptive context with quantitative measures. These foreign keys ensure that every row in the fact table corresponds to valid entries in the associated tables, preventing orphaned records and supporting efficient querying across the . For instance, a fact table might include foreign keys to date, product, and dimensions, allowing of transactions at the of these attributes. Fact tables employ two primary types of keys for dimensions: surrogate keys and natural keys. Surrogate keys are artificially generated integer identifiers, typically starting from 1 and incrementing sequentially, serving as the primary keys in dimension tables and referenced by foreign keys in the fact table. They provide durability against changes in source systems, facilitate handling of slowly changing dimensions by allowing multiple versions of dimension rows, and optimize storage and join performance due to their compact size compared to alphanumeric alternatives. In contrast, natural keys are business-generated identifiers from operational systems, such as product SKUs or employee IDs, which are often retained as attributes within dimension tables but not used as primary keys to avoid issues with duplicates or modifications across sources. The combination of multiple foreign keys in a fact table defines its , or the level of detail represented by each row, ensuring that the table captures atomic events without aggregation. For example, foreign keys to daily date, individual product, and specific store dimensions might establish a granularity of daily per product per store, allowing flexible aggregation to higher levels like monthly totals without data loss. This structure supports ad hoc queries by aligning the fact table's detail with events, such as point-of-sale transactions. Fact tables are typically wide due to the numerous foreign keys required to link multiple dimensions, but they incorporate minimal constraints and indexing on measure columns to facilitate high-volume bulk loads and real-time updates. constraints are often omitted on the composite set to avoid performance overhead during ETL processes, while is enforced through application logic or deferred checks. Indexing, when applied, focuses on dimension foreign keys for query optimization, using techniques like indexes on low-cardinality attributes to balance load speed and retrieval efficiency. In transaction fact tables modeling header-line structures, such as orders, foreign keys reference both header-level dimensions (e.g., or promotion) and line-item-specific dimensions (e.g., product), with the header identifier often stored as a —a simple text or numeric attribute without a separate dimension table. This approach embeds header context directly in the line-item fact table, enabling analysis at the finest grain while avoiding unnecessary joins; for example, an order number serves as a grouping key for aggregating line items without linking to a full header .

Measure types

Additive measures

In data warehousing, additive measures are numeric facts stored in a fact table that can be legitimately summed across any combination of dimensions to produce meaningful aggregates, without distortion or invalid results. This property makes them the most flexible and useful type of measure for . The aggregation rule for additive measures is straightforward and unrestricted: the total value for any subset of the data is obtained by summing the measure across the relevant records. Mathematically, this is expressed as: Total=∑measure\text{Total} = \sum \text{measure} where the occurs over any desired combination of attributes, such as time periods, products, or geographic regions. This full additivity supports efficient (OLAP) operations, including roll-ups (aggregating to higher levels) and drill-downs (to lower levels), as the sums remain valid at every . Common examples of additive measures include sales revenue, units sold, and , which are frequently found in transaction fact tables tracking events like orders or shipments. For instance, summing sales revenue across all products in a yields the regional , a key metric. These measures dominate designs, comprising the majority of facts due to their alignment with typical quantitative business reporting needs. To identify whether a measure is additive, evaluate if partial sums across dimensions produce sensible business interpretations—for example, the sum of daily sales quantities equaling a monthly total, or aggregated by segment representing segment performance. If such aggregations hold true without requiring special adjustments, the measure qualifies as additive.

Semi-additive and non-additive measures

In , semi-additive measures are those that can be meaningfully aggregated using summation across certain dimensions but not others, requiring alternative aggregation functions for the restricted dimensions to avoid misleading results. A classic example is account balances in a financial fact table, where the measure can be summed across account types or dimensions to yield a total balance, but over the time dimension, summation would incorrectly accumulate balances across periods; instead, functions like average, minimum, maximum, or last value are applied. For instance, the current balance for an account can be computed as the last value of the measure along the time dimension, expressed as:

Current balance = LAST_VALUE(balance_measure) ORDER BY time_dimension

Current balance = LAST_VALUE(balance_measure) ORDER BY time_dimension

This approach ensures accurate reporting, such as a customer's total outstanding balance across multiple accounts at the end of a reporting period. Another common semi-additive measure appears in snapshots, where quantities can be summed across product categories or locations but not across time periods, as doing so would overstate levels; aggregation over time typically uses the latest snapshot or an to reflect ongoing positions. Non-additive measures, in contrast, cannot be summed across any dimension without losing their semantic meaning, necessitating aggregations like averages, counts, minima, or maxima, or deriving them from underlying additive components. Ratios such as percentage discounts or profit margins exemplify non-additive measures, as summing them—for instance, across products or time—produces nonsensical results like an inflated discount rate; instead, these are often stored in the fact table but aggregated by first summing numerator and denominator values separately (e.g., total discount amount and ) and then computing the ratio in the layer. Other examples include rates like unit prices or readings, where aggregation might involve averaging across entities but never direct summation. To handle both semi-additive and non-additive measures effectively, designs often incorporate views, calculated columns, or measures in OLAP tools and BI platforms to enforce the appropriate aggregation rules, preventing users from inadvertently applying invalid summations during queries. This practice aligns with the principles of by preserving the integrity of the measures while enabling flexible analysis across valid dimension combinations.

Types of fact tables

Transaction fact tables

Transaction fact tables represent the most fundamental type of fact table in , capturing individual business events or transactions at their atomic grain. Each row corresponds to a single measurement event occurring at a specific point in space and time, such as a line item on an or a shipment detail. This design ensures one row per transaction line item, recording discrete occurrences like or orders without aggregation. These tables are characterized by their fine and high volume, as they store at the lowest possible level of detail, often resulting in millions or billions of rows in large-scale systems. Measures in transaction fact tables are defined at the event level, such as the quantity of a product sold or the dollar amount of a transaction, and are typically additive for summarization across . The includes foreign keys linking to dimension tables (e.g., date, product, customer), degenerate dimensions like transaction IDs, and optional timestamps, enabling precise event tracking. This atomic level supports sparse patterns, where rows exist only for actual events, aligning with event-oriented sources like operational . Common use cases for transaction fact tables include retail sales tracking, where each purchase line item is recorded, and order systems, which log individual fulfillment events. These tables facilitate detailed reporting, such as analyzing by product category over time, and support what-if scenario by allowing flexible aggregation without loss of . For instance, in a retail environment, they enable queries on purchasing patterns at the item level. A representative example is a transaction fact table in a , which might include foreign keys to date, product, store, and dimensions, along with measures for sold and amount. The table structure could appear as follows:
Column NameTypeDescription
Date_KeyForeign key to date dimension
Product_KeyForeign key to product dimension
Store_KeyForeign key to store dimension
Customer_KeyForeign key to dimension
Transaction_ID (order number)
Quantity_SoldMeasure: units sold
Sales_AmountMeasure: dollar value of sale
This setup allows for atomic analysis, such as total sales for a specific product in a given store on a particular day. The primary advantages of transaction fact tables lie in their flexibility for aggregation and their expressiveness in supporting complex queries. By maintaining atomic grain, they enable the maximum slicing and dicing of data across multiple dimensions, accommodating unpredictable analytical needs and providing the foundation for derived summaries. This design also integrates seamlessly with event-driven data pipelines, ensuring to source systems.

Periodic snapshot fact tables

Periodic snapshot fact tables capture the state of measures at fixed, regular intervals, such as daily account balances or end-of-month levels, where each row represents a summary of activity or status for a specific entity over that predefined time period. These tables are particularly suited for scenarios where the involves monitoring ongoing conditions rather than individual events, ensuring a consistent view of performance metrics across time. Key characteristics include a time dimension key that identifies the snapshot date or period, with measures reflecting the cumulative or current state at that point rather than incremental changes. The tables are designed to be predictably dense, meaning every relevant —such as all customer accounts or product SKUs—appears in each snapshot row, even if occurred, which may result in null or zero values for certain measures. This structure often accommodates semi-additive measures, like balances that can be summed across dimensions other than time but not aggregated over multiple periods. Common use cases involve tracking trends in stable entities over time, such as daily banking account balances to monitor customer or monthly stock levels to assess efficiency. In banking, for instance, the table might record end-of-day balances for each account, while in retail, it could summarize units on hand for products at the close of each fiscal month, enabling analysis of changes without storing every transaction. A representative example is a daily account balance fact table, which includes foreign keys to the account dimension (e.g., customer ID and account type) and the date dimension (e.g., snapshot day), along with measures such as current balance, available credit, and interest accrued as of that day. This design allows queries to easily compute period-over-period changes, like balance growth from one month to the next. Advantages of periodic snapshot fact tables include reduced storage requirements compared to transaction-level for high-volume processes, as they aggregate information into fewer rows focused on key states. They also facilitate efficient trend analysis and reporting, providing reliable, complete datasets for without the need for a full historical .

Accumulating snapshot fact tables

Accumulating snapshot fact tables capture the progression of a by updating individual rows multiple times as milestones are reached, providing a comprehensive view of the process lifecycle from initiation to completion. Unlike transaction fact tables that record immutable , these tables summarize measurements at predictable steps within a defined , such as progressing from placement to delivery. Key characteristics include multiple foreign keys to date dimensions for each milestone, such as order date, ship date, and delivery date, which are initially null and populated as events occur. Measures often encompass durations between events, like days to ship or total processing time, along with counters for completed steps; these tables uniquely allow row updates during the process rather than inserting new rows. Additionally, they incorporate foreign keys to other dimensions, such as product or , and may include degenerate dimensions like order numbers. These fact tables are particularly suited to use cases involving , where tracking manufacturing or distribution pipelines reveals bottlenecks, or customer journeys, enabling analysis of engagement stages from inquiry to purchase. They support pipeline analysis by allowing queries on average lag times across cohorts, facilitating monitoring without joining multiple transaction tables. A representative example is an order accumulation fact table for fulfillment processes, with a combining order ID and line item; it links to multiple date dimensions for milestones and includes measures such as ship lag days (ship date minus order date) and a completion status flag. As the order advances—e.g., from tendered to shipped—the row is updated to reflect current progress, culminating in final metrics upon delivery. Advantages include enhanced visibility into process efficiency through a single table that aggregates evolving states, reducing query complexity for lifecycle reporting. They often employ semi-additive measures, such as status flags indicating current stage (e.g., shipped or delivered), which can be aggregated across non-time dimensions but not summed over time to avoid distortion.

Design process

Determining the grain

In , the of a fact table refers to the finest level of detail represented by each row, defining the specific event or that generates the data. This is determined by the physical source of the measurements in the operational system, ensuring the fact table captures atomic, non-aggregatable events. For instance, the might specify one row per individual order line item or per daily inventory count, establishing the core . The process of determining the grain begins with selecting the relevant , such as or sales tracking, and then explicitly declaring the grain in business terms to align with how is collected in source systems. Designers must start at the lowest, most atomic level possible—often tied to a physical event like a scanner beep in retail—before identifying associated dimensions and measures. This declaration ensures consistency and prevents deviations during , as the grain serves as the foundation for all subsequent design choices. Choosing an inappropriate grain can lead to significant issues, including aggregation errors such as double-counting when mixed granularities are present in the same table, or unnecessary data duplication if the level of detail does not match business needs. It must align closely with source system structures to avoid integration challenges, and misalignment can compromise query accuracy across reports. Additionally, finer grains enable detailed analysis but increase storage requirements and may impact query performance due to larger table sizes, while coarser grains reduce storage but limit analytical flexibility. Examples illustrate the trade-offs: a fine-grained fact table for retail might record one row per item scan, supporting granular queries on by product and time, whereas a coarser grain of one row per day per store suits high-level trend summaries but sacrifices detail for efficiency. In , a per-transaction grain allows tracking of movements, enhancing precision in forecasting. Key rules govern grain determination to maintain design integrity: the grain must be declaratively stated upfront and remain uniform and consistent throughout the table, avoiding mixtures of levels that could introduce asymmetries or reporting inconsistencies; multiple grains should be handled in separate fact tables rather than one; and designs should prioritize atomic data from sources over pre-aggregated summaries to maximize long-term utility.

Steps in designing a fact table

Designing a fact table in involves a systematic process to ensure it captures the right level of detail and supports analytical queries effectively. This process builds on the foundational choice of and integrates descriptive contexts with measurable data, following principles established by . The steps emphasize collaboration with business stakeholders and alignment with source systems to create a robust, query-friendly structure.
  1. Choose the business process: Begin by selecting a specific to model, such as sales orders or inventory shipments, which defines the scope of the fact table and ensures it addresses key operational activities. This step translates business requirements into a focused , avoiding overly broad or unrelated entities.
  2. Declare the grain: Establish the granularity of the fact table by defining the lowest level of detail for each row, such as one row per line item in a transaction, as explored in the determining the section. This declaration must precede identifying other components to maintain consistency across the model.
  3. Identify dimensions: Determine the descriptive attributes that provide context for the facts, such as , product, or time, and incorporate them as in the fact table to link to dimension tables. These enable slicing and dicing of data in queries, with each ensuring .
  4. Define facts and measures: Select the quantitative metrics to store, such as sales amount or quantity shipped, choosing additive measures that can be summed across dimensions or semi-additive ones like account balances that sum across most but not all dimensions. All facts must align with the declared grain, and non-additive measures should be derived from additive components where possible.
  5. Handle special cases: Address unique scenarios by incorporating degenerate dimensions, such as order numbers stored directly as attributes in the fact table without a separate dimension table, or by denormalizing hierarchies in dimensions to flatten multi-level structures like product categories for improved query performance. For ragged hierarchies with variable depths, use bridge tables or pathstring attributes to model relationships without complicating the fact table.
  6. Validate the design: Test the fact table using sample data to verify row counts, constraints, and query results against expectations, while assessing ETL processes for to handle large volumes without degradation. Involve experts in workshops to confirm accuracy and iterate on the model as needed.
Best practices include starting with a simple atomic fact table and iterating based on feedback to refine complexity, as well as using conformed dimensions—standardized across multiple fact tables—to enable enterprise-wide integration and reporting. This iterative approach ensures the remains adaptable to evolving business needs while prioritizing for query efficiency.

References

  1. https://www.techtarget.com/searchdatamanagement/definition/fact-table
  2. https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-fact-tables
  3. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
  4. https://www.kimballgroup.com/2003/01/fact-tables-and-dimension-tables/
  5. https://www.kimballgroup.com/2008/11/fact-tables/
  6. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/
  7. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube/
  8. https://www.databricks.com/glossary/star-schema
  9. https://www.ewsolutions.com/dimensional-modeling-what-it-is-and-when-to-use-it/
  10. https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf
  11. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
  12. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/facts-for-measurement/
  13. https://www.zentut.com/data-warehouse/fact-table/
  14. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/degenerate-dimension/
  15. https://www.kimballgroup.com/2003/06/design-tip-46-another-look-at-degenerate-dimensions/
  16. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/conformed-fact/
  17. http://www.r-5.org/files/books/computers/databases/warehouses/Ralph_Kimball_Margy_Ross-The_Data_Warehouse_Toolkit-EN.pdf
  18. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/
  19. https://www.ibm.com/docs/en/ida/9.1.1?topic=models-measures
  20. https://www.holistics.io/blog/the-three-types-of-fact-tables/
  21. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table/
  22. https://telefonicatech.uk/blog/everything-you-need-to-know-about-snapshot-fact-tables/
  23. https://www.kimballgroup.com/2007/07/keep-to-the-grain-in-dimensional-modeling/
  24. https://www.ibm.com/docs/en/ida/9.1.1?topic=phase-step-identify-grain
  25. https://www.red-gate.com/blog/facts-about-facts-organizing-fact-tables-in-data-warehouse-systems
  26. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process/
  27. https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/
Add your contribution
Related Hubs
User Avatar
No comments yet.