A method and apparatus for relating dimension tables

Abstract

This invention discloses a method and apparatus for associating dimension tables, relating to the field of big data technology. One specific implementation of the method includes: aggregating fact tables from various data sources into a master data wide table; obtaining attribute codes and their corresponding attributes from the data dimension tables to generate key-value pairs with attribute codes as keys and attributes as values; associating the master data wide table with the key-value pairs to generate a temporary table; in the temporary table, matching the attribute codes in the master data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and filling the temporary table with the values. This implementation can solve the technical problems of performance overhead and low serial execution efficiency caused by multiple joins of dimension tables.

Description

Technical Field

[0001] This invention relates to the field of big data technology, and in particular to a method and apparatus for relating dimension tables. Background Technology

[0002] In offline big data development, it's common to see fields with the same attributes appear multiple times in a single fact table, such as city names. In a complete supply chain, starting from the warehouse, passing through multiple sorting or transshipment centers, reaching the station, and finally the customer, each organization or individual in the supply chain has an address attribute that displays the city name, thus requiring multiple associations with the address dimension table.

[0003] The current mainstream approach is to store certain dimensional or feature information in a unified dimension table and then assign a unique code as the identifier. For example, for city information, the city name, province, country, and latitude / longitude are all stored in the city information dimension table, using the city code as the unique identifier. Then, the city code is used in the fact table to replace other city information, thus reducing redundant data. When compiling statistical data, the fact table joins the dimension table using the code to obtain the necessary information. Taking city name as an example, the fact table uses the city code in the fact table to join the city information dimension table to obtain the city name, which is then populated into the corresponding fields to generate the final report. Generally, a left join clause is used to join the dimension tables; if there are multiple city codes in the fact table, the dimension tables are joined multiple times.

[0004] In the process of realizing this invention, the inventors discovered at least the following problems in the prior art:

[0005] 1) Each code in the fact table needs to be associated with the dimension table. During the MapReduce phase, multiple jobs are generated. Each job has to traverse the fact table and the dimension table, which greatly increases IO (input / output) and reduces the overall operating efficiency of the system.

[0006] 2) Each left join clause is a fragment that is independent of each other but also dependent on each other. It is a serial relationship. If the previous left join clause is not completed, the next left join clause cannot be executed. The whole cannot be executed in parallel, resulting in low execution efficiency. Summary of the Invention

[0007] In view of this, embodiments of the present invention provide a method and apparatus for associating dimension tables to solve the technical problems of performance consumption and low serial execution efficiency caused by multiple associations of dimension tables.

[0008] To achieve the above objectives, according to one aspect of the present invention, a method for associating dimension tables is provided, comprising:

[0009] The fact tables from various data sources are aggregated into a master data wide table;

[0010] Retrieve the attribute codes and their corresponding attributes from the data dimension table, and generate key-value pairs with the attribute codes as keys and the attributes as values;

[0011] The main data wide table is associated with the key-value pairs to generate a temporary table;

[0012] In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and the values ​​are then filled into the temporary table.

[0013] Optionally, the attribute codes and their corresponding attributes are obtained from the data dimension table, and key-value pairs are generated with the attribute codes as keys and the attributes as values, including:

[0014] Based on the required dimension data, find the corresponding data dimension table;

[0015] Obtain each attribute code and the attribute corresponding to each attribute code from the data dimension table;

[0016] For each attribute code and its corresponding attribute, generate a key-value pair with the attribute code as the key and the attribute as the value.

[0017] Optionally, the master data wide table is associated with the key-value pairs to generate a temporary table, including:

[0018] The key-value pairs are added as a field to the end of each row of the main data wide table to generate a temporary table.

[0019] Optionally, in the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and the values ​​are then filled into the temporary table, including:

[0020] In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs in parallel using the SELECT clause to obtain the values ​​corresponding to the keys;

[0021] The value is filled into the temporary table.

[0022] Optionally, matching the attribute codes in the master data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys includes:

[0023] For each attribute code in the master data wide table, determine whether a key with the same attribute code can be matched in the key-value pair;

