Report data extraction method and device, computer equipment, readable storage medium and program product

Abstract

The invention relates to a report data extraction method and device, computer equipment, a readable storage medium and a program product. The method comprises the steps of obtaining configuration information of a target report, wherein the target report is associated with a plurality of original tables; obtaining a field type corresponding to the configuration information based on the field attribute of the original table, and obtaining updated field information corresponding to the configuration information based on the field type; determining table field information of a target physical table based on the update field information, and creating the target physical table based on the table field information; and obtaining a field value corresponding to the table field information from the data of the original table, and extracting the field value to the target physical table. By adopting the method, the data required by the report can be queried based on the single target physical table, so that the database access times and the cross-table association query times are reduced, and the report generation efficiency is improved.

Description

Technical Field

[0001] The present application relates to the field of report technology, and in particular to a report data extraction method, apparatus, computer equipment, computer-readable storage medium, and computer program product. Background Art

[0002] With the development of computer technology, the amount of data that enterprises or other organizations need to manage is increasing rapidly. People can use this data to obtain effective data for decision-making assistance, etc. In traditional technology, the extracted effective data can usually be converted into reports to visualize the data. However, due to the large amount of data associated with the report and its complexity, the speed of querying the report is slow, which leads to low efficiency in report generation. Summary of the invention

[0003] Based on this, it is necessary to provide a report data extraction method, device, computer equipment, computer-readable storage medium and computer program product that can extract the data required for the report into a single table, thereby querying the data required for the report based on the table, thereby improving the report generation speed.

[0004] In a first aspect, the present application provides a report data extraction method, comprising:

[0005] Obtaining configuration information of a target report, wherein the report is associated with multiple original tables;

[0006] Acquire the field type corresponding to the configuration information based on the field attribute of the original table, and acquire the updated field information corresponding to the configuration information based on the field type;

[0007] Determine table field information of a target physical table based on the update field information, and create the target physical table based on the table field information;

[0008] The field value corresponding to the table field information is obtained from the data of the original table, so as to extract the field value to the target physical table.

[0009] In a second aspect, the present application also provides a report data extraction device, comprising:

[0010] A configuration information acquisition module, used to acquire configuration information of a target report, wherein the report is associated with multiple original tables;

[0011] A field information acquisition module, used for acquiring a field type corresponding to the configuration information based on a field attribute of the original table, and acquiring updated field information corresponding to the configuration information based on the field type;

[0012] A table information acquisition module, used to determine table field information used to create a target physical table based on the updated field information, and create the target physical table based on the table field information;

[0013] The extraction module is used to obtain the field value corresponding to the table field information from the data of the original table, so as to extract the field value to the target physical table.

[0014] In a third aspect, the present application further provides a computer device, comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps in the above method when executing the computer program.

[0015] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program implements the steps in the above method when executed by a processor.

[0016] In a fifth aspect, the present application also provides a computer program product, including a computer program, which implements the steps in the above method when executed by a processor.

[0017] The report data extraction method, device, computer equipment, computer readable storage medium and computer program product obtain the configuration information of the target report associated with multiple original tables, obtain the field type corresponding to the configuration information based on the field attributes of the original table, obtain the update field information corresponding to the configuration information based on the field type, and then determine the table field information used to create the target physical table based on the update field information, create the target physical table based on the table field information, and then obtain the field value corresponding to the table field information from the data of the original table, and extract the field value to the target physical table, so that the data required for the report are stored in the change field information, and then stored in the target physical table. When querying the report, the data in the report can be obtained through the target physical table. Compared with querying from multiple original tables, the number of database accesses can be reduced, the query speed can be improved, and the report generation efficiency can be improved. In addition, by analyzing the configuration information of the report and obtaining the corresponding update field information based on the field type corresponding to the configuration information, the data integrity of the target physical table can be improved, so that the data required for the report can be obtained from the target physical table, thereby further improving the report query speed and the report generation efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments of the present application or related technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0019] Figure 1 A schematic diagram of a personnel inflow and outflow table and an associated original table in one embodiment;

[0020] Figure 2 An application environment diagram of a report data extraction method in an embodiment;

[0021] Figure 3 The figure is a flow chart of a report data extraction method in one embodiment;

[0022] Figure 4 A schematic diagram of a report configuration page in an embodiment;

[0023] Figure 5 A schematic diagram of a mapping relationship between report configuration items and table fields in an embodiment;

[0024] Figure 6 A schematic diagram of a process for obtaining updated field information in one embodiment;

[0025] Figure 7 A schematic diagram of a process for obtaining an original field in one embodiment;

[0026] Figure 8 A schematic diagram of the structure of a target model corresponding to a report in an embodiment;

[0027] Fig. 9 A schematic diagram of a process for generating metadata in one embodiment;

[0028] Fig.10 A schematic diagram of a target physical table change in one embodiment;

[0029] Fig.11 A schematic diagram of data extraction in one embodiment;

[0030] Fig.12 A schematic diagram of a target physical table storage situation in one embodiment;

[0031] Fig.13 It is a flowchart of a report data extraction method in another embodiment;

