Configure Many-To-Many Relationships

Author: Ian Britz

Introduction

A Many-To-Many Relationship is one where a Member from one Dimension can be related to any number of members from another Dimension, and vice versa. This type of relationship is particularly important to understand when it's necessary for 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 only access 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. Then when Department A accessed 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, it would appear to tell us that we had $2,000(!) on-hand in our Short-Term Cash account.

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

Example

Step 1. First, we need to remove the Department Dimension from our 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 the inherent nature of 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.

Step 2. 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.

Step 3. Now we can define a relationship between the Dimensions through the usual method and apply it to the rows on the Form.

Step 4. In the Form, we can confirm that we've recreated the original functionality.

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