Consolidated Fact Tables

A consolidated fact table combines facts from multiple related business processes into a single fact table at a common grain. This can simplify cross-process analysis by avoiding joins between separate transaction fact tables at query time.

For example, an order fulfillment process might have separate fact tables for orders, shipments, and deliveries. A consolidated fact table could bring relevant measures from these processes together at the order-line grain, such as:

order_line	ordered_qty	shipped_qty	delivered_qty	order_amount
1001	5	5	4	$500

However, consolidated fact tables are usually a complement to, not a replacement for the underlying transaction facts. They may also be difficult to populate in real time because later-stage facts - such as shipment or delivery - may not exist when the initial transaction occurs.