Quick BI:Create and Manage Datasets

Entry

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 icon 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 SQL

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

   

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 Use Custom SQL Statement to Create a Dataset.

Placeholder management

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

For more information, see Placeholder.

HINT statement

You can click the icon 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.

Build a model

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.

Configuration entry

• 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 ① in the following figure, and then configure the field.

• On the data preview of the Preview tab on the dataset creation page, move the pointer over the desired field, click the Settings icon that is marked as ② in the following figure, and then configure the field.

Function Description

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.

Batch Configuration fields

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.

   

   Configuration Item	Description
   ① Convert to Measure/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.

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 new folders

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.

Switch data sources

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.

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.

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.