All Products
Document Center

Quick BI:Create and Manage Datasets

Last Updated:Apr 28, 2024

In the Quick BI, datasets are used to analyze data in a visualized manner. You can create datasets based on the data tables that you want to analyze. Quick BI, you can create datasets in a visualized manner or by using custom SQL statements.


Data is obtained. For more information, see Connect to a data source.

Go to the Event Center page

  1. On the Quick BI homepage, go to the Create Dataset page.

    Entry 1: Create a dataset in the resource entry outside the space.


    Entry 2: Quickly create a dataset on a resource list in a space.


    Entry 3: Create a dataset on the Datasets page.


    Entry 4: On the Data Sources page, find the data table that you want to manage and click the imageicon to create a dataset.


    Method 5: On the Data Sources page, click Create Dataset.


  2. Go to the dataset creation page.


Create a dataset by using an SQL statement

You can create a dataset by using one of the following custom SQL statements:

  1. Use one of the following entries to create a dataset.

    • Entry 1: On the Data Sources page, click Create Dataset in the upper-right corner.


    • Entry 2: Create a dataset. In the left-side navigation pane, select a data source. If no data table is available on the canvas, click Use SQL to create a dataset.


    • Entry 3: On the dataset editing page, select SQL code to create a table.


  2. In the dialog box that appears, enter the SQL code and click Run.


    SQL sample code:

    SELECT  report_date,
    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.


  3. Click Confirm.

    Save the dataset that is created by using the SQL statement.

    To edit the code, move the pointer over the table on the canvas and click Edit Code. Alternatively, click the destination table on the canvas. In the right pane, click Edit Code.


    Quick BI SQL code supports placeholders. For more information about scenarios and help, see Custom SQL.

Placeholder management

You can click the image.pngicon in the top navigation bar to manage placeholders. image


Variable Name

The name of the placeholder. You can customize the name.


The type of the placeholder. The value can be an expression placeholder or a value placeholder. The type cannot be modified.

Variable Type

Valid values: Text, Value, Date-Year, Date-Quarter, Date-Year, Date-Year, Week, Date-Year, Month, Day, and Date-Year, Month, Day, Hour, Minutes, and Seconds.

Query Default Value

The default effective range supports Dataset Only and Global Effective modes.

  • Dataset Only: This parameter takes effect only on the dataset editing page.

  • Global: takes effect on downstream dashboards and workbook tables.

A maximum of 150 characters can be entered in the input box.


The default value of the expression placeholder must be a complete expression, for example, area = "northeast".


You can delete placeholders.

Create Placeholder

Supports creating placeholders.


For more information, see Placeholder.

HINT statement

You can click the imageicon in the top navigation bar of the dataset editing page and follow the steps shown in the following figure to configure a HINT statement.


The following data sources support HINT statements:

Alibaba MaxCompute, Hive, PostgreSQL, Alibaba AnalyticDB for PostgreSQL, Alibaba PolarDB for PostgreSQL, Alibaba Hologres, Alibaba PolarDB for Oracle, Oracle, SQL Server, ClickHouse, Presto (including PrestoDB and Trino), and Impala.

  • You cannot set the HINT statement when a data table is associated with a data source.

  • You can specify a maximum of 20 HINT statements.

  • sql to create a dataset. If you modify the HINT statement, you must re-run the sql code to obtain the data.

  • The input content is limited to 50 characters in length. Only English, numbers, underscores, and dots are allowed.

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.


For more information about how to create a model and associate it with data, 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.






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 set the daily granularity to January 1, 2021 or 2021-01-01.

  • You can configure a date field of the week granularity to the day of the week from which the week starts.


    • Click Register Function to register a date function.


    • If you do not have the write permission on the database, you cannot use the automatic function registration feature. You can manually download the function script and run the script on the data source.

    • Data sources such as MaxCompute, Presto, Hive, and Apache Doris do not support automatic registration. You must manually download the function script and run the script on the data source.


      For more information about data sources that support custom week, see Data source items.

  • You can configure the default display format and unit conversion method for measure fields.


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 accidentally delete some date granularity fields, you can click Synchronize Date Granularity to synchronize all date granularity fields from the source data to the dataset.


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.


Quickly copy a field. The generated dimension will automatically include a copy as a prompt.

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

Converts a dimension field to a measure field and 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.

Hierarchy: For example, province, city, and district. You can set these three fields as a hierarchy. When you configure drill-down in a dashboard, you can automatically drill down based on the hierarchy.

Move To

Quickly move a dimension to an existing hierarchy or folder.


Configure the default sorting method.

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


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

You can drag one or more fields to adjust the order or move them to another folder.


  • You cannot select hierarchies, including date hierarchies, during multiple selections.

  • You cannot select fields in the date hierarchy when you select multiple objects.

Usage notes

  • 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 copy a date field. You can convert the date field to a text type and then copy the field.

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. In the Fields section, click Batch Configuration.

  2. You can select multiple fields at the same time and modify the field configurations at the bottom of the page.



    ① Convert to Measure /Convert to Dimension

    If the fields that you select are all dimensions, you can convert the fields to measures at a time.

    If all the selected fields are measures, you can convert them to dimensions at a time.

    ② Hide and unhide

    You can hide or unhide fields at a time.

    ③ Delete

    You can delete multiple fields at a time.

    ④ Use description as the field name

    Batch use description is supported as the field name.

Create 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.

Grouping dimensions are used in dimension value grouping scenarios. For example, you can group the province field into several regions and group the customer age into groups that are 0 to 18 years old, 19 to 40 years old, and over 40 years old.

  1. On the Data Preview page, click Create Group Dimension.

  2. In the Create Grouping Field dialog box, perform the following steps and click OK.


    After you save the configuration, add a dimension field to the dataset and enter the group name in the column based on the configuration. 新增字段For more information about group dimensions, see Group dimensions.

Add 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, you can use the customer name to calculate the number of customers. For example, you can run the COUNT(DISTINCT) command.

  • Four operations: For example, the unit price is calculated as [Transaction Amount] / [Number of Customers].

  • Split and merge characters: For example, you can merge provinces and cities into CONCAT([province], [city]).

  • Complex grouping: For example, if the customer level meets certain conditions, the VIP customer is defined as CASE WHEN [transaction amount]>1000 AND [number of transactions]>5 THEN 'VIP' ELSE 'ordinal' END.

For more information about calculated fields, see Create a calculated field.

  1. On the Data Preview page, click Create Calculated Field.

  2. In the Create Calculated Field dialog box, set the parameters and click OK.

    1. Enter a field name.

    2. Click Reference Function and click Reference Field to 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. That is, the brackets entered in the English input method.

      • 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. Set the Data Type and Field Type parameters and click OK.


Create directories

You can follow the steps shown in the figure to create folders on dimensions and measures.


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.


Set filter conditions in the dataset. 数据集过滤条件设置

Switch data sources


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

If a database is migrated, you can use the switch data source function to switch the data table corresponding to the dataset from one data source to another.

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.



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.



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.