[0032] Fig.14 It is a structural block diagram of a report data extraction device in one embodiment;

[0033] Fig.15 is an internal structure diagram of a computer device in one embodiment;

[0034] Fig.16 FIG. 4 is a diagram showing the internal structure of a computer device in another embodiment. DETAILED DESCRIPTION

[0035] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0036] like Figure 1 As shown, the personnel inflow and outflow table is associated with multiple original tables, such as the basic table of employment experience, the basic table of certificate information, etc. If a report query is performed on the personnel inflow and outflow table and a corresponding report is generated, it may take a lot of time, resulting in low report generation efficiency.

[0037] To improve or solve the above problem, the server can extract the data associated with the report into a single target physical table, so that when querying the report later, the generated report can be obtained based on the single target physical table without involving cross-library or multi-table associated queries, which can improve the efficiency of report query and generation. For details, please refer to the following embodiments.

[0038] The report data extraction method provided in the embodiment of the present application can be applied to Figure 2 In the application environment shown. Among them, the terminal 202 communicates with the server 204 through the network. The data storage system can store the data that the server 204 needs to process. The data storage system can be integrated on the server 204, or it can be placed on the cloud or other network servers. After the user configures the target report at the terminal 202, wherein the target report is associated with multiple original tables, the terminal 202 sends the configuration information to the server 204. The server 204 obtains the configuration information of the target report, obtains the field type corresponding to the configuration information based on the field attributes of the original table associated with the target report, and obtains the updated field information corresponding to the configuration information based on the field type. After that, the server 204 determines the table field information of the target physical table based on the updated field information, and creates the target physical table based on the table field information. After creating the target physical table, the data can be correspondingly extracted into the target physical table, so that when the user queries the configured report, the generated report can be obtained through the target physical table, reducing the number of database accesses. Among them, the terminal 202 can be, but is not limited to, various personal computers, laptops, smart phones, tablet computers, and portable wearable devices. The portable wearable device may be a smart watch, a smart bracelet, a head-mounted device, etc. The head-mounted device may be a virtual reality (VR) device, an augmented reality (AR) device, smart glasses, etc. The server 204 may be an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.

[0039] In an exemplary embodiment, Figure 3 As shown, a report data extraction method is provided, which is applied to Figure 1 The server in the example is used to illustrate, including the following steps 302 to 308. Among them:

[0040] Step 302: Obtain configuration information of the target report.

[0041] Among them, the target report is associated with multiple original tables. The configuration information in the target report can be the information obtained by the user configuring the target report based on the original fields in the original table. Exemplarily, when the user configures the target report, it is also necessary to configure the original table associated with the target report. After the association, the report items of the target report can be configured based on the original fields in the original table. Therefore, the configuration information includes the report configuration items configured by the user. The report configuration items are, for example, the report items in the report (such as row report items, column report items), and the filter items when querying the report. Optionally, the configuration information also includes the report type configured by the user. The report type is, for example, a table, a chart, etc. The table can be a summary table or a detailed table. The chart can be a bar chart, a line chart, a pie chart, etc.

[0042] Optionally, if the user creates multiple worksheets under the target report and configures these worksheets accordingly based on the original fields associated with the target report, the server can obtain the configuration information of the worksheet associated with the target report.

[0043] Step 304: Acquire the field type corresponding to the configuration information based on the field attributes of the original table, and acquire the updated field information corresponding to the configuration information based on the field type.

[0044] The updated field information may be information of all fields associated with the configuration information. For a report configuration item, multiple fields may be associated, so such a report configuration item needs to obtain information of all associated fields. For example, report configuration item A is obtained based on the sum of field C and field D of the original table B, then the server needs to obtain field information of field C and field D as the updated field information corresponding to report configuration item A.

[0045] Since the report configuration items in the target report are determined based on the original fields in the original table, the field type corresponding to the configuration information can be obtained based on the field attributes of the original table. For example, when the user configures the target report, the original fields of the original table are displayed on the terminal, and the user can select the configuration information of the target report from the original fields displayed on the terminal. Figure 4 As shown, the original field A is one of the row report items of the target report, and the user can drag the original field A to the row configuration box of the target report.

[0046] The field attributes of the original table refer to the field attributes of the original fields in the original table. The field attributes may be, for example, the type of the field, the identifier of the field, etc. The type of the field may be the control type of the field. Figure 5 As shown, the control types include basic data, text, integer, long integer, decimal, date, check box, drop-down, large text, etc. The field of the basic data type is a field with a unique identifier. The identifier of the field can be the field name of the field. Among them, the analysis object corresponds to the report.

[0047] Since it is necessary to determine the fields in the target physical table and the field information (such as length, data type, etc.) when creating the target physical table, the above information can be determined based on the field type corresponding to the configuration information. For example, if the field type is basic data, the corresponding field information can be bignit. In addition, for fields of certain field types, some additional processing may be required. For example, for a report configuration item whose field type is basic data, that is, the field type of the original field in the original table corresponding to the report configuration item is basic data, then it is necessary to obtain the update field information corresponding to the configuration information based on the field type. Optionally, the updated field information can be the field information of the original field and its corresponding identification field.

