Business Model & Mapping: Logical Joins & Fact-Dimension Design

Explain how logical dimension and fact tables are modeled in the BMM layer with logical joins, and why this differs fundamentally from physical joins.

In the BMM layer you stop describing the database and start describing the star schema. The two kinds of join exist for different reasons.

A logical fact table is like a job title such as "Delivery Driver" — the actual person fulfilling that role might be different people (LTSs) on different days depending on who's available and the specific route (grain), but from the customer's perspective, it's always just "the delivery driver."

Key Concepts

    physical join   real SQL, between physical tables
    logical join    a conceptual relationship between a logical
                    fact and a logical dimension

A logical join carries no SQL. It says "Sales Facts relates to Product". Which physical join is used to satisfy that depends on which logical table source the server picks.

Logical table sources are the mechanism. A logical table is built from one or more LTSs, each mapping to physical tables:

    logical fact "Sales"
      LTS 1 -> F_SALES_DAILY      (daily grain)
      LTS 2 -> F_SALES_MONTHLY    (monthly aggregate)
      LTS 3 -> F_SALES_2019_ARCH  (old data, a different table)

One logical fact, three physical sources. Reports reference the logical fact and never know.

That single structure gives you three things.Aggregate navigation. Content levels on each LTS let the server choose the smallest sufficient table.

Fragmentation. Content filters let one LTS hold current data and another the archive, with the server querying one or both depending on the date range requested.

Multi-source. Different LTSs can point at different databases entirely, and the server federates.

Dimensions are modelled the same way, and their hierarchies and levels are what make aggregate navigation possible — because a fact LTS declares its grain in terms of a dimension's levels.

The question interviewers ask: why bother with logical joins when physical joins exist?Physical joins say how two tables can be joined. Logical joins say what the model means. The server reasons about the model to decide which sources to use, then uses the physical joins to generate SQL against the ones it chose.

Remove the logical layer and you have one fixed path from report to table, which is exactly what aggregate navigation and federation require you not to have.

Where it goes wrong. An LTS whose mappings are incomplete, or whose content level is wrong, produces queries against the wrong table with no error — the same silent-wrongness problem as a bad physical join, one layer up.

What the interviewer is probing.1. "What makes a logical table a fact?" Probing: the modelling. Stalls: "It has numbers." Moves up: it contains measures with aggregation rules and sits at the many end of logical joins to dimensions.

2. "What is the content level used for?" Probing: the key concept. Stalls: "Nothing." *Moves up:* declaring the grain each logical table source holds, which is what lets the server choose the right source and aggregate correctly.

3. "What happens if the aggregation rule is wrong?" Probing: the consequence. Stalls: "A warning." Moves up: numbers are silently wrong — a ratio summed instead of recalculated is the classic case, and nobody notices for months.

4. "How do you handle a measure that should not sum over time?" Probing: the depth. Stalls: "Sum it." Moves up: a semi-additive rule such as last or average over the time dimension, declared in the measure definition.