Drill-Across

Drilling across is the technique for putting measures from two or more fact tables into one report. The Kimball Group defines it as making separate queries against each fact table, where the row headers of each query consist of identical conformed attributes, and then aligning the results on those common row headers. BI tools implement the same idea under names such as “stitch” or “multipass query.”

Why not just join the fact tables?

Because when the join multiplies rows, the result is wrong in a way that raises no error. The Kimball Group states the rule as a prohibition for BI applications: a BI application must never issue SQL that joins two fact tables together across their foreign keys, because the cardinality of the result cannot be controlled. An order that shipped in three parcels joins to three shipment rows, so its order amount appears three times and every sum over it is inflated — the mechanism of double counting. Whether a direct join is safe depends on which measures are summed and how rows match. A verified one-to-one match — both tables at one row per order — preserves the totals of both sides. A one-to-many join is also correct for measures from the many side alone: joining shipments to their order and summing shipment cost gives the right figure, and only summing the order amount alongside it is wrong. Unmatched rows need a decision too. The rule protects tools that generate SQL without knowing any of this.

How to do it

1. Decide the grain of the final report — for example, one row per month and customer.
2. Aggregate each fact table separately to exactly that grain, using conformed attributes for the grouping columns.
3. Combine the aggregated results on those grouping columns.

WITH ordered AS (
    SELECT DATE_TRUNC('month', order_date) AS month, customer_key,
           SUM(order_amount) AS total_ordered
    FROM orders
    GROUP BY 1, 2
),
shipped AS (
    SELECT DATE_TRUNC('month', ship_date) AS month, customer_key,
           COUNT(*) AS shipment_count
    FROM shipments
    GROUP BY 1, 2
)
SELECT COALESCE(o.month, s.month)               AS month,
       COALESCE(o.customer_key, s.customer_key) AS customer_key,
       o.total_ordered,
       s.shipment_count
FROM ordered o
FULL OUTER JOIN shipped s
  ON s.month = o.month AND s.customer_key = o.customer_key;

Three details carry the correctness. Both sides must be aggregated to the report’s grain, not merely summarized at their own storage grain; if orders were grouped per order and shipments per shipment before joining, the fan-out would return. And the combining join is a full outer join, so a customer-month with orders but no shipments, or shipments but no orders, is not silently dropped. And the keys it joins on must not be null: a null customer key does not equal another null, so an unknown customer’s orders and shipments in one month would come back as two rows. Give unknown members a real key, or, if the report deliberately groups unknowns, compare with IS NOT DISTINCT FROM — which groups them for reporting without claiming they are the same customer.

What it depends on

The merge only works if the row headers genuinely mean the same thing in both results. If orders use a customer key from the sales system and shipments use one from the logistics system, or “month” is a calendar month on one side and a fiscal period on the other, the results align on labels that do not correspond. That is why drilling across and conformed dimensions are described together: the technique is simple, and the agreement it relies on is the hard part.

How drilling across fits with grain and additivity is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.

Further reading

• Kimball Group, Drilling Across
• Kimball Group, Multipass SQL to Avoid Fact-to-Fact Table Joins
• PostgreSQL Documentation, Comparison Functions and Operators