[0024] If so, then retrieve the value corresponding to the key;

[0025] If not, then retrieve the default value.

[0026] Optionally, after filling the temporary table with the value, the method further includes:

[0027] The data in the temporary table is filtered to generate a wide table of result data;

[0028] The resulting wide table is stored in a table in the data warehouse.

[0029] Optionally, the data in the temporary table is filtered to generate a wide table of result data, including:

[0030] The data in the temporary table is filtered using the SELECT clause to generate a wide table of result data.

[0031] Additionally, according to another aspect of the present invention, an apparatus for associating dimension tables is provided, comprising:

[0032] The aggregation module is used to aggregate fact tables from various data sources into a master data wide table;

[0033] The generation module is used to obtain attribute codes and their corresponding attributes from the data dimension table, and generate key-value pairs with the attribute codes as keys and the attributes as values.

[0034] The association module is used to associate the main data wide table with the key-value pairs, thereby generating a temporary table;

[0035] A fill module is used to match the attribute codes in the main data wide table with the keys in the key-value pairs in the temporary table to obtain the values ​​corresponding to the keys, and then fill the values ​​into the temporary table.

[0036] Optionally, the generation module is further configured to:

[0037] Based on the required dimension data, find the corresponding data dimension table;

[0038] Obtain each attribute code and the attribute corresponding to each attribute code from the data dimension table;

[0039] For each attribute code and its corresponding attribute, generate a key-value pair with the attribute code as the key and the attribute as the value.

[0040] Optionally, the association module is further configured to:

[0041] The key-value pairs are added as a field to the end of each row of the main data wide table to generate a temporary table.

[0042] Optionally, the filling module is further configured to:

[0043] In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs in parallel using the SELECT clause to obtain the values ​​corresponding to the keys;

[0044] The value is filled into the temporary table.

[0045] Optionally, the filling module is further configured to:

[0046] For each attribute code in the master data wide table, determine whether a key with the same attribute code can be matched in the key-value pair;

[0047] If so, then retrieve the value corresponding to the key;

[0048] If not, then retrieve the default value.

[0049] Optionally, a filtering module is also included for:

[0050] After the values ​​are filled into the temporary table, the data in the temporary table is filtered to generate a wide table of result data.

[0051] The resulting wide table is stored in a table in the data warehouse.

[0052] Optionally, the filtering module is further configured to:

[0053] The data in the temporary table is filtered using the SELECT clause to generate a wide table of result data.

[0054] According to another aspect of the present invention, an electronic device is also provided, comprising:

[0055] One or more processors;

[0056] Storage device for storing one or more programs.

[0057] When the one or more programs are executed by the one or more processors, the one or more processors implement the method described in any of the above embodiments.

[0058] According to another aspect of the present invention, a computer-readable medium is also provided, on which a computer program is stored, which, when executed by a processor, implements the methods described in any of the above embodiments.

[0059] According to another aspect of the present invention, a computer program product is also provided, including a computer program that, when executed by a processor, implements the methods described in any of the above embodiments.

[0060] One embodiment of the above invention has the following advantages or beneficial effects: By employing the technique of obtaining attribute codes and their corresponding attributes from the data dimension table, generating key-value pairs with the attribute codes as keys and the attributes as values, associating the main data wide table with the key-value pairs, and then matching the attribute codes in the main data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, thereby filling the values ​​into a temporary table, the technical problems of performance overhead and low serial execution efficiency caused by multiple dimension table joins in the prior art are overcome. This invention's embodiment associates dimension tables based on key-value pairs, requiring only one join, thus solving the performance overhead caused by multiple dimension table joins. Furthermore, since the key-value pairs are associated with a single field, parallel execution is possible, thereby solving the problem of low efficiency caused by serial execution of multiple dimension table joins.

[0061] The further effects of the aforementioned unconventional alternative methods will be explained below in conjunction with specific implementation methods. Attached Figure Description

[0062] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0063] Figure 1 This is a schematic diagram of the main flow of the method for associating dimension tables according to an embodiment of the present invention;

