Data Relationships: Many-to-Many Relationship Resolutions

[intapiuser]

For general information on types of data relationships, potential complications and how to resolve them, see the following articles: 

• Data Relationships: Introduction to Many-to-Many 
• Data Relationships: Check Type of relationship
• Data Relationships: One-to-Many Relationship 
• Data Relationships: The Invisible Many to Many
• Data Relationships: Many-to-Many Relationship Resolutions (You are here)

There are several methods to resolve and bypass a many-to-many relationship; the solution depends on the business model and the logic of the business questions at hand. The following solutions differ by business logic and the schema at hand, each solution can be applied to each schema respectively.

Resolutions for 2 tables, one relationship

• Two Tables, One Relationship
• Aggregated Table

Possible resolutions for more than 2 tables, more than 1 relationship

• Lookup Function
• Concatenate the two tables into one

Resolutions for 2 tables, one relationship 

Two Tables, One Relationship

The direct solution for Many-to-Many would be to break this relationship into two separate one-to-many relationships, as seen the image below. The logic behind testing this issue can be visualized in the decision tree below. Follow the steps below to resolve the Many-to-Many this way: 

1. Create a custom dimension by using a custom SQL expression in the Elasticube. In the expression of this table select all the individual values for the identifier column from both sides, the expression should look like this:

SELECT * FROM 
(SELECT DISTINCT r.GuestID, r.GuestName FROM [Reservations] r 
UNION 
SELECT DISTINCT p.GuestID, p.GuestName FROM [Payments] p)
AS G

This query will take all Guest Id values from both tables, and using the UNION statement, will bring in only the unique values from both tables, making this a complete list of all distinct Guest Id values.

1. Merge the Guest Id field from the new 'linking' table to the other two Guest Id fields from the other two tables, thus creating two One-To-Many Relationship.
2. You can now use this Guest Id field as the rows or axes elements of a widget, pulling the unique values from the new Guest Dimension, with measures from the two other tables.

 Image 1. Custom Common Dimension

Aggregated Table

In situations where we have more than one fact table (A Fact table is a primary table containing the measures or fields used for calculations in the dashboard) in our Elasticube, there are several situations when an aggregated table can resolve a many-to-many relationship.

 Image 2. Incorrect Connections

Assuming we'd like to segment our data according to a few different dimensions, creating relationships directly between these fields can and will create many-to-many relationships in one of two ways, according to the schema:

1. Both tables don't hold unique values, and all values from one table are held in the second table. In this scenario either a linked dimension (as described in solution 1) or an aggregated table can be created which will hold all the unique values and the desired calculations for one of the tables. In order to create an aggregate table, one can create a custom SQL expression and aggregate values from the table which holds all values; its own, and the subset present in the other table with the following expression:

SELECT i.OrderDateKey, i.ProductKey, sum(i.DiscountAmount), sum(i.SalesAmount),
avg(i.UnitPriceDiscountPct) 
FROM [FactInternetSales] i 
GROUP BY i.OrderDateKey, i.ProductKey

This custom SQL expression will select the distinct OrderDateKey and their corresponding ProductKey from the FactInternetSales , grouped by these fields, together with single value aggregations for the different fields : Discount Amount, Sales Amount and the average unit Price discount.

Now merge the OrderDateKey and Product Key between this table and the two other tables and you will be able to pull the values from this new table into the rows or axes panel of a widget in Sisense Web with measures and additional aggregations.

1. Both fact tables don't hold unique values, and there are different values for several fields in both the tables. Resolving this scenario would incorporate both solutions from sections 2.1 and 1. In this scenario one should create an aggregated table as stated in 2.1, and a dimension table as stated in 1. The final resolution should look like this:

Image 3. New Schema with dim & aggregated table

Resolutions for more than two tables, more than one relationship 

Using the Lookup function

In most scenarios we'll aggregate values according to a given id, from the unique side of the relationship to the duplicate side. However in specific cases it'll be vice versa.

For example in the following scenario, in which we have 3 tables, and between them two one-to-many relationships, this can potentially create a many-to-many relationship, if we were to query the two leaf tables. This means that the query result table will have multiple rows which won't be distinguishable one from another.

Image 4: Two consecutive M-to-M relationships

Using the Lookup Function, we can import values from a remote table by matching values in a field. This will create a new field in the table with the matching value of the identifying field from the other table.

Taking the following example of tables T1, T2 and T3, we'd like to run a query which will display aggregations from the duplicate id's from T1, with a measure from T3. If we ran the query as is, we'd get multiple values for the query's result set, and we won't be able to run this aggregation.

In order to resolve this, we'll use the Lookup function in order to import the values from T3 into T2 and then re-run the query only on tables T1 and T2. We can import the values of 'M3' from the 'T3' table into the 'T2' table. Create a new custom column, and use the Lookup function to import the values of attribute, In this case, the Lookup function should look like this:

Lookup([T3],[T3].[M3], [T2].id2,[T3].id2)

Running this statement in table T2 will import the matching values of T3.M3 from T3 according to the matching results in id2 between the two tables.

Image 5: Two consecutive Many to One relationships after Lookup fix

Concatenate the two tables into one

Assuming we have 2 separate tables with duplicate id values in each, and each holding different columns for each id, we can create a custom table which will hold all values for every id, and pull the aggregations from this new table.

Notice that the two original tables; Table_1, Table_2 have different columns.

 Image 6: Concatenating tables 

Using the following SQL statement, we can import the data from both tables, with the id's and the columns respectively:

SELECT s.id AS id, s.m1, s.m2, ToInt(NULL) m3 , ToInt(NULL) m4
FROM [Table 1] s
UNION
SELECT t.id, ToInt(NULL) , ToInt(NULL) , t.m3, t.m4
FROM [Table 2] t

This will create a table with 5 columns:

Id, M1 (from table_1), M2 (from table_1), M3 (from table_2), M4 (from table_2)

The values missing from each table respectively will be NULL's which will result in the following table:

Image 7: Concatenated table; result set

Image 8: Determining a Many-to-Many relationship; decision tree. This is based on the first example with the Payments and Reservations tables.