Data export method, device, equipment and computer readable storage medium

Abstract

The present application provides a data export method, device, equipment and computer-readable storage medium. The method includes: configuring parameters related to the data export operation of a multidimensional database, including the type of the target relational database, determining a matching proprietary client development library according to the type, and establishing a connection relationship between the multidimensional database and the target relational database through the development library; creating a fact data table of the target relational database according to the dimension structure of the multidimensional data cube of the multidimensional database, and creating a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional cube; based on the connection relationship, exporting the attribute data of each dimension member of the multidimensional cube to the dimension table in the target relational library, and exporting the business data of the multidimensional cube to the fact data table in the target relational library. According to the embodiment of the present application, the data export efficiency of the multidimensional database is improved, and manual operation errors in the data export process are avoided.

Description

Technical Field

[0001] The present application belongs to the technical field of multi-dimensional online analytical processing, and in particular, relates to a data export method, device, equipment and computer-readable storage medium. Background Art

[0002] Multidimensional database is the carrier of multidimensional online analytical processing technology (MOLAP). According to the data storage method, it can usually be divided into traditional multidimensional database and in-memory multidimensional database.

[0003] Among them, the traditional multidimensional database stores data based on disk files. When calculating massive data, frequent disk reading and writing will occur. The performance of disk reading and writing in traditional multidimensional databases is much lower than the performance of data reading and writing in memory. Therefore, the computing efficiency of traditional multidimensional databases will be greatly reduced by disk reading and writing. The in-memory multidimensional database is a multidimensional database based on in-memory computing. All data resides in memory. Among them, in-memory computing has high requirements for the memory resource configuration of the server, and the current price of memory is relatively expensive. Based on this, how to export data from a multidimensional database to save memory resources has become a problem of concern to technicians in this field.

[0004] In the related art, the data export methods provided by multidimensional databases mainly include the following two methods: (1) exporting the data in the multidimensional database to a disk file. (2) exporting the data in the multidimensional database to a single fact table pre-built in a third-party relational database through the Open Database Connectivity (ODBC) method.

[0005] However, when the above method (1) is adopted, the export efficiency is low because the disk file needs to be operated; and the data on the disk file is cold data, which completely loses its analytical value without being loaded into the database. When the above method (2) is adopted, although the analytical value of the exported data can be retained, this method requires manual creation of relational database tables in the relational database before the multidimensional database export operation, which is prone to the risk of manual operation errors; secondly, this method uses the ODBC general interface, resulting in low data processing performance.

[0006] Therefore, how to improve the data export efficiency of a multidimensional database and reduce the risk of manual operation errors during data export are technical problems that technical personnel in this field urgently need to solve. Summary of the invention

[0007] The embodiments of the present application provide a data export method, apparatus, device and computer-readable storage medium, which can improve the data export efficiency of a multidimensional database and avoid manual operation errors during the data export process.

[0008] In a first aspect, an embodiment of the present application provides a data export method, the method comprising:

[0009] Configure parameters related to data export operations of a multidimensional database, including the type of the target relational database;

[0010] Determine a proprietary client development library that matches the type of the target relational database according to the type of the target relational database, and establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library;

[0011] Creating a fact data table of a target relational database according to the dimension structure of a multidimensional data cube of the multidimensional database, and creating a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube;

[0012] Based on the connection relationship, the preset data export operation is executed to export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relational library;

[0013] Based on the connection relationship, the preset data export operation is executed to export the business data of the multidimensional data cube to the fact data table in the target relational library.

[0014] Optional parameters related to the data export operation of the multidimensional database, including: the name of the multidimensional data cube of the multidimensional database, the IP address of the target relational database, the listening port of the target relational database, the user name of the target relational database, the password of the target relational database, the data volume threshold parameter of the multidimensional database and the default batch value of the data export operation. The data volume threshold parameter applies to the data volume of a single dimension member attribute and the business data volume of the multidimensional cube.

[0015] Optionally, creating a fact data table of a target relational database according to the dimension structure of a multidimensional data cube of the multidimensional database, and creating a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube, includes:

[0016] Automatically analyze the dimensional structure of the multidimensional data cube of the multidimensional database to obtain analysis results;

[0017] Based on the preset data volume threshold parameter, determine whether the data volume of the multidimensional data cube is a large data volume, or whether the data volume of the member attributes of a single dimension is a large data volume;

[0018] If yes, a temporary relational database table for testing the optimal batch size is automatically created based on the analysis results. The temporary relational database table has multiple tables. The structure of the temporary relational database table for testing the optimal batch size for exporting member attribute data of a single dimension is the same as that of the dimension table; the structure of the temporary relational database table for testing the optimal batch size for exporting business data is the same as that of the fact data table.

[0019] Automatically create fact tables in the target relational database based on the analysis results;

[0020] Determine whether all dimensions of the multidimensional data cube have been analyzed;

[0021] If not, then analyze the member attribute structure of a single dimension of the multidimensional data cube;

[0022] According to the analysis results of the member attribute structure, a dimension table of a single dimension of the target relational database is automatically created.

[0023] Optionally, based on the connection relationship, a preset data export operation is performed to export the attribute data and business data of each dimension member of the multidimensional data cube to the dimension table and the fact data table respectively, including:

[0024] According to the number of cell values ​​of the multidimensional data cube or the number of members of a single dimension, a comparison is made with a pre-configured data volume threshold parameter to determine whether the total business data volume of the multidimensional cube to be exported or the attribute data volume of a single dimension exceeds the preset threshold;

[0025] If so, perform multiple batch data export operations with different batch sizes for part of the data, import part of the data into a pre-created temporary relational database table, compare the export times of different batch sizes to obtain an optimal batch size, and use the optimal batch size as the first batch size of the data export operation;

[0026] If not, the second batch size is set according to the pre-configured default batch value; that is, the first batch size is the batch size for the best export performance when the data volume is large, and the second batch size is the default configured batch size when the data volume is small;

[0027] Based on the connection relationship, the data export operation is performed according to the first batch size or the second batch size, and the attribute data of the dimension members and the business data of the multidimensional data cube are exported to the dimension table and the fact data table respectively.

