A relationship between data sources specifies the logic used to combine data from one or more data sources. In Sisense, we create relationships between data sources in the Elasticube. How we connect the data sources determines what data is reflected on the dashboard level. Poorly modeled data relationship can lead to complex data sets that return incorrect results or use excessive computing resources and return no results at all. Hence it is foremost important to ensure that the core relationship between data sources is properly modeled in the Elasticube.
Types of Data Relationships
One-to-One relationship – The simplest relationship scenario, where the field used to connect both tables only has unique values in every row.
One-to-Many relationship – In this scenario 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 in this scenario, causing excessive calculations for every query run against it.
Business Case Example
To put it in the simplest way, Many-to-Many relationships exist when the value in each field used to create a relationship between tables is contained multiple times in each table. For example, a hotel may have a table with reservation data and a table with payments data. In both tables the name of the guest is stored. A guest can have multiple reservations under their name as well as multiple payments for their stay recorded in their name. If a relationship between the reservation and payments table was created based on the guests name a many-to-many relationship would be created, as the guests name appears multiple times in each table. In general 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 or 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 relationship in your Elasticube.