A relationship between tables specifies the field/s which the 2 tables have in common.
In Sisense, we create relationships between tables in the ElastiCube. Poorly modeled data relationships can lead to one or more of the following:
Complex data sets that return incorrect results
Use excessive computing resources
Return no results at all.
Hence it is highly important to ensure that the relationships between tables are properly modeled in the ElastiCube.
Types of Data Relationships
One-to-One relationship – The field used to connect both tables only has unique values in every row.
One-to-Many relationship – One table holds unique values for every row, but the other table holds duplicate values for any or all of the corresponding values in the first table.
Many-to-Many relationship – There are duplicated values in both sides of the table.
Business Case Example
A Many-to-Many relationship exists when the value in the field the relationship is based on, is contained multiple times in each table.
For example, a Company has 2 tables: Sales and Purchases. In both tables we have the date on which the transaction has been performed.
If a relationship between the Sales and Purchases were created based on the date, a many-to-many relationship would have been created. Since a date can appear multiple times because there were several transactions performed every day.
To sum up, when a field from two or more tables contains the same value, and these values are duplicated in both tables a connection created based on this field will result in a many-to-many relationship.
Test and Resolve Data Relationship Issues
There are several methods to identify and resolve/bypass a many-to-many relationship. The solution depends on the business model and logic of the business question at hand. Here are the resources that can help you test, identify and properly model data relationships in your ElastiCube.