Facts by Representation
Most fact measures are simple, additive numbers. Real business processes, however, produce measures that don't fit this pattern: textual rather than numeric outcomes, amounts recorded in multiple currencies, quantities reported in different units, and events timestamped across time zones.
Conformed Facts
Conformed facts are measurements with a consistent, agreed-upon definition across the enterprise. When the same metric appears in multiple fact tables or data marts, it should use the same business rules, calculation logic, units and naming conventions.
For example, sales_amount should have the same definition - such as net sales after discounts and excluding tax - whether it appears in a retail sales fact table or an online sales fact table. This ensures users get consistent and comparable results, regardless of which fact table they query.
NOTE
A conformed fact is not simply a measure with the same name. Two revenue measures are not conformed if they use different business rules.
Textual Facts
Although fact tables primarily contain numeric measures, sometimes a fact or measure may have a textual value associated with a specific business event.
Consider the fact table for an Employee Swipe Card log. While an individual card swipe does not generate a numeric quantity to sum, it does produce a specific textual result for that exact moment in time.
| swipe_date | swipe_time | employee_name | card_reader_location | swipe_status |
|---|---|---|---|---|
| Jan 1 | 08:00 | Alice | Front Door | Granted |
| Jan 1 | 08:05 | Bob | Front Door | Denied |
| Jan 1 | 08:06 | Bob | Front Door | Granted |
Here, the swipe_status column is a textual fact because it captures the discrete outcome of a specific event. While you cannot sum this column, it can still be analyzed by counting the occurrences (for example, counting the total number of Granted or Denied swipes).
💡 Textual Fact or Dimension?
The key distinction is what the text value describes. If it describes the outcome or state of a specific business event, it can be modeled as a textual fact. If it describes an entity itself and provides descriptive context for filtering or grouping, it belongs in a dimension table instead.
Multiple Currency Facts
Fact tables that record financial transactions in multiple currencies should store both the original transaction amount and the amount converted to a standard currency.
The original amount preserves the value in the transaction's true currency, while the standard-currency amount provides a consistent basis for enterprise-wide reporting. The conversion should be performed during ETL using an approved business rule, such as an agreed exchange-rate source and conversion date.
The fact table should also contain a currency dimension key identifying the transaction's original currency.
| date_key | product_key | currency_key | local_sales_amount | standard_usd_sales_amount |
|---|---|---|---|---|
| Jan 1 | Laptop | EUR | €1,000 | $1,100 |
| Jan 1 | Mouse | GBP | £40 | $50 |
This allows users to analyze transactions in their original currency or aggregate them consistently using the standard currency.
Multiple Units of Measure Facts
Some business processes require the same quantity to be reported in multiple units of measure. For example, a supply chain may need to report quantities as pallets, cases or individual units.
Rather than storing the same fact separately in every unit, store the quantity in an agreed standard unit along with the conversion factors needed to derive the other units.
| date_key | product_key | qty_standard_pallets | cases_per_pallet | units_per_case |
|---|---|---|---|---|
| Jan 1 | Widget | 5 | 10 | 100 |
From this row:
- Cases:
5 × 10 = 50 - Units:
5 × 10 × 100 = 5,000
The conversion factors should be stored with the fact row because conversion rules can vary by product, packaging configuration, or transaction. Views can then expose the quantity in the unit required by different users without duplicating the underlying facts.
Timestamps and Time Zones
When a business process requires time-of-day precision, a timestamp can be stored directly in the fact table. When transactions occur across multiple time zones, it is generally preferable to store the timestamp in a canonical representation, such as UTC, and convert it to the required local time when presenting the data.
For example, an order occurring at:
2026-09-12 10:30:00 UTC
represents the same instant as:
2026-09-12 16:00:00 IST2026-09-12 06:30:00 EDT
The underlying event remains the same; only its time-zone representation changes.
This avoids storing multiple redundant timestamps for the same event while allowing BI tools and applications to present the timestamp in the appropriate local time zone.