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 the result is wrong in a way that raises no error. The Kimball Group states the rule as a prohibition: 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.

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;

Two 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.

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.

References: Kimball Group, Drilling Across; Kimball Group, Multipass SQL to Avoid Fact-to-Fact Table Joins.