Conceptual Modeling and Fact Definition in Data Warehousing

Conceptual Modelling Foundations

Data warehousing begins with a conceptual model that captures the business reality the warehouse will serve. At this level, designers represent objects, their properties, and the associations among them, distinguishing between dimensions (the descriptive attributes) and facts (the measurable events). The model must also encode the temporal dimension, because every fact is generated by a process that evolves over time. By explicitly linking time to facts, the warehouse can store historical snapshots and support analyses that span months, years, or even decades, fulfilling the time‑variant characteristic of a data warehouse.

Fact Definition and Temporal Aspects

A fact is the quantitative core of a warehouse – for example, a sales transaction that records dollars sold, units sold, or average sales. Facts are processes that generate data; they are never static. Consequently, each fact row includes a time key that ties the measurement to a specific point or interval (day, week, month, etc.). This time association is mandatory: without it, the warehouse would lose its ability to provide historical insight, a key differentiator from operational databases that typically store only the current value.

Multidimensional Data Model and Schemas

The conceptual model is realized through a multidimensional data model, visualized as a data cube. The cube consists of:

• Dimension tables (e.g., Item, Time, Branch) that store descriptive attributes such as itemname, brand, or dayoftheweek.
• A fact table that holds the measures (e.g., dollars_sold) and foreign keys linking to each dimension.

Different schema patterns organise these tables:

• Star schema places a single fact table at the centre surrounded by denormalised dimension tables, offering simple query performance.
• Snowflake schema normalises hierarchical dimensions into multiple related tables, reducing redundancy at the cost of slightly more complex joins.
• Fact constellation (galaxy) schema shares dimension tables among multiple fact tables, supporting complex analytical scenarios where several business processes intersect.

In literature, an n‑D base cuboid represents the most detailed level of the cube, while the apex cuboid (0‑D) provides the highest‑level aggregation. The lattice of all cuboids forms the complete data cube, enabling roll‑up and drill‑down operations across dimensions.

Integration, Non‑Volatility and ETL Considerations

A data warehouse is integrated: it consolidates heterogeneous sources—relational databases, flat files, transaction logs—through ETL (Extract, Transform, Load) processes. During transformation, multifield mappings (M:1 and 1:M) reconcile differing source structures into a unified target schema. Once loaded, the warehouse is non‑volatile; it does not support operational updates, transaction processing, or concurrency control. Access is limited to the initial load and subsequent analytical queries.

The subject‑oriented nature of the warehouse means it is organised around core business subjects such as Customer, Product, or Sales, providing decision makers with a concise, focused view while omitting irrelevant operational details. Together, these conceptual and architectural principles enable a data warehouse to serve as a reliable, historical, and analytical foundation for enterprise decision‑making.