Quick start for data preparation

For learning videos related to data preparation, please refer to: [Data Preparation]

1.1 Version

1.2 Application scenarios

Fetch the data tables to be used in the database to FineBI, and use FineBI to make some basic settings for the tables in the database: establish associations, set field types, and so on.

1.3 Applicable people

administrator

Click on the link to [Receive the Getting Started Guide Task], download the data used in this case, and follow the tutorial to personally complete the data preparation work.

In the analysis thinking, the sales analysis requirements of retail stores have been introduced. The administrator first needs to connect the database with FineBI, and then use FineBI to process the historical data of the orders in the database.

2.1 Create a data connection

Purpose: Use MySQL database to retrieve the data that FineBI needs to analyze.

Reference document: MySQL data connection

1) The administrator logs in to FineBI, the default login address is http://localhost:37799/webroot/decision.

Click Manage>Data Connection>Data Connection Management>New Data Connection, as shown in the figure below:

2) Select "MySQL" for the newly created data connection type, as shown in the figure below:

3) Name the newly created data connection sale_database, and fill in the corresponding "Database Name", "Host", "Port", "Username" and "Password" according to the specific information of the database. "Encoding" and "Driver" are generally the default options. As shown below:

• The sample database "username" and "password" are both finebi.

For more data connection configuration information, please refer to the document: Configure Data Connection

Description:

According to the database information in the example, the URL will be automatically generated after filling in the corresponding database name, host, and port: jdbc:mysql://solutions.finebi.com:3306/finebi

(The standard format is jdbc:mysql://localhost:port/dbname, here replace the corresponding content according to the ip, port and name of the sample database).

Note: If the data update fails, add the parameter?useUnicode=true&characterEncoding=UTF-8 after the generated URL

4) After configuration, click Test Connection, click Save after the connection is successful, and successfully create a new MySQL data connection named sale_database, as shown in the following figure:

5) The newly created data connection "sale_database" is shown in the following figure:

2.2 New business package

The business package is equivalent to a folder for storing data tables.

1) After the data connection is successfully created, click Data Preparation> Add a Business Package, and name the newly created business package "Gross Profit Analysis", as shown in the figure below:

2.3 New data table

Connect the tables in the database to FineBI.

1) Click to enter the "Gross Margin Analysis" business package, choose to Add Table, and add the table type to choose from Database Table, SQL data set, EXCEL dataset and Self-Service Dataset Choose to add a database table here.

2) Select the sales_database data connection created earlier to enter the database table selection interface. Select the product information dimension table, product sales detail table, and store information dimension table contained therein, as shown in the following figure:

3) After clicking OK, these three tables will appear under "Gross Margin Analysis" in the business package, as shown in the figure below:

Note: The association relationship and escape name will be automatically obtained when the data table is added. If you need to modify the default table name, just click on the right side of the table name... and select Rename.

2.4 Basic table processing

All the steps in this chapter do not need to be done to help users understand the functions of basic table processing.

1) Get some incorrect field types directly from the database, the administrator can set the fields of the table at this time, click on the right side of the table name... and select Edit, as shown in the following figure:

2) Click Preview, a data preview will appear on the right. You can check whether the fields in the table are used, as shown in the following figure:

Note: If the preview fails, change the data link URL to jdbc:mysql://solutions.finebi.com:3306/finebi? useUnicode=true&characterEncoding=UTF-8

3) Click + to perform Field type conversion, field settings, row and column conversion and self-looping column on the table (this example does not require operation), as shown in the following figure:

Field type conversation: the user can change the field type of each field;

Create a self-looping column: When there is a hierarchical relationship between departments but the data table is not displayed hierarchically, this function converts the organization table into a tree structure and saves it;

Row and Column Conversion: Convert the rows in the relational data table into columns;

3) After editing, click the OK button in the upper right corner to save. The names of fields and data tables can be renamed, so that users who use data tables can understand the meanings of data tables and fields, as shown in the following figure:

2.5 Add association

In this example, the association relationship has been established in the database, and FineBI will automatically obtain the existing association relationship. (The steps in this chapter do not need to be done, to help users understand the associated functions)

If no association relationship has been established in the database before, how to use FineBI to establish an association relationship between these tables?

1) When the data table is added, the association relationship and escape name will be automatically obtained. If there is no association, you need to add an association relationship for it here. Click the Commodity sales schedule, select the associated view, and click the add association button, as shown in the figure below:

2) Associate the store code of the "Commodity sales schedule" with the store code field of the "Store Information Dimension Table" according to the N:1 relationship, and click OK, as shown in the following figure:

3) Use the same method to associate the product code field of the "Commodity sales schedule" with the product code field of the "Product Information Dimension Table" in an N:1 relationship, as shown in the following figure:

In this way, an association can be established between the tables.

2.6 Data update

After performing the associated configuration or basic table operation on the basic table, you need to update the table, click on the Update the business package, you can update all the tables under the business package, as shown in the following figure:

At this point, you can see the update status of the basic table in the update progress of the data table. If the completion time that appears is the time you update, it means that the update is successful. As shown below: