Create self-service dataset

1.Overview

1.1 Version

1.2  Application scenarios

When using FineBI self-service dataset, you need to select the fields first, and then you can do a series of data processing and analysis operations.

1.3 Function introduction

Select field means add fields that need data processing to the dataset, and unnecessary tables and fields do not need to be added. This operation mode enhances the practicability and speeds up the processing. At the same time, the self-service dataset can select fields by cross table for the two datasets that have been associated.

2.1 Create self-service dataset

There are two methods to create a self-service dataset.

1. Click on "Data Preparation", select a package and click "Add Table" → "Database Table", choose the database you need. As shown in the figure below:

 Then click "Add Table" → "Self-Service Dataset". As shown in the figure below:

2. Create a shortcut to directly create a self-service dataset for the table you want to use, as shown in the following figure:

2.2 Select the field

Enter the self-service dataset configuration interface, select the table to be used, and check the fields to be used in the table. As shown in the figure below:

Note: Only data tables that have been added and updated can be selected here. Data tables that have not been updated cannot be used to create self-service datasets.

When selecting the fields, they are sorted according to the order of the original data table by default. You can also select sort by type, that is, sort by field type.

The meaning of each field type icon is shown in the following table:

2.3 Select association tables

After selecting the fields in the table, other tables associated with the table in the  package can also be selected, while the table not associated with the table is grayed out and cannot be selected. Select  "ORDERDETAILS" table and select "ORDERID", as shown in the following figure:

See Section 3.1 for the range of optional fields.

If the association relationship of the used basic table changes during self-service dataset creation, you will be prompted: the association relationship of the current table changes, get it now.

Users need to click "get it now" to update the association relationship of the used table. As shown in the figure below:

2.4 Table structure display

1) Click the "table structure display" icon to view the information "field type, field name and source table" of the selected field, as shown in the following figure:

2）Click one of the fields to view more detailed information about the modified field, as shown in the following figure:

2.5 Data processing

1）After field selection, if further processing is required, click on the left  to realize the functions of Filter, Grouping Summary, New column, field settings, Sort, left and right merge and merge up and down for the formed self-service dataset. As shown in the figure below:

2) After adding a function, you can set this function in details. The data preview area is shown below, as shown in the following figure:

Note: In the direct connection version, you need to click preview to view the data in the data preview area.

2.6 Save the self-service dataset

After selecting the fields and completing the required data processing, click "Save / Save As" above to save the self-service dataset, as shown in the figure below:

2.7 Source table data change

When editing the self-service dataset, if the source table is changed and the update is successful, the prompt "Source table data change, get now" appears above the self-service dataset. Users need to click "get now" to update the source table used for editing. As shown in the figure below:

If the fields in the source table used by the self-service dataset have changed, you need to re-select the fields in the "selected fields".

3.1 Select field range

Note: the main table is the table at end 1 in the association relationship, and the sub table is the table at end n in the association relationship. For more information, see: Adding an association between tables.

1) When the first field is selected, all fields of all tables can be selected;

2) After selecting a field of table B, you can continue to select fields in the main table, sub table and main table of the main table associated with table B.

Tables associated with tables in this chain (except the sub tables of the main table of table B cannot be selected), others can be selected.

As shown in the above figure, if table B is selected for the first field, other sub tables of table a in the main table of table B cannot be selected, that is, table E cannot continue to select fields.

3.1.1 Result display after selecting associated table fields

1) 1: N example of associated selected field results

The result of selecting fields under the 1: N association relationship is the result of joining the associated fields based on the N-end table as the left table consolidation basis.

Sample Data:

StudentSpecialty.xlsx

StudentInfo.xlsx

The table of students' information "StudentInfo" is as follows:

The table of students' speciality "StudentSpeciality" is as follows:

Established the 1: N association according to the name field, as shown in the following figure:

Update the business package:

When selecting a table and a field, select all the fields of the student information table and the student specialty table. The result of the selection is that the name field is used as the consolidation basis, and the student specialty table at the N-end is used as the result of the join in the left table, as shown in the following figure:

Note: the association results are independent of the order of table selection.

The execution result of the association in the database is:

select * from 'StudentSpeciality' LEFT JOIN 'StudentInfo' on StudentSpeciality.'name'='StudentInfo'.'name'

2) Result of 1:1 selected field Association

If the "StudentSpeciality" table and the "StudentInfo" table establish a 1:1 association according to the name field, the result is as shown in the following figure:

First select the fields of the student information table, and then select the fields of the student specialty table. As a result, the student information table of the first selected table is used as the N-end, and the join is made according to the associated fields as the consolidation basis, as shown in the following figure:

The results of execution in the database are:

select * from 'StudentInfo' LEFT JOIN 'StudentSpeciality' on StudentInfo.'name'=StudentInfo.'name'

If the fields of the student specialty table are selected first, and then the fields of the student information table are selected, the results will take the student specialty table selected first as the N-end, and join according to the associated fields as the consolidation basis, as shown in the following figure:

The results of execution in the database are:

select * from 'StudentSpeciality' LEFT JOIN 'StudentInfo' on  'StudentSpeciality'.'name'='StudentInfo'.'name'

3.2 Tree structure display

In version 5.1.2, when the fields in the original table are named "A", "A-B" and "A-C", the self-service dataset fields will be automatically displayed in a tree structure, as shown in the following figure:

Note: only the original name can be automatically judged as a tree display, and the escape name cannot.

3.3 Field display after self circulation column

In the 2020-08-04 version of FineBi, when a self-service dataset is created by using a table with Self-circular columns, when fields are selected, they will not be displayed as Self-circular columns, but as ordinary fields, as shown in the following figure:

3.4 Self looping column selection field

In versions prior to 5.1.2, when creating self-service dataset selection fields using the basic table after creating self circulation columns, only the result column behind the circulation column can be selected, and the original column cannot be selected, as shown in the following figure:

3.5 The value is blank after the field is selected

After adding a field, the value in the preview interface may be empty. This problem is caused by the association of the original data itself when adding field consolidation. See Section 2.3 for details.

3.6 The association path of selected fields in self-service dataset  

3.6.1 Check only one path

This path is used by default, and the other path is disabled, as shown in the following figure:

3.6.2 Check multiple paths

Selections will be made when making dataset.

3.6.3 No path is checked

If all paths are unchecked, the shortest path is selected by default, as shown in the following figure:

3.7 The table is grayed or not displayed

3.7.1 Service package is grayed and cannot be selected

1) Problem

When creating a new self-service dataset, the business package cannot be selected, as shown in the following figure:

2) Cause analysis

There is no data table available in the current business package.

Note 1: SQL datasets and database tables can be used as long as they have business package permission.

Note 2: the data processing user cannot select the self-service dataset and excel dataset created by the data analysis user.

3.7.2 Grayed table cannot be selected

• The selected table is not associated with the unselected table. See section 3.5 of this document for details.

• If the table to be selected is not updated, you can update the data.

3.7.3 Excel dataset or self-service dataset is not displayed when creating a new self-service dataset

The excel dataset and self-service dataset created by the data analysis user cannot be selected by the data processing user. For details of analysis users, see: Bi design users