Datasets in Quick BI are the basis for visual analysis. You can create datasets based on data tables whose data you want to analyze. Quick BI allows you to create datasets on UIs or by using SQL statements.

Prerequisites

The required data is obtained.

Create a dataset

Create and edit a datasets
You can use one of the following methods to create a dataset:
  • On the Datasets page, click Create Dataset in the upper-right corner. Create Dataset_intl
  • On the Data Sources page, find the desired data table and click the Create Dataset icon in the Actions column. 2_intl
  • On the Data Sources page, click Create Dataset with SQL in the upper-right corner. intl_3
Go to the dataset creation page. Go to the dataset creation page

Create a dataset by using an SQL statement

Two entry points are provided for you to create a dataset by using an SQL statement.

  1. Use one of the following entry points to create a dataset by using an SQL statement:
    • Entry point 1: On the Data Sources page, click Create Dataset with SQL in the upper-right corner. intl_3
    • Entry point 2: In the left-side navigation pane of the dataset creation page, select a data source. If no data table is displayed on the canvas, click Execute an SQL statement to create a table. Create Dataset with SQL_intl
  2. In the SQL editor, enter an SQL statement and click Run.
    Enter an SQL statement
    Sample statement:
    SELECT  report_date,
            order_level,
            shipping_type,
            area,
            price,
            order_number
    from    company_sales_record
    where   ${report_date :report_date}
    and     ${order_level :order_level}
    and     ${order_number :order_number}
    After the statement is successfully executed, you can view the execution result on the Results tab. Results
  3. Click Confirm Edit.
    Save the dataset that is created by using the SQL statement.

Build a model

If the data that you want to analyze is stored in different tables, you can associate the tables to build a model for data analysis. Build a model

For more information about how to associate tables and build a model, see Build a model.

Configure a field

After the model is built, Quick BI automatically previews the data and parses the data to obtain dimension and measure fields for subsequent data analysis.

You can edit the name or type of a field and the default display format of a numeric field or a date field.

Entry points

  • In the field list of the Preview tab on the dataset creation page, move the pointer over the desired field, click the Settings icon that is marked as 1 in the following figure, and then configure the field.
  • On the field table of the Preview tab on the dataset creation page, move the pointer over the desired field, click the Settings icon that is marked as 2 in the following figure, and then configure the field.
Entry points

Operations

Operation Description
Edit Edit the display name and description for a dimension or measure field.
  • You can configure the default display formats for date fields of different granularities.

    For example, you can configure a date field of the day granularity in the YYYYMMDD (example: 20210101) or YYYY-MM-DD (example: 2021-01-01) format.

  • You can configure a date field of the week granularity to the day of the week from which the week starts.
  • You can configure the default display format and unit conversion method for measure fields.
Hide Hide a field. After a field in a dataset is hidden, the field does not appear when you select the dataset for analysis on a dashboard or in a workbook.
Change Dimension Type or Change Measure Type Configure the field type. You can change the type of a field to Date (source data format), Geo, String, Number, or Image.
  • You can configure the format of the source data for a date field.

    For example, if the data format for a date field in the source data is 20210101, select YYYYMMDD. This format is used as the date format for the input date values in the query control for subsequent analysis.

    If the desired field is a date field and you want to change the date display format on a dashboard, select the field and configure the date display format on the field edit page.

  • If the desired field is a geographic field such as province or city, and you want to use the field to create a map chart, select the geo type for the field and select the geographic granularity that corresponds to the field.
  • If the desired field is an image field and you want to use the field to display an image on a dashboard when a cross table, ranking board, or ticker board is generated, select the image type for the field. An image field is saved as an image URL.
Synchronize Date Granularity Synchronize only date fields.
If you delete some date fields by mistake, you can click Synchronize Date Granularity to synchronize all date fields in the source data to the dataset.
Note The datasets that are created in Quick BI V3.7.3 or an earlier version do not contain the ymdhms field. If you want to use this field, you can click Synchronize Date Granularity to add the field.
Copy Copy a field. The name of the duplicate dimension ends with _Duplicate.

You cannot directly copy date fields. If you want to copy a date field, you must convert the field to the string type.

Convert to Measure or Convert to Dimension Convert a dimension field to a measure field or convert a measure field to a dimension field.
Default Aggregates Specify the default aggregation method. You can specify the default aggregation method only for a measure field.

During dashboard analysis, the aggregation method that you specified in the dataset is used by default.

Default Display Format Configure a numeric display format. You can configure a numeric display format, such as Integer or Percentage, only for a measure field.

During dashboard analysis, the numeric display format that you configured in the dataset is used by default.

Create Hierarchy Create a hierarchy based on the current dimension. You can create a hierarchy only for a dimension field.

For example, you can configure the province, city, and district fields as a hierarchy. After you create a hierarchy, the system automatically drills down through data based on the hierarchy when you configure and drill down through data on a dashboard.

Move To Quickly move a dimension to an existing hierarchy or a folder.
Sort Configure the default sorting method.

During dashboard analysis, the sorting method that is configured in the dataset is used by default.

Delete Delete a field.

If you want to retrieve a field that you deleted, you can click the table on the canvas and select the field in the right-side panel to add the field.

Drag a field to adjust the order Configure a field

Precautions

  • If a field is used as a calculated field, group dimension, or filter condition, you cannot change the type of the field, switch the field between dimension and measure, or delete the field.
  • You cannot directly copy date fields. If you want to copy a date field, convert the field to the string type.

Configure multiple fields at a time

If you want to edit a large number of fields, you can configure the fields at a time.

  1. On the dataset creation page, click the Batch Configuration tab.
  2. Modify the field configurations.
    You can select multiple fields at the same time and modify the field configurations at the bottom of the page. Batch Configuration

Create a group dimension

If a field in a data table needs to be processed before the field can be used for data analysis, you can use group dimensions and calculated fields.

Group dimensions are used in scenarios where dimension values are grouped. For example, the province field is divided into multiple regions, and the customer age field is divided into age groups, such as the ages from 0 to 18 years old, the ages from 19 to 40 years old, and the ages over 40 years old.

  1. On the Preview tab of the dataset creation page, click Create Group Dimension.
    Create Group Dimension
  2. In the New Group Fields dialog box, perform the steps that are shown in the following figure to configure the parameters and click OK.
    Configure group fields
    After you save the configurations, a dimension field is added to the dataset. You can enter group names in this column based on the configurations. Add a fieldFor more information about how to create a group dimension, see Create a group dimension.

Create a calculated field

If the data that you want to analyze needs to be processed based on the source data, instead of being obtained from a data table, you can create a calculated field.

Quick BI supports various field calculation methods to help you process data in a more efficient manner.
  • Aggregate: For example, use COUNT(DISTINCT [customer name]) to calculate the number of customers based on customer names.
  • Basic operations: For example, use the formula [Transaction amount]/[Number of customers] to calculate the unit price.
  • Character segmentation and merging: For example, use CONCAT([province], [city]) to merge the province and city fields.
  • Complex grouping: For example, use CASE WHEN [Transaction amount]>1000 AND [Number of transactions]>5 THEN 'VIP' ELSE 'normal' END to specify that customers who meet the specified condition are defined as VIP customers.
For more information about how to create a calculated field, see Create a calculated field.
  1. On the Preview tab of the dataset creation page, click Create Calculated Field.
    Create Calculated Field
  2. In the New Calculated Fields dialog box, configure the parameters and click OK.
    1. Enter a field name in Label Name.
    2. Select fields in the Click to reference field and Click to reference function sections and edit the field expression.
      When you edit an expression, take note of the following points:
      • After you enter a left bracket ([) in the Field Expression field, a measure list appears for you to select the desired measure.
      • The parentheses and brackets that you entered in the Field Expression field must be half-width characters.
      • Only the functions that are supported by each data source can be used.

        You can view the functions that are supported by a specific data source in the function section on the right side of the New Calculated Fields dialog box or query the functions that are supported by the data source type.

    3. Specify Data Type and Type. Then, click OK.
      Add a calculated field

Filter data

If you need specific data during data analysis, you can add a filter to obtain the required data.

If you configure multiple field filters, data that meets all the conditions is obtained for subsequent analysis. Filter data
Configure filter fields for the dataset. Set Filter Fields

Switch data sources

Notice When you perform cross-source queries, you cannot switch data sources for a dataset.

If a database is migrated, you can click the Switch Data Sources icon to switch the table that corresponds to a dataset of the database from one data source to another data source.

For example, after a dataset and dashboard are created based on a MaxCompute table, data is synchronized from MaxCompute to AnalyticDB for MySQL V3.0. In this case, if you click the Switch Data Sources icon, Quick BI automatically switches the table to the new data source. Before you switch data sources, make sure that the new data source has a table with the same name as the table of the original data source.

The syntax varies based on the type of data source. If the data source is changed, you must check whether the configurations of the calculated field are valid. Switch Data Sources

FAQ

How do I use field descriptions as field names?

You can use a table name or description as a field name when you create a dataset in your workspace.

You can also select multiple desired fields and click Use Description as Field Name on the Batch Configuration tab of the dataset creation page. Configure field names
Note If the description of a field is empty, you cannot use the description as the field name.

How do I quickly update a dataset if a field in a physical table changes?

If the physical field cannot be found, the field may be used for analysis or displayed on the dashboard. Quick BI does not automatically delete fields. You can click the table on the canvas, view the field changes in the right-side panel, and then delete invalid fields in one click.