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 exact 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
Giving two measurements the same name is not enough. If one sales_amount metric includes taxes and another sales_amount metric excludes them, they are not conformed.
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 Currencies
When a business records financial transactions in different currencies, the fact table must store both the original local amount and the amount converted to a standard currency.
The local amount preserves the true value of the transaction. The standard amount provides a consistent baseline for company-wide reporting. Because exchange rates change daily, you must lock in the conversion rate at the exact time of the transaction using an agreed-upon exchange rate source.
The fact table must also include a currency dimension key to identify the original currency used.
| 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 group them together using the standard currency.
Multiple Time Zones
When a business process requires time-of-day precision, a timestamp can be stored directly in the fact table. If the business operates across different time zones, these timestamps should be stored using a canonical representation, such as UTC.
NOTE
A canonical representation is simply the standard, agreed-upon format used to store data consistently across a system. For time zones, UTC acts as this single baseline that all other local times are calculated from.
Unlike exchange rates that constantly change, time zone differences are fixed and predictable rules. Because of this, you only need to store the standard UTC time in the database. Your reporting tools can then automatically calculate the correct local time on the fly.
For example, two orders placed at the same instant in Hyderabad and New York have the same stored UTC timestamp. The table below shows how they appear in a report: the UTC timestamp comes from the fact table, while the local time is calculated by the reporting layer using each store’s time zone.
| order_key | store_location | stored_time_utc | Local time (calculated in report) |
|---|---|---|---|
| 1 | Hyderabad | 2026-09-12 10:30:00 UTC | 2026-09-12 16:00:00 IST |
| 2 | New York | 2026-09-12 10:30:00 UTC | 2026-09-12 06:30:00 EDT |
In this design, local time is a derived report field, not a stored fact-table column. Both orders occurred at the same instant; their displayed local times differ because the stores are in different time zones.
Business Dates and Fiscal Calendars
If a business day ends at 04:00, a sale at 01:30 on September 13 counts toward September 12. Similarly, if the financial year starts in April, September is counted as fiscal month 6. These rules dictate how data is grouped for reports, but they never change the original stored timestamps.
Multiple Units of Measure
Some business processes require the same quantity to be reported in multiple units. For example, a supply chain might track inventory in pallets, cases or individual units.
Instead of creating separate columns for every possible unit, store the quantity in one standard unit along with the conversion factors needed to derive the others.
| date_key | product_key | qty_standard_pallets | cases_per_pallet | units_per_case |
|---|---|---|---|---|
| Jan 1 | Widget | 5 | 10 | 100 |
From this single row, a reporting tool can calculate:
- Cases:
5 pallets × 10 cases = 50 cases - Units:
5 pallets × 10 cases × 100 units = 5,000 units
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.