[0028] Optionally, the method further includes: deleting a pre-created temporary relational database table.

[0029] Optionally, compare the export times for different batch sizes to find the best batch size, including:

[0030] Determine the initial batch size and maximum batch value;

[0031] Determine whether the first test batch value of the current test is less than or equal to the maximum batch value;

[0032] If yes, the number of batches for this test is calculated according to the preset test data volume and the first test batch value, and the export is performed to obtain the export result;

[0033] Determine the total time consumed to export a preset amount of test data under the first test batch value according to the export result;

[0034] Incrementing the initial batch size to obtain a second test batch value, and determining whether the second test batch value is less than or equal to the maximum batch value, until all valid batch values ​​are tested;

[0035] Record the export time corresponding to each valid batch value and compare them to get the shortest export time;

[0036] Determine the value of the first batch size based on the shortest export time.

[0037] Optionally, the method further includes:

[0038] Determine whether all attribute data of dimension members or business data of multidimensional data cubes are exported;

[0039] If not, the data export operation continues according to the first batch size or the second batch size until all the business data and member attribute data are exported.

[0040] In a second aspect, an embodiment of the present application provides a data export device, including:

[0041] A configuration module, used to configure parameters related to the data export operation of the multidimensional database, the parameters including the type of the target relational database;

[0042] A connection module, used to determine a proprietary client development library matching the type of the target relational database according to the type of the target relational database, and to establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library;

[0043] A creation module, used to create a fact data table of a target relational database according to the dimension structure of a multidimensional data cube of the multidimensional database, and to create a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube;

[0044] The export module is used to execute the preset data export operation based on the connection relationship, and export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relationship library; based on the connection relationship, execute the preset data export operation, and export the business data of the multidimensional data cube to the fact data table in the target relationship library.

[0045] Optional parameters related to the data export operation of the multidimensional database, including: the name of the multidimensional data cube of the multidimensional database, the IP address of the target relational database, the listening port of the target relational database, the user name of the target relational database, the password of the target relational database, the data volume threshold parameter of the multidimensional database and the default batch value of the data export operation. The data volume threshold parameter applies to the data volume of a single dimension member attribute and the business data volume of the multidimensional cube.

[0046] Optionally, create modules to:

[0047] Automatically analyze the dimensional structure of the multidimensional data cube of the multidimensional database to obtain analysis results;

[0048] Based on the preset data volume threshold parameter, determine whether the data volume of the multidimensional data cube is a large data volume, or whether the data volume of the member attributes of a single dimension is a large data volume;

[0049] If yes, a temporary relational database table for testing the optimal batch size is automatically created based on the analysis results. The temporary relational database table has multiple tables. The structure of the temporary relational database table for testing the optimal batch size for exporting member attribute data of a single dimension is the same as that of the dimension table; the structure of the temporary relational database table for testing the optimal batch size for exporting business data is the same as that of the fact data table.

[0050] Automatically create fact tables in the target relational database based on the analysis results;

[0051] Determine whether all dimensions of the multidimensional data cube have been analyzed;

[0052] If not, then analyze the member attribute structure of a single dimension of the multidimensional data cube;

[0053] According to the analysis results of the member attribute structure, a dimension table of a single dimension of the target relational database is automatically created.

[0054] Optionally, export modules for:

[0055] According to the number of cell values ​​of the multidimensional data cube or the number of members of a single dimension, a comparison is made with a pre-configured data volume threshold parameter to determine whether the total business data volume of the multidimensional cube to be exported or the attribute data volume of a single dimension exceeds the preset threshold;

[0056] If so, perform multiple batch data export operations with different batch sizes for part of the data, import part of the data into a pre-created temporary relational database table, compare the export times of different batch sizes to obtain an optimal batch size, and use the optimal batch size as the first batch size of the data export operation;

[0057] If not, the second batch size is set according to the pre-configured default batch value; that is, the first batch size is the batch size for the best export performance when the data volume is large, and the second batch size is the default configured batch size when the data volume is small;

[0058] Based on the connection relationship, the data export operation is performed according to the first batch size or the second batch size, and the attribute data of the dimension members and the business data of the multidimensional data cube are exported to the dimension table and the fact data table respectively.

[0059] Optionally, the device further includes: a deletion module, used to delete the pre-created temporary relational database table.

[0060] Optionally, export the module, also used to:

[0061] Determine the initial batch size and maximum batch value;

[0062] Determine whether the first test batch value of the current test is less than or equal to the maximum batch value;

[0063] If yes, the number of batches for this test is calculated according to the preset test data volume and the first test batch value, and the export is performed to obtain the export result;

[0064] Determine the total time consumed to export a preset amount of test data under the first test batch value according to the export result;

[0065] Incrementing the initial batch size to obtain a second test batch value, and determining whether the second test batch value is less than or equal to the maximum batch value, until all valid batch values ​​are tested;

[0066] Record the export time corresponding to each valid batch value and compare them to get the shortest export time;

[0067] Determine the value of the first batch size based on the shortest export time.

[0068] Optionally, export the module, also used to:

[0069] Determine whether all attribute data of dimension members or business data of multidimensional data cubes are exported;

[0070] If not, the data export operation continues according to the first batch size or the second batch size until all the business data and member attribute data are exported.

[0071] In a third aspect, an embodiment of the present application provides a data export device, the data export device comprising: a processor and a memory storing computer program instructions;

[0072] When the processor executes the computer program instructions, the data export method shown in the first aspect is implemented.

[0073] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, on which computer program instructions are stored. When the computer program instructions are executed by a processor, the data export method shown in the first aspect is implemented.

[0074] The data export method, device, equipment and computer-readable storage medium of the embodiment of the present application can, on the one hand, determine a proprietary client development library that matches the type of the target relational database, and establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library, and then perform a preset data export operation based on the connection relationship, export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relational library, and export the business data of the multidimensional data cube to the fact data table in the target relational library. In this way, compared with the method of exporting data through ODBC driver access in the related art, the data export efficiency of the multidimensional database can be improved.

