icCube

Documentation

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. There is an option in the link called 'Bridge' allowing to create such relation :

OLAP Fact creation

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.