Build a Model - Quick BI - Alibaba Cloud Documentation Center

A JOIN operation is used to join different tables. If the data that you want to analyze is stored in different tables, you can join the tables to build a model for data analysis.

Prerequisites

A dataset is created. For more information, see Create and manage datasets.

Background information

Quick BI supports the Left Outer Join, Inner Join, and Full Outer Join operations. The following figure shows how to perform the preceding JOIN operations to join a student table and a student score table. 连接方式

Note

The preceding figure shows only the principles of the JOIN operations. When you join tables, the original columns of the tables that you join are retained.

Limits

You can join tables from different data sources only in Quick BI Enterprise Standard.
After Alibaba Cloud Data Lake Analytics (DLA) is configured in Quick BI Enterprise Standard, you can use Quick BI Enterprise Standard to join tables from the data sources MaxCompute, MySQL, and Oracle.
MySQL data sources do not support the Full Outer Join operation.

Procedure

Select a data source
Select or create an associated table.
You can select Data Table Association Data Table, Data Table Association SQL Code Creation Table, SQL Code Creation Table Association Data Table, and SQL Code Creation Table Association SQL Code Creation Table.
Note
For more information about how to edit the SQL code, see Custom SQL.
1. Data table association data table
  On the dataset edit page, perform the steps that are shown in the following figure to drag tables to the canvas.
2. Table association SQL code creation table
  1. Double-click or drag a table to the canvas.
  2. Double-click or drag SQL code to create a table to the canvas.
  3. On the SQL code editing page, enter an SQL statement, click Run, and then Confirm to edit the table.
3. SQL code to create a table associated with a data table
  1. Click Use SQL Code to Create Dataset. In the Create Dataset dialog box, enter the SQL statement and click Run and Confirm to edit the table.
  2. Double-click or drag a table to the canvas.
4. SQL Code Creating a Table Associating SQL Code Creating a Table
  1. Click Use SQL Code to Create Dataset. In the Create Dataset dialog box, enter the SQL statement and click Run and Confirm to edit the table.
  2. Double-click or drag SQL code to create a table to the canvas.
  3. On the SQL code editing page, enter an SQL statement and click Run and Confirm to edit the table.
In the Add Association Relationship panel, perform the steps that are shown in the following figure to join tables.
Note
- The following types of JOIN operations are supported: Left Outer Join (), Inner Join (), and Full Outer Join ().
- MySQL does not support Full Outer Join ().
- When you configure join types, make sure that the fields you want to associate exist in the tables you want to join.

After the configuration is complete, you can click the join icon, such as 关联图标 , on the canvas to view the join result.

If you no longer need a table in the join, move the pointer over the table and click the Delete icon ().

Note

You can delete the table of the lowest level from a model.

Configure a custom join condition

When you configure a custom join condition, you can create a custom formula as a field that you want to associate with the custom join condition.

In the Add Association Relationship panel, click Create Custom Formula from the drop-down list in which you select the associated field.
In the Edit Formula dialog box, enter a field expression in the Field Expression section and click OK.
In the Edit Formula dialog box, click OK. You can view the new field expression in the Associate Data section of the Add Association Relationship panel.
You can also modify the custom formula that you created.

Configure filters before you join tables

You can separately configure filters for the two tables that you want to join. The filter operation is performed before the tables are joined.

Click the icon on the right of the table in which the field that you want to associate with the custom join condition exists.
In the Set Filter Fields dialog box, configure filter conditions and click OK.
In the Add Association Relationship panel, click OK.