Add table association

1.1 Version suport

FineBI	Feature Update
5.1	-
5.1.13	The association line that failed to configure will be marked in red after updating, which is convenient for users to troubleshoot

1.2 Application

In some scenarios, it is necessary to extract multiple fields from multiple tables and merge them into one table for analysis. At this time, you can create associations between multiple tables, and add the fields of multiple tables to the self-service data set.

1.3 Function Introduction

FineBI can create and read the relationship between tables. There are two ways to obtain the association relationship:

• When adding a DB table to the business package, the system will automatically read the relations between tables in the database.

• Manually establish the relationship between tables in FineBI.

1.4 Direct Data Connection

• Direct data associated with Excel

• In the direct connection version, there are only three databases "Microsoft SQL Server 2016, Oracle, Pivotal Greenplum Database" that support the association of data tables with Excel.

• Before establishing the association, you need to change the field value of the SystemOptimizationConfig.excelExtractDataBase in theFINE_CONF_ENTITY table to true.

• In direct data connection, The two tables that are associated cannot come from different data connections. Otherwise, an error will be reported after the setting: [DIRECT-ETL] unsupported data source: databases on different hosts/ports

2.1 Supported scope

• Basic table: Users can set up associations of basic tables with administrative authority (basic tables include: Excel, DB tables, SQL data sets)

• Self-service data set: Only the self-service data set of processing nature can be associated, and the extraction setting of this self-service data set needs to be set to "Extract Data".

Note: The creator and administrator of the self-service data set can create associations for it. Others need to obtain the collaboration permission of the self-service data set before they can create associations.

2.2 Difference between association and left-and-right merge

3.1 Add a single table association

Log in to the decision system, go to Data Preparation> Business Package, and click on one table to see the associated view options of the single table. As shown below:

In the association view setting area, you can directly click the add association button to set associations for the table.

Enter the association setting interface, you need to select the associated table and associated field, and add the associated direction. The direction of association indicates which table is the primary table.

Associated field: the field that serves as the identifier.You need to choose carefully according to the actual situation. The association relationship corresponding to the actual data cannot be violated. For example, the actual main table will not become a sub-table only because it is manually set as a sub-table.

• Main table: A table that contains "associated fields", uses "associated fields" to associate with other tables, and "associated fields" are used as a unique identifier for each piece of data in the main table. Therefore, the "associated fields" contained in each record in the main table cannot be repeated.

• Sub table: also contains "associated field", but its "associated field" is not a unique identifier.

So the association direction 1:N means that the "association field" in the main table is unique, but the "association field" of the sub-table is not unique, and their relationship is 1:N.

Here we select ORDERID as the associated field for FRDemo_ORDERS; select FRDemo_ORDERSDETAIL as associated table, and ORDERID for the field; select direction of 1:N, which means FRDemo_ORDERS is the main table.

Note:

• The associated fields of the main table cannot have duplicate values, which means that field A of the same table cannot corresponds to multiple main table fields. That is, the ORDERID of the FRDemo_ORDERSDETAIL above can only be configured with one main table field, and no other fields of the N:1 relationship can be configured for this field, otherwise the subsequent configuration will overwrite the previously configured association.

• The following special characters cannot be included in the associated field: . []=()

• Pay attention to whether the case of the field value in the associated field can match, FineBI is case sensitive.

3.2 Add joint association

3.2.1 Application

A record cannot be determined by a primary key in the user's system.

For example, in FRDemo_ORDERSDETAIL, in general there is an ORDERIDas the primary key. But here in the detail, there may be multiple detailed records that use the same ORDERID. In this case, the ORDERID cannot be used alone, and there will be duplication. You can use another field as the joint primary key.

3.2.2 Add the association

Select corresponding associations and click "Edit".

In the association setting interface, click "Add Joint Association Field", and select the required field to add association, as shown in the figure below:

3.3 Create the self-service data set

When creating a self-service data set, you can select fields across tables for the associated data set.

Click Add self-service data set, select FRDemo_ORDERSDETAIL, and select all the fields in it, as shown in the figure below:

Switch to FRDemo_ORDERSand select CUSTOMERID and EMPID to merge the two tables together, and then save.

For the added association, click the edit button to modify, as shown in the figure below:

You can re-select the associated fields and the associated direction, as shown in the following figure:

For the unneeded association between tables, you can delete them by clicking the delete button, as shown in the following figure:

In addition to viewing the association in the association view area of the single table, you can also view it in the association view area under the data preparation node, as shown in the following figure: