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.

The BMM layer is where an RPD developer stops thinking like a DBA and starts thinking like a data modeler. Logical joins here describe relationships between logical tables, not literal SQL join syntax — the BI Server figures out at query time which underlying logical table sources (and therefore which physical joins) to actually use to satisfy that logical relationship.

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

A logical fact table (e.g., "Sales Facts") is built from one or more logical table sources (LTS) — each LTS maps to one or more physical tables and declares the mapping expressions for each logical column. This is exactly the mechanism that enables aggregate navigation: a single logical fact table can have multiple LTSs, one pointing to the detailed daily fact table and another pointing to a pre-aggregated monthly summary table, with content levels on each LTS telling the BI Server which one to use for a given query's grain.

logical table sources (LTS)

Logical dimension tables are modeled similarly, and their key structure combined with hierarchies and levels (defined via the Dimension editor) is what powers both drill-down behavior in Answers and aggregate navigation on the fact side (a fact LTS can declare it's aggregated "at the Month level of the Time hierarchy," and the server only routes there when a query doesn't need finer grain).

hierarchies and levels

A classic interview question: "why do logical joins matter if physical joins already exist?" The answer is that logical joins define the *conceptual* star-schema relationships the BI Server reasons about for query generation and multi-source aggregation, while physical joins are only used once a specific LTS has been chosen — the two layers of joins solve different problems and both must be correct.