Quick BI:Dataset combinations

Background

This section describes the difference between a dataset combination and a dataset join.

• Dataset join: Joins data first, then aggregates the data.

  If your data tables are closely related and need to be joined for analysis in many scenarios, you can use the join feature in the dataset module. The resulting joined dataset can be used in different scenarios.

  Example of a left outer join:

  • Left table

    

  • Right table

    

  • Join result

    

    Note

    In this example, the left and right tables are joined based on User ID and Customer ID. A left outer join uses all rows from the left table. It returns data from the right table if a match is found. If no match is found, it returns a hyphen (-). For example, the user with the User ID VIP004 has no corresponding Customer ID in the right table. As a result, the Outlet ID, Order Number, and Quantity fields all show a hyphen (-).

• Dataset combination: Aggregates data first, then joins the data. If your data tables have flexible join requirements, such as needing different tables for analysis in different scenarios, you can create separate datasets for each table. Then, you can use the dataset combination feature in a dashboard. This lets you flexibly adjust the number of joined datasets and configure the join relationships as needed.

  Example:

  • Left table

    

  • Right table

    

  • Dataset combination result

    

    Note

    Explanation:

    • In this example, the left and right tables are first aggregated and then joined based on User ID and Customer ID. For the user with the User ID VIP001, the order amounts are 3000 and 1000, for a total of 4000. The quantities are 5 and 35, for a total of 40.

    • If a row in the left table corresponds to multiple dimension values in the right table, an asterisk (*) is displayed. For example, the user with the User ID VIP001 corresponds to multiple outlet IDs and order numbers in the right table. As a result, an asterisk (*) is displayed.

    • If a row in the left table has no matching data in the right table, a hyphen (-) is displayed. For example, the user with the User ID VIP004 has no corresponding data in the right table. As a result, the Outlet ID and Order Number fields show a hyphen (-).

Prerequisites

1. To use dataset combinations in a dashboard, you must have an existing dashboard and charts. For more information, see Create a dashboard and Overview of visualization charts.

2. To use dataset combinations in a data dashboard, you must have an existing data dashboard. For more information, see Create a data visualization screen.

3. To use dataset combinations in a workbook, you must have an existing workbook. For more information, see Create a workbook.

Entry points

• Dashboard or data dashboard

  On the dashboard or data dashboard edit page, select a chart. In the Data panel, click the dataset (①), select Switch dataset and clear field configuration (②), click Multi-select (③), select multiple datasets (④), and then click OK (⑤).

  

  Note

  You can select a maximum of 10 datasets.

  If no dataset is selected for the chart, click Select a dataset in Step ①.

• Workbook

  On the workbook edit page, insert a dataset table or a free-form cell. In the Data panel, click the dataset (①), click Multi-select (②), select multiple datasets (③), and then click OK (④).

  

After you click OK, the Edit dataset combination page appears.

Create a dataset combination

This example shows how to use a dataset combination in a dashboard.

• On the Edit dataset combination page, configure the following items:

  

  Configuration item	Description
  ① Dataset combination name	Enter a name for the dataset combination, such as "Sales order data".
  ② Main dataset	Select one dataset as the main dataset.
  ③ Auxiliary dataset	Select one or more auxiliary datasets. Click the icon to add datasets. Click the icon to set the corresponding auxiliary dataset as the main dataset. Click the icon to delete the corresponding auxiliary dataset.
  ④ Join conditions	Set the join conditions between the main and auxiliary datasets. The system automatically joins fields that have the same name. You can also click Add Join in the upper-right corner to manually add join conditions.

  Click OK to create the dataset combination.

  The data for the chart automatically switches to the newly created dataset combination. In this example, the dataset is automatically changed to "Sales order data".

  

• Click the icon next to the dataset to select Preview data or Go to Edit dataset combination.

  

  • Preview data

    On the data preview page, you can view All datasets and Used datasets, and preview the data.

    

  • Edit dataset combination

    Click Go to Edit dataset combination to open the Edit dataset combination page. On this page, you can edit the dataset combination. For more information, see Create a dataset combination.

Manage dataset combinations

On the dataset combination management page, you can edit, copy, and delete dataset combinations.

Entry point

1. Click the icon to the right of the dataset combination.

2. The Dataset combination management page appears.

Operations

On the Dataset combination management page, you can view the combination name, dataset details, and the number of charts that use the combination. You can also edit, copy, or delete the combination.

• Click Edit to open the page for editing the dataset combination. For more information, see the Create a dataset combination section in this topic.

  

• Click Copy to create a duplicate of the dataset combination. You can rename the copied combination and perform other operations as needed.

  

• Click Delete to delete the dataset combination.

  

  Note

  You can delete a dataset combination only if it is not used by any charts (the chart count is 0). If a chart uses the combination, the deletion will fail. In this case, you must first modify the charts that use the combination before you can delete it.

Use a dataset combination to build a chart

This example shows how to use a dataset combination to create a chart in a dashboard.

• Select a dataset combination and drag fields.

  In this example, select the Sales order data dataset.

  1. On the field tab, double-click or drag the Region field from the main dataset (Order data) to the Category Axis/Dimensions area.

  2. Double-click or drag the Order amount field from the main dataset (Order data) and the Profit amount field from the auxiliary dataset (Sales data) to the Value Axis/Measures area.

  3. Click Update. The system automatically updates the chart.

     

     Note

     When you use a dataset combination, the system sends a query to each data source. The results are returned as aggregate data, which are then joined and rendered. You must ensure that each dimension value in the main dataset has only one matching value in the auxiliary dataset. Therefore, all fields in the auxiliary dataset, except for the join fields, must be aggregated. The aggregation method for dimension values is as follows: If there is only one enumerated value, that value is displayed. If there are multiple enumerated values, an asterisk (*) is displayed.

     For example, if there is only one Product Package Box value for a given province and region, that value is displayed (Small box). If there are multiple Product Package Box values, * is displayed.

     (Province and Region are join fields. The Product package field is not a join field.)

     

• Click the auxiliary dataset to view the join status of its fields. You can manually join or unjoin fields.

  • The icon indicates that the field is currently a join field. Click the icon to unjoin the field.

    

  • The icon indicates that the field is not currently a join field. Click the icon to make it a join field.

    

    Note

    • When you drag a join field into the chart, the corresponding join icon is automatically enabled.

    • At least one join icon must be enabled. Otherwise, the system cannot calculate the join relationship between the auxiliary and main datasets.

    • Enabling or disabling the join icon affects only the current chart.

Scenarios

Limits

Unsupported features

1. Dataset combinations are supported in dashboards, data dashboards, and workbooks. They are not supported in Downloads and ad hoc analysis.

2. The following charts do not support dataset combinations:

   • Detail tables, metric decomposition trees, and metric relationship graphs in dashboards.

   • Detail data in workbooks.

   • Detail tables in data dashboards.

3. The following features are not supported for charts that use dataset combinations:

   Drilling, alert monitoring, dataset replacement, fluctuation analysis, automatic insights, and AI chat.

4. The following chart capabilities are not supported for charts that use dataset combinations:

   • Subtotals in cross tables.

   • Adding data comparisons in line charts.

5. A dataset combination can display a maximum of 10,000 rows of data.

Unsupported under specific conditions

1. During re-aggregation:

   • Aggregation methods are limited to sum, count, maximum, and minimum.

   • Period-over-period comparisons and secondary indicators in cross tables are not supported.

   • Auxiliary lines are not supported.

2. Fields in auxiliary datasets do not support secondary indicators in cross tables.

3. Totals are not supported when rows and columns are mixed in a cross table.