Multipath data association

Many redundant multipaths are caused by users adding tables and establishing non-standard associations between tables.

If the original database has a complete primary and foreign key relationship, when the data is directly read into FineBI, its data relationship structure can be maintained standard.

The following operations are mainly for if the database does not add a complete primary and foreign key relationship, just read the original data directly and write only the associated fields of the corresponding table. Or because BI supports loading data from multiple data sources, when there is no primary and foreign key relationship between the timetables read from different databases or the database table and the uploaded EXCEL file, we provide standardized operations for FineBI to read the data and establishing the association relationship in the multidimensional database can be more standardized.

In response to the above-mentioned irregular data association, we provide an operation method to standardize the data association established in FineBI.

In the relationship between the data tables in the business package, a method is provided to identify the primary key, and the foreign key can be directly reflected by establishing the relationship between the tables through the fields. As shown below:

When establishing an association relationship, you can set the association relationship between tables one-to-one or one-to-many, that is, during the association operation, you can choose whether one side is "1" or "N" to identify the direction of association ("1" is the main table, and "N" is the sub-table). This ensures that the established association relationship is consistent with the user's actual business logic and database storage logic. For the setting of the association relationship between tables, please refer to the association view (single table).

Note: If the information marked by the user does not match the actual data, such as non-distinct fields set as the primary key and other information, it will be automatically corrected when the table is updated, but some related settings will not take effect.

At present, there are two tables of customer table and contract table in a company's business, and these two tables need to establish an association relationship. Read the database data directly and establish the association, without distinguishing the relationship between the main and sub-tables and establishing the primary key. However, because of the incomplete storage data, directly judging the data in the associated fields, it is found that the data in the customer table and the contract table are one-to-one matching, that is, one customer corresponds to one contract, which creates a lot of redundant multi-path situations, and these multipaths are wrong and do not exist in business logic.

But from the company's real business logic, the customer table is the main table, and the contract table is the sub-table, that is, the same customer is allowed to be associated with multiple contracts, and vice versa. We need to follow this actual logic to the table setting interface of the business package for operation settings.

For the operation of creating an association relationship between tables, for details, see: Association View.

3.1 Read primary and foreign keys

Directly read the primary and foreign key information of the original database table, or whether the field is set with a unique constraint. If it has been added to the database, it can be directly read into the timetable of the business package. If there is a foreign key, and the corresponding table is also in the business package, you can directly establish the data association relationship.

3.2 Set primary key

If the added data table does not have information such as primary and foreign keys, it can be added to the data table setting page, which can be manually added by the user.

3.3 Relationship matching and association direction

Add data association information interface to the data table, you can set the data relationship matching to be one-to-one or one-to-many, and if it is one-to-one, you can also choose the association direction, that is, the data in table A and table B are one-to-one. The direction can be set to A->B according to the actual business logic, instead of using the B->A association relationship, that is, using A to associate B with a 1:N selection.