[0064] Figure 2 This is a schematic diagram of the aggregated fact table and associated key-value pairs according to an embodiment of the present invention;

[0065] Figure 3 This is a schematic diagram of the main flow of a method for using a related dimension table according to a possible embodiment of the present invention;

[0066] Figure 4 This is a schematic diagram of the main flow of a method for associating dimension tables according to another applicable embodiment of the present invention;

[0067] Figure 5 This is a schematic diagram of the main modules of the apparatus for generating a related dimension table according to an embodiment of the present invention;

[0068] Figure 6 This is an exemplary system architecture diagram in which embodiments of the present invention can be applied;

[0069] Figure 7 This is a schematic diagram of the structure of a computer system suitable for implementing terminal devices or servers of the present invention. Detailed Implementation

[0070] The following description, in conjunction with the accompanying drawings, illustrates exemplary embodiments of the present invention, including various details to aid understanding. These details should be considered merely exemplary. Therefore, those skilled in the art will recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of the invention. Similarly, for clarity and brevity, descriptions of well-known functions and structures are omitted in the following description.

[0071] Figure 1 This is a schematic diagram illustrating the main flow of a method for using a related dimension table according to an embodiment of the present invention. As one embodiment of the present invention, such as... Figure 1 As shown, the method for associating dimension tables may include:

[0072] Step 101: Aggregate the fact tables from each data source into a master data wide table.

[0073] In this step, based on specific business needs, the fact tables from various data sources are aggregated into a master data wide table, such as... Figure 2 As shown, the aggregated data can be deduplicated or not, depending on the specific business needs. For example, when counting the number of employees, the aggregated data needs to be deduplicated, but when counting attendance, it does not need to be deduplicated.

[0074] It's important to note that the master data wide table only stores the codes for the dimension data, not the actual dimension data. For example, the city field in the master data wide table only stores the city code, not the city name, province, country, or other information. It's also important to note that the other fields in the master data wide table are the same as those in the final result data wide table.

[0075] Step 102: Obtain the attribute code and its corresponding attribute from the data dimension table, and generate key-value pairs with the attribute code as the key and the attribute as the value.

[0076] The dimension table stores attribute codes and their corresponding attributes. For example, for a city, the attribute code is "1," and the corresponding attribute is "Beijing." This embodiment of the invention retrieves the attribute codes and their corresponding attributes from the data dimension table, thereby generating key-value pairs with the attribute code as the key and the attribute as the value.

[0077] Key-value pairs: In computer science, name-value pairs, also known as key-value pairs or attribute-value pairs, are a fundamental data representation in computing systems and applications. Designers often desire open data structures that can be expanded in the future without modifying existing code or data. In this context, all or part of the data model can be represented as a collection of tuples.<name,value> Each element is a name-value pair, depending on the specific application and the implementation chosen by the programmer.

[0078] Optionally, step 102 may include: finding the data dimension table corresponding to the dimension data to be filled; obtaining each attribute code and the attribute corresponding to each attribute code from the data dimension table; and generating a key-value pair with the attribute code as the key and the attribute as the value for each attribute code and its corresponding attribute. In an embodiment of the present invention, the dimension data (i.e., attributes) to be filled is first determined, such as city name or name; then the data dimension table corresponding to the dimension data is found; then each attribute code and the required attribute corresponding to each attribute code are obtained from the data dimension table; finally, code / required attribute key-value pairs are generated, such as... Figure 2 As shown.

[0079] Taking city names as an example, the city code and its corresponding city name are retrieved from the dimension table. The city code is used as the key and the city name is used as the value to generate city code / city name key-value pairs, such as {1:"Beijing",2:"Tianjin",3:"Shanghai",......}.

[0080] Optionally, the generated key-value pairs can be temporarily stored in a cache so that the master data wide table can be associated with the key-value pairs in step 103.

[0081] Step 103: Associate the master data wide table with the key-value pairs to generate a temporary table.

