Relationship Management

Overview

What is Relationship

"Relationship" is a flexible connection line created between logical tables in the data source: it describes how two tables are associated with each other based on common fields, but does not merge the tables. When analyzing metrics from a table, the "relationship" is used to expand the dimensions of the analysis. After creating a relationship between two tables, the tables remain independent and do not form a logical or physical table, and no data association calculations are performed.

• You need to select the matching fields between the two tables (usually one side is the primary key of one table, which represents that the field's value satisfies the conditions of uniqueness and non-nullability; the matching field of the other side is the referencing side).
• You do not need to select "left join", "right join" or other connection types when creating, because in metric analysis, the type will be automatically determined based on the table where the metric or dimension is located.

Use cases

After establishing relationships between multiple tables, users can analyze dimensions and metrics from multiple tables together in metric analysis.

For example, in the analysis of event data, we can introduce dimensions from the Product, Seller, and User tables through three relationships:

1. Analyze order events based on "seller's country".
2. Analyze order events based on the price of the product.
3. Analyze events based on the country of the user.

Introduction

Users can establish relationships using two types of tables

• EUI model tables pre-set by Sensors Analytics
• Database tables imported by users

Note: For imported user data tables, you need toactivate the data warehouse application, , manage the table application, and then use the associated relationship.

Requirements for the associated relationship.

• In establishing the "Relationship", the fields defining the relationship must have the same data type (currently supported: text, numeric).
• Supports the association between two individual fields of a form, but currently does not support association after combining multiple fields.
• In EUI, the relationship between Events and Users does not require user operation and is built-in.
• The fields defining the relationship must have one side as the "primary key", and the primary key can be defined in "Table Application Management".

How to add table associations

This example establishes an association between "Events" and "Seller Table".

Add association

1) Go to the Metrics Platform - Relationship Management page and click "Create Relationship"

2) Select "Seller Table" as the primary key table from the dropdown

3) In the reference table dropdown, select "Events Table", then select the reference field "SellerID". This reference field indicates that the SellerID value in the Events table comes from the SellerID field in the "Seller Table", and based on the "SellerID", it can find a unique record in the "Seller Table". (Association fields should be selected carefully based on the actual data correspondence and should not violate the actual data correlation)

Note: The "associated field" in the 1-side table cannot have duplicate values, so the "1" on the 1 side also represents that each field value in the associated field is unique.

Requirements for Associated Fields

• The 1-side represents that the associated field is the primary key of the table, satisfying the constraints of being unique and not null

4) After selecting the associated field, close the settings drawer. Name and describe the relationship, then submit in the upper right corner to complete the creation

Cross-table Use of Metric Dimensions

The purpose of establishing a relationship is to analyze the dimensions and metrics of two related tables together.

So after establishing the relationship, you can use metric dimensions across tables in metric analysis: The "Seller Country" dimension comes from the "Seller Table" and the "Seller Count" metric, which has occurred in the order events, comes from the "Event Table". Here, you can analyze the "Seller Count" metric from the event table based on the registered country of the sellers.

Relationship Management

Once a relationship is created, it is in "online status". Administrators can perform operations such as offline, delete, online, and name editing on the relationship.

Search

Users can search for relationships by relationship name, table name used in the relationship, and field name.

Modify the name and description of the relationship

Click the "Edit" button to modify the name and description of the relationship that has already been added.

Offline a Relationship

If the underlying physical table on which the relationship depends is deleted, the associated fields are deleted, or the table or field names are changed, the relationship will become "offline".

In addition, administrators can manually mark a relationship that is no longer needed as "offline", which means it cannot be used in custom business analysis.

An offline relationship can be deleted, and once deleted, it cannot be recovered.

Permissions

By default, administrators have the permission to manage relationships, while other roles only have the permission to view.

Common FAQs about Relationships

1) Can more than one relationship be created between two tables?

Yes, for example, in an e-commerce order, there may be two different user IDs: "Placing User ID" and "Promoting User ID"; both can be associated with the primary key of the user table.

2) Does it support composite primary keys?

Currently not supported.

3) Which types of fields cannot be used to establish relationships?

Fields other than text and numbers cannot establish associations.

4) Can I create many-to-many associations?

Many-to-many associations can lead to dimension inflation and confusion in business meaning, so they are currently not supported.

5) How can I modify associations?

You can create a new association, which will not affect the analysis that has already been used. After confirming that the old association is not being used, you can delete it.