Bridge tables

A logical data model may contain one or more many-to-many relationships. Physical data modelling techniques transform a many-to-many many-relationships into one-to many-relationships by adding additional tables. These are referred to as bridge tables.

The key difference between a bridge table and a fact table is that the bridge table relationship is mandatory. The bridge table relationship restricts the data from one subject area based on the records that are returned from another subject area. A fact table does not provide this restriction because the other two data sets operate as non-conformed dimensions. A filter that is applied to one data set has no impact on the other data set.

There are a number of benefits to creating bridge tables:

  • Properly joining the data streams that exist on each side of the bridge.
  • Filtering both data streams when a filter is applied on only one stream. If the data from one stream is properly associated with the other stream, applying a filter on any column filters out a whole row of data. You can use detail filters and summary filters.
  • Avoiding double counting.

For example, you have a model that contains two star schemas that represent real estate properties and property owners. Multiple properties are owned and shared between multiple owners. The property owners schema contains two query subjects, OWNER and OWNER_FACT. The properties schema contains two query subjects, PROPERTY and PROPERTY_FACT. Add a bridge table to capture the many-to-many relationship between the OWNER and the PROPERTY tables. The bridge table contains the OWNER_ID and PROPERTY_ID query items. It also contains other query items that provide context or meaning to the relationship, such as share_percentage. The cardinality of the bridge table is [1..n] in both relationships and the cardinality of the OWNER and PROPERTY tables is [1..n].

In the following figure, the table OWNER_PROPERTY_BRIDGE was created to capture the owners of properties and the properties that are owned by owners.

Figure 1. Joining two schemas with a bridge table
Example of joinging two schemas with a bridge table

When a query is executed, dynamic query mode determines which tables must be joined together. Query subjects which are on the many side of a one-to-many relationship are treated as facts. In the previous figure, OWNER_PROPERTY_BRIDGE is assumed to be a fact due to the one-to-many relationships. This may cause queries to not return the expected result set.

A query subject has a usage property which can be set to the value of Bridge. This property enables dynamic query mode to generate queries knowing that the query subject is not a fact due to the one-to-many relationships. A model may include several bridges:

  • An owner may own several properties.
  • A property may have several owners.
  • A property may have several maintenance contracts with the same contractor.

The following figure is an example.

Figure 2. Joining two schemas with multiple bridge table
Example of joining two schemas with 2 bridge tables

Restrictions

Bridge tables are subject to the following restrictions.

  • Dynamic query mode assumes that a bridge table has been used to resolve a many-to-many relationship between dimensions. Design techniques which introduce a bridge table between a dimension and fact table are not supported.
  • Dynamic query mode requires non-ambiguous join paths between two query subjects. In the following figure, a model defines two join paths between OWNER and PROPERTY.
    Figure 3. Joining two schemas with a bridge table
    Example of joining two schemas with a bridge table

    If a query only references items from OWNER, PROPERTY, and CONTRACTOR, dynamic query mode is unable to determine which bridge table it should use and will return the following error.

    • Query 'Query1' has implicit and explicit references to multiple parallel bridge query subjects: [VIEW].[OWNER_PROPERTY_BRIDGE], [VIEW].[OWNER_PROPERTY_BROKER_BRIDGE].
  • Reports may include summaries if grouped headers or footers are automatically calculated by dynamic query mode. Queries may also include expressions used to filter data or compute new attributes and measures. Dynamic query mode may return an error for some scenarios when bridge query subjects are present.
  • A string, mathematical, or logical expression using items or filters cannot reference items from query subjects on either side of a bridge query subject.
    • The query is not supported. Query 'Query1' contains data item 'FullName' which has an expression that refers to opposing sides of a bridge query subject.
    • The query is not supported. Query 'Query1' contains a detail filter which has an expression that refers to opposing sides of a bridge query subject
    • The query is not supported. Query 'Query1' contains a summary filter which has an expression that refers to opposing sides of a bridge query subject
  • Queries must have be set to perform automatic grouping and summarization of data. Dynamic query mode does not allow a detail query which projects items from subjects on either side of a bridge query subject.
    • The query is not supported. Query 'Query1' references a bridge query subject and therefore cannot have the property 'Auto Group and Summarize' set to No.
  • Items which compute an aggregate value cannot include the pre-filter clause.
    • Query 'Query1' contains data item 'PrefilteredTotal' which has a pre-filter aggregate expression, this is not supported when the query references a bridge query subject.
  • Average summaries within a bridge query are not supported. A bridge query that refers to a fact having its default aggregation set to average will fail with the following error.
    • The query is not supported. Query 'Query1' contains data item 'costs' which has an implicit average aggregate expression, this is not supported when the query references a bridge query subject.
  • Analytic expressions are not supported and will generate the following errors.
    • The query is not supported. Query 'Query1' contains data item 'theRank' which has an analytic aggregate expression, this is not supported when the query references a bridge query subject.
    • The query is not supported. Query 'Query1' contains data item 'nextRow' which has running aggregate expression, this is not supported when the query references a bridge query subject.
  • Cross tab reports have very limited support when including a bridge query. Here are the main errors generated in that context.
    • The query is not supported: only simple crossTab queries are supported when a bridge table is accessed.
    • The query is not supported. Query 'Query1' contains an explicit aggregate expression which is not supported in a Relational crosstab.
  • Reports created against a DMR package. In general, the whole query must be pushed to the relational provider which is where the bridge table logic has been implemented. If the query requires local OLAP processing, then the bridge functionality cannot be applied and the following error is generated.
    • The query is not supported. DMR query 'Query1' requires OLAP processing which is not supported when the subquery references a bridge query subject.
  • Hand-coded aggregate expressions, similar to a pure relational query, are not supported and generate the following error.
    • The query is not supported. Query 'Query1' contains an explicit aggregate expression which is not supported against a DMR model.
  • Custom summary expressions. These are typically associated with report headers and footers. While simple hand-coded expressions may be supported (for example, Total (propertyValue for ower)), more complex expression will generate the following error.
    • The query is not supported: custom footer expressions are not supported when a bridge table is accessed.