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
- For information about the compatibility between Quick BI and AnalyticDB for MySQL, see Compatibility overview.
- Purchase Quick BI by using your Alibaba Cloud account. For more information, see Purchases, upgrades, downgrades, renewals, and overdue payments.
Connection to AnalyticDB for MySQL
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.
- 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 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 icon and view the data of the current dataset.
- Click the icon and create a dashboard. Import data from the table to generate a chart for visualized display.
- Click the icon and create a workbook. Import data from the table to generate a workbook for visualized display.
- Click the 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.
- 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.
- Download the demo data.
- Create a table in an AnalyticDB for MySQL cluster.
- 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.
- 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.
- 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'
- After the table is created, use Kettle to import the downloaded data to AnalyticDB for MySQL.
- For information about how to use Kettle, see Use Kettle to synchronize local data to AnalyticDB for MySQL.
- After the import is complete, execute the
SELECT COUNT(*) FROM demo_orders
statement. 3,672 entries are returned.
- Import the
demo_orders
table to the dataset.- Add a column named Gross profit to the Measures list and define the column by using
the following formula:
sum([Sales] - [Cost])/sum([Sales])
. - 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.
- Add a column named Gross profit to the Measures list and define the column by using
the following formula:
FAQ
- 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.
- Q: How do I synchronize the changes in data source schemas?
A: On the dataset editing page, click Sync Table Schema.
- 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.
- 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.