Data Integrity Insurance of the Subject Model

In the Join operation, you manually select Left Join, Right Join, Full Join, and Inner Join to confirm which table to retain to retain complete data.

In SQL statements, we use Left Join, Right Join, Outer Join, and Inner Join to confirm which table to retain to retain complete data.

However, when you use the subject model, it seems that we do not need to perform the merge manually. Although you can avoid difficulties in choosing, you may wonder whether the data is still complete. Is the data you want to retain still there?

Don't worry, the topic model has a smarter processing method, and you do not need to manually confirm the Join operation. This article will introduce the method to you.

Function Description

There are two principles for the subject model to process the data integrity:

If the component utilizes indicator fields, the complete table where the indicator is located should be retained.

If only dimension fields are utilized in the component, the tables where the dimension fields are located take the intersection.

You can recall the way Join processes the data integrity.

Both tables have data the other one does not have, and the retained data by performing Left Join, Right Join, Full Join, and Inner Join are different.

When you perform Join, you obtain a fixed data table.

In the subject model, you may want the data retention to be dynamic. You can determine the Join method according to the fields dragged in the component.

You can download the sample data table: Data Integrity.xlsx.

Create a new subject, upload sample data, and establish a model relationship between Sales Amount Table and Regional Manager Table.

Principle one: The dragged-in indicator data must be complete.

If you drag an indicator field into the component, the table where the indicator field is located must be retained completely. This processing method ensures data completion.

1. The component utilizes the indicator field Sales Amount from Sales Amount Table, so all fields from Sales Amount Table in the component are retained completely.

2. The indicator fields of Sales Amount Table and Regional Manager Table are utilized in the component, so the fields of these two tables are retained completely in the component.

Principle 2: If only dimension fields are involved, take the intersection.

If there are only dimension fields in the component, the tables where the dimension fields are located take the intersection.

For example, only the dimension fields of Sales Amount Table and Regional Manager Table are utilized in the component, so the intersection between these two tables is taken.

When you perform the Join operation, you need to manually select Left Join, Right Join, or Inner Join.

However, if you do not have enough understanding of the data table, you may encounter calculation problems.

For example, when you use the following two tables, you may encounter the scenario:

Hangzhou does not exist in Regional Manager Table because Hangzhou is the headquarters without any regional manager set.

Suzhou does not exist in Sales Amount Table because Suzhou is a newly established region, with a designated manager but no sales amount data yet.

Without knowing the specific situation of the table, you may perform the most commonly used operation Left Join. When you use the merged data table to calculate the number of employees, the data is missing, resulting in incorrect total values.

Left Join, Right Join, and Inner Join all result in varying degrees of data loss.

However, you can avoid the above problems with the subject model.

When you want to calculate Sales Amount, the Sales Amount field must be complete, and when you want to calculate Number of Employees, the Number of Employees field must be complete.

There is no error in the total value.