Creating a Model by Associating Multiple Tables

Association Relationship

• A LEFT JOIN associates two tables by using the left table as the basis and applying specific conditions. The result includes all records from the left table, and the right table's records that match with the left table.
• An INNER JOIN associates two tables based on specific conditions. The result includes records that match both the two tables.
• A RIGHT JOIN associates two tables by using the right table as the basis and applying specific conditions. The result includes all records from the right table, and the left table's records that match with the right table.
• A FULL JOIN associates two tables based on specific conditions and removes duplicate records.

Prerequisites

• You have subscribed to DataArts Insight.
• A project has been created by referring to Creating a Project.

• A data source has been connected by referring to Connecting to a Data Source.

Procedure

1. Click in the upper left corner to select a region.
2. In the lower left corner of the navigation pane, select an enterprise project from Enterprise Project.
3. In the project list on the console, click a project to go to the project page.
4. In the navigation pane on the left, choose Data Management > Datasets. On the displayed page, click Create Dataset in the upper right corner.
5. Choose the desired data source from My Data Sources.
6. Select a data table. Select a database and schema.
7. Select or create a table to associate with.

   You can associate a data table with another data table, and associate a data table with a table generated through SQL code or vice versa.

   Note

   For details about how to edit SQL code, see Creating a Dataset Using Custom SQL Statements. Only one SQL data table can be created in a dataset.

   1. To associate a data table with another data table, drag the table to associate from the list and drop it into the position indicated by the arrow in the figure.
   2. To associate a data table with a table generated through SQL code, do as follows:
      1. Drag a table and click Edit SQL Statement. On the SQL editing page that is displayed, edit SQL statements.
      2. Execute the SQL statements, and once they have been successfully executed, click Finish and Exit.
      3. On the dataset management page is displayed, check that the tables are associated.

         Figure 1 Creating an SQL table

         
         

         Figure 2 Associating a data table with a table generated through SQL code

         
         
   3. To associate a table generated through SQL code with a data table, do as follows:
      1. Click Edit SQL Statement and edit the SQL statements.
      2. Execute the SQL statements, and once they have been successfully executed, click Finish and Exit.
      3. On the dataset management page, drag a data table to associate them.

         Figure 3 Creating an SQL table

         
         
8. Edit the table association relationship.
   1. If the data model to be analyzed is complex and multiple tables need to be associated, use an association relationship to select the endpoint table.

      Figure 4 Selecting an endpoint table

      
      
   2. Select table fields as needed.
   3. Select an association relationship.
      • A LEFT JOIN associates two tables by using the left table as the basis and applying specific conditions. The result includes all records from the left table, and the right table's records that match with the left table.
      • An INNER JOIN associates two tables based on specific conditions. The result includes records that match both the two tables.
      • A RIGHT JOIN associates two tables by using the right table as the basis and applying specific conditions. The result includes all records from the right table, and the left table's records that match with the right table.
      • A FULL JOIN associates two tables based on specific conditions and removes duplicate records.
   4. Configure associated fields.

      When configuring data association, make sure the associated fields are those selected from the associated data table.

9. Click OK to finish the data association.
10. Click Refresh Preview to view the associated data.