QSDA: Synthetic Key, Circular Reference and a Broken Match Rate

Synthetic Key, Circular Reference and a Broken Match Rate

A mid-size distributor runs a Qlik Sense app on two ERP fact tables: Orders (14.2M rows, with OrderDate and PromiseDate) and Shipments (11.6M rows, with ShipDate and OrderDate). The developer built one master calendar and linked it to all four date fields. The model now shows a synthetic key $Syn1 containing those dates plus a fourth field. Shipments still reports 11.6M rows, but a KPI that should match about 94% of orders to shipments returns 58%, and Qlik warns of a circular reference along Calendar-Orders-Shipments. Users also complain that selecting a single month in the calendar changes order and shipment totals at the same time.

1. Explain what produced both the synthetic key and the circular reference, say which symptom is the more serious, and give one fix you would ship this week and one you would propose for the next release.

Read the worked answer

Cause. The synthetic key is a symptom, not the disease. Four date fields share the same names across two tables, so Qlik cannot tell which pair belongs together and fabricates $Syn1 from the shared field names. Because $Syn1 also contains fields that the calendar is linked to, the same key creates a second join path between Calendar, Orders and Shipments, and Qlik reports that path as a circular reference. The single calendar linked to four date fields is what made two distinct roles look like one relationship.

Which is worse. The circular reference. A synthetic key is ugly but deterministic; a circular reference means the table graph no longer has one predictable join path, so which table the calendar filters first becomes dependent on load order and can change between reloads. That is why the match-rate KPI cannot be trusted until the loop is gone.

This week (safe, reversible). Break the ambiguity: give every date field a unique name (OrderDate, PromiseDate, ShipDate, ShipmentOrderDate) using explicit AS or QUALIFY *, and remove the four-way link into the single calendar. $Syn1 and the loop both disappear. Accept that users temporarily lose the shared month selector.

Next release. Reintroduce cross-subject date filtering deliberately - a role-playing calendar or a canonical date bridge with an explicit design decision attached - rather than letting Qlik infer it. Test with explicit row counts on both facts before and after.

2. The business insists on one month selector that filters orders by order date and shipments by ship date. Compare role-playing calendars with a canonical date bridge. State the consequence of the bridge for row counts and for measures.

Read the worked answer

Role-playing calendars. Two copies of the calendar - Calendar_Order keyed on %OrderDateKey, Calendar_Ship keyed on %ShipDateKey. Each fact keeps exactly one row per physical row, associations are unambiguous, and Sum(Amount) and Count(OrderKey) stay additive and correct. Cost: two selectors. If a user wants January on both, they select January in both, or you add a month island driven by set analysis or triggers. That is a usability cost, not a correctness cost.

Canonical date bridge. One calendar, plus a bridge table with one row per (object key, date) pair - an order with three date roles appears three times. Selecting a month filters cleanly through the bridge. Cost: the bridge sits on the many side of the relationship, so the fact table fans out and every additive measure multiplies by the number of date roles per row. Sum(OrderAmount) on an order-grain chart returns 2x or 3x the true value unless the measures are rewritten as Count(DISTINCT OrderKey) or the amount is kept in a separate fact that does not traverse the bridge.

Recommendation. For a distributor with a fixed, known set of date roles, role-playing calendars are the right default - no fan-out, no measure rewrite, no silent triple counting. Reserve the bridge for the genuine 'tell me everything that happened around any date' requirement, and if you use it, document the multiplication rule and audit two KPIs against a hand-calculated total.

3. What does a 58% match rate tell you about key consistency, and what would you inspect before accepting any model redesign?

Read the worked answer

A match rate that far below the expected 94% is a data-model signal, and the cause may not be the date fields at all. Inspect, in this order:

Granularity. Is the key OrderNo on both sides, or OrderNo on one and OrderLineNo on the other? If one order ships as three lines, a header-to-line match will never reach 94% and the fix is a granularity decision, not a join fix.
Datetime vs date. If OrderDate carries a timestamp in one table and is truncated in the other, the association either fails or fans out; compare Floor() of both.
NULLs. Orders never shipped have a NULL ship date. Decide whether those are unmatched or not-yet-matched - the KPI definition, not the script, answers this.
Type and format. Numeric vs text keys, leading zeros, padded codes. Check with IsNum() and Len() on a sample.
Fan-out. If the circular reference persisted in any published copy, duplicate associations inside $Syn1 alone can depress a distinct-count ratio without any missing data.

Practical test. Build a temporary table with a left join from Orders to Shipments plus a Missing flag, and count the flag by month. If unmatched volume is spread evenly across months it is a key defect; if it is concentrated in the last few weeks it is simply in-transit volume and the 94% target itself is wrong.