[0048] Step 306: determine table field information for creating a target physical table based on the updated field information, and create the target physical table based on the table field information.

[0049] Exemplarily, after obtaining the updated field information, the information of all fields associated with the target report is obtained. The server can determine the table field information based on the updated field information, and create the target physical table based on the table field information.

[0050] In some embodiments, after the user configures a report, a corresponding target model can be generated for the report. All information about the report can be obtained through the target model. Among them, the target model includes a target physical table. Optionally, the target model can also include a target association table, a multilingual table, a privacy table, etc. The target association table is used to store information about the original table associated with the report, which is convenient for subsequent problem location. The multilingual table is used to store information about multilingual fields, so that the multilingual table can be queried to obtain detailed information about the multilingual fields when it is needed.

[0051] Optionally, the table field information may be a table field type and a table field name. The server may determine the table field type and the table field name of the target physical table based on the updated field information, and then create the target physical table based on the table field type and the table field name.

[0052] Step 308: Obtain the field values ​​corresponding to the table field information from the data of the original table, and extract the field values ​​to the target physical table.

[0053] For example, after creating the target physical table, the server can obtain the field values ​​corresponding to the table field information from the data of the original table, and extract the field values ​​into the target physical table. In this way, when generating a report, the data of each cell in the report can be obtained through the target physical table without cross-table query.

[0054] In some embodiments, the field values ​​corresponding to the table field information may be extracted into the target physical table at regular intervals, so that the data in the target physical table has high real-time performance.

[0055] In the above report data extraction method, by obtaining the configuration information of the target report associated with multiple original tables, the field type corresponding to the configuration information is obtained based on the field attributes of the original table, and the update field information corresponding to the configuration information is obtained based on the field type, and then the table field information of the target physical table is determined based on the update field information, and the target physical table is created based on the table field information, and then the field value corresponding to the table field information is obtained from the data of the original table, and the field value is extracted to the target physical table, so that the data required by the report are stored in the change field information, and then stored in the target physical table, and the data in the report can be obtained through the target physical table when querying the report. Compared with querying from multiple original tables, the number of database accesses can be reduced, the query speed can be improved, and the report generation efficiency can be improved. In addition, by analyzing the configuration information of the report, the corresponding update field information is obtained based on the field type corresponding to the configuration information, the data integrity of the target physical table can be improved, so that the data required by the report can be obtained from the target physical table, thereby further improving the report query speed and the report generation efficiency.

[0056] In an exemplary embodiment, in order to improve the report query speed, in addition to the table fields corresponding to the report configuration items in the target physical table, for report configuration items whose field type is the target type, there is also an identification field corresponding to the report configuration item in the target physical table. For example, if the field type corresponding to the report configuration item is basic data, and the field belonging to the basic data is also a primary key in the original table, then when creating the target physical table, it is necessary to additionally generate an identification field for the report configuration item to store the unique identifier corresponding to the report configuration item, such as an ID value. Figure 6 As shown, the configuration information includes report configuration items, and obtaining the field type corresponding to the configuration information based on the field attributes of the original table includes steps 602 to 604. Among them:

[0057] Step 602: determine the original field corresponding to the report configuration item in the original table.

[0058] Step 604: Acquire the field type of the report configuration item based on the field attribute of the original field.

[0059] As mentioned above, the field properties of the original field include the field type of the original field, so after determining the original field corresponding to the report configuration item in the original table, the field type of the report configuration item can be obtained based on the field type of the original field. The field type can be a control type.

[0060] In actual applications, when configuring a report item for a report, the report item may be obtained by processing the fields in the original table. For example, the value of the report item may be obtained by the values ​​of multiple fields in the original table. Then the target physical table needs to store these fields to correctly obtain the value of the report item. Therefore, in some embodiments, Figure 7 As shown, determining the original field corresponding to the report configuration item in the original table includes steps 702 to 706. Among them:

[0061] Step 702: Obtain the configuration type of the report configuration item based on the configuration information.

[0062] Step 704: Obtain associated fields of the report configuration item based on the configuration type.

[0063] Step 706, determining the original field corresponding to the associated field in the original table.

[0064] Among them, the configuration type is used to configure the report configuration items. For example, the configuration types include initial configuration, calculation configuration, and functional configuration. Initial configuration means that the value of the report configuration item is directly obtained based on the corresponding original field. Calculation configuration means that the value of the report configuration item is obtained by performing some processing on the field value of the corresponding original field. Functional configuration means that some functional configurations are performed on the report configuration items, such as custom sorting configuration, field sorting configuration, drill-down configuration, date splitting configuration, header merging, and row and column transposition configuration. When the user performs the above functional configurations on the report configuration items, the associated fields associated with the configuration will be selected.