[0082] In this step, the main data wide table is associated with the key-value pairs generated in step 102. Optionally, step 103 may include adding the key-value pairs as a field to the end of each row of the main data wide table, thereby generating a temporary table. Since no specific encoding needs to be specified during the association, there is no specific association condition; 1=1 can be used instead, always true. The two tables will be cascaded, but the key-value pair table contains only one row of data, which is associated with all rows in the main data wide table. Therefore, it is equivalent to adding the encoding / required attribute key-value pair as a field to the end of each row of the main data wide table. For example, adding {1:"Beijing",2:"Tianjin",3:"Shanghai",......} as a field to the end of each row of the main data wide table generates a temporary table.

[0083] Taking city name as an example, the main data wide table is associated with the city code / city name key-value pair. The association condition is 1=1. The subquery of the key-value pair only outputs one row of data, which is a key-value pair. Therefore, the city code / city name key-value pair will be appended to the end of each data row in the main data wide table, thereby generating a new temporary table. This solves the performance overhead caused by multiple associations of dimension tables. In this embodiment of the invention, the dimension table only needs to be associated once.

[0084] Hive SQL stores the results in a temporary table each time it joins two tables, making it convenient for later use. This temporary table stores all the data after the main data wide table and the key-value pairs are concatenated. You can treat the temporary table as an intermediate result table.

[0085] Step 104: In the temporary table, the attribute code in the main data wide table is matched with the key in the key-value pair to obtain the value corresponding to the key, and the value is filled into the temporary table.

[0086] The data in the temporary table consists of a main data wide table and key-value pairs. Therefore, the attribute codes in the main data wide table are matched one by one with the keys in the key-value pairs to obtain the values ​​corresponding to the keys with the same attribute codes. Then, the matched values ​​are filled into the corresponding fields of the temporary table.

[0087] Optionally, step 104 may include: in the temporary table, matching the attribute codes in the main data wide table with the keys in the key-value pairs in parallel using a SELECT clause to obtain the values ​​corresponding to the keys; and populating the values ​​into the temporary table. Taking city names as an example, in the SELECT clause, the city codes in the main data wide table are matched with the keys (i.e., city codes) of the city code / city name key-value pairs to obtain the required values ​​(i.e., city names), for example, in the format city_names[city_id]. For multiple city codes, the same city code / city name key-value pair can be reused.

[0088] It should be noted that when processing the SELECT clause, the cursor actually operates within the same data row. This can be understood as processing these fields separately and executing them in parallel, thus solving the problem of low efficiency caused by multiple joins of dimension tables being executed serially.

[0089] Optionally, matching the attribute codes in the master data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys includes: for each attribute code in the master data wide table, determining whether a key with the same attribute code can be matched in the key-value pairs; if so, obtaining the value corresponding to the key; if not, obtaining a default value. Matching each attribute code in the master data wide table with the keys in the key-value pairs, if a key with the same attribute code can be matched, the value corresponding to that key is filled into the corresponding field of the temporary table; if a key with the same attribute code cannot be matched, a default value (generally null) is filled into the corresponding field of the temporary table.

[0090] Based on the various embodiments described above, it can be seen that the embodiments of the present invention solve the technical problems of performance overhead and low serial execution efficiency caused by multiple joins of dimension tables in the prior art by obtaining attribute codes and their corresponding attributes from the data dimension table, generating key-value pairs with attribute codes as keys and attributes as values, associating the main data wide table with the key-value pairs, and then matching the attribute codes in the main data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, thereby filling the values ​​into a temporary table. The embodiments of the present invention associate dimension tables based on key-value pairs, requiring only one join, thus solving the performance overhead caused by multiple joins of dimension tables. Moreover, since the key-value pairs are associated with a single field, parallel execution is possible, thereby solving the problem of low efficiency caused by serial execution of multiple joins of dimension tables.

[0091] Figure 3 This is a schematic diagram of the main flow of a method for using a related dimension table according to a possible embodiment of the present invention. As another embodiment of the present invention, such as... Figure 3 As shown, the method for associating dimension tables may include:

[0092] Step 301: Aggregate the fact tables from each data source into a master data wide table.

[0093] Step 302: Obtain the attribute code and its corresponding attribute from the data dimension table, and generate key-value pairs with the attribute code as the key and the attribute as the value.

