Edit basic table

For concepts, please refer to: Basic table

1.1 Version

1.2 Application scenarios

After finishing adding database table, adding SQL dataset, and adding EXCEL dataset, sometimes it is necessary to process the added basic tables such as field selection and field type settings.

1.3 Function introduction

FineBI can edit the added basic table.

The extracted version supports "field type conversion", "field settings", creation of "self-circulating columns", and "row and column conversion".

The direct connection version supports "field type conversion" and "field settings".

2.1 Enter editing interface

There are two ways to enter the editing interface, as shown in the figure below:

Support to open the editing page of multiple basic tables or self-service datasets, as shown in the figure below:

2.2 Select fields

After entering the basic table editing interface, you can select the fields that need to be used, and uncheck the fields that do not need to be used.

1) For example, only the fields of "ORDERID, CUSTOMERID, EMPID and AMOUNT", click "Preview", as shown in the figure below:

2.3 Field type conversion

Note: "Direct connection version" supports field type conversion from 519 onwards.

1) Click "+" to add "Field type conversion", as shown in the figure below:

2) Set the field type of "AMOUNT" to "value", as shown in the figure below:

Note: The memory occupied by different field types is different. For example, converting a "text" type field to a "date" type will increase the memory occupied by the table.

Note: When the converted format is not standardized (for example, the text "a" is converted into a value), the field will be empty after the conversion in the extracted version, and the field may be empty or an error will be reported in the direct version.

2.4 Field settings

In the field settings, you can adjust the order of the fields, as shown in the following figure:

2.5 Self-circulating column and row-column conversion

In the Spider version, users can also set "self-circulating column" and "row and column conversion" for the basic table.

2.6 Save and update

After operating the basic table, click "Save". As shown below:

If you are using extracted data, you also need to click "Update Data" on the data preview interface to update the changes just made. As shown below:

3.1 Rules of field type conversion

Extracted data-The text format that supports conversion to date type is shown in the following table. If there is no hour, minute and second in the text, the hour, minute, and second will be automatically completed to 0 after being converted into time.

Real-time data-The text format that supports conversion to date type is shown in the following table. If there is no hour, minute and second in the text, the hour, minute and second will be automatically completed to 0 after being converted into time.

3.2 Failed to get data

If the data connection is disconnected, it will prompt:Failed to get data, please check the current data source connection and try again. The user needs to check whether the database is properly connected.

3.3 Calculation exceeds data limit

Problem description

When the user is performing "row and column conversion" and "self-circulating column", the preview area prompts: Your dataset calculation exceeds the data volume limit and cannot be successfully calculated and updated. Please reduce the data that needs to be updated or contact the administrator for processing.

Reason analysis

This error will occur when the amount of data calculated by "row and column conversion" and "self-circulating column" exceeds the data access limit set by the administrator.

Solutions

• Users who have this error can reduce the amount of data in the data table, so that no error will be reported after "row and column conversion" and "self-circulating column" are performed;

• Contact the administrator to solve the problem, and the administrator can modify the data access limit. (The unit of data access is: row)

3.4 The edited SQL dataset dose not display the added field

If you modify the SQL statement of the SQL dataset, add the "AMOUNT" field, and click "OK", as shown in the figure below:

After modifying the SQL dataset, the data table will not directly update the added fields, you need to click the "Edit" button of the base table. Manually check the newly added fields, as shown in the figure below:

3.5 Preview error after field type conversion

Problem description

The project is deployed in the WebSphere container, the project is the Spider engine, the SQL dataset is clicked to edit, the field type conversion is performed, and the preview button is clicked to report an error:

Error 500: com.fr.third.springframework.web.util.NestedServletException: Handler processing failed; nested exception is java.lang.NoClassDefFoundError: com.finebi.spider.cluster.spark.ContextManager (initialization failure)

Reason analysis

Caused by the class loading mechanism in the J9 VM.

Solutions

Solution 1: Migrate the project deployed in the WebSphere container to other containers, such as the Tomcat container.

Note: If you need to migrate the project to another container, you can contact technical support. Technical support contact information: QQ "800049425", telephone "400-811-8890".

Solution 2: Disable J9 VM and replace it with JDK8 in Oracle's official website.