[0075] On the other hand, the embodiments of the present application can automatically create fact data tables of the target relational database based on the dimension structure of the multidimensional data cube of the multidimensional database, and automatically create dimension tables of the target relational database based on the member attribute structure of the dimensions constituting the multidimensional data cube. There is no need for a relational database administrator to analyze the source multidimensional data cube structure and the member attribute structure, and there is no need to manually create relational database tables, thereby avoiding manual operation errors during the data export process. BRIEF DESCRIPTION OF THE DRAWINGS

[0076] In order to more clearly illustrate the specific implementation methods of the present invention or the technical solutions in the prior art, the drawings required for use in the specific implementation methods or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some implementation methods of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0077] Figure 1 It is a flowchart of a data export method provided by an embodiment of the present application;

[0078] Figure 2 It is a schematic diagram of a parameter configuration interface related to a data export operation of a multidimensional database provided by an embodiment of the present application;

[0079] Figure 3 It is a flowchart of establishing a multidimensional database and connecting it to a target relational database provided by an embodiment of the present application;

[0080] Figure 4 It is a flowchart of creating a fact data table and a dimension table provided by an embodiment of the present application;

[0081] Figure 5 It is a schematic diagram of a dimensional structure of an exemplary multidimensional database source Cube provided by an embodiment of the present application;

[0082] Figure 6 is a schematic diagram of an exemplary creation of a fact data table provided by an embodiment of the present application;

[0083] Figure 7 It is a schematic diagram of a member attribute structure of a model dimension of an exemplary multidimensional database provided by an embodiment of the present application;

[0084] Figure 8 It is a flowchart of creating a model dimension table of a target relational database provided by an embodiment of the present application;

[0085] Fig. 9 It is a flowchart of exporting business data and member attribute data of a multidimensional data cube to a fact data table and a dimension table provided by an embodiment of the present application;

[0086] Fig.10 It is a schematic diagram of a process for obtaining an optimal batch size provided by an embodiment of the present application;

[0087] Fig.11 is a structural schematic diagram of a data export device provided by an embodiment of the present application;

[0088] Fig.12 It is a structural diagram of a data export device provided by an embodiment of the present application. DETAILED DESCRIPTION

[0089] The features and exemplary embodiments of various aspects of the present application will be described in detail below. In order to make the purpose, technical solutions and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings and specific embodiments. It should be understood that the specific embodiments described herein are only intended to explain the present application, rather than to limit the present application. For those skilled in the art, the present application can be implemented without the need for some of these specific details. The following description of the embodiments is only to provide a better understanding of the present application by illustrating the examples of the present application.

[0090] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the statement "include..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0091] In order to solve the risk of manual operation errors and low data processing performance when using relevant data export methods, the embodiments of the present application provide a data export method, device, equipment and computer-readable storage medium. The data export method provided by the embodiments of the present application is first introduced below.

[0092] Figure 1 FIG. 1 is a flow chart of a data export method provided by an embodiment of the present application. Figure 1 As shown, the data export method includes the following steps S001 to S005:

[0093] S001. Configure parameters related to the data export operation of the multidimensional database, the parameters including the type of the target relational database.

[0094] In one embodiment, parameters related to the data export operation of a multidimensional database include: the name of the multidimensional data cube of the multidimensional database, the IP address of the target relational database, the listening port of the target relational database, the user name of the target relational database, the password of the target relational database, the data volume threshold parameter of the multidimensional database and the default batch value of the data export operation, and the data volume threshold parameter applies to the data volume of a single dimension member attribute and the business data volume of the multidimensional cube.

[0095] For example, Figure 2 As shown, it is a schematic diagram of a parameter configuration interface related to the data export operation of a multidimensional database provided in an embodiment of the present application. The parameters of the parameter configuration interface are used to indicate that the multidimensional data cube Cube data of the multidimensional database is exported to the corresponding relational database, wherein the parameters of the parameter configuration interface can be divided into three configuration item groups, namely, the multidimensional database data source, the target relational database and batch configuration from top to bottom.

[0096] The configuration item group of the multidimensional database data source may include the name of the multidimensional data cube Cube of the multidimensional database. In the embodiment of the present application, the Cube name may be configured as Sales, for example.