[0065] Exemplarily, the server can obtain the configuration type of the report configuration item based on the configuration information, so as to obtain the associated fields of the report configuration item based on the configuration type. For example, if the configuration type of the report configuration item is custom sorting, it is necessary to generate the sorting number field corresponding to the report configuration item in addition. If the configuration type of the report configuration item is field sorting, it is necessary to obtain the dependent field associated with the field sorting of the report configuration item. If the configuration type of the report configuration item is drill-down configuration, it is necessary to obtain the drill-down field associated with the report configuration item. If the configuration type of the report configuration item is date splitting, it is necessary to obtain the original date field corresponding to the report configuration item. If the configuration type of the report configuration item is calculation configuration, it is necessary to determine whether there is a non-aggregable calculation indicator for the calculation configuration. If so, it is necessary to obtain the dependent field associated with the report configuration item. For example, the report configuration item is a non-aggregable calculation field, and the report configuration item exists in the summary table. The value can only be calculated after querying and calculating, such as the "sum of age + sum of length of service" of some people. It cannot be found directly by querying and obtaining data, and it needs to be summarized and calculated in the code. "Age" and "Service Length" are two dependent fields of this non-aggregable calculation field. Therefore, in order to obtain the value of the report configuration item, it is necessary to store the field value of the "Age" field and the field value of the "Service Length" field in the target physical table, that is, the associated fields of the report configuration item. In some embodiments, if there is a header merge and row and column transposition configuration, the corresponding original fields also need to be obtained.

[0066] After determining the associated field corresponding to the report configuration item, the original field corresponding to the associated field in the original table can be determined, and then the field type of the associated field can be obtained based on the field properties of the original field.

[0067] In this embodiment, by obtaining the configuration type of the report configuration item based on the configuration information, obtaining the associated field of the report configuration item based on the configuration type, and determining the original field corresponding to the associated field in the original table, all fields corresponding to the report can be obtained, and the values ​​of the required fields can be stored in the target physical table, so that the data in the target physical table is highly rich, and the data in the report can be obtained based on the target physical table without cross-table query, thereby improving the query speed.

[0068] In some embodiments, some field information may need to be additionally configured for some specific types of fields. For example, for a field with a unique identifier, an additional column needs to be added to the target physical table to store the unique identifier. In this way, the speed of querying information in the target physical table can be improved based on these unique identifiers. Therefore, obtaining updated field information corresponding to the configuration information based on the field type may include the following steps: if the field type of the report configuration item belongs to the target type, the field information of the report configuration item is obtained based on the field attributes of the original field, and the identification field is configured for the report configuration item. The corresponding updated field information of the report configuration item is obtained based on the field information and the identification field of the report configuration item. If the field type of the associated field does not belong to the target type, the corresponding updated field information of the report configuration item is obtained based on the field attributes of the associated field.

[0069] Among them, the target type refers to a field type with a unique identifier. In this embodiment, if the field type is basic data, the field type belongs to the target type. The identification field is a field in the target physical table, and the field value corresponding to the identification field can be the unique identifier of the original field corresponding to the report configuration item. That is, when the field type of the report configuration item belongs to the target type, in addition to creating the table field corresponding to the report configuration item in the target physical table, it is also necessary to create a table field corresponding to the identification field corresponding to the report configuration item. At this time, the updated field information includes the field information of the report configuration item and the field information of the identification field corresponding to the report configuration item.

[0070] Optionally, when the field type of the associated field does not belong to the target type, after obtaining the update field information corresponding to the report configuration item based on the field properties of the associated field, the update field information may only be the information of the table field corresponding to the report configuration item, or it may be the information of the table field corresponding to all fields associated with the report configuration item.

[0071] In this embodiment, by determining the original field corresponding to the report configuration item in the original table, the field type of the report configuration item is obtained based on the field attributes of the original field. If the field type of the report configuration item belongs to the target type, the field information of the report configuration item is obtained based on the field attributes of the original field, and the report configuration item is configured with an identification field. Based on the field information of the report configuration item and the identification field, the corresponding updated field information of the report configuration item is obtained. In this way, the data of the target physical table can be expanded, and the speed and accuracy of subsequent report generation can be improved through the identification field.

[0072] It is understandable that if Figure 8As shown, due to the diversity of data, some data may be multilingual data. If there are multilingual fields in the report configuration items in the report, the target model needs to include a multilingual table. The detailed field values ​​of the multilingual fields are stored in the multilingual table, and only the default language field values ​​are stored in the target physical table. In this way, the data redundancy of the target physical table can be reduced and the structure of the target physical table can be streamlined. In some embodiments, if the field type corresponding to the configuration information belongs to a multilingual field and does not belong to the target type, the field attributes of the configuration information in the original table are obtained, and the updated field information corresponding to the configuration information is obtained based on the field attributes of the configuration information in the original table; the table field information of the multilingual table is determined based on the updated field information, and a multilingual table is created based on the table field information of the multilingual table and the table identification information of the target physical table. When the multilingual field is implemented, the basic data id to which the field belongs is additionally checked, and the multilingual table is found through the basic data id, and the multilingual field is implemented.

