Configure Many-to-Many Relationships

Author: Ian Britz

Introduction

A Many-To-Many Relationship is when you relate a Member from one Dimension to any number of Members from another Dimension, and vice versa. This relationship type is useful when you need different Dimension Members to access the same value.

For instance, imagine there are two different accounts containing the different amounts of cash a business has on hand.

Now let's assume there are two departments, one which can access the cash in both of the accounts, and one which can access only the short-term cash.

The traditional approach would be to create a Validation Model, define the relationships between the departments and the accounts, and then join the two models as Linked Models. When Department A accesses the data, they would see both the accounts, while Department B would see only the short-term cash account.

However, if we examine the Model Data directly, we can see there's a problem. The data shows that there's $1,000 in Short-Term Cash for Department A and $1,000 in Short-Term Cash for Department B. If we aggregate the department data, Kepion would tell us that we have $2,000(!) on-hand in our Short-Term Cash account.

Let's take a look a solution to this problem and how to configure a relationship where different Dimension Members can access the same value.

Example

1. Remove the Department Dimension from our Model.

2. Create a Validation Model and link it to the Cash Flow Model. 

This Dimension is what's causing our data to be duplicated. However, we still need to use this Dimension so that we can Filter by Departments on the Form. This is possible through Linked Models.

The image above shows the structure of our Linked Models. Even though Department is no longer a Dimension in the Cash Flow Model, we can still access it through the Validation Model.

3. In the Layout tab of the Form, we can access these Linked Dimensions at the bottom of the Dimension List. Here we can add the Dimensions as if they're part of the Model, without them actually intersecting the data.

4. Go to Advanced > Relationship  and define a relationship between the Dimensions.

5. Apply the relationship to the Form's rows.

Go to the Main tab to verify the relationship has been properly established.

The key difference is that the values are no longer duplicated in the Model. Both Dimension Members are accessing the same value.