Working with many-to-many relationships

Applies to:Kyvos Enterprise  Kyvos Cloud (Managed Services on AWS)  Kyvos Azure Marketplace

Kyvos AWS Marketplace  Kyvos Single Node Installation (Kyvos SNI)  Kyvos Free (Limited offering for AWS)

A many-to-many relationship occurs when multiple records in one table are associated with various records in another table. For example, a many-to-many relationship exists between customers and accounts when customers have multiple accounts, and accounts can be opened jointly by many customers.

To provide insights into data, you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a bridge table. Each record in a join table includes a match field that contains the value of the primary keys of the two tables it joins. (In the join table, these match fields are foreign keys.) These foreign key fields are populated with data as records in the join table are created from either table it joins.

To create a many-to-many relationship, perform the following steps: 

1. From the Toolbox, select Relationships.
2. Click the Action menu ( ⋮ ) at the top of the Relationships column and click Add Relationship.
   Or type a name of a file or folder to search for in the Search box.

3. In the File column, drag more than one file onto the relationships workspace.

4. In the file, click the field that you want to map.
5. Drag a field to map it to a field in a different node to create a relationship. 
6. Click the Line to view the relationship details.
7. Specify the bridge node using the Node Type field in the Properties pane. Define the relationship between bridge and fact and bridge and dimension, as shown in the following figure.

Adding bridge dimension for many-to-many relationships

To add a bridge dimension for many-to-many relationships, perform the following steps: 

1. Add a third node where each record contains the primary keys of the two tables you will join.
2. Drag a field from each of the first two nodes to map them to a field in the third node to create the relationships. 
3. Select that third node, then view its properties in the Properties panel.
4. Under Node Type, select Bridge.

Adding bridge dimension for many-to-many relationships from semantic model designer

To add a bridge dimension for many-to-many relationships while viewing a semantic model, perform the following steps.

1. From the Toolbox, click Semantic Models.
2. Select a semantic model from the list.
3. Click the Refine tab, to confirm you are looking at the Physical View.
4. On the Dimensions column, click the + icon.
5. In the Dimension Properties, specify the Name for the dimension.
6. Select the Bridge option from the Type drop-down list.
7. Select the Bridge Node from the drop-down list.
   
8. Click Save. 

Properties

Selecting a node shows its properties in the Properties panel. You can specify the node to be Fact, Dimension, or Bridge using the corresponding option from the Node Type area. 

It also shows the Source information and Summary of all the relations created in the designer.

You can also define the relationship style as a line or a curve.

Creating relationship from Properties pane

You can also use Properties to create the relationship. 

To use the properties, perform the following steps: 

1. In the Properties pane, navigate to the Relationships section, and click Add Relationship. 
   The source node is the currently selected node. Or you can click the drop-down arrow to view the list and select a different source node.
2. Click to select a field.
3. Select the target node from the list and click to select a field.
4. Click Apply. 

Related topics

• Working with relationships