Meridian Retail: Two Date Roles Cause an 11.8-Million-Row Report
Meridian Retail has one Date lookup table containing date_key, calendar date, fiscal quarter, fiscal year, and week. The project contains Order Date and Ship Date as attributes built from that same lookup. A fulfillment report places Sales by Order Fiscal Quarter and Ship Fiscal Quarter on one grid. The report returns 11.8 million rows against an expected 380,000, and the sales total is inflated. Sales by Order Fiscal Quarter alone reconciles. The fiscal year starts February 1 and uses a 53-week calendar every fifth year. Some orders have no shipment yet, and returns are recorded against the original order date. The designer created Ship Date as an alias of Date and relied on automatic joins. Analysts need order-quarter cohorts, ship-quarter fulfillment, and on-time rate in one dossier, and they cannot accept two separate reports.
1. Diagnose the row explosion and the inflated Sales total. Explain why this is a role-playing dimension problem rather than a bad filter.
Read the worked answer
The project has one logical Date role used twice. Order Date and Ship Date both resolve to the same Date lookup and the same attribute identity, so the engine cannot keep the two roles independent when both are on the template. The report grain becomes every combination of order-date and ship-date values that the join can produce for an order, not one order date paired with its one ship date.
The expected grain is one row per order per order-quarter and ship-quarter pair. The actual grain is a many-to-many combination: an order with an order date in Q1 and a ship date in Q2 can be matched to many date rows, and the same sales amount is repeated across those combinations. That both multiplies rows and inflates Sales. Removing Ship Fiscal Quarter removes the second role and restores the reconciliation, which points to role collapse rather than a filter problem.
This is not fixed by a report filter on fiscal year. It is a semantic-layer modeling defect: role-playing dimensions need distinct logical attributes, each with its own lookup alias and relationships. The 11.8 million rows are a symptom; the inflated Sales total is the real business risk because it can reach a dashboard unchallenged.
2. Design the date-role model and the null-shipment handling. Include how on-time rate should be defined.
Read the worked answer
Create two distinct attributes, Order Date and Ship Date, each based on its own lookup alias or view of the date table. Each gets its own ID, descriptors, and relationships to Fiscal Period and Calendar. Do not reuse one Date attribute twice, and do not rely on an alias that still shares the same logical identity. The physical model can use two views or a role-playing alias pattern, but the semantic layer must expose two independent date dimensions.
Make Ship Date nullable and add an explicit 'Not Shipped' member so unshipped orders remain in order-cohort counts without creating false ship dates. Returns should remain tied to the original order date unless the business explicitly wants return-date analysis, in which case add a third role rather than overloading Ship Date.
Define on-time rate at the order grain: shipped on or before the promised ship date divided by orders that were due in the period. Keep the numerator and denominator in one metric definition with the same filters, and display it by Order Fiscal Quarter for cohorts and by Ship Fiscal Quarter for fulfillment. Test partial shipments, multiple parcels, null ship dates, and returns. Confirm that the row count equals distinct orders for the selected cohort and that Sales matches the order-only report.
3. The 53-week fiscal calendar and the February start add risk. What would you verify before certifying the dossier?
Read the worked answer
Verify that fiscal period is a stored attribute with defined start and end dates, not a string derived from calendar month. Test the year boundary around February 1, including the 53rd week, leap years, and the transition from the prior fiscal year. Confirm that the 53rd week is assigned to the correct fiscal quarter and that week numbering does not create duplicate period labels.
Test the date roles independently: Sales by Order Fiscal Quarter should match the reconciled order report; Shipments by Ship Fiscal Quarter should match the warehouse report. Then test them together and compare distinct order counts to the expected cohort count. Include orders that cross the fiscal year boundary, orders shipped in a later fiscal year, and orders never shipped.
Check that on-time rate denominators exclude orders not yet due and that the 'Not Shipped' member is visible but not counted as late without a rule. Run the dossier against both the fiscal hierarchy and the calendar hierarchy and confirm that drill paths do not mix roles. Finally, document the fiscal calendar owner and the 53-week rule in the metric definition so future modelers do not rebuild it inconsistently.