AnalyticDB:FineBI

Configure an external MySQL database

1. Start FineBI.

2. Click the server URL and follow the instructions to configure Account Settings.

3. Click Next Step to select the database. Click Configure Database in the External Database section.

4. On the External Database Configuration page, follow the instructions on the page to configure the parameters.

   Note

   You must specify the parameters for connecting to the MySQL database instead of the AnalyticDB for MySQL database.

   Parameter	Description
   Database Type	The engine of the database. Select MySQL from the drop-down list.
   Driver	The type of the driver. Select com.mysql.jdbc.Driver from the drop-down list.
   Database Name	The name of the database into which FineBI metadata is imported. You can also create a new database. After you have connected FineBI to the MySQL server, the CREATE DATABASE finedb; statement is automatically executed to create the finedb database.
   Host	The address of the MySQL server, which can be the address of a local or remote server.
   Port Number	The port number of the MySQL server address. The default port number is 3306.
   Username	The account created in the MySQL server.
   Password	The password of the account.
   URL	The URL generated by the system.

5. After you configure the preceding parameters, click Enable New Database to import FineBI metadata.

6. After you import the FineBI metadata, click Logon to log on to the MySQL server.

Connect to the AnalyticDB for MySQL database

1. Start FineBI. In the left-side navigation pane, choose Management System > Database Endpoint > Data Endpoint Management > Create Database Endpoint > All > AnalyticDB.

   

2. In the dialog box that appears, set parameters for the AnalyticDB for MySQL database.

   

   Parameter	Description
   Data Connection Name	The name of the database endpoint. We recommend that you choose an identifiable name to facilitate subsequent management.
   Driver	The type of the driver. Select com.mysql.jdbc.Driver from the drop-down list.
   Database Name	The name of the AnalyticDB for MySQL database.
   Host	The public endpoint or VPC endpoint of the AnalyticDB for MySQL cluster. You can view the endpoint on the Cluster Information page in the AnalyticDB for MySQL console. For more information, see Endpoint.
   Port	3306.
   Username	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 used to connect to the AnalyticDB for MySQL cluster.

3. After you configure the preceding parameters, click Test to test the connectivity. After the connection is successful, click Save in the upper-right corner.

Create a table

1. In the left-side navigation pane, choose Data Preparation > Real-time Data > Business Package > Create Table. Select the previously configured AnalyticDB for MySQL URL, select a table type, and add datasets to the data-based decision system for subsequent data analysis and dashboard display.

   • Database table: You can directly add tables in the AnalyticDB for MySQL database.

   • SQL datasets: You can execute SQL statements to generate datasets that are required for analysis.

   • Self-service datasets: You can add self-service datasets to database tables or SQL datasets.

2. Select base table fields. Then you can perform operations such as filtering, grouping, summarizing, adding columns, and merging.

   

Example

In market basket analysis, user consumption data is analyzed and different commodities are associated to mine their connections. The following example demonstrates how to create a table and import data, and use FineBI to connect to an AnalyticDB for MySQL database and create a basket analysis dashboard.

1. Download the Summary table of commodity sales table sample data.

2. Create a table in the AnalyticDB for MySQL database.

   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. Several columns of data in Summary table of commodity sales show that their distribution by document number is relatively random. JOIN operations are performed in market basket analysis based on document numbers. Therefore, the document number column is used as the partition key. Date is selected as a level-2 partition because no data will be imported to this column. The lifecycle is set to 100.

   3. Execute the following statement to create a table:

      Create Table `demo_sales` (
       'Date' date,
       'City' varchar,
       'Document No' varchar,
       'Store name' varchar,
       'Commodity class' varchar,
       'Commodity name' varchar,
       'Province' varchar,
       'Cost' double,
       'Gross profit' double
       'Quantity' bigint,
       'Sales' double,
      ) DISTRIBUTED BY HASH ('Document No') 
      PARTITION BY VALUE ('Date') LIFECYCLE 100 
      INDEX_ALL='Y' STORAGE_POLICY='COLD' COMMENT='Summary table of merchandise sales'

3. After the table is created, use Kettle to import the local data in Excel format to the AnalyticDB for MySQL database.

   1. For more information about how to use Kettle, see Use Kettle to import data to Data Warehouse Edition.

   2. After the data is imported, execute the SELECT COUNT(*) FROM demo_orders statement. 40514 entries are returned.

4. Create self-service datasets in FineBI.

   1. In the navigation pane, choose Data Preparation > Real-time Data. Add a business package named adb.

   2. Create self-service datasets in the business package, and select the AnalyticDB connection_demo_sales fields (all fields of the demo_sales table in the AnalyticDB for MySQL database.

5. Add data by referencing examples in FineBI documentation to generate the required dashboard.

Troubleshooting

1. No external database is selected during initialization. After initialization, I want to configure the external database.

   Solution:

   1. Start the data-based decision system. Choose Management System > System Management. On the General tab, click To Be Configured.

   2. Enter the parameters of the external database.

   3. After the external database is configured, click Enable New Database.

2. An external MySQL database is configured, but data import fails.

   Solution:

   1. Check the version and encoding method of the MySQL database. For more information, see Configure external MySQL database. FineBI supports MySQL 5.x.

   2. Check whether the MySQL database uses the utf8 (UTF-8 unicode) character set encoding method, or execute the CREATE DATABASE finedb CHARACTER SET utf8 statement to create a new utf8-encoded database.

3. An external AnalyticDB for MySQL database is configured, but connection to it fails.

   Solution: The external database must be a MySQL database, instead of an AnalyticDB for MySQL database.

4. No option is available to extract data or real-time data in the Data Preparation hierarchy tree.

   Solution: The version downloaded from the FineBI official website does not support direct connections to the AnalyticDB for MySQL database. You must contact FineBI technical support engineers to obtain the required version.

5. Data is not synchronized between FineBI and the AnalyticDB for MySQL database.

   Solution: For more information, see Section 3.2 in Real-time data.

6. No data is displayed after I update data.

   Solution: If no data is displayed in a single row, the calculation result of the row may be null. If no data is displayed in the entire column and a function is used on the column, the function may be incompatible in AnalyticDB for MySQL. Contact AnalyticDB for MySQL technical support engineers.

7. I fail to preview or save data.

   Solution:

   1. First, check whether a function is used on the column, but the column data types are supported by the function and invalid values exist after column data is converted. For example, AnalyticDB for MySQL will report an error if you enter the Chinese charters of city in a column. AnalyticDB for MySQL is used to find absolute values of numeric fields. The value is invalid when the Chinese charters of city are converted to a numerical value.

   2. If the error message contains a string of numbers as shown in the following figure, it is highly probable that the error is reported in AnalyticDB for MySQL. You can provide the string of numbers (it is a process ID) to AnalyticDB for MySQL technical support engineers for troubleshooting.

   3. If the error message contains only an error code, it is highly probable that the error is reported in FineBI.