Facts: Many to Many using a Bridge Table

Since version 6.1 there is a new way creating a many to many structure based on a bridge table. A bridge table is an intermediate table that defines a many-to-many relation between a fact row and a dimension table. Before 6.1 a way solving this in icCube was to create a 'fake' hierarchy or dimension. Now it's possible to do this directly in the fact link definition (measure groups).

We need to create a new schema that will have three tables, 'Dim', 'Fact' and 'Bridge.

Dim Table (the one defining our dimension) :

    dim_member_id, name
    1, I'm One
    2, I'm Two

Fact Table (the one defining the facts):

    ft_id, value
    1,1
    2,2

Bridge Table (the one defines the many-to-many between the Dim Table and the Fact Table):

    br_member_id, br_fid
    1,1
    2,1
    2,2

As you can see the bridge table allows for binding the second row of the fact table to both dimension members "I'm One" and "I'm Two". After creating our dimension, defining the many-to-many relation in the measure group is straight forward. Select 'Last Level (default)' as the link method and click on the link as shown on the image below:

And set up your bridge definition that is allowing to create such relation.

Eventually, we can deploy the cube and run this MDX query to check the many-to-many :

    SELECT
        [Dim].[Dim].members on 0
    FROM [Cube]
    

Both the schema and the data are available for download here.

Next chapter: Facts: Many to Many using Arrays.