[0097] The configuration item group of the target relational database may include the type of the target relational database ( Figure 2 Database type), IP address of the target relational database ( Figure 2 The target relational database listening port ( Figure 2 database port), the user name of the target relational database ( Figure 2 The target relational database password ( Figure 2 For example Figure 2 As shown, in one application embodiment, the type of the target relational database can be configured as Oracle, the IP address of the target relational database can be configured as 192.168.12.233; the listening port of the target relational database can be configured as 1521, for example, and the user name of the target relational database can be configured as exp-user, etc.

[0098] The configuration item group of batch configuration may include threshold parameters for the size of multidimensional data cube data or the size of member attribute data of a single dimension ( Figure 2 The default batch size for data export operations and other parameter information.

[0099] The threshold parameter can be used to determine the amount of data in the source Cube or the amount of member attribute data in a single dimension during the data export operation. The unit is the number of Cube cell values ​​or the number of single dimension members.

[0100] The default batch value refers to the default batch size when exporting small amounts of data; batch refers to the size of a batch when exporting in batches, and the unit of batch is the number of cell values. For example, if the data of a cube is divided into multiple batches for export, and each batch of data is exported at one time, then the batch is the number of cell values ​​exported in each batch.

[0101] In one embodiment, Figure 2 As shown, the data volume threshold can be configured as 1000000, and the default batch size can be configured as 1000, for example.

[0102] It should be noted that the parameter information of each configuration item group listed above is only an exemplary description of the embodiment of the present application and does not impose any limitation on the embodiment of the present application.

[0103] S002. Determine a proprietary client development library that matches the type of the target relational database according to the type of the target relational database, and establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library.

[0104] Proprietary client development library, also known as relational database specific client development library, usually each type of relational database can release its own proprietary client development library for application development, for example, Oracle database has released a proprietary client development library, ClickHouse database has also released a proprietary client development library. This client development library is different from the general ODBC driver. This proprietary client development library can use the native access interface API unique to the relational database that matches the client development library type, which can provide higher performance than the general ODBC driver.

[0105] In practical applications, such as Figure 3 As shown, when establishing a connection relationship, the configuration information of the data export operation can be loaded, and then the type of the target relational database is determined according to the exported parameter configuration information, and the proprietary client development library matching the type of the target relational database is loaded according to the type of the target relational database, and finally the connection relationship between the multidimensional database and the target relational database is established through the proprietary client development library.

[0106] Among them, when loading the proprietary client development library, the embodiment of the present application can load the proprietary client development library matching the type from the storage space storing each proprietary client development library based on the preset specific client selection mechanism of the target relational database according to the type of the target relational database. Optionally, the storage space is used to store specific proprietary API proprietary client development libraries of various types of relational databases. For example, Figure 3 As shown, the storage space can include proprietary client development libraries corresponding to relational databases such as Oracle, ClickHouse, and Greenplum.

[0107] S003. Create a fact data table of the target relational database according to the dimension structure of the multidimensional data cube of the multidimensional database, and create a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube.

[0108] In the embodiment of the present application, the method provided by S003 is adopted. On the one hand, the relational database administrator can be exempted from the process of analyzing the source cube structure and the dimension member attribute structure, and the process of manually creating the relational database table, thereby improving the integration of the data export operation, simplifying the process of the data export operation, and reducing the error probability caused by manual operation. On the other hand, the fact table and the dimension table can be created at the same time during the creation process, thereby ensuring the integrity of the source data of the export operation and improving the analytical value of the exported data in the relational database.

[0109] like Figure 4 As shown, in the embodiment of the present application, the fact data table and the dimension table can be created through the following steps S401 to S407:

[0110] S401. Automatically analyze the dimensional structure of a multidimensional data cube of a multidimensional database to obtain analysis results.

[0111] like Figure 5 As shown in FIG. 1 , a schematic diagram of the dimension structure of an exemplary multidimensional database source Cube provided in an embodiment of the present application is shown. Figure 5 As shown in the figure, taking the multidimensional database Cube CarSales (car sales) as an example, the multidimensional database Cube includes four dimensions, namely model (model), month (month), region (region) and account (accounting subject). It should be noted that Figure 5 The dimensional structure of the multidimensional database source Cube shown is only an exemplary description of the embodiment of the present application and does not impose any limitation on the embodiment of the present application.

[0112] S402: Based on a preset data volume threshold parameter, determine whether the data volume of the multidimensional data cube is a large data volume, or whether the member attribute data volume of a single dimension is a large data volume.

[0113] In the embodiment of the present application, if the data volume of the multidimensional data cube is greater than or equal to a preset data volume threshold parameter, the data volume of the multidimensional data cube is considered to be a large data volume; otherwise, the data volume of the multidimensional data cube is considered to be a small data volume.

[0114] S403. If so, a temporary relational database table for testing the optimal batch size is automatically created according to the analysis results. The temporary relational database table has multiple tables, wherein the structure of the temporary relational database table for testing the optimal batch size for exporting member attribute data of a single dimension is the same as the structure of the dimension table; the structure of the temporary relational database table for testing the optimal batch size for exporting business data is the same as the structure of the fact data table.

[0115] S404: Automatically create a fact data table of the target relational database according to the analysis results.

[0116] Fact table can be used to record all detailed business data exported from the source Cube.

[0117] In an embodiment of the present application, the creation of a fact table can be achieved by mapping each dimension in the Cube model to the two-dimensional table structure of the target relational database, and then adding measurement fields to store actual business data based on the dimensions of the Cube model.

[0118] For example, Figure 6 As shown in the figure, you can map the dimensions in the example Cube model to the two-dimensional table structure of the relational database, and then add measurement fields to store actual business data based on the four dimensions of the Cube model (model, month, region, and account), thereby creating a fact table.

[0119] Using the above example, assume that the dimensions of the Cube model include model, month, region, and account, the multidimensional database Cube is CarSales (car sales), and the fact table of the target relational database is TBL_CAR_SALE. In actual applications, you can refer to the dimensional model of the Cube and add dimension fields by following the steps below:

[0120] (1) Add the field model_id corresponding to the model dimension of the multidimensional database CubeCarSales to the fact table TBL_CAR_SALE of the target relational database.

[0121] (2) Add the field month_id corresponding to the month dimension of the multidimensional database CubeCarSales to the fact table TBL_CAR_SALE of the target relational database.

[0122] (3) Add the field region_id corresponding to the region dimension of the multidimensional database CubeCarSales to the fact data table TBL_CAR_SALE of the target relational database.

[0123] (4) Add the field account_id corresponding to the account dimension of the multidimensional database CubeCarSales to the fact table TBL_CAR_SALE of the target relational database.

[0124] (5) Add the metric field value to the fact data table TBL_CAR_SALE in the target relational database. Up to this step, the automatic creation of the fact data table in the target relational database can be achieved.

[0125] It should be noted that the four fields model_id, month_id, region_id, and account_id added in the above steps (1) to (4) are all foreign keys, which are respectively associated with the primary key fields of the four dimension tables in the star data model.

[0126] S405: Determine whether all dimensions of the multidimensional data cube have been analyzed.

[0127] In the embodiment of the present application, if the analysis is completed, the process ends; otherwise, if the analysis is not completed, S406 is continued.

[0128] S406: If not, analyze the member attribute structure of a single dimension of the multidimensional data cube.

[0129] Figure 4 After the process is completed, the sample multidimensional database multidimensional cube CarSales will create four dimension tables in the relational database: TBL_MODEL_DIM, TBL_MONTH_DIM, TBL_REGION_DIM, TBL_ACCOUNT_DIM. They correspond to the four dimensions of the multidimensional cube CarSales: Model, Month, Region, Account.

[0130] like Figure 7 As shown, it is a schematic diagram of a member attribute structure of a model dimension of an exemplary multidimensional database provided in an embodiment of the present application. Figure 7 As shown, the model dimension of the example multidimensional database includes three attributes, namely ModelName (model name), EngineSize (engine displacement), and Type (model type).

[0131] S407: Automatically create a dimension table of a single dimension of the target relational database according to the analysis result of the member attribute structure.

[0132] Dimension table, used to record all attribute values ​​of the model dimension that constitutes the source Cube.

[0133] In an embodiment of the present application, the dimension table can be created by mapping each dimension attribute in the attribute model of the model dimension to a two-dimensional table structure of a relational database.

[0134] like Figure 8As shown, it is a schematic diagram of a process of creating a model dimension table of a target relational database provided by an embodiment of the present application. The figure is explained by taking the model dimension among the multiple dimensions constituting the example multidimensional database Cube as an example. The modeling method of other dimensions of the Cube is the same as the model dimension, which will not be described here. Among them, the dimension table TBL_MODEL_DIM of the model in the target relational database is composed of 4 fields.

[0135] Specifically, Figure 8 As shown, taking the dimension table TBL_MODEL_DIM as an example, the field model_id corresponding to the model dimension member list of the multidimensional database can be added to the dimension table TBL_MODEL_DIM of the target relational database, that is, the column where 101, 102, ..., 107 are located. Among them, the field model_id is the primary key of the dimension table TBL_MODEL_DIM of the star data model of the target relational database, corresponding to the field model_id of the same name in the fact table TBL_CAR_SALES of the star data model, and the field is the foreign key of the fact table TBL_CAR_SALES.

[0136] Similarly, the field month_name corresponding to the ModelName attribute of the model dimension of the multidimensional database can be added to the dimension table TBL_MODEL_DIM of the target relational database; the field engine_size corresponding to the EngineSize attribute of the model dimension of the multidimensional database can be added to the dimension table TBL_MODEL_DIM of the target relational database; and the field car_type corresponding to the Type attribute of the model dimension of the multidimensional database can be added to the dimension table TBL_MODEL_DIM of the target relational database.

[0137] S004. Based on the connection relationship, a preset data export operation is executed to export the attribute data of each dimension member of the multidimensional data cube to a dimension table in a target relational library.

[0138] S005. Based on the connection relationship, execute the preset data export operation to export the business data of the multidimensional data cube to the fact data table in the target relationship library.

[0139] like Fig. 9 As shown, in the embodiment of the present application, the business data and member attribute data of the multidimensional data cube can be exported to the fact data table and the dimension table respectively according to the following steps.

[0140] S901. Compare the number of cell values ​​of the multidimensional data cube or the number of members of a single dimension with a pre-configured data volume threshold parameter to determine whether the total business data volume of the multidimensional cube to be exported or the attribute data volume of a single dimension exceeds the preset threshold.

[0141] S902. If yes, perform multiple batches of data export operations with different batch sizes for part of the data, import part of the data into a pre-created temporary relational database table, compare the export times of different batch sizes to obtain the best batch size, and use the best batch size as the first batch size for the data export operation.

[0142] It should be noted that the optimal batch size may vary depending on the type / class of relational database, multidimensional database server, relational database server, and network configuration.

[0143] S903. If not, set the second batch size according to the pre-configured default batch value; that is, the first batch size is the batch size for optimal export performance when the data volume is large, and the second batch size is the default configured batch size when exporting a small data volume.

[0144] In an embodiment of the present application, taking into account the problem that the time overhead of the test process for determining the optimal batch size may exceed the overhead of exporting a small amount of data itself, if the total amount of data to be exported does not exceed the preset data amount threshold parameter, the data amount is considered to be a small amount of data, and the second batch size is set to a pre-configured default batch size value.

[0145] like Fig.10 As shown, in S902, the export times of different batch sizes are compared to obtain the optimal batch size, which specifically includes the following implementation steps:

[0146] S1001, determine the initial batch size and the maximum batch value;

[0147] S1002, determining whether the first test batch value of the current test is less than or equal to the maximum batch value;

[0148] S1003: If yes, the number of batches for this test is calculated according to the preset test data volume and the first test batch value, and the export is performed to obtain the export result;

[0149] S1004. Determine the total time consumed to export a preset amount of test data under the first test batch value according to the export result;

[0150] S1005, increasing the initial batch size to obtain a second test batch value, and determining whether the second test batch value is less than or equal to the maximum batch value, until all valid batch values ​​are tested;

[0151] S1006. Record the export time corresponding to each valid batch value, and compare them to obtain the shortest export time;

[0152] S1007. Determine a value of the first batch size according to the shortest export time.

[0153] For example, assuming that the initial batch size is 500, the maximum batch value is 2000, and the test data volume is 60,000 data items, the initial batch size is 500, that is, 500 items are exported in each batch, and all are exported in 120 batches.

[0154] Then, based on the maximum batch value of 2000, determine whether the batch value of the current test has reached the maximum batch value, where the effective range of the batch value of the test is [500 / batch, 2000 / batch]. If the batch value of the current test does not reach the maximum batch value, the batch of the test is considered to be a valid batch value, and the number of batches of this test is calculated based on the test data volume and the batch value, and the export is performed. After that, the total time for exporting 60,000 data under the batch value of this test is recorded.

[0155] Subsequently, the batch value of the next test is obtained by increasing the number of batches by 500, and the above judgment step is performed to determine whether the batch value of the next test reaches the maximum batch value. In this process, the effective values ​​of the batch value of the test are [500 / batch, 1000 / batch, 1500 / batch, 2000 / batch], and a total of 4 tests are performed.

[0156] After all valid batch values ​​are tested, the export times of the four recorded tests are compared, and the corresponding optimal batch value is obtained according to the shortest export time, which is used as the value of the first batch size.

[0157] The method for determining the optimal batch value provided in the embodiment of the present application can ensure that the optimal batch size can be intelligently determined under the conditions of large or small data volumes, and data can be exported in multiple batches, thereby ensuring that data export can achieve the best performance of the target database type under the hardware configuration conditions.

[0158] S904: Based on the connection relationship, perform a data export operation according to the first batch size or the second batch size, and export the attribute data of the dimension members and the business data of the multidimensional data cube to the dimension table and the fact data table respectively.

[0159] Optionally, in the embodiment of the present application, after executing S904, the pre-created temporary relational database table may also be deleted / cleaned.

[0160] In one embodiment, it can also be determined whether all attribute data of dimension members or business data of the multidimensional data cube are exported; if not, the data export operation is continued according to the first batch size or the second batch size until all business data and member attribute data are exported.

[0161] By adopting the data export method of the embodiment of the present application, on the one hand, a proprietary client development library matching the type of the target relational database can be determined, and a connection relationship between the multidimensional database and the target relational database can be established through the proprietary client development library, and then a preset data export operation is performed based on the connection relationship, and the attribute data of each dimension member of the multidimensional data cube is exported to the dimension table in the target relational library, and the business data of the multidimensional data cube is exported to the fact data table in the target relational library. In this way, compared with the method of exporting data through ODBC driver access in the related art, the data export efficiency of the multidimensional database can be improved.

[0162] Specifically, the target relational database specific proprietary client development library selection mechanism proposed in this application abandons the general ODBC driver access method and uses the proprietary API proprietary client development library specific to each relational database type. Under the same test environment, taking Oracle database as an example, after actual comparison tests, it is verified that the data export performance when using the database type specific proprietary API proprietary client development library is 50 times faster than the ODBC connection method.

[0163] On the other hand, the embodiments of the present application can automatically create fact data tables of the target relational database based on the dimension structure of the multidimensional data cube of the multidimensional database, and automatically create dimension tables of the target relational database based on the member attribute structure of the dimensions constituting the multidimensional data cube. There is no need for a relational database administrator to analyze the source multidimensional data cube structure and the member attribute structure, and there is no need to manually create relational database tables, thereby avoiding manual operation errors during the data export process.

[0164] In addition, the multi-batch and batch data export method proposed in this application can be expanded into a multi-threaded concurrent mode to further improve the performance of data export.

[0165] Fig.11 FIG. 1 shows a schematic diagram of the structure of a data export device provided in an embodiment of the present application. Fig.11 As shown, the data export device includes:

[0166] Configuration module 1101, used to configure parameters related to the data export operation of the multidimensional database, the parameters including the type of the target relational database;

[0167] A connection module 1102 is used to determine a proprietary client development library that matches the type of the target relational database according to the type of the target relational database, and to establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library;

[0168] A creation module 1103 is used to create a fact data table of a target relational database according to the dimension structure of a multidimensional data cube of the multidimensional database, and to create a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube;

[0169] The export module 1104 is used to execute the preset data export operation based on the connection relationship, and export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relationship library; based on the connection relationship, execute the preset data export operation, and export the business data of the multidimensional data cube to the fact data table in the target relationship library.

[0170] Optional parameters related to the data export operation of the multidimensional database, including: the name of the multidimensional data cube of the multidimensional database, the IP address of the target relational database, the listening port of the target relational database, the user name of the target relational database, the password of the target relational database, the data volume threshold parameter of the multidimensional database and the default batch value of the data export operation. The data volume threshold parameter applies to the data volume of a single dimension member attribute and the business data volume of the multidimensional cube.

[0171] Optionally, a module 1103 is created, for:

[0172] Automatically analyze the dimensional structure of the multidimensional data cube of the multidimensional database to obtain analysis results;

[0173] Based on the preset data volume threshold parameter, determine whether the data volume of the multidimensional data cube is a large data volume, or whether the data volume of the member attributes of a single dimension is a large data volume;

[0174] If yes, a temporary relational database table for testing the optimal batch size is automatically created based on the analysis results. The temporary relational database table has multiple tables. The structure of the temporary relational database table for testing the optimal batch size for exporting member attribute data of a single dimension is the same as that of the dimension table; the structure of the temporary relational database table for testing the optimal batch size for exporting business data is the same as that of the fact data table.

[0175] Automatically create fact tables in the target relational database based on analysis results;

[0176] Determine whether all dimensions of the multidimensional data cube have been analyzed;

[0177] If not, then analyze the member attribute structure of a single dimension of the multidimensional data cube;

[0178] According to the analysis results of the member attribute structure, a dimension table of a single dimension of the target relational database is automatically created.

[0179] Optionally, the export module 1104 is used to:

[0180] According to the number of cell values ​​of the multidimensional data cube or the number of members of a single dimension, a comparison is made with a pre-configured data volume threshold parameter to determine whether the total business data volume of the multidimensional cube to be exported or the attribute data volume of a single dimension exceeds the preset threshold;

[0181] If so, perform multiple batch data export operations with different batch sizes for part of the data, import part of the data into a pre-created temporary relational database table, compare the export times of different batch sizes to obtain an optimal batch size, and use the optimal batch size as the first batch size of the data export operation;

[0182] If not, the second batch size is set according to the pre-configured default batch value; that is, the first batch size is the batch size for the best export performance when the data volume is large, and the second batch size is the default configured batch size when the data volume is small;

[0183] Based on the connection relationship, the data export operation is performed according to the first batch size or the second batch size, and the attribute data of the dimension members and the business data of the multidimensional data cube are exported to the dimension table and the fact data table respectively.

[0184] Optionally, the device further includes: a deletion module, used to delete the pre-created temporary relational database table.

[0185] Optionally, the export module 1104 is further used to:

[0186] Determine the initial batch size and maximum batch value;

[0187] Determine whether the first test batch value of the current test is less than or equal to the maximum batch value;

[0188] If yes, the number of batches for this test is calculated according to the preset test data volume and the first test batch value, and the export is performed to obtain the export result;

[0189] Determine the total time consumed to export a preset amount of test data under the first test batch value according to the export result;

[0190] Incrementing the initial batch size to obtain a second test batch value, and determining whether the second test batch value is less than or equal to the maximum batch value, until all valid batch values ​​are tested;

[0191] Record the export time corresponding to each valid batch value and compare them to get the shortest export time;

[0192] Determine the value of the first batch size based on the shortest export time.

[0193] Optionally, the export module 1104 is further used to:

[0194] Determine whether all attribute data of dimension members or business data of multidimensional data cubes are exported;

[0195] If not, the data export operation continues according to the first batch size or the second batch size until all the business data and member attribute data are exported.

[0196] Fig.11 Each module / unit in the device shown has the function of realizing Figure 1 The functions of each step in the process can achieve the corresponding technical effects, which will not be described in detail here for the sake of brevity.

[0197] Fig.12 A schematic diagram of the structure of a data export device provided in an embodiment of the present application is shown.

[0198] The data export device may include a processor 1201 and a memory 1202 storing computer program instructions.

[0199] Specifically, the processor 1201 may include a central processing unit (CPU), or an application specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiments of the present application.

[0200] The memory 1202 may include a large capacity memory for data or instructions. By way of example and not limitation, the memory 1202 may include a hard disk drive (HDD), a floppy disk drive, a flash memory, an optical disk, a magneto-optical disk, a magnetic tape, or a universal serial bus (USB) drive or a combination of two or more of these. Where appropriate, the memory 1202 may include a removable or non-removable (or fixed) medium. Where appropriate, the memory 1202 may be inside or outside the data export device. In a particular embodiment, the memory 1202 may be a non-volatile solid-state memory.

[0201] In one embodiment, the memory 1202 may be a read-only memory (ROM). In one embodiment, the ROM may be a mask-programmed ROM, a programmable ROM (PROM), an erasable PROM (EPROM), an electrically erasable PROM (EEPROM), an electrically rewritable ROM (EAROM), or a flash memory, or a combination of two or more of these.

[0202] The processor 1201 implements any one of the data export methods in the above embodiments by reading and executing computer program instructions stored in the memory 1202 .

[0203] In one example, the data export device may further include a communication interface 1203 and a bus 1210. Fig.12 As shown, the processor 1201, the memory 1202, and the communication interface 1203 are connected via a bus 1210 and communicate with each other.

[0204] The communication interface 1203 is mainly used to implement communication between various modules, devices, units and / or equipment in the embodiments of the present application.

[0205] Bus 1210 includes hardware, software or both, and the parts of data export equipment are coupled to each other.For example, but not limitation, bus may include accelerated graphics port (AGP) or other graphics bus, enhanced industrial standard architecture (EISA) bus, front side bus (FSB), hypertransport (HT) interconnection, industrial standard architecture (ISA) bus, infinite bandwidth interconnection, low pin count (LPC) bus, memory bus, micro channel architecture (MCA) bus, peripheral component interconnection (PCI) bus, PCI-Express (PCI-X) bus, serial advanced technology attachment (SATA) bus, video electronics standard association local (VLB) bus or other suitable bus or two or more of these combinations. In suitable cases, bus 1210 may include one or more buses. Although the present application embodiment describes and shows a specific bus, the application considers any suitable bus or interconnection.

[0206] In addition, in combination with the data export method in the above embodiment, the embodiment of the present application can provide a computer-readable storage medium for implementation. The computer-readable storage medium stores computer program instructions; when the computer program instructions are executed by a processor, any one of the data export methods in the above embodiment is implemented.

[0207] It should be clear that the present application is not limited to the specific configuration and processing described above and shown in the figures. For the sake of simplicity, a detailed description of the known method is omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present application is not limited to the specific steps described and shown, and those skilled in the art can make various changes, modifications and additions, or change the order between the steps after understanding the spirit of the present application.

[0208] The functional modules shown in the above-described block diagram can be implemented as hardware, software, firmware or a combination thereof. When implemented in hardware, it can be, for example, an electronic circuit, an application specific integrated circuit (ASIC), appropriate firmware, a plug-in, a function card, etc. When implemented in software, the elements of the present application are programs or code segments that are used to perform the required tasks. Programs or code segments can be stored in machine-readable media, or transmitted on a transmission medium or a communication link by a data signal carried in a carrier wave. "Machine-readable media" can include any medium capable of storing or transmitting information. Examples of machine-readable media include electronic circuits, semiconductor memory devices, ROM, flash memory, erasable ROM (EROM), floppy disks, CD-ROMs, optical disks, hard disks, optical fiber media, radio frequency (RF) links, etc. Code segments can be downloaded via computer networks such as the Internet, intranets, etc.

[0209] It should also be noted that the exemplary embodiments mentioned in this application describe some methods or systems based on a series of steps or devices. However, this application is not limited to the order of the above steps, that is, the steps can be performed in the order mentioned in the embodiment, or in a different order from the embodiment, or several steps can be performed simultaneously.

[0210] The above reference is according to the method of the embodiment of the present application, the flow chart of the device (system) and the computer program product and / or the block diagram described various aspects of the present application.It should be understood that each square box in the flow chart and / or the block diagram and the combination of each square box in the flow chart and / or the block diagram can be realized by computer program instructions.These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer or other programmable data processing device to produce a machine so that these instructions executed by the processor of the computer or other programmable data processing device enable the realization of the function / action specified in one or more square boxes of the flow chart and / or the block diagram.Such a processor can be but is not limited to a general-purpose processor, a special-purpose processor, a special application processor or a field programmable logic circuit.It can also be understood that each square box in the block diagram and / or the flow chart and the combination of the square boxes in the block diagram and / or the flow chart can also be realized by the dedicated hardware that performs the specified function or action, or can be realized by the combination of dedicated hardware and computer instructions.

[0211] The above is only a specific implementation of the present application. Those skilled in the art can clearly understand that for the convenience and simplicity of description, the specific working processes of the systems, modules and units described above can refer to the corresponding processes in the aforementioned method embodiments, and will not be repeated here. It should be understood that the protection scope of the present application is not limited to this. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in this application, and these modifications or replacements should be included in the protection scope of this application.

Claims

1. A data export method, characterized in that: include: Configuring parameters related to the data export operation of the multidimensional database, the parameters including the type of the target relational database; Determining a proprietary client development library matching the type of the target relational database according to the type of the target relational database, and establishing a connection relationship between the multidimensional database and the target relational database through the proprietary client development library; Creating a fact data table of the target relational database according to the dimension structure of the multidimensional data cube of the multidimensional database, and creating a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube; Based on the connection relationship, a preset data export operation is performed to export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relationship library; Based on the connection relationship, a preset data export operation is performed to export the business data of the multidimensional data cube to the fact data table in the target relationship library; The step of creating a fact data table of the target relational database according to the dimension structure of the multidimensional data cube of the multidimensional database, and creating a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube, comprises: Automatically analyzing the dimensional structure of the multidimensional data cube of the multidimensional database to obtain analysis results; Based on a preset data volume threshold parameter, determining whether the data volume of the multidimensional data cube is a large data volume, or whether the member attribute data volume of a single dimension is a large data volume; If so, a temporary relational database table for testing the optimal batch size is automatically created according to the analysis result, and the temporary relational database table has multiple tables, wherein the structure of the temporary relational database table for testing the optimal batch size derived from the member attribute data of a single dimension is the same as the structure of the dimension table; the structure of the temporary relational database table for testing the optimal batch size derived from the business data is the same as the structure of the fact data table; Automatically create a fact data table of the target relational database according to the analysis result; Determining whether all dimensions of the multidimensional data cube have been analyzed; If not, analyzing the member attribute structure of a single dimension of the multidimensional data cube; According to the analysis result of the member attribute structure, a dimension table of a single dimension of the target relational database is automatically created.

2. The data export method according to claim 1, characterized in that: Parameters related to the data export operation of the multidimensional database include: the name of the multidimensional data cube of the multidimensional database, the IP address of the target relational database, the listening port of the target relational database, the user name of the target relational database, the password of the target relational database, the data volume threshold parameter of the multidimensional database and the default batch value of the data export operation, the data volume threshold parameter is applicable to the data volume of a single dimension member attribute and the business data volume of the multidimensional cube.

3. The data export method according to claim 1, characterized in that: Based on the connection relationship, a preset data export operation is performed to export the attribute data and business data of each dimension member of the multidimensional data cube to the dimension table and the fact data table respectively, including: According to the number of cell values ​​of the multidimensional data cube or the number of members of a single dimension, a comparison is made with a pre-configured data volume threshold parameter to determine whether the total business data volume of the multidimensional cube to be exported or the attribute data volume of a single dimension exceeds the preset threshold; If yes, perform multiple batches of data export operations with different batch sizes for part of the data, import the part of the data into a pre-created temporary relational database table, compare the export times of the different batch sizes to obtain an optimal batch size, and use the optimal batch size as the first batch size of the data export operation; If not, the second batch size is set according to the pre-configured default batch value; that is, the first batch size is the batch size for the best export performance when the data volume is large, and the second batch size is the default configured batch size when the data volume is small; Based on the connection relationship, a data export operation is performed according to the first batch size or the second batch size to export the attribute data of the dimension members and the business data of the multidimensional data cube to the dimension table and the fact data table respectively.

4. The data export method according to claim 3, characterized in that: The method further includes: deleting a pre-created temporary relational database table.

5. The data export method according to claim 3, characterized in that: Compare the export time of different batch sizes to get the best batch size, including: Determine the initial batch size and maximum batch value; Determine whether the first test batch value of the current test is less than or equal to the maximum batch value; If yes, the number of batches for this test is calculated according to the preset test data volume and the first test batch value, and the export is performed to obtain the export result; Determine, according to the export result, the total time consumed to export the preset test data volume under the first test batch value; Incrementing the initial batch size to obtain a second test batch value, and determining whether the second test batch value is less than or equal to the maximum batch value, until all valid batch values ​​are tested; Record the export time corresponding to each of the valid batch values, and compare them to obtain the shortest export time; A value of the first batch size is determined according to the shortest export time.

6. The data export method according to claim 3, characterized in that: The method further comprises: Determine whether all the attribute data of the dimension members or the business data of the multidimensional data cube are exported; If not, continue to perform the data export operation according to the first batch size or the second batch size until all of the business data and the member attribute data are exported.

7. A data export device, characterized in that: include: A configuration module, used to configure parameters related to the data export operation of the multidimensional database, wherein the parameters include the type of the target relational database; A connection module, used to determine a proprietary client development library matching the type of the target relational database according to the type of the target relational database, and to establish a connection relationship between the multidimensional database and the target relational database through the proprietary client development library; A creation module, used to create a fact data table of the target relational database according to the dimension structure of the multidimensional data cube of the multidimensional database, and to create a dimension table of the target relational database according to the member attribute structure of the dimension constituting the multidimensional data cube; An export module, used to perform a preset data export operation based on the connection relationship, and export the attribute data of each dimension member of the multidimensional data cube to the dimension table in the target relationship library; Based on the connection relationship, a preset data export operation is performed to export the business data of the multidimensional data cube to the fact data table in the target relationship library; Wherein, the creation module is used to: Automatically analyzing the dimensional structure of the multidimensional data cube of the multidimensional database to obtain analysis results; Based on a preset data volume threshold parameter, determining whether the data volume of the multidimensional data cube is a large data volume, or whether the member attribute data volume of a single dimension is a large data volume; If so, a temporary relational database table for testing the optimal batch size is automatically created according to the analysis result, and the temporary relational database table has multiple tables, wherein the structure of the temporary relational database table for testing the optimal batch size derived from the member attribute data of a single dimension is the same as the structure of the dimension table; the structure of the temporary relational database table for testing the optimal batch size derived from the business data is the same as the structure of the fact data table; Automatically create a fact data table of the target relational database according to the analysis result; Determining whether all dimensions of the multidimensional data cube have been analyzed; If not, analyzing the member attribute structure of a single dimension of the multidimensional data cube; According to the analysis result of the member attribute structure, a dimension table of a single dimension of the target relational database is automatically created.

8. A data export device, characterized in that: The data export device includes: a processor and a memory storing computer program instructions; When the processor executes the computer program instructions, the data export method according to any one of claims 1 to 6 is implemented.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer program instructions, and when the computer program instructions are executed by a processor, the data export method according to any one of claims 1 to 6 is implemented.

Images