Multiple Currencies, Units of Measure, and Time Zones
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.