[0073] In this embodiment, by creating a multilingual table and storing the field information of the report configuration items whose field types correspond to the original fields and belong to the multilingual fields in the multilingual table, unnecessary data stored in the target physical table can be reduced. By creating an additional multilingual table to store more information of the report configuration items and storing the default information of such report configuration items in the target physical table, such as the field value corresponding to the default language, the data conciseness in the target physical table can be improved.

[0074] When extracting report data, there may be extraction failures, which may cause failures when users query reports. Figure 8 As shown, in order to locate report failures in a timely manner and discover problems in report generation, the target model may also include a target association table. The target association table is used to store information about the original table associated with the target physical table. For example, the table name or table identifier of the original table is stored. In this way, the problem can be located through the original table. Exemplarily, the server may obtain table identification information of multiple original tables associated with the report; store multiple table identification information and table identification information of the target physical table into the target association table, so as to locate the problem based on the target association table when a failure occurs in report extraction.

[0075] In this embodiment, by storing the table identification information of the target physical table and the table identification information of the original table associated with the report in the target association table, the problem can be located through the target association table, thereby improving the target model and enhancing the usability of the target model.

[0076] In a possible implementation, corresponding metadata may be generated for a report to store information of fields corresponding to the report, so as to facilitate management of field information that needs to be extracted. Exemplarily, the server generates metadata corresponding to the target physical table based on the updated field information, and the metadata is used to store the updated field information; based on the metadata, the field type and field name of the table field in the target physical table are obtained to create the target physical table based on the field type and field name of the table field.

[0077] In this embodiment, by using metadata to store updated field information corresponding to the report, it is convenient to manage the field information, and the field information stored in the target physical table can be quickly understood through the metadata.

[0078] Alternatively, if Fig. 9 As shown, the server generates metadata according to the following process:

[0079] 1. Create a metadata XML template.

[0080] 2. Call platform methods to generate design-time entity metadata.

[0081] 3. Call the platform method to generate design-time form metadata.

[0082] 4. Clear the child runtime metadata cache.

[0083] 5. Rebuild runtime metadata.

[0084] The generated metadata integrates the template metadata by default, which hides all buttons and basic information areas of the form and list. This ensures that no operations can be performed and no system data can be seen through the connection, thus avoiding permission issues.

[0085] After the user configures the report, the server generates metadata and target physical tables corresponding to the report. When the data extraction time arrives, the server can extract data from the original table to the target physical table, so that the data in the target physical table is highly real-time, reducing the occurrence of report data not being updated in a timely manner. However, if the user's configuration of the report changes, the metadata and target physical table corresponding to the report need to be regenerated. Exemplarily, if the update field information changes and metadata already exists, the metadata and target physical table need to be deleted; the target physical table corresponding to the data is regenerated based on the update field information, and the target physical table is regenerated based on the regenerated metadata.

[0086] In some embodiments, Fig.10As shown, if the field value of the original field corresponding to the report configuration item changes, the field value of the table field corresponding to the report configuration item in the target physical table is updated based on the changed original field. Optionally, based on the identification information of the metadata corresponding to the changed field, the data in the target physical table is updated. When the filter field in the configuration information of the target report changes, that is, the filter field of the report changes, it may cause an increase or decrease in data. Therefore, the data needs to be regenerated. Optionally, the report identifier is obtained based on the identification information of the metadata corresponding to the changed field, and then the data related to the changed field in the target physical table is deleted based on the report identifier. Then the data related to the changed field is re-inserted into the target physical table. In this way, incremental extraction of the target physical table can be achieved, the extraction time can be reduced, and the efficiency of data extraction can be improved.

[0087] In some embodiments, in order to reduce the number of database accesses and improve performance, the amount of data extracted each time can be ensured to be consistent during the process of extracting data to the target physical table. Fig.11 As shown, 1000 main entity IDs (report IDs) are transmitted through the data transmission service. Since these reports may be associated with multiple original tables, the amount of data generated is unknown. If the data generated by the association of these 1000 main entities is directly queried in full, it may cause failures, so paginated queries are required. Simply define 10,000 data to be queried per page. When the number of columns is too large, such as 100, 1 million cell data will be queried each time, and the memory pressure will be very high. Define 1000 data to be queried per page. When the amount of data is too large, such as 100,000 data, it is necessary to query the database 100 times, and the database access is too frequent. Therefore, in this embodiment, the server queries the cell data volume C (for example, 100,000) each time, and the number of queries each time P = cell data volume C / (number of fields in the target physical table) N, to ensure that the amount of data queried each time is basically the same, which will not cause excessive memory pressure, but also reduce the number of database accesses and improve performance.

[0088] It is understandable that when the user needs to generate a report after configuring the report, that is, when querying the data in the report to obtain the report, the server can obtain the table field and the field value of the table field corresponding to the report configuration item in the report based on the target physical table; determine the value of the report item based on the table field and the field value of the table field, and send the report item and the value of the report item to the terminal to display the data of the report on the terminal. Optionally, the configuration information of the report includes a filter field. When the user needs to generate a report, the report information (such as a report identifier) ​​and the filter field are sent to the server. The server determines the target physical table based on the report information, and then obtains the table field and the corresponding field value corresponding to the report item in the report from the target physical table based on the filter field; then, the corresponding report item value can be determined based on the table field and the field value of the table field, and the report item and the value of the report item can be sent to the terminal, so that the target report required by the user can be displayed on the terminal. By querying the report data in the target physical table, the report generation efficiency can be improved, the number of database accesses can be reduced, and the security of the database can be improved.

