Quick BI:Joins and Merges

Quick BI supports single-table and multi-table models and lets you join or merge multiple tables. This topic describes how to build a physical model.

Limitations

• Joining and merging tables from different data sources is available only in the Professional Edition.

• Cross-source joins and merges require enabling extraction acceleration with the Quick Engine. For a list of data sources that support this feature, see Data source feature list.

• MySQL data sources do not support full joins.

• A model supports up to five levels of joins (five join nodes). There is no limit on the number of joins at each level.

• You can merge a maximum of five tables.

• For API data sources, joins and merges are supported only for data sources in extraction mode. The direct connection mode supports only single-table modeling.

Create a physical model

If you decide to use a physical model, do not build the model directly on the relational model canvas. After you drag the first table onto the canvas, click the icon next to the logical table and select Enter Physical Canvas, or double-click the logical table to enter the physical model canvas. Then, you can configure multi-table joins and merges.

1. Click the icon next to the logical table and select Enter Physical Canvas, or double-click the logical table to enter the physical model canvas.

   

2. The data source of the current logical table is selected by default, but you can change it manually.

3. In the data table list on the left, you can hover over a target data table and click the icon to view the table details.

   

   Hover over a target data table and click the icon to copy the table name.

4. Drag a second data table to the canvas to join or merge it. You can also create a table using custom SQL.

5. You can view the Table Details.

   

6. On the Table Details page, you can select the fields that you want to include.

   

7. If you no longer need the data table, you can Delete it.

   

Join tables

Procedure

1. Enter the physical model canvas.

2. Select or create the tables to join.

   You can join a data table with another data table, a data table with a table created from SQL, or two tables created from SQL.

   Note

   For more information about editing SQL statements, see Use custom SQL.

   1. Join two data tables

      On the dataset edit page, drag the data tables to the canvas as shown in the following figure.

      

   2. Join a data table with a table created from SQL

      

      1. Double-click or drag a data table to the canvas.

      2. Double-click or drag Create Table with SQL to the canvas.

      3. In the SQL editor, enter the SQL statement, click Run, and then click Confirm Edit to create the table.

   3. Join a table created from SQL with a data table

      

      1. Click Create Dataset with SQL, enter the SQL statement, click Run, and then click Confirm Edit to create the table.

      2. Double-click or drag a data table to the canvas.

   4. Join two tables created from SQL

      

      1. Click Create Dataset with SQL, enter the SQL statement, click Run, and then click Confirm Edit to create the table.

      2. Double-click or drag Create Table with SQL to the canvas.

      3. In the SQL editor, enter the SQL statement, click Run, and then click Confirm Edit to create the table.

3. Select the fields you want to include based on your business needs.

   

4. In the New Join panel, configure the data join.

   

   1. Select a join type: left join, right join, inner join, or full join.

      Note

      MySQL data sources do not support full joins.

   2. Set the join keys.

      1. The join operator is = by default. You can also set it to ≠, >, >=, <, or <=.

         

      2. You can click Add Join Key to add multiple join keys.

         

      3. You can Delete a join key.

         

      4. You can Clear All Join Keys.

         

5. Click Complete and Go to Data Processing to go to the data processing page. On the data processing page, you can click Model Configuration in the left-side navigation pane or click Source Table -> Model Details to return to the Model Configuration page. For more information about operations on the data processing page, see Create a dataset. You can click the join icon on the canvas to view the join relationship. If a node is no longer needed, click the icon on the right side of the node and select Delete.

   Note

   You can delete only the last node in the model.

Customize join conditions

When you configure join conditions, you can create a calculated field to use as a join key.

1. When selecting a join key, choose Create custom calculation from the drop-down list.

2. Enter the field expression and click OK.

3. After you click OK, the new field expression appears as a join key.

4. You can also edit the custom calculation.

Pre-join filtering

When creating a join, you can apply separate filter conditions to each table. The filtering is performed before the join operation.

1. Click the icon to the right of the table you want to filter.

2. Set the filter conditions.

3. Click OK to finish setting the pre-join filter conditions.

Cross-source join

If your required data tables are in different data sources, you can use a cross-source join to connect them into a single model for analysis.

1. On the dataset edit page, find the data table in the target database from the data table list and drag it to the canvas.

2. Switch the data table list to another target database, find the required data table, and drag it to the canvas.

3. Configure the join relationship between the data tables. For detailed instructions, see Procedure in this topic. After you finish, save the dataset.

4. Click Advanced settings in the top toolbar.

   

5. Click Acceleration settings and enable Quick Engine.

   

6. After you enable the Quick Engine, you must configure its parameters. For configuration details, see Use extraction acceleration.

7. Click Save to complete the extraction acceleration configuration.

Merge tables

Merging combines information from different data sources or data tables into a single dataset, which makes analysis, processing, and reporting easier.

Procedure

1. Enter the physical model canvas and perform the merge operation.

   

   If a logical table contains only a single table, you can also merge tables directly on the relational model canvas.

   

2. Select or create the tables to merge.

   You can merge a data table with another data table, a data table with a table created from SQL, or two tables created from SQL.

   Note

   • For more information about editing SQL statements, see Use custom SQL.

   • You can merge a maximum of five tables.

   The following example shows how to merge a data table with a table created from SQL:

   

   1. Double-click or drag a data table to the canvas.

   2. Drag Create Table with SQL below the table you want to merge it with.

   3. In the SQL editor, enter the SQL statement, click Run, and then click Confirm Edit to create the table.

3. Configure the table merge.

   

   1. Fields with the same name and data type are automatically matched.

   2. You can click a field box and select a different field from the drop-down list to manually adjust the matching.

      Note

      The drop-down list shows only fields of the same data type.

      

   3. Select the fields you want to include based on your business needs.

      

4. After you configure the table merge, click Complete and Go to Data Processing to finish the configuration and go to the data processing page.

   

   For more information about operations on the data processing page, see Create a dataset.

Cross-source merge

When the data tables you need are stored in different data sources, you can use a cross-source merge to combine them into a single model for analysis. The following steps describe how to perform a cross-source merge:

1. On the dataset edit page, find the data table in the target database from the data table list and drag it to the canvas.

2. Switch the data table list to another target database, find the required data table, and drag it to the canvas.

   

3. Configure the merge relationship between the data tables as needed. For detailed instructions, see Configure the table merge in this topic. When finished, click Complete and Go to Data Processing to go to the data processing page, and then Save the dataset.

4. Click Advanced settings in the top toolbar.

   

5. Click Acceleration settings and enable Quick Engine.

   

6. After you enable the Quick Engine, you must configure its parameters. For configuration details, see Use extraction acceleration.

7. Click Save to complete the extraction acceleration configuration.

Switch data tables

If you need to use a different data table, you can switch it on the Table Details page. You can switch to a table from the same data source or from a different one.

Usage notes

• You cannot switch data tables for the following data source types:

  • Data sources created with custom SQL

  • File data sources in an exploration space

  • API data sources

  • DingTalk Sheets data sources

  • Lark Sheets data sources

  • Lark Bitable data sources

• When you switch a data table, fields with the same name are automatically matched. Ensure that the fields in the target table are consistent with those in the original table.

• If the data source type of the target table is different from that of the original table, check your calculated fields after switching. If extraction acceleration is configured, you must reset it.

Procedure

1. On the Table Details page, click Switch Data Table.

2. On the Switch Data Table page, select a data source and a data table.

   

3. Click OK.

4. In the confirmation dialog that appears, click OK.