Association Setting- FineBI Document

Last update： 2024-03-14

Overview

Version

FineBI Version	Functional Change
6.0	-

Application Scenario

The function is mainly used for permission control. For details, see Quadruple Table Model.

Function Description

There are two methods to obtain the association in FineBI.

Method one: When you add a database table to the public data, the system will automatically read the table associations in the database.
Method two: You can manually add associations among tables in FineBI. Only users with management permissions for the dataset in Public Data can create associations. For details, see Public Data Management Permission.

Note:

Self-service datasets with permission inheritance enabled cannot be associated.

Notes for direct-connected data tables:

The two associated tables cannot come from two different data sources. Only extracted data tables can come from two different data sources.
Only tables in some databases can be associated with Excel tables. For details, see Direct Connect Databases Supporting Integrated Analysis with Excel.

Procedures for Adding Associations

The Contract Demo table records the ID of each contract client but does not have specific information about the clients, which is recorded in another table Contract Fact Table. In this case, you need to add an association between these two tables so that you can view the corresponding client information when querying the contract information.

Adding Associations

1. Select the Contract Demo table and add an association to it, as shown in the following figure.

In this example, all associations of the original table are removed for better explanation.

2. Go to the association setting page.

A: Select the table that needs to be associated with the Contract Demo table.
B: The associated field is the link between two tables. Since the field Contract ID is included in both tables, it is the associated field.
C: It refers to the association relationship between two tables. You need to select the relationship based on the actual situation. That is, the association relationship cannot violate the corresponding association of the actual data.

Association Relationship	Example
1:1 (one-to-one)	You can understand it as the relationship between a citizen and their ID number. One person only has one ID number.
1:N (one-to-many)	You can understand it as the relationship between states and cities. One state has N cities.
N:1 (many-to-one)	Multiple cities belong to one state.

Note:

The associated field in the 1-side table cannot have duplicate values, so "1" of the 1-side also indicates that each field value in the associated field is unique.

3. Set the association based on the relationship between the two tables, and click Confirm and Update, as shown in the following figure.

In the Contract Fact Table, each client corresponds to only one piece of client information, so the table is on the 1 side.

In the Contract Demo table, a client may have multiple contracts, so the table is on the N-side.

Notes for associated fields:

Duplicate values cannot be found in the associated field of the 1-side table.
The following special characters cannot be included in the associated field: . [ ] = ( ).
Note whether the case of the values of the associated fields can match. FineBI can distinguish the case of field values.

Adding Joint Associations

Sometimes you may fail to determine a record with a single associated field in the 1-side table.

For example, there is a table that records the prices of each type of mobile phone. However, only the brand or the model cannot be used as an identifier for a mobile phone.

In this case, the combination of the brand and the model can serve as an identifier for a mobile phone. The combination of two fields is unique.

When using the above table as the 1-side table, you can add the brand and model fields together as the associated fields.