Working with many-to-many relationships

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

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. To add a bridge node for many-to-many relationships, do the following:

   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.

7. Define the relationship between ‘bridge and fact’ and ‘bridge and dimension.’

   Open

   

Creating bridge dimension from Bridge node for many-to-many semantic models

To create a bridge dimension from Bridge node for many-to-many semantic model, perform the following steps.

1. From the Toolbox, click Semantic Models.

2. Select a semantic model from the list.

3. Drag a Bridge node candidate dimension from the list on the left into the dimension column.

4. Click Save. 

Open

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