[0089] In some embodiments, Fig.12 As shown in the figure, the data required for the report, that is, the data required for the target physical table, is extracted from different databases through DTS (Data Transfer Service) and incremental synchronization. If the amount of data of the report to be extracted is in the first case, the target physical table can be stored in the middle platform library. If the amount of data of the extracted report is in the second case, a new library can be applied for and the target physical table can be stored in the new library. If the amount of data of the extracted report is in the third case, table partitioning can be performed. The amount of data of the extracted report is in the fourth case. Since the amount of report data is too large, querying the report may affect other businesses. In this way, physical isolation of the database can be performed. Among them, the amount of data corresponding to the fourth case is greater than the amount of data corresponding to the third case, which is greater than the amount of data corresponding to the second case, which is greater than the amount of data corresponding to the first case. In this way, the storage method of the table is determined based on the situation of the target physical table, which reduces the impact of the query on other businesses when querying the physical table during report generation, and improves the robustness of database queries.

[0090] In an exemplary embodiment, in order to improve the efficiency of report query and generation, the configuration information of the target report can be obtained, and the target report is associated with multiple original tables; the configuration type of the report configuration item is obtained based on the configuration information; the associated field of the report configuration item is obtained based on the configuration type; the original field corresponding to the associated field in the original table is determined; and the field type of the report configuration item is obtained based on the field attributes of the original field.

[0091] If the field type of the report configuration item belongs to the target type, the field information of the report configuration item is obtained based on the field properties of the original field, and the identification field is configured for the report item; based on the field information and identification field of the report configuration item, the corresponding updated field information of the report configuration item is obtained.

[0092] If the field type of the associated field does not belong to the target type, the update field information corresponding to the report item is obtained based on the field properties of the associated field.

[0093] Optionally, the target model created for the report may include a multi-language table and a target association table in addition to the target physical table. For a multi-language table, if the field type corresponding to the configuration information belongs to a multi-language field and does not belong to the target type, the field attributes of the configuration information in the original table are obtained, and the updated field information corresponding to the configuration information is obtained based on the field attributes of the configuration information in the original table; the table field information of the multi-language table is determined based on the updated field information, and the multi-language table is created based on the table field information of the multi-language table and the table identification information of the target physical table. In order to obtain the target association table, the table identification information of multiple original tables associated with the report may be obtained; the multiple table identification information and the table identification information of the target physical table are stored in the target association table, so as to locate the problem based on the target association table when a failure occurs in report extraction. Optionally, the target model may also include a privacy table. Figure 8 Can be Figure 1 The target model for the report shown. Figure 8 The main table in the table is the target physical table. The server stores all the fields used for query and calculation in the main table, which reduces the association of physical tables when querying and retrieving data. The report configuration items whose field type is the basic data (target type) are implemented according to the field attributes of the corresponding original field, and an additional identification field (ID field) is implemented. Other attributes that are not the target type are implemented according to the field attributes, and all attributes are pulled together into one layer. The identification information of the associated intermediate table is implemented in the id relationship table, and the main table is associated with the main table id (not used in report queries, the combined primary key determines the uniqueness of the data, and locates analysis problems). Multi-language fields other than specific types (such as names) are implemented in multi-language tables, and the default language is stored in the main table.

[0094] like Fig.13 As shown, when the scheduled task starts, all reports that need to be extracted are marked with extraction marks. That is, data is extracted to the target physical table. If the extraction is in progress, real-time data is checked during query. If the report is a virtual entity or file data source analysis object, report data extraction is not supported.

[0095] Through the report identifier, query the specific three metadata to obtain the row field (or category axis), column field (or value axis, legend), and filter field of the report configuration. Then obtain the report metadata through the report identifier to obtain whether the plug-in on the metadata implements a specific interface method. If the method is implemented, additionally extract the analysis object field (secondary filter field) returned by the interface method. After obtaining the extracted field information (updated field information), create the target physical table and extract data to the target physical table.

[0096] Before extraction, you can preset an application to store metadata generated by report extraction. The generated metadata code is "application code_8-bit random number". Before generating metadata, check whether the metadata code is repeated. Then generate metadata, generate a physical table based on the metadata, and save the extracted field information.

[0097] The extracted fields are then encapsulated as report row fields, and data is queried in batches of 100,000 cells.

[0098] When encapsulating report row fields, you can first obtain the original report field B corresponding to the extracted field A, create a new row field object C, and then set the value of the original report field B to the row field C to prevent data errors caused by redundant field information. After the extraction is completed, change the extraction flag to completed.

[0099] Through the above target model, multiple entities are aggregated into a single entity, that is, the information of multiple original tables is aggregated into one physical table, which can speed up report query speed and thus improve report generation efficiency.

