31st January 2019Many-to-many “m2m” relations are defined by a relation where one object is related to multiple other ones and vice versa, e.g. a bank account can be shared by multiple people and one person can be the owner of multiple accounts. This article will show how to create these particular aggregations.
On the example we will use for this article, we have 4 bank customers: Phil, Kate, Josh and Samantha; where Kate & Josh as well as Kate & Samantha have joint accounts, respectively named “Account – KJ” and “Account – KS”.
The difficulty of managing these relations when data modelling lies on the fact that you want to avoid counting several times the same amount.
On the left, you have the balances per account and on the right, the assets by each customer. If you sum the amounts of the customer’s assets you’ll end up with a total of 800, that is more than the sum of all bank accounts (600). Why? Kate and Josh’s joint account has a total balance of 100, Kate’s balance in this account is 100 so is Josh’s balance, but when aggregating both the total is not 200.
The figure below represents the same data as above, showing in one table only, both balances per account and per customer.
This ‘strange’ visual effect in the pivot table is due to the m2m relations as parent members (Accounts’ total balance) are possibly not the aggregation of their children (Customers) anymore. Note that in fact, Kate & Josh’s joint account has a total balance of 100.
Be most careful when manipulating m2m relations as not all analytical and reporting tools offer this functionality out-of-the-box and you can end up with miscalculated results.
MANY-TO-MANY RELATIONS IN ICCUBE
There are two ways setting up (out-of-the-box!) m2m relations in icCube. The first by creating two separate dimensions and using a bridge table, and the second by creating one dimension with two hierarchies.
The difference between the two methods is that the first will show all possible combinations even if they don’t exist by construction and the second method removes non-existent combinations.
Let’s explore how to construct the data model using both these methods: Many-to-Many relations using a Bridge table and Many-to-Many dimensions.
For both methods, the data is set as follows:
- Customers’ table:
- Balance table:
| AccountName , Customers |
|---|
| Account – P , Phil |
| Account – K , Kate |
| Account – J , Josh |
| Account – S , Samantha |
| Account – KJ , Kate |
| Account – KJ , Josh |
| Account – KS , Kate |
| Account – KS , Samantha |
| AccountName , Amount |
|---|
| Account – P , 100 |
| Account – K , 100 |
| Account – J , 100 |
| Account – S , 100 |
| Account – KJ , 100 |
| Account – KJ , 100 |
| Account – KS , 100 |
| Account – KS , 100 |
FIRST METHOD: MANY-TO-MANY USING A BRIDGE TABLE
The bridge table method is the usual m2m method. Construct both your dimensions separately: a multi-level dimension for Customers and another for AccountName.
Now on the Facts table, for linking Customers to its corresponding data, create a Bridge table as follows:
By doing so, you are creating a link between your two tables through the AccountName. Therefore, the customers are now associated to their corresponding account(s).
The bridge schema is available for download here. Unzip and then import into the icCube builder.
SECOND METHOD: MANY-TO-MANY DIMENSIONS
You may create m2m relations by defining several hierarchies in a same dimension. This will result in showing only combinations {Customer, Account} that exist on your table. This behavior is called autoexist, it performs a NON EMPTY to remove empty tuples, and is in fact faster than a NON EMPTY.
The data used is exactly the same as the method above. Create the dimension BankAccount from your Customers’ table containing two hierarchies: AccountName and Customers.
No need to create a bridge on your facts table. Find below a screenshot of the BankAccount dimension browsing, as well as the facts table for this second method.
The m2m dimension schema is available for download here. Unzip and then import into the icCube builder.
Find more information on Many-to-Many relations in our Documentation: Many-to-Many using a Bridge Table and Many-to-Many dimensions.
By Nathalie Leroy Tapia Heredia