[0094] Step 303: Add the key-value pair as a field to the end of each row of the main data wide table to generate a temporary table.

[0095] Step 304: In the temporary table, for each attribute code in the main data wide table, determine whether a key with the same attribute code can be matched in the key-value pair; if yes, proceed to step 305; if no, proceed to step 306.

[0096] Step 305: Obtain the value corresponding to the key and fill the value into the temporary table.

[0097] Step 306: Obtain the default value and fill the temporary table with the default value.

[0098] Furthermore, the specific implementation details of the method for associating dimension tables in one of the reference embodiments of the present invention have been described in detail in the above-described method for associating dimension tables, so the details will not be repeated here.

[0099] Figure 4 This is a schematic diagram of the main flow of a method for using a related dimension table according to another possible embodiment of the present invention. As another embodiment of the present invention, such as... Figure 4 As shown, the method for associating dimension tables may include:

[0100] Step 401: Aggregate the fact tables from each data source into a master data wide table.

[0101] Based on specific business needs, the fact tables from various data sources are aggregated into a master data wide table. The master data wide table only stores the codes of the dimension data, and does not store the actual dimension data.

[0102] Step 402: Locate the data dimension table corresponding to the dimension data to be filled in.

[0103] For example, if the dimension data to be populated is name, then the dimension table storing the name data will be retrieved.

[0104] Step 403: Obtain each attribute code and the attribute corresponding to each attribute code from the data dimension table.

[0105] Step 404: For each attribute code and its corresponding attribute, generate a key-value pair with the attribute code as the key and the attribute as the value.

[0106] Taking a name as an example, generate name code / name key-value pairs, such as {1:"Zhang San",2:"Li Si",3:"Wang Wu",......}.

[0107] Step 405: Add the key-value pair as a field to the end of each row of the main data wide table to generate a temporary table.

[0108] For example, adding {1:"Zhang San",2:"Li Si",3:"Wang Wu",......} as a field to the end of each row of the main data wide table will generate a temporary table.

[0109] Step 406: In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs in parallel using the SELECT clause to obtain the values ​​corresponding to the keys, and the values ​​are then filled into the temporary table.

[0110] Step 407: Filter the data in the temporary table to generate a wide table of result data.

[0111] After filling the temporary table with the required attributes, you can use the SELECT clause to filter the data in the temporary table, removing unnecessary data, and thus generating a wide table of result data.

[0112] Step 408: Store the resulting wide table of data into a table in the data warehouse.

[0113] After generating the final result wide table, store the result wide table in a Hive table for easy subsequent querying or data processing.

[0114] In addition, the specific implementation details of the method for associating dimension tables in another reference embodiment of the present invention have been described in detail in the above-described method for associating dimension tables, so the content will not be repeated here.

[0115] Figure 5 This is a schematic diagram of the main modules of an apparatus for generating a related dimension table according to an embodiment of the present invention. Figure 5 As shown, the apparatus 500 for the associated dimension table includes an aggregation module 501, a generation module 502, an association module 503, and a filling module 504. The aggregation module 501 aggregates fact tables from various data sources into a main data wide table. The generation module 502 obtains attribute codes and their corresponding attributes from the data dimension table, generating key-value pairs with attribute codes as keys and attributes as values. The association module 503 associates the main data wide table with the key-value pairs to generate a temporary table. The filling module 504 matches the attribute codes in the main data wide table with the keys in the key-value pairs in the temporary table to obtain the values ​​corresponding to the keys, and fills the values ​​into the temporary table.

[0116] Optionally, the generation module 502 is further configured to:

[0117] Based on the required dimension data, find the corresponding data dimension table;

[0118] Obtain each attribute code and the attribute corresponding to each attribute code from the data dimension table;

[0119] For each attribute code and its corresponding attribute, generate a key-value pair with the attribute code as the key and the attribute as the value.

[0120] Optionally, the association module 503 is further configured to:

[0121] The key-value pairs are added as a field to the end of each row of the main data wide table to generate a temporary table.

[0122] Optionally, the filling module 504 is further configured to:

