Quick BI is a new-generation self-service business intelligence (BI) platform tailored for cloud users and enterprises. It provides an easy-to-use graphical user interface (GUI) and supports flexible and efficient multidimensional analysis to help you gain precision data-driven insights into your business. AnalyticDB for MySQL data sources can be added to Quick BI to build BI systems.

Prerequisites

Connection to AnalyticDB for MySQL

  1. Log on to the Quick BI Pro console.
  2. In the top navigation bar, click Workspace. In the left-side navigation pane of the Workspace page, click Data Sources. On the Data Sources page, click Create Data Source in the upper-right corner.
  3. In the Add Data Source dialog box, click AnalyticDB for MySQL 3.0.
    2021040903
  4. In the Add AnalyticDB for MySQL 3.0 Database dialog box, configure the parameters.

    If you purchase AnalyticDB for MySQL and Quick BI resources by using the same Alibaba Cloud account, the AnalyticDB for MySQL clusters that belong to the account are displayed in the Select Database drop-down list. After you select a cluster, the Name and Database Address parameters are set to the name and endpoint of the cluster.

    For information about how to add whitelists in the AnalyticDB for MySQL console, see Configure a whitelist.

    2021040701
    Parameter Description
    Name The name of the data source. We recommend that you specify a descriptive name to facilitate subsequent management.
    Database Address The virtual private cloud (VPC) endpoint of the AnalyticDB for MySQL cluster.

    You can view the endpoint in the AnalyticDB for MySQL console. For more information, see Endpoint.

    Port Number The port number corresponding to the VPC endpoint of the AnalyticDB for MySQL cluster to which you want to connect.
    Database The name of the database in the AnalyticDB for MySQL cluster.
    Account The account used to connect to the AnalyticDB for MySQL cluster. You can use one of the following account types:
    • Privileged account
    • Standard account
    Password The password of the account used to connect to the AnalyticDB for MySQL cluster.
  5. After you configure the preceding parameters, click Test Connection. After the connection passes the connectivity test, click OK.

Visualized data analysis

In the left-side navigation pane, click Datasets. On the Datasets page, click Create Dataset in the upper-right corner. Specify the address endpoint of the AnalyticDB for MySQL cluster, select a table, and add the dataset to the data-based decision making system for subsequent data analysis and presentation on dashboards.

You can analyze data in a visualized manner by using one of the following methods:
  1. Drag and drop the corresponding columns of the table: Perform visualized data analysis by using the console. Find a table for which you want to create a dataset and click the 2021040704 icon in the Actions column to create a dataset. On the Datasets page, find the dataset that you want to manage and click an icon in the Actions column to perform the corresponding operation.
    • Click the 2021040705 icon and view the data of the current dataset.
    • Click the 2021040706 icon and create a dashboard. Import data from the table to generate a chart for visualized display.
    • Click the 2021040707 icon and create a workbook. Import data from the table to generate a workbook for visualized display.
    • Click the 2021040708 icon and perform other operations, such as data masking, cache configuration, and cache clearing. For more information about operations related to dashboards, see Dashboard overview.
  2. Ad hoc query: Use SQL statements to perform visualized data analysis.
    • On the Data Sources page, click Ad Hoc Query in the upper-right corner to go to the Ad Hoc Query page.
    • Enter an SQL statement to query data based on your business requirements and click Run.
    • After you obtain the data that you want to query, click Create Dataset.
    • In the Save Custom SQL dialog box, set the Name, Save To, and SQL parameters.
    • For more information about how to use the ad hoc query feature, see Use ad hoc queries for data modeling.

Example

In the following example, analysis on the decrease in gross profit is performed by dragging and dropping a table.

  1. Download the demo data.
  2. Create a table in an AnalyticDB for MySQL cluster.
    1. Use a column whose values are evenly distributed as the distribution key, and a column whose values are of the TIME type as the partition key. For more information, see Schema design.
    2. In the demo table, order IDs are randomly distributed. Therefore, the order ID column is used as the partition key, and the date column is used as the secondary partition key. Set lifecycle to 100. This is because no data is imported in subsequent operations.
    3. The following statements are used to create the table:
      Create Table `demo_orders` (
        'Product ID' varchar,
        'Country' varchar,
        'Date' date,
        'Channel ID' varchar,
        'Channel name' varchar,
        'Channel type' varchar,
        'Order ID' varchar,
        'Cost' double,
        'Quantity' bigint,    
        'Sales' double,
        'Gross profit' double
      ) DISTRIBUTE BY HASH ('Order ID') 
      PARTITION BY VALUE ('Date') LIFECYCLE 100 
      INDEX_ALL = 'Y' STORAGE_POLICY = 'COLD' COMMENT = 'demo_orders'
  3. After the table is created, use Kettle to import the downloaded data to AnalyticDB for MySQL.
    1. For information about how to use Kettle, see Use Kettle to synchronize local data to AnalyticDB for MySQL.
    2. After the import is complete, execute the SELECT COUNT(*) FROM demo_orders statement. 3,672 entries are returned.
  4. Import the demo_orders table to the dataset.
    1. Add a column named Gross profit to the Measures list and define the column by using the following formula: sum([Sales] - [Cost])/sum([Sales]).
    2. In Quick BI, dimensions define the columns that can be queried by using the GROUP BY clause, and measures define the calculated values after the GROUP BY clause is executed. You can switch between dimensions and measures by copying or converting data, which is not required in this example.

FAQ

  1. Q: What do I do if the connectivity test fails?

    A: Check whether the account name and password are correct and whether whitelists of the cluster are properly configured.

  2. Q: How do I synchronize the changes in data source schemas?

    A: On the dataset editing page, click Sync Table Schema.

    2021040901
  3. Q: How do I join a dataset with a table?

    A: You can join a dataset with a table on the editing page of the dataset. The statements used resemble the JOIN statement in SQL. For more information, see Join a dataset with a table.

    2021040902
  4. Q: How do I add measures to dimensions or add dimensions to measures?

    A: Dimensions and measures are defined in the definition of tables within a dataset. If you want to convert a column from a measure to a dimension, you can right-click the column on the dataset editing page and select Convert to Dimension. Then, click Save and refresh the page. The modified schema is displayed on the page.