AnalyticDB:Quick BI

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.

   

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 an IP address whitelist.

   

   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.

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 Table 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
      ) DISTRIBUTED 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 import data to Data Warehouse Edition.

   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.

   

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.

   

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.