[0100] In some embodiments, a terminating function may be provided: during data extraction, extraction may be terminated, and after termination, data is no longer extracted. When storing data, whether to continue storing is determined based on the status field set when performing the extraction termination operation. If it is terminated, storage is stopped.

[0101] In some embodiments, the server also provides a disaster recovery compensation mechanism. If the extraction status is abnormal due to force majeure factors such as server crash, shutdown and restart, network paralysis, etc., when the next normal extraction occurs, it is determined whether the previous extraction was abnormal. If it was abnormal, the extraction will be normal, otherwise it will be skipped.

[0102] In some embodiments, the server is provided with a dirty data scanning and repairing function. If force majeure occurs, there will be a scheduled task to repair the dirty data. The specific details are as follows: the metadata and table information generated by all data extraction are obtained from the metadata table, and then it is judged based on whether the metadata code is bound to the report. If not bound, the data is recorded in the dirty data record table. The next time the scheduled task is executed, if it has not been bound, the metadata and table (dirty data) are deleted, otherwise the record is deleted from the dirty data record table (mis-scan).

[0103] It should be understood that, although the steps in the flowcharts involved in the above embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0104] Based on the same inventive concept, the embodiment of the present application also provides a report data extraction device for implementing the report data extraction method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in one or more report data extraction device embodiments provided below can refer to the limitations of the report data extraction method above, and will not be repeated here.

[0105] In an exemplary embodiment, Fig.14 As shown, a report data extraction device is provided, including: a configuration information acquisition module 1401, a field information acquisition module 1402, a table information acquisition module 1403 and an extraction module 1404, wherein:

[0106] Configuration information acquisition module 1401, used to acquire configuration information of each worksheet in the report, where the report is associated with multiple original tables;

[0107] A field information acquisition module 1402, used for acquiring a field type corresponding to the configuration information based on the field attribute of the original table, and acquiring updated field information corresponding to the configuration information based on the field type;

[0108] The table information acquisition module 1403 is used to determine the table field information of the target physical table based on the updated field information, and create the target physical table based on the table field information;

[0109] The extraction module 1404 is used to obtain field values ​​corresponding to table field information from data of multiple original tables, so as to extract the field values ​​into a target physical table.

[0110] In some embodiments, the field information acquisition module 1402 is also used to determine the original field corresponding to the report configuration item in the original table; obtain the field type of the report configuration item based on the field attributes of the original field; if the field type of the report configuration item belongs to the target type, obtain the field information of the report configuration item based on the field attributes of the original field, and configure the identification field for the report item; obtain the corresponding updated field information of the report configuration item based on the field information and identification field of the report configuration item.

[0111] In some embodiments, the field information acquisition module 1402 is also used to obtain the configuration type of the report configuration item based on the configuration information; obtain the associated field of the report configuration item based on the configuration type; determine the original field corresponding to the associated field in the original table; if the field type of the associated field does not belong to the target type, then obtain the updated field information corresponding to the report item based on the field properties of the associated field.

[0112] In some embodiments, the report data extraction device also includes a multi-language table generation module, which is used to obtain the field attributes of the configuration information in the original table if the field type corresponding to the configuration information belongs to a multi-language field and does not belong to the target type, and obtain the updated field information corresponding to the configuration information based on the field attributes of the configuration information in the original table; determine the table field information of the multi-language table based on the updated field information, and create a multi-language table based on the table field information of the multi-language table and the table identification information of the target physical table.

[0113] In some embodiments, the table information acquisition module 1403 is also used to generate metadata corresponding to the target physical table based on the updated field information, and the metadata is used to store the updated field information; based on the metadata, the field type and field name of the table field in the target physical table are obtained to create the target physical table based on the field type and field name of the table field.

[0114] In some embodiments, the table information acquisition module 1403 is also used to delete the metadata and the target physical table if the updated field information changes and metadata already exists; regenerate the metadata corresponding to the target physical table based on the updated field information, and regenerate the target physical table based on the regenerated metadata.

[0115] In some embodiments, the report data extraction device also includes an association table generation module, which is used to obtain table identification information of multiple original tables associated with the report; store multiple table identification information and table identification information of the target physical table into a target association table, so as to locate the problem based on the target association table when a report extraction failure occurs.

[0116] In some embodiments, the report data extraction device also includes a data query module, which is used to obtain the table fields and field values ​​of the table fields corresponding to the report items in the report based on the target physical table when the user queries the report data; determine the numerical value of the report item based on the table fields and the field value of the table fields, and send the report item and the numerical value of the report item to the terminal to display the report data on the terminal.

[0117] Each module in the above-mentioned report data extraction device can be implemented in whole or in part by software, hardware or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute the operations corresponding to each module.

[0118] In an exemplary embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as shown in FIG. Fig.15 As shown. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data such as data of the original table, data of the target physical table, and configuration information corresponding to the report. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a report data extraction method is implemented.

