In is not uncommon in a data estate to have two tables that need to be related to one another using Many To Many Relationships, where each record in one table can relate to multiple records in the other table and vice versa. A common example of this is a database of parents and children, where each parent can have multiple children, and each child can have multiple parents.
The following uses the example of Parents and Children to clarify how this can be done in TimeXtender Classic.
Data Warehouse Tables
We start out with the two dimension tables of parents and children.
Dimension 1: Parents

Dimension 2: Children

Bridge Table: Parent_Child
We need a third table that functions as a Bridge Table and contains all the relationships between the parents and children. Each row in this table represents a single relationship, so each ParentID or ChildID may appear in multiple rows in the Bridge Table in order to create all the relationships that they are a part of.

Many to Many Relationship in a Semantic Tabular Model
The Bridge Table is used to create the many-to-many relationships that will filter the data in both directions as shown below.
- The first step is to setup the proper table relations in the Data Warehouse. As with most relations in TimeXtender, you want to drag from the One side of the relation to the Many side. In this scenario, the Bridge Table is the Many side for both relations.
- The next step is to drag all three tables into the Semantic Model, bringing in the Bridge Table last, which will ensure TimeXtender automatically detects both relationships from the MDW.
- Since the fields in the Bridge Table are only used to relate the model, and not typically used for end-user analysis, these foreign keys could be hidden in the model.
- Once the tables are in the Semantic model, set the Filter Direction to “To Both Tables” on both relations under the Bridge Table. This will ensure that when filtering one side of the table that it will filter all the way through the bridge table to the table on the other side.

- Once you deploy, execute, and open the model in PowerBI, the Model tab will appear as follows:


Many To Many Relationships in a SSAS Multidimensional OLAP Cube.
The Parents and Children tables will be added as Dimension tables and Fact Tables (Fact Table Dimensions in the Relation), while the Parent_Child Bridge Table will be added as just a fact table. The Parent and Children tables are both linked directly to the Parent_Child fact table. Having a dimensions linked to the intermediate fact table allows the dimensions to use the intermidiate fact table to link with each other.
NOTE: You do not need an intermediate fact table if you have a dimension linking the fact tables directly.
- Start by adding the Children and Parent tables as regular dimensions.
- Dimention 1: Children table:

- Dimension Two: Parents table

- The next step is to add a cube with all three tables as fact tables.

- After adding all three tables as fact tables, create a standard count measure for each table.
- The Parent Count measure uses the ParentID field:

- The Child Count measure uses the ChildID field:

- The Parent Child Count Dummy Measure uses the DW_ID field.

- Add the Parent and Children Dimension tables to the cube and setup the dimensional relations as follows:

- For the Children dimension, the relation is to the dbo.Parent_Child fact table using the ChildID. Select “Facttable_Dimension” under the dbo.Children fact table.
- For the Parents dimension, the relation is to the dbo.Parent_Child fact table using the ParentID. Select “Facttable_Dimension” under the dbo.Parents fact table.
- The final step is to add the Many To Many relations between the Children and Parent dimension tables going through the Parent_Child table.
- Right-click on the Children dimension and select “Add Many To Many Relation.”

- Configure the dialog to relate to the Parents Dimension through the Parent_Child fact table.

- Right-click on the Children dimension and select “Add Many To Many Relation” and relate to the Children table through the Parent_Child fact table.

- The Completed Cube in TimeXtender Classic appears as follows:

- Deploy and Execute the Cube to ensure that everything is valid.
- If we view the cube in PowerBI, the models and tables appear as follows:

