Composite Fact Layer (CFL)

Definition

The Composite Fact Layer (CFL) is an architectural layer in a semantic engine that makes multi-fact, multi-grain analytical queries correct by construction. When a single query references measures drawn from two or more independent fact tables, the CFL detects the multi-fact pattern, decomposes the query into per-fact sub-queries at the correct grain, aggregates each fact independently, then recomposes the results over their shared (conformed) dimensions — eliminating fan-trap over-counting and chasm-trap under-counting.

The Problem: The Fan Trap

Suppose you have two fact tables, orders and support_tickets, both related to customers. You ask: "Total order revenue and total support tickets, by customer, last quarter." A naive generator joins both facts through the shared customer dimension:

SELECT c.customer_id,
       SUM(o.amount)      AS revenue,
       COUNT(t.ticket_id) AS tickets
FROM   customers c
JOIN   orders          o ON o.customer_id = c.customer_id
JOIN   support_tickets t ON t.customer_id = c.customer_id
GROUP  BY c.customer_id;

This runs cleanly and returns plausible numbers. But revenue is multiplied by the number of tickets per customer, and tickets is multiplied by the number of orders. The grains collide; the totals are silently wrong.

The Resolution: Split, Aggregate, Recompose

The CFL recognises that the query spans two independent facts and rewrites it as independent aggregations, recomposed over the shared dimension:

SELECT customer_id, SUM(revenue) AS revenue, MAX(tickets) AS tickets
FROM (
  SELECT customer_id, SUM(amount) AS revenue, NULL AS tickets
  FROM   orders          GROUP BY customer_id
  UNION ALL
  SELECT customer_id, NULL AS revenue, COUNT(*) AS tickets
  FROM   support_tickets GROUP BY customer_id
)
GROUP BY customer_id;

The three steps, generalised:

1. Decompose the query into one sub-query per fact, each at that fact's own grain.
2. Aggregate each fact independently, so no measure is inflated by another fact's cardinality.
3. Recompose over the conformed dimensions (via UNION ALL or a full outer join on the shared keys).

Why It Belongs in the Engine, Not the Prompt

Fan-trap prevention is a property of the SQL generator, not of any individual query or prompt. You cannot reliably prompt-engineer an LLM out of fan-traps across an open-ended set of multi-fact questions, and hand-written SQL drifts the moment the model changes. Because the CFL lives inside the OrionBelt Semantic Layer (OBSL) compiler and is driven by the same join logic as every other query, every multi-fact request is split correctly, deterministically, for every consumer — AI agents, BI tools, and reports alike.

Relationship to Other OrionBelt Concepts

• Artefacts Composability Resolution (ACR) surfaces cross-fact measures separately, precisely because they recombine through the CFL at a higher level.
• The Semantic Sidecar pattern is how the CFL reaches every consumer through one governed API.
• Governed Text-to-SQL relies on the CFL so AI agents get fan-trap-free numbers without reasoning about join cardinality.

Frequently Asked Questions