All Products
Search
Document Center

Quick BI:Create and Manage Datasets

Last Updated:Jan 25, 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.

Prerequisites

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

Operation

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

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

    image..png

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

    image..png

    Entry 3: Create a dataset on the Datasets page.

    image..png

    Method 4: On the Data Sources page, click the icon next to the imagedata table to create a dataset.

    image..png

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

    image..png

  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.

      image..png

    • 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 Code to Create First Table on the canvas.

      image..png

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

      image

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

    image..png

    Sample SQL statements:

    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.

    image..png

  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.

    image..png

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

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 set the HINT statement.

image

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.

Note
  • 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

During data analysis, when the data to be analyzed is stored in different tables, you can join multiple tables through data association to form 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.

配置入口

Operations

Section

Operation

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

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

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

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.

Sort

Configure the default sorting method.

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

Delete a data cache

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

字段配置

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 modify field configurations.

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

    image..png

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.

    image..png

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

    image..png

    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 use COUNT(DISTINCT).

  • 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 'ordinary' 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.

      image..png

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.

image..png

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

Switch data sources

Important

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.

image..png

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.

image..png

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.