[0123] In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs in parallel using the SELECT clause to obtain the values ​​corresponding to the keys;

[0124] The value is filled into the temporary table.

[0125] Optionally, the filling module 504 is further configured to:

[0126] For each attribute code in the master data wide table, determine whether a key with the same attribute code can be matched in the key-value pair;

[0127] If so, then retrieve the value corresponding to the key;

[0128] If not, then retrieve the default value.

[0129] Optionally, a filtering module is also included for:

[0130] After the values ​​are filled into the temporary table, the data in the temporary table is filtered to generate a wide table of result data.

[0131] The resulting wide table is stored in a table in the data warehouse.

[0132] Optionally, the filtering module is further configured to:

[0133] The data in the temporary table is filtered using the SELECT clause to generate a wide table of result data.

[0134] It should be noted that the specific implementation details of the device for the associated dimension table described in this invention have been described in detail in the method for the associated dimension table described above, so the details will not be repeated here.

[0135] Figure 6 An exemplary system architecture 600 is shown, which can be used to apply the method or apparatus for associating dimension tables according to embodiments of the present invention.

[0136] like Figure 6 As shown, system architecture 600 may include terminal devices 601, 602, and 603, a network 604, and a server 605. Network 604 serves as the medium for providing communication links between terminal devices 601, 602, and 603 and server 605. Network 604 may include various connection types, such as wired or wireless communication links or fiber optic cables, etc.

[0137] Users can use terminal devices 601, 602, and 603 to interact with server 605 via network 604 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 601, 602, and 603, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social media platform software, etc. (for example only).

[0138] Terminal devices 601, 602, and 603 can be various electronic devices with displays and web browsing capabilities, including but not limited to smartphones, tablets, laptops, and desktop computers.

[0139] Server 605 can be a server that provides various services, such as a backend management server that supports shopping websites browsed by users using terminal devices 601, 602, and 603 (this is just an example). The backend management server can analyze and process data such as received item information query requests, and then feed the processing results back to the terminal devices.

[0140] It should be noted that the method for associating dimension tables provided in this embodiment of the invention is generally executed by server 605, and correspondingly, the device for associating dimension tables is generally set in server 605.

[0141] It should be understood that Figure 6 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.

[0142] The following is for reference. Figure 7 It shows a schematic diagram of the structure of a computer system 700 suitable for implementing a terminal device of the present invention. Figure 7 The terminal device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of the present invention.

[0143] like Figure 7 As shown, the computer system 700 includes a central processing unit (CPU) 701, which can perform various appropriate actions and processes based on programs stored in read-only memory (ROM) 702 or programs loaded from storage section 708 into random access memory (RAM) 703. The RAM 703 also stores various programs and data required for the operation of the system 700. The CPU 701, ROM 702, and RAM 703 are interconnected via a bus 704. An input / output (I / O) interface 705 is also connected to the bus 704.

[0144] The following components are connected to the I / O interface 705: an input section 706 including a keyboard, mouse, etc.; an output section 707 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and speakers, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN card, modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 710 as needed so that computer programs read from it can be installed into the storage section 708 as needed.

[0145] In particular, according to the embodiments disclosed in this invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this invention include a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via communication section 709, and / or installed from removable medium 711. When the computer program is executed by central processing unit (CPU) 701, it performs the functions defined above in the system of this invention.

[0146] It should be noted that the computer-readable medium shown in this invention can be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium can be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In this invention, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media can also be any computer-readable medium other than computer-readable storage media, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted using any suitable medium, including but not limited to: wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.

[0147] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer programs according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0148] The modules described in the embodiments of the present invention can be implemented in software or hardware. The described modules can also be housed in a processor; for example, a processor can be described as including a convergence module, a generation module, an association module, and a filling module, wherein the names of these modules do not necessarily limit the module itself.

[0149] In another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiments; or it may exist independently and not assembled into the device. The computer-readable medium carries one or more programs, which, when executed by the device, implement the following method: aggregating fact tables from various data sources into a main data wide table; obtaining attribute codes and their corresponding attributes from data dimension tables to generate key-value pairs with attribute codes as keys and attributes as values; associating the main data wide table with the key-value pairs to generate a temporary table; in the temporary table, matching the attribute codes in the main data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and filling the temporary table with the values.

[0150] In another aspect, embodiments of the present invention also provide a computer program product, including a computer program that, when executed by a processor, implements the methods described in any of the above embodiments.

[0151] According to the technical solution of this invention, by obtaining attribute codes and their corresponding attributes from the data dimension table, generating key-value pairs with attribute codes as keys and attributes as values, associating the main data wide table with the key-value pairs, and then matching the attribute codes in the main data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, thereby filling the values ​​into a temporary table, this technical approach overcomes the performance overhead and low efficiency of serial execution caused by multiple dimension table joins in the prior art. This invention, based on key-value association of dimension tables, only requires one join, solving the performance overhead caused by multiple dimension table joins. Furthermore, since the key-value pairs are associated with a single field, parallel execution is possible, thus resolving the low efficiency problem caused by serial execution of multiple dimension table joins.

[0152] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for associating dimension tables, characterized in that, include: The fact tables from various data sources are aggregated into a master data wide table; Retrieve the attribute codes and their corresponding attributes from the data dimension table, and generate key-value pairs with the attribute codes as keys and the attributes as values; The main data wide table is associated with the key-value pairs to generate a temporary table; In the temporary table, the attribute codes in the main data wide table are matched in parallel with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and the values ​​are then filled into the temporary table. The process of associating the main data wide table with the key-value pairs to generate a temporary table includes: The key-value pairs are added as a field to the end of each row of the main data wide table to generate a temporary table.

2. The method according to claim 1, characterized in that, Retrieve attribute codes and their corresponding attributes from the data dimension table, and generate key-value pairs with the attribute code as the key and the attribute as the value, including: Based on the required dimension data, find the corresponding data dimension table; Obtain each attribute code and the attribute corresponding to each attribute code from the data dimension table; For each attribute code and its corresponding attribute, generate a key-value pair with the attribute code as the key and the attribute as the value.

3. The method according to claim 1, characterized in that, In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs to obtain the values ​​corresponding to the keys, and the values ​​are then filled into the temporary table, including: In the temporary table, the attribute codes in the main data wide table are matched with the keys in the key-value pairs in parallel using the SELECT clause to obtain the values ​​corresponding to the keys; The value is filled into the temporary table.

4. The method according to claim 1, characterized in that, Matching the attribute codes in the master data wide table with the keys in the key-value pairs to obtain the values ​​corresponding to the keys includes: For each attribute code in the master data wide table, determine whether a key with the same attribute code can be matched in the key-value pair; If so, then retrieve the value corresponding to the key; If not, then retrieve the default value.

5. The method according to claim 1, characterized in that, After filling the temporary table with the value, the process also includes: The data in the temporary table is filtered to generate a wide table of result data; The resulting wide table is stored in a table in the data warehouse.

6. The method according to claim 5, characterized in that, The data in the temporary table is filtered to generate a wide table of result data, including: The data in the temporary table is filtered using the SELECT clause to generate a wide table of result data.

7. An apparatus for associating dimension tables, characterized in that, include: The aggregation module is used to aggregate fact tables from various data sources into a master data wide table; The generation module is used to obtain attribute codes and their corresponding attributes from the data dimension table, and generate key-value pairs with the attribute codes as keys and the attributes as values. The association module is used to associate the main data wide table with the key-value pairs, thereby generating a temporary table; A fill module is used to match the attribute codes in the main data wide table with the keys in the key-value pairs in parallel in the temporary table to obtain the values ​​corresponding to the keys, and fill the values ​​into the temporary table; The association module is also used to add the key-value pair as a field to the end of each row of the main data wide table, thereby generating a temporary table.

8. An electronic device, characterized in that, include: One or more processors; Storage device for storing one or more programs. When the one or more programs are executed by the one or more processors, the one or more processors implement the method as described in any one of claims 1-6.

9. A computer-readable medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the method as described in any one of claims 1-6.

Images