Conform a date dimension
Share one date dimension between an orders fact and a shipments fact so order-to-ship timing is comparable.
Concept
conformed-dimensions
The primary modeling idea this problem reinforces.
Requirements
3
Business needs the model must satisfy.
A retailer runs two processes (ordering and shipping) and wants order-to-ship timing analyzed on one consistent calendar. The date dimension already exists; the two process facts do not.
Conformed dimensions are what make cross-process analysis possible. If ordering and shipping each carry their own raw dates, the calendars drift and timing comparisons stop being trustworthy.
- Add an orders fact and a shipments fact, each at its own event grain.
- Reference the existing date dimension from both facts.
- Declare surrogate-key foreign keys and N:1 relationships from each fact to the date dimension.
Template
Conformed date dimension starter
The model users start from in the full workspace.
Entities
1
Seeded tables visible in the public preview.
Dialect
postgresql
Generated SQL target for this starter model.
This preview is read-only. Open the workspace to edit and verify; saving needs a free account.
Open workspaceTry the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.