Harborline Foods: Revenue Inflates When Shipments Join the Grid

This is a fact-table fan-out, not a metric arithmetic error. At the Item and Warehouse grain, each order line matches every shipment line for that item and warehouse. If an item has one $100 order line and four shipment lines, the joined result has four rows and Revenue sums to $400. Returns add a third multiplier: order line × shipment line × return line.

The metric's default level is order line, but the report evaluates at the grain of the attributes on the template. Once Shipment attributes are present, the engine cannot know that Revenue belongs only to the order line, so it repeats Revenue on each shipment row and the dossier sums the repeats. The 18.4% gap is consistent with an average shipment fan-out well above one and varies by item because parcel counts vary.

A quick diagnostic is to compare row counts and distinct order-line counts by item. Another is to sum Revenue by Item with and without a Shipment attribute; the ratio approximates shipments per order line. Adding Return Amount makes the test noisier because returns multiply independently. The defect is the missing grain contract between the two facts, not a bad sum.

Option A is a persisted allocation fact at shipment grain. Allocate order-line revenue to shipment lines using a documented rule, such as shipped units or shipped value, then model Revenue and Shipped Units on the same grain. This supports a true row-level grid and makes fill rate and allocated revenue additive. The cost is that the allocation is a business rule: partial shipments and returns change how revenue is assigned, and Finance must approve the rule.

Option B keeps the facts separate and joins only at a conformed aggregate grain, such as Item, Warehouse, and day. Use multi-pass SQL or an Intelligent Cube with separate passes: one pass aggregates order revenue, another aggregates shipment units, and the dossier combines them at the common grain. This avoids fan-out and is simpler to reconcile, but it cannot show shipment-level detail beside order revenue.

I recommend B for the director's screen, with two coordinated zones in one dossier rather than tabs, because the requirement is fill rate and revenue by Item and Warehouse, not shipment-line revenue. If shipment-line revenue is later required, adopt A with a Finance-approved allocation and certify the allocation metric. Do not use a fixed metric level alone; a level does not prevent fan-out when shipment attributes are on the report. Document the grain of every fact and remove reliance on automatic joins for unrelated facts.

Add a daily reconciliation dossier that compares Revenue by GL account and day to the general ledger, with a variance threshold such as 0.5% and an owner for exceptions. Add a grain-control query that compares fact row counts to distinct order-line counts and flags ratios outside the expected parcel range. In the ETL, test that order-line amounts are unique per order line and that shipment quantities are unique per shipment line.

In the semantic layer, document fact grain and attribute relationships, require review before adding unrelated facts to one template, and prefer separate fact containers or a certified allocation. Add a regression test with items that have zero, one, and four shipments, plus partial shipments and returns. Publish the test results with the certification record.

Finally, use impact analysis before changing the dossier, and give Finance a signed reconciliation pack for one full close period. The pack should show the old and new Revenue, row counts, and the allocation rule if Option A is used. That converts the fix from a developer assertion into an auditable control.