[0119] In an exemplary embodiment, a computer device is provided. The computer device may be a terminal, and its internal structure diagram may be as shown in FIG. Fig.16As shown. The computer device includes a processor, a memory, an input / output interface, a communication interface, a display unit and an input device. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface, the display unit and the input device are connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The input / output interface of the computer device is used to exchange information between the processor and the external device. The communication interface of the computer device is used to communicate with an external terminal in a wired or wireless manner, and the wireless manner can be implemented through WIFI, a mobile cellular network, near field communication (NFC) or other technologies. When the computer program is executed by the processor, a report data extraction method is implemented. The display unit of the computer device is used to form a visually visible picture, which can be a display screen, a projection device or a virtual reality imaging device. The display screen can be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer device can be a touch layer covering the display screen, or a button, trackball or touchpad set on the computer device shell, or an external keyboard, touchpad or mouse.

[0120] Those skilled in the art will understand that Fig.15 and Fig.16 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0121] In an exemplary embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps in the above-mentioned method embodiments when executing the computer program.

[0122] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps in the above-mentioned method embodiments are implemented.

[0123] In one embodiment, a computer program product is provided, including a computer program, which implements the steps in the above method embodiments when executed by a processor.

[0124] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0125] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0126] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0127] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A report data extraction method, characterized in that: The method comprises: Acquire configuration information of a target report, where the target report is associated with multiple original tables; Acquire the field type corresponding to the configuration information based on the field attribute of the original table, and acquire the updated field information corresponding to the configuration information based on the field type; Determine table field information for creating a target physical table based on the update field information, and create the target physical table based on the table field information; The field value corresponding to the table field information is obtained from the data of the original table, and the field value is extracted to the target physical table.

2. The method according to claim 1, characterized in that The configuration information includes report configuration items, and the acquiring the field type corresponding to the configuration information based on the field attribute of the original table includes: Determine the original field corresponding to the report configuration item in the original table; Acquire the field type of the report configuration item based on the field attribute of the original field; Determining the original field corresponding to the report configuration item in the original table includes: Based on the configuration information, a configuration type of the report configuration item is obtained; based on the configuration type, an associated field of the report configuration item is obtained; and an original field corresponding to the associated field in the original table is determined.

3. The method according to claim 2, characterized in that The acquiring, based on the field type, the updated field information corresponding to the configuration information includes: If the field type of the report configuration item belongs to the target type, the field information of the report configuration item is obtained based on the field attribute of the original field, and an identification field is configured for the report configuration item; Acquire update field information corresponding to the report configuration item based on the field information of the report configuration item and the identification field; If the field type of the associated field does not belong to the target type, the update field information corresponding to the report configuration item is obtained based on the field attribute of the associated field.

4. The method according to claim 3, characterized in that The method further comprises: If the field type corresponding to the configuration information belongs to a multilingual field and does not belong to the target type, obtaining the field attributes of the configuration information in the original table, and obtaining the updated field information corresponding to the configuration information based on the field attributes of the configuration information in the original table; Table field information of a multi-language table is determined based on the update field information, and the multi-language table is created based on the table field information of the multi-language table and the table identification information of the target physical table.

5. The method according to claim 1, characterized in that The determining the table field information of the target physical table based on the updated field information includes: Generate metadata corresponding to the target physical table based on the updated field information, where the metadata is used to store the updated field information; The field type and field name of the table field in the target physical table are obtained based on the metadata, so as to create the target physical table based on the field type and the field name of the table field.

6. The method according to claim 5, characterized in that The step of acquiring the field type corresponding to the configuration information based on the field attribute of the original table and acquiring the updated field information corresponding to the configuration information based on the field type comprises: If the update field information is changed and the metadata already exists, deleting the metadata and the target physical table; The metadata corresponding to the target physical table is regenerated based on the updated field information, and the target physical table is regenerated based on the regenerated metadata.

7. The method according to claim 1, characterized in that The method further comprises: Acquire table identification information of a plurality of original tables associated with the report; The table identification information of the plurality of tables and the table identification information of the target physical table are stored in a target association table, so as to locate the problem based on the target association table when a failure occurs in report extraction.

8. The method according to any one of claims 1 to 7, characterized in that: The step of extracting the field value to the target physical table includes: When a user queries the data of the report, the table fields corresponding to the report configuration items in the report and the field values ​​of the table fields are obtained based on the target physical table; The value of the report configuration item is determined based on the table field and the field value of the table field, and the report configuration item and the value of the report configuration item are sent to a terminal to display the data of the report on the terminal.

9. A report data extraction device, characterized in that: The device comprises: A configuration information acquisition module, used to acquire configuration information of a target report, wherein the target report is associated with multiple original tables; A field information acquisition module, used to acquire the field type corresponding to the configuration information based on the field attributes of the original table, and acquire the updated field information corresponding to the configuration information based on the field type; A table information acquisition module, used to determine table field information of a target physical table based on the update field information, and create the target physical table based on the table field information; The extraction module is used to obtain the field value corresponding to the table field information from the data of the original table, so as to extract the field value to the target physical table.

10. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 8 are implemented.

11. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 8 are implemented.

12. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 8 are implemented.

Images