One physical date dimension can play several roles

An order has order, payment, shipping, receipt, and refund dates. Copying date data for every role increases maintenance and drift.

Use one conformed dimension with named semantic relationships for each foreign key.

Put the time role into the metric contract

Order value usually follows order date; fulfilment duration spans order to receipt; refunds differ when reported by refund or original order date.

Declare a default and allow an explicit role switch.

One question may use two roles

“Ordered this month and received this month” filters both relationships. One global date filter cannot express it safely.

Model aliases, join paths, and filter scope instead of letting query generation guess.

Interpret null dates with process state

A blank ship date can mean not yet shipped rather than missing data; a cancelled order may never have a receipt date.

Status and quality flags determine inclusion in duration metrics.

Accept across role boundaries

Test sales by order date, fulfilment by receipt date, cross-month receipt, unshipped orders, refunds, and dual-date filters.

BuildTable can be evaluated for date-role semantics; aliases, filter paths, and generated role selection need POC verification.

Share the physical calendar and expose semantic roles

Maintain one governed calendar with fiscal, holiday, and working-day attributes. Facts carry order, paid, ship, delivered, and refund keys and join the calendar through named aliases.

Expose each role as an explicit semantic object with descriptions, default metrics, and filter behavior. Physical reuse without semantic names invites a query tool to select the wrong relationship.

Make time role part of the metric contract

Order value, shipped value, and refunds by event date or original order date are different metrics. Record default role, timezone, day boundary, and allowed alternatives.

Durations need two roles and often timestamps in addition to date keys. Generated queries should use the governed duration rather than inventing subtraction logic.

Scope filters independently across two roles

“Ordered in July and delivered in August” needs two aliases and two predicates. One global date filter cannot express the question safely.

Prevent tools from merging same-named fields or comparing series on different event axes without labels. Make every active time scope visible to the user.

Separate not-yet, unknown, and not-applicable dates

A blank ship date for an open order is not the same as a missing source value, and a digital product may not require shipping. Model process state and quality explicitly.

Handle future reservations, invalid dates, late events, cancellations, and repeated fulfilment without forcing every order into one date per role.

Test roles and verify BuildTable scope

Test key integrity, fiscal uniqueness, timestamp alignment, cross-month, timezone, cancellation, refund restatement, and dual-role filters against source orders.

BuildTable may be evaluated for role semantics, but relationship generation, default choice, dual filtering, and AI role selection require POC verification. Separate physical dimensions remain valid when calendars or grains truly differ.

How many date dimensions should an order with several business dates use?