Retail conformed dimensions

A retailer wants net-of-returns revenue and margin by customer and product, with product list price reflected as it was at sale time. Orders and returns are separate processes; the customer and date dimensions exist, and product currently overwrites price in place.

Real enterprise models combine patterns: conformance makes orders and returns comparable, while a Type 2 product dimension keeps historical price correct. Getting one without the other produces wrong net revenue.

• Add orders and returns facts at their own grains.
• Share the customer, product, and date dimensions across both facts.
• Floor: make the product dimension Type 2 with an effective-from so price versions are preserved.
• Reference each dimension through surrogate-key foreign keys.
• Grade up: bound each product version with an effective-to so validity windows are explicit.