Data warehouse extraction and optimization method, device, equipment and storage medium thereof

Abstract

The embodiment of the present application belongs to the field of data warehouse optimization technology, and relates to a data warehouse refinement optimization method, device, equipment and storage medium thereof, which are applied to the field of financial technology technology, including analyzing and obtaining data in all ODS layer tables that have been physically deep-processed; counting and obtaining data in layer tables that meet a preset cross-layer reference threshold, and counting and obtaining data in forms whose ODS layer is referenced more than a preset form reference threshold; using the above three types of data as data to be reorganized and developed; and performing unified processing of data indicator caliber; based on the results of the unified processing, migrating the target data to the public dimension table pre-built in the DIM layer, and completing the refinement and optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse layering and the form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing.

Description

Technical Field

[0001] The present application relates to the field of financial technology, and is mainly used in the process of optimizing data warehouses in the financial industry, and in particular to a data warehouse refining optimization method, device, equipment and storage medium thereof. Background Art

[0002] Data warehouses often contain multiple summary reports. These reports are then processed independently based on business needs and processing logic. This results in distinct processing chains for different business logic or product processing requirements. For example, in the financial industry, insurance, banking, or trading businesses, each business platform frequently retrieves raw data from the data warehouse for cleaning, processing, and indicator calculation. This results in long development times, the inability to reuse the same fields and indicators, high duplication, and significant resource consumption.

[0003] Traditionally, the financial industry has processed data marts using a standalone model, tailored to the metrics and dimensionality requirements of reports. Data processing begins at the underlying data warehouse or ODS. This is especially true for businesses like insurance, banking, or trading, which involve a large amount of business data and complex task logic. Each layer has multiple processing links, ultimately aggregated to the application layer. Depending on actual needs, some data may also require processing at the report processing layer of the visualization platform. This results in long links between the underlying and intermediate tables, a relatively narrow scope for business logic processing and product coverage, and low reusability. Furthermore, the presence of duplicate business logic increases the likelihood of repeated processing, increasing overall resource consumption, maintaining high costs, and impacting user satisfaction. Summary of the Invention

[0004] The purpose of the embodiments of the present application is to propose a data warehouse refinement optimization method, device, equipment and storage medium thereof to improve the scientific processing of data in the data warehouse, reduce storage resource consumption, reduce data storage costs, and improve user satisfaction with data processing.

[0005] In order to solve the above technical problems, the present application provides a data warehouse refinement optimization method, which adopts the following technical solutions:

[0006] A data warehouse refining and optimization method includes the following steps:

[0007] Based on the preset judgment conditions, determine whether there are ODS layer tables in the target data warehouse that exceed the preset table quantity and are physically deep processed;

[0008] If more ODS tables than the preset number of forms are physically processed, the business scenarios and form design logic corresponding to all ODS tables are analyzed. Based on the analysis results, the data in all ODS tables that are physically processed are obtained.

[0009] According to all business link tasks in the business scenario, count and obtain data in the layer table that meets the preset cross-layer reference threshold, count the forms in the ODS layer whose number of references exceeds the preset form reference threshold, and obtain data in the forms whose number of references exceeds the preset form reference threshold;

[0010] All the data in the ODS layer tables that have been physically processed, the data in the layer tables that meet the preset cross-layer reference threshold, and the data in the forms that have been referenced more than the preset form reference threshold are used as data to be reorganized and developed;

[0011] Performing unified data indicator caliber processing on the data to be reorganized and developed to obtain a unified processing result;

[0012] According to the unified processing result, the data to be reorganized and developed is migrated to the public dimension table pre-built in the DIM layer, completing the refinement and optimization of the target data warehouse.

[0013] Furthermore, the preset judgment condition includes a first judgment condition and a second judgment condition. The step of judging whether there are ODS layer tables exceeding a preset table quantity in the target data warehouse that have been subjected to physical deep processing according to the preset judgment condition specifically includes:

[0014] Count the proportion of business link tasks that read data from ODS layer tables in the total business link tasks;

[0015] According to the first judgment condition, identifying whether the task volume ratio value meets a preset task ratio threshold, wherein the first judgment condition is specifically comparing the magnitude relationship between the task volume ratio value and the task ratio threshold;

[0016] If the task volume ratio is greater than the task ratio threshold, then the task volume ratio satisfies the task ratio threshold;

[0017] Counting the percentage of data that is constructed as data in the DWD layer table in the target data warehouse after the data in the ODS layer table is read;

[0018] According to the second judgment condition, identifying whether the data volume ratio value meets a preset data volume ratio threshold, wherein the second judgment condition is specifically comparing the size relationship between the data volume ratio value and the data volume ratio threshold;

[0019] If the data volume ratio value is greater than the data volume ratio threshold, then the data volume ratio value meets the data volume ratio threshold;

[0020] If the task volume ratio value satisfies the task ratio threshold and the data volume ratio value satisfies the data volume ratio threshold, then the ODS layer tables in the target data warehouse that exceed the preset table quantity are physically deep-processed.

[0021] Furthermore, the step of counting and obtaining data in the layer table that meets a preset cross-layer reference threshold according to all business link tasks in the business scenario specifically includes:

[0022] Obtaining execution processing logs corresponding to all business link tasks, wherein the execution processing logs include data flow information within the target data warehouse during the execution of the business link tasks;

[0023] Based on the inter-data flow information, statistics are generated on the ratio between the amount of data referenced across layers in the target data warehouse and the amount of all referenced data in the target data warehouse, wherein the cross-layer reference refers to cross-layer references between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer, and TMP layer of the target data warehouse;

[0024] Determining whether the proportional relationship meets a preset cross-layer reference threshold;

[0025] If the proportional relationship meets the cross-layer reference threshold, data in the layer table that meets the cross-layer reference threshold is obtained.

[0026] Furthermore, the step of determining whether the proportional relationship meets a preset cross-layer reference threshold specifically includes:

[0027] Identifying, by comparison, a magnitude relationship between the proportional relationship and the cross-layer reference threshold;

[0028] If the proportional relationship is less than the cross-layer reference threshold, the proportional relationship satisfies the preset cross-layer reference threshold; otherwise, the proportional relationship does not satisfy the preset cross-layer reference threshold.

[0029] Furthermore, the execution processing log also includes form call information in the target data warehouse during the execution of the business link task, and the step of counting forms in the ODS layer whose reference counts exceed a preset form reference threshold specifically includes:

[0030] Based on the form call information, count the number of times each change order in the ODS layer is called;

[0031] Identify, by comparison, the relationship between the number of times each change order in the ODS layer is called and the form reference threshold;

[0032] If the number of times the current form is called is greater than the form reference threshold, the current form is the target form; otherwise, the current form is not the target form;

[0033] All target forms are obtained as forms in the ODS layer whose reference times exceed a preset form reference threshold.

[0034] Furthermore, the step of obtaining the data in the form whose number of citations exceeds a preset form citation threshold specifically includes:

[0035] According to the form name information of the target form, perform a full coverage query on all target forms;

[0036] A full coverage query result of data corresponding to all target forms is obtained as the data in the form whose number of citations exceeds a preset form citation threshold.

[0037] Furthermore, the step of performing data indicator standardization processing on the data to be reorganized and developed to obtain a unified processing result specifically includes:

[0038] Naming the data indicators to be reorganized and developed in a unified naming method;

[0039] Build a unified data indicator calculation method;

[0040] Build a unified statistical dimension for data indicators;

[0041] Constructing a unified data indicator display format, wherein constructing a unified data indicator display format includes setting a unified number of decimal display digits if the data to be reorganized and developed involves decimal value display;

[0042] Set the update time for data refinement optimization.

[0043] In order to solve the above technical problems, the present application also provides a data warehouse refinement and optimization device, which adopts the following technical solutions:

[0044] A data warehouse refining and optimization device, comprising:

[0045] The deep processing judgment module is used to judge whether there are ODS layer tables in the target data warehouse that exceed the preset table quantity and are subject to physical deep processing based on the preset judgment conditions;

[0046] The first acquisition module is used to analyze the business scenarios and form design logic corresponding to all ODS layer tables if more than a preset number of forms have been physically deep-processed, and obtain data from all ODS layer tables that have been physically deep-processed based on the analysis results;

[0047] The second acquisition module is used to count and obtain data in layer tables that meet a preset cross-layer reference threshold based on all business link tasks in the business scenario, and to count forms in the ODS layer whose number of references exceeds a preset form reference threshold, and obtain data in the forms whose number of references exceeds the preset form reference threshold;

[0048] A target data determination module is used to take all the data in the ODS layer tables that have been physically deep-processed, the data in the layer tables that meet the preset cross-layer reference threshold, and the data in the forms that have been referenced more than the preset form reference threshold as data to be reorganized and developed;

[0049] A data indicator caliber unification module is used to perform data indicator caliber unification processing on the data to be reorganized and developed, and obtain a unified processing result;

[0050] The refinement and optimization module is used to migrate the data to be reorganized and developed into the public dimension table pre-built in the DIM layer according to the unified processing result, so as to complete the refinement and optimization of the target data warehouse.

[0051] In order to solve the above technical problems, the embodiment of the present application further provides a computer device, which adopts the following technical solution:

[0052] A computer device includes a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the data warehouse refinement optimization method described above when executing the computer-readable instructions.

[0053] In order to solve the above technical problems, the embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solution:

[0054] A computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the steps of the data warehouse refinement optimization method as described above.

[0055] Compared with the prior art, the embodiments of the present application have the following beneficial effects:

[0056] The data warehouse refinement optimization method described in the embodiment of the present application is to determine whether there are ODS layer tables in the target data warehouse that have been physically deep-processed, exceeding the preset form quantity; analyze and obtain data in all physically deep-processed ODS layer tables; count and obtain data in layer tables that meet the preset cross-layer reference threshold, count and obtain data in forms whose ODS layer references exceed the preset form reference threshold; use the above three types of data as data to be reorganized and developed; perform data indicator caliber unification processing on the data to be reorganized and developed to obtain a unified processing result; and according to the unified processing result, migrate the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, thereby completing the refinement optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse stratification and form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] In order to more clearly illustrate the solutions in this application, a brief introduction will be given below to the drawings required for use in the description of the embodiments of this application. Obviously, the drawings described below are some embodiments of this application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0058] Figure 1 is an exemplary system architecture diagram to which the present application may be applied;

[0059] Figure 2 A flowchart of an embodiment of a data warehouse refinement optimization method according to the present application;

[0060] Figure 3 yes Figure 2 A flowchart of a specific embodiment of step 201 is shown;

[0061] Figure 4 yes Figure 2 A flowchart of a specific embodiment of step 203 is shown;

[0062] Figure 5 yes Figure 4 A flowchart of a specific embodiment of step 401 is shown;

[0063] Figure 6 yes Figure 4 A flowchart of a specific embodiment of step 402 is shown;

[0064] Figure 7 A structural diagram of an embodiment of a data warehouse refinement and optimization device according to the present application;

[0065] Figure 8 yes Figure 7 A schematic structural diagram of a specific embodiment of the module 701 is shown;

[0066] Figure 9 yes Figure 7 A schematic structural diagram of a specific embodiment of the module 703 is shown;

[0067] Figure 10 A schematic structural diagram of an embodiment of a computer device according to the present application. DETAILED DESCRIPTION

[0068] Unless otherwise defined, all technical and scientific terms used herein have the same meanings as commonly understood by those skilled in the art to which this application belongs. The terms used in the specification of the application are for the purpose of describing specific embodiments only and are not intended to limit this application. The terms "including" and "having" and any variations thereof in the specification and claims of this application and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of this application or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

[0069] References herein to "embodiments" mean that a particular feature, structure, or characteristic described in connection with the embodiments may be included in at least one embodiment of the present application. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor does it constitute an independent or alternative embodiment that is mutually exclusive of other embodiments. It is understood, both explicitly and implicitly, by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0070] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings.

[0071] like Figure 1 As shown, system architecture 100 may include terminal devices 101, 102, 103, a network 104, and a server 105. Network 104 is a medium for providing communication links between terminal devices 101, 102, 103 and server 105. Network 104 may include various connection types, such as wired or wireless communication links or fiber optic cables.

[0072] Users can use terminal devices 101, 102, and 103 to interact with server 105 via network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, and 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0073] Terminal devices 101, 102, and 103 can be various electronic devices with display screens and support web browsing, including but not limited to smartphones, tablet computers, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 (Moving Picture Experts Group Audio Layer IV), laptop computers, desktop computers, etc.

[0074] The server 105 may be a server that provides various services, such as a background server that provides support for web pages displayed on the terminal devices 101 , 102 , and 103 .

[0075] It should be noted that the data warehouse refining and optimization method provided in the embodiment of the present application is generally executed by a server / terminal device, and accordingly, the data warehouse refining and optimization device is generally set in the server / terminal device.

[0076] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0077] Continue to refer Figure 2 , shows a flow chart of an embodiment of the data warehouse refinement optimization method according to the present application. The data warehouse refinement optimization method includes the following steps:

[0078] Step 201 : Based on a preset judgment condition, it is determined whether there are any ODS layer tables in the target data warehouse that have been physically deep-processed and that exceed a preset table quantity.

[0079] In this embodiment, the target data warehouse may be a data warehouse for the financial industry. Taking financial industry order data as an example, the data stored in the ODS layer table may be basic information data of goods, basic information data of purchasing customers, order payment data, etc.

[0080] In this embodiment, the preset judgment condition includes a first judgment condition and a second judgment condition.

[0081] Continue to refer Figure 3 , Figure 3 yes Figure 2 The flowchart of a specific embodiment of step 201 shown includes:

[0082] Step 301: Count the proportion of the number of business link tasks for reading data from the ODS layer table in the total number of business link tasks.

[0083] Step 302: Identify whether the task volume ratio value satisfies a preset task ratio threshold value based on the first judgment condition, wherein the first judgment condition is specifically comparing the task volume ratio value with the task ratio threshold value;

[0084] Step 303: If the task volume ratio is greater than the task ratio threshold, the task volume ratio satisfies the task ratio threshold.

[0085] Step 304: Count the percentage of data in the DWD layer table constructed in the target data warehouse after the data in the ODS layer table is read;

[0086] Step 305: Identify whether the data volume ratio value satisfies a preset data volume ratio threshold value based on the second judgment condition, wherein the second judgment condition is specifically comparing the data volume ratio value with the data volume ratio threshold value;

[0087] Step 306: If the data volume ratio is greater than the data volume ratio threshold, the data volume ratio satisfies the data volume ratio threshold.

[0088] Step 307: If the task volume ratio value satisfies the task ratio threshold and the data volume ratio value satisfies the data volume ratio threshold, then the ODS layer tables in the target data warehouse that exceed the preset table quantity are physically deep-processed.

[0089] For example, for any data processing link in the financial industry order business theme (for example, payment data), the number of business link tasks of the payment data in the ODS layer table is directly read. Through the reading results, it is learned that the number of business link tasks of the payment data in the ODS layer table accounts for more than 20% of the total number of tasks, and the amount of data of the downstream output of the ODS layer read as the detail layer table (such as DMD) accounts for more than 30% of the total data volume in the ODS layer, indicating that the ODS layer table exceeding the preset form quantity is physically deep-processed, and it is necessary to analyze the order business scenario and form design logic.

[0090] In this embodiment, the business link task of the data in the ODS layer table may be a data processing task of the ODS layer, and the data processing task of the ODS layer may be a task of processing payment data in an order business.

[0091] In this embodiment, the downstream output of the ODS layer being read is the data volume of the detail layer table (such as DMD), which refers to the data volume constructed as the data in the DWD layer table in the target data warehouse after the data in the ODS layer table is read.

[0092] Through the first judgment condition and the second judgment condition, the ODS layer tables that have been physically deep processed in the target data warehouse are comprehensively screened out, so as to further refine and optimize the ODS layer tables that have been physically deep processed, improve the scientific processing of the ODS layer tables, reduce storage resource consumption, reduce data storage costs, and improve user satisfaction with data processing.

[0093] Step 202: If there are ODS layer tables that have been physically deep processed exceeding the preset number of forms, the business scenarios and form design logic corresponding to all ODS layer tables are analyzed, and based on the analysis results, the data in all ODS layer tables that have been physically deep processed are obtained.

[0094] In this embodiment, physical deep processing judgment rules are pre-set, and through the physical deep processing judgment rules and the analysis results, all data in the ODS layer table that has been physically deep processed and all data in the ODS layer that has not been physically deep processed are distinguished.

[0095] In this embodiment, the pre-set physical deep processing judgment rules are specifically, setting the link complexity and form design logic complexity for the business link tasks corresponding to different business scenarios in advance; if the link complexity and form design logic complexity corresponding to the target ODS layer table both meet the corresponding complexity thresholds, then the target ODS layer table is the ODS layer table that has been physically deep processed; if any of the link complexity and form design logic complexity corresponding to the target ODS layer table does not meet the corresponding complexity threshold, then the target ODS layer table is the ODS layer table that has not been physically deep processed.

[0096] By pre-setting physical deep processing judgment rules, the ODS layer tables that have been physically deep processed in the target data warehouse are screened out, thereby clarifying the ODS layer tables that have been physically deep processed and performing refinement and optimization. In particular, data warehouses in the financial industry often involve massive amounts of basic data. Improving the scientific processing of ODS layer tables can effectively reduce storage resource consumption, reduce data storage costs, and improve user satisfaction with data processing.

[0097] Step 203: Based on all business link tasks in the business scenario, count and obtain the data in the layer table that meets the preset cross-layer reference threshold, and count the forms in the ODS layer that are referenced more than the preset form reference threshold, and obtain the data in the forms that are referenced more than the preset form reference threshold.

[0098] Continue to refer Figure 4 , Figure 4 yes Figure 2 The flowchart of a specific embodiment of step 203 shown includes:

[0099] Step 401: Count and obtain data in the layer table that meets a preset cross-layer reference threshold based on all business link tasks in the business scenario;

[0100] Through all business link tasks in the business scenario, the amount of data referenced across layers between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer and TMP layer of the target data warehouse is identified, that is, the DWD layer should originally reference the data in the ODS layer, and the cross-layer reference refers to the ADS layer directly referencing the data in the ODS layer without going through the DWD layer. For example, the cross-layer reference rate is 20%, and the preset cross-layer reference threshold is 30%. Then, the form design of each layer in the data warehouse is not perfect, and the reference stickiness between each layer is not strong. The forms of each layer in the data warehouse can be further refined and optimized so that the cross-layer reference rate between each layer in the data warehouse is greater than the cross-layer reference threshold, thereby ensuring the scientificity and rationality of the data warehouse layering and the form design in each layer.

[0101] Step 402: Counting forms in the ODS layer whose reference counts exceed a preset form reference threshold based on all business link tasks in the business scenario;

[0102] Through all the business chain tasks in the business scenario, forms that are referenced more than a preset form reference threshold in the ODS layer of the target data warehouse are identified. For example, in all the business chain tasks in the entire business scenario, a form in the ODS layer is referenced 60 times, while the preset form reference threshold is 20 times. This indicates that the design of the form is unreasonable and needs to be refined and optimized in time. Through refinement and optimization, the data in the form is reorganized to ensure that the number of references of each form after sorting is no more than 20 times, thereby ensuring the scientificity and rationality of the data warehouse layering and the form design in each layer.

[0103] Step 403: Acquire the data in the form whose number of citations exceeds a preset form citation threshold.

[0104] Continue to refer Figure 5 , Figure 5 yes Figure 4 The flowchart of a specific embodiment of step 401 shown includes:

[0105] Step 501: Obtain execution processing logs corresponding to all business link tasks, wherein the execution processing logs include data flow information within the target data warehouse during the execution of the business link tasks;

[0106] Step 502: Based on the inter-data flow information, a proportional relationship between the amount of data referenced across layers in the target data warehouse and the amount of all referenced data in the target data warehouse is calculated. The cross-layer reference refers to cross-layer references between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer, and TMP layer of the target data warehouse.

[0107] Step 503: determine whether the ratio relationship meets a preset cross-layer reference threshold;

[0108] Step 504: If the proportional relationship meets the cross-layer reference threshold, then obtain the data in the layer table that meets the cross-layer reference threshold.

[0109] In this embodiment, the step of determining whether the proportional relationship meets the preset cross-layer reference threshold specifically includes: identifying the size relationship between the proportional relationship and the cross-layer reference threshold by comparison; if the proportional relationship is smaller than the cross-layer reference threshold, then the proportional relationship meets the preset cross-layer reference threshold, otherwise, the proportional relationship does not meet the preset cross-layer reference threshold.

[0110] By comparing the execution processing logs corresponding to all business link tasks in the business scenario with a preset method, the data and data volume referenced across layers between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer and TMP layer of the target data warehouse are identified, thereby clarifying the data with weak reference stickiness in the data warehouse, and further refining and optimizing the forms of each layer in the data warehouse so that the cross-layer reference rate between layers in the data warehouse is greater than the cross-layer reference threshold, thereby ensuring the scientificity and rationality of the data warehouse layering and the form design in each layer, and improving the reference stickiness of data in the forms between layers.

[0111] In this embodiment, the execution processing log also includes the form calling information in the target data warehouse during the execution of the business link task.

[0112] Continue to refer Figure 6 , Figure 6 yes Figure 4 The flowchart of a specific embodiment of step 402 shown includes:

[0113] Step 601: Based on the form call information, count the number of times each change order in the ODS layer is called;

[0114] Step 602: Identify the relationship between the number of times each change order in the ODS layer is called and the form reference threshold by comparison;

[0115] Step 603: If the number of times the current form is called is greater than the form reference threshold, the current form is the target form; otherwise, the current form is not the target form;

[0116] Step 604 : Acquire all target forms as forms in the ODS layer whose reference times exceed a preset form reference threshold.

[0117] By using the form call information in the target data warehouse during the execution of the business chain task, the forms in the ODS layer of the target data warehouse whose reference times exceed the preset form reference threshold are identified, and timely refinement and optimization are performed. The data in the form is reorganized through refinement and optimization, so as to ensure that the reference times of each form after sorting is no more than the preset reference threshold, thereby ensuring the scientificity and rationality of the data warehouse layering and the form design in each layer.

[0118] In this embodiment, the step of obtaining the data in the form whose number of citations exceeds the preset form reference threshold specifically includes: performing a full coverage query on the form data of all target forms according to the form name information of the target form; obtaining the full coverage query results of the data corresponding to all target forms as the data in the form whose number of citations exceeds the preset form reference threshold.

[0119] Step 204 , taking all the physically deep-processed ODS layer table data, the layer table data that meets the preset cross-layer reference threshold, and the form data whose reference times exceed the preset form reference threshold as data to be reorganized and developed.

[0120] Step 205: performing data indicator caliber unification processing on the data to be reorganized and developed to obtain a unified processing result.

[0121] In this embodiment, the step of performing unified processing on the data indicators of the data to be reorganized and developed and obtaining the unified processing results specifically includes: naming the data indicators of the data to be reorganized and developed in a unified naming method; constructing a unified data indicator calculation method; constructing a unified data indicator statistical dimension; constructing a unified data indicator display format, wherein the construction of the unified data indicator display format includes setting a unified number of decimal display digits if the data to be reorganized and developed involves decimal value display; and setting an update time for data refinement optimization.

[0122] By standardizing the data to be reorganized and developed before refining and optimization, it is ensured that the data to be reorganized and developed have a unified data indicator format when refining and optimization are carried out, avoiding the situation where the refining and optimization processing is messy and difficult due to the inconsistency of data indicator types and formats, making the refining and optimization process as scientific and standardized as possible, and reducing errors during refining and optimization.

[0123] In this embodiment, by setting the update time for data refinement and optimization, when refining and optimizing the data in the data warehouse thereafter, the data warehouse refinement and optimization method can be continuously executed multiple times according to the update time, thereby realizing regular multiple refinement and optimization, and ensuring the later maintenance of the data warehouse.

[0124] Step 206 : Based on the unified processing result, the data to be reorganized and developed is migrated to the public dimension table pre-built in the DIM layer, thereby completing the refinement and optimization of the target data warehouse.

[0125] By migrating the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, the data in the dimension table with unscientific or unreasonable design in the data warehouse can be saved in the public dimension table, and such data can be cached in the form of public dimension table, thereby reducing the cache pressure of the ODS layer, DWD layer, DWS layer, and ADS layer of the data warehouse, and reducing the consumption of storage resources and call resources.

[0126] This application determines whether there are ODS layer tables in the target data warehouse that have been physically deep-processed, exceeding the preset number of forms; analyzes and obtains data in all physically deep-processed ODS layer tables; counts and obtains data in layer tables that meet the preset cross-layer reference threshold, counts and obtains data in forms whose ODS layer references exceed the preset form reference threshold; uses the above three types of data as data to be reorganized and developed; performs data indicator caliber unification processing on the data to be reorganized and developed to obtain a unified processing result; and according to the unified processing result, migrates the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, thereby completing the refinement and optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse layering and the form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing.

[0127] The embodiments of the present application can acquire and process relevant data based on artificial intelligence technology. Artificial Intelligence (AI) is the theory, method, technology, and application system that uses digital computers or machines controlled by digital computers to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use knowledge to achieve optimal results.

[0128] Fundamental AI technologies generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing, operating / interaction systems, and mechatronics. AI software technologies primarily encompass computer vision, robotics, biometrics, speech processing, natural language processing, and machine learning / deep learning.

[0129] In an embodiment of the present application, by refining and optimizing the target data (e.g., payment data) screened out from the financial industry data warehouse, the scientificity and rationality of the data warehouse layering and form design in each layer are ensured, the scientific processing of data in the data warehouse is improved, storage resource consumption is reduced, data storage costs are reduced, and user satisfaction with payment data processing is improved.

[0130] Further references Figure 7 , as a response to the above Figure 2 The present application provides an embodiment of a data warehouse refinement optimization device. Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0131] like Figure 7 As shown, the data warehouse refining and optimization device 700 of this embodiment includes: a deep processing judgment module 701, a first acquisition module 702, a second acquisition module 703, a target data determination module 704, a data indicator caliber unification module 705 and a refining and optimization module 706. Among them:

[0132] The deep processing judgment module 701 is used to judge whether there are ODS layer tables exceeding the preset table quantity in the target data warehouse that are subject to physical deep processing according to the preset judgment conditions;

[0133] The first acquisition module 702 is configured to analyze the business scenarios and form design logic corresponding to all ODS layer tables if more than a preset number of forms have been physically deep processed, and acquire data from all ODS layer tables that have been physically deep processed based on the analysis results;

[0134] The second acquisition module 703 is configured to count and acquire data in layer tables that meet a preset cross-layer reference threshold based on all business link tasks in the business scenario, count forms in the ODS layer whose number of references exceeds a preset form reference threshold, and acquire data in the forms whose number of references exceeds the preset form reference threshold;

[0135] The target data determination module 704 is configured to use the data in all physically deep-processed ODS layer tables, the data in the layer tables that meet the preset cross-layer reference threshold, and the data in the forms that have been referenced more than the preset form reference threshold as data to be reorganized and developed;

[0136] The data indicator caliber unification module 705 is used to perform data indicator caliber unification processing on the data to be reorganized and developed, and obtain a unification processing result;

[0137] The refinement and optimization module 706 is used to migrate the data to be reorganized and developed into the public dimension table pre-built in the DIM layer according to the unified processing result, so as to complete the refinement and optimization of the target data warehouse.

[0138] Continue to refer Figure 8 , Figure 8 yes Figure 7 The structure diagram of a specific embodiment of the module 701 is shown, wherein the deep processing judgment module 701 includes a first judgment submodule and a second judgment submodule, wherein:

[0139] The first judgment submodule includes a first statistical unit 801, a first identification unit 802, and a first comparison unit 803, wherein:

[0140] The first statistical unit 801 is used to count the proportion of the number of business link tasks that read data in the ODS layer table in the total business link tasks;

[0141] A first identification unit 802 is configured to identify, based on the first judgment condition, whether the task volume ratio value satisfies a preset task ratio threshold value, wherein the first judgment condition is specifically a comparison between the task volume ratio value and the task ratio threshold value;

[0142] A first comparing unit 803 is configured to determine that if the task volume ratio is greater than the task ratio threshold, the task volume ratio satisfies the task ratio threshold;

[0143] The second judgment submodule includes a second statistical unit 804, a second identification unit 805, and a second comparison unit 806, wherein:

[0144] The second statistical unit 804 is used to count the proportion of data volume constructed as data in the DWD layer table in the target data warehouse after the data in the ODS layer table is read;

[0145] A second identification unit 805 is configured to identify, based on the second judgment condition, whether the data volume ratio value satisfies a preset data volume ratio threshold value, wherein the second judgment condition is specifically a comparison between the data volume ratio value and the data volume ratio threshold value;

[0146] A second comparing unit 806 is configured to determine that if the data volume ratio value is greater than the data volume ratio threshold, the data volume ratio value satisfies the data volume ratio threshold;

[0147] The deep processing judgment module 701 also includes a comprehensive judgment unit 807, which is used to determine if the task volume ratio value meets the task ratio threshold and the data volume ratio value meets the data volume ratio threshold, then the ODS layer tables in the target data warehouse that exceed the preset form quantity are physically deep processed.

[0148] Continue to refer Figure 9 , Figure 9 yes Figure 7 The schematic diagram of the structure of a specific embodiment of the module 703 is shown, wherein the second acquisition module 703 includes a cross-layer reference analysis submodule, an ODS layer reference analysis submodule and an ODS layer reference data acquisition submodule, wherein,

[0149] The cross-layer reference analysis submodule includes a log acquisition unit 901, a third statistics unit 902, a third judgment unit 903, and a first data acquisition unit 904, wherein:

[0150] The log acquisition unit 901 is configured to acquire the execution processing logs corresponding to all business link tasks, wherein the execution processing logs include the data flow information within the target data warehouse during the execution of the business link tasks;

[0151] A third statistical unit 902 is configured to calculate, based on the inter-data flow information, a proportional relationship between the amount of data referenced across layers in the target data warehouse and the amount of all referenced data in the target data warehouse, wherein the cross-layer reference refers to cross-layer references between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer, and TMP layer of the target data warehouse;

[0152] The third judging unit 903 is configured to judge whether the ratio relationship satisfies a preset cross-layer reference threshold;

[0153] The first data acquisition unit 904 is configured to acquire data in the layer table that meets the cross-layer reference threshold if the proportion relationship meets the cross-layer reference threshold;

[0154] The ODS layer reference analysis submodule includes a fourth statistical unit 905, a third identification unit 906, a third comparison unit 907 and a target form acquisition unit 908, wherein:

[0155] The fourth statistical unit 905 is configured to count the number of times each change order in the ODS layer is called based on the form call information;

[0156] The third identification unit 906 is used to identify the relationship between the number of times each change order in the ODS layer is called and the form reference threshold by comparison;

[0157] The third comparing unit 907 is configured to determine that the current form is a target form if the number of times the current form is called is greater than the form reference threshold; otherwise, the current form is not a target form;

[0158] The target form acquisition unit 908 is used to acquire all target forms as forms in the ODS layer whose reference counts exceed a preset form reference threshold;

[0159] The ODS layer reference data acquisition submodule includes a data query unit 909 and a second data acquisition unit 910, wherein:

[0160] The data query unit 909 is used to perform a full coverage query on form data of all target forms according to the form name information of the target form;

[0161] The second data acquisition unit 910 is used to acquire the data full coverage query results corresponding to all target forms as the data in the form whose number of citations exceeds the preset form citation threshold.

[0162] This application determines whether there are ODS layer tables in the target data warehouse that have been physically deep-processed, exceeding the preset number of forms; analyzes and obtains data in all physically deep-processed ODS layer tables; counts and obtains data in layer tables that meet the preset cross-layer reference threshold, counts and obtains data in forms whose ODS layer references exceed the preset form reference threshold; uses the above three types of data as data to be reorganized and developed; performs data indicator caliber unification processing on the data to be reorganized and developed to obtain a unified processing result; and according to the unified processing result, migrates the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, thereby completing the refinement and optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse layering and the form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing.

[0163] Those skilled in the art will appreciate that all or part of the processes in the above-described method embodiments can be implemented by instructing related hardware via computer-readable instructions. The computer-readable instructions can be stored in a computer-readable storage medium, and when the program is executed, it can include the processes in the above-described method embodiments. The aforementioned storage medium can be a non-volatile storage medium such as a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0164] It should be understood that although the steps in the flowcharts of the accompanying drawings are shown in sequence as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some of the steps in the flowcharts of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0165] To solve the above technical problems, the present application also provides a computer device. Figure 10 , Figure 10 This is a basic structural block diagram of the computer device in this embodiment.

[0166] The computer device 10 includes a memory 10a, a processor 10b, and a network interface 10c that are interconnected through a system bus. It should be noted that the figure only shows a computer device 10 having components 10a-10c, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0167] The computer device may be a desktop computer, notebook computer, PDA, cloud server, etc. The computer device may interact with the user via a keyboard, mouse, remote control, touchpad, or voice control device.

[0168] The memory 10a includes at least one type of readable storage medium, including flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the memory 10a may be an internal storage unit of the computer device 10, such as the hard disk or memory of the computer device 10. In other embodiments, the memory 10a may also be an external storage device of the computer device 10, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash memory card, etc. equipped on the computer device 10. Of course, the memory 10a may also include both the internal storage unit of the computer device 10 and its external storage device. In this embodiment, the memory 10a is generally used to store the operating system and various application software installed on the computer device 10, such as computer-readable instructions for the data warehouse refinement optimization method. In addition, the memory 10a can also be used to temporarily store various types of data that have been output or are to be output.

[0169] In some embodiments, the processor 10b may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. The processor 10b is generally used to control the overall operation of the computer device 10. In this embodiment, the processor 10b is used to execute computer-readable instructions stored in the memory 10a or process data, such as executing computer-readable instructions for the data warehouse refinement optimization method.

[0170] The network interface 10c may include a wireless network interface or a wired network interface. The network interface 10c is generally used to establish a communication connection between the computer device 10 and other electronic devices.

[0171] The computer device proposed in this embodiment belongs to the technical field of data warehouse optimization in the financial industry. This application determines whether there are ODS layer tables in the target data warehouse that have been physically deep-processed, exceeding the preset form quantity; analyzes and obtains data in all physically deep-processed ODS layer tables; counts and obtains data in layer tables that meet the preset cross-layer reference threshold, counts and obtains data in forms whose ODS layer references exceed the preset form reference threshold; uses the above three types of data as data to be reorganized and developed; performs data indicator caliber unification processing on the data to be reorganized and developed, and obtains unified processing results; according to the unified processing results, migrates the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, and completes the refinement and optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse stratification and form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing.

[0172] The present application also provides another embodiment, namely, providing a computer-readable storage medium, wherein the computer-readable storage medium stores computer-readable instructions, and the computer-readable instructions can be executed by a processor to enable the processor to perform the steps of the data warehouse refinement optimization method as described above.

[0173] The computer-readable storage medium proposed in this embodiment belongs to the technical field of data warehouse optimization in the financial industry. This application determines whether there are ODS layer tables in the target data warehouse that have been physically deep-processed, which exceeds the preset form quantity; analyzes and obtains data in all physically deep-processed ODS layer tables; counts and obtains data in layer tables that meet the preset cross-layer reference threshold, counts and obtains data in forms whose ODS layer references exceed the preset form reference threshold; uses the above three types of data as data to be reorganized and developed; performs data indicator caliber unification processing on the data to be reorganized and developed, and obtains a unified processing result; according to the unified processing result, migrates the data to be reorganized and developed to the public dimension table pre-built in the DIM layer, and completes the refinement and optimization of the target data warehouse. This ensures the scientificity and rationality of the data warehouse layering and the form design in each layer, improves the scientific processing of data in the data warehouse, reduces storage resource consumption, reduces data storage costs, and improves user satisfaction with data processing.

[0174] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus the necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0175] Obviously, the embodiments described above are only some of the embodiments of the present application, rather than all of the embodiments. The preferred embodiments of the present application are given in the accompanying drawings, but they do not limit the patent scope of the present application. The present application can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present application more thorough and comprehensive. Although the present application has been described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions described in the aforementioned specific embodiments, or to make equivalent replacements for some of the technical features therein. Any equivalent structure made using the contents of the present application specification and the accompanying drawings, directly or indirectly used in other related technical fields, is also within the scope of patent protection of the present application.

Claims

1. A data warehouse refinement optimization method, characterized in that: The steps include: Based on the preset judgment conditions, determine whether there are ODS layer tables in the target data warehouse that exceed the preset table quantity and are physically deep processed; If more ODS tables than the preset number of forms are physically processed, the business scenarios and form design logic corresponding to all ODS tables are analyzed. Based on the analysis results, the data in all ODS tables that are physically processed are obtained. According to all business link tasks in the business scenario, count and obtain data in the layer table that meets the preset cross-layer reference threshold, count the forms in the ODS layer whose number of references exceeds the preset form reference threshold, and obtain data in the forms whose number of references exceeds the preset form reference threshold; All the data in the ODS layer tables that have been physically processed, the data in the layer tables that meet the preset cross-layer reference threshold, and the data in the forms that have been referenced more than the preset form reference threshold are used as data to be reorganized and developed; Performing unified data indicator caliber processing on the data to be reorganized and developed to obtain a unified processing result; According to the unified processing result, the data to be reorganized and developed is migrated to the public dimension table pre-built in the DIM layer, completing the refinement and optimization of the target data warehouse.

2. The data warehouse refinement and optimization method according to claim 1, characterized in that: The preset judgment conditions include a first judgment condition and a second judgment condition. The step of judging whether there are ODS layer tables exceeding a preset table quantity in the target data warehouse that have been subjected to physical deep processing according to the preset judgment conditions specifically includes: Count the proportion of business link tasks that read data from ODS layer tables in the total business link tasks; According to the first judgment condition, identifying whether the task volume ratio value meets a preset task ratio threshold, wherein the first judgment condition is specifically comparing the magnitude relationship between the task volume ratio value and the task ratio threshold; If the task volume ratio is greater than the task ratio threshold, then the task volume ratio satisfies the task ratio threshold; Counting the percentage of data that is constructed as data in the DWD layer table in the target data warehouse after the data in the ODS layer table is read; According to the second judgment condition, identifying whether the data volume ratio value meets a preset data volume ratio threshold, wherein the second judgment condition is specifically comparing the size relationship between the data volume ratio value and the data volume ratio threshold; If the data volume ratio value is greater than the data volume ratio threshold, then the data volume ratio value meets the data volume ratio threshold; If the task volume ratio value satisfies the task ratio threshold and the data volume ratio value satisfies the data volume ratio threshold, then the ODS layer tables in the target data warehouse that exceed the preset table quantity are physically deep-processed.

3. The data warehouse refinement and optimization method according to claim 1, characterized in that: The step of counting and obtaining data in the layer table that meets a preset cross-layer reference threshold according to all business link tasks in the business scenario specifically includes: Obtaining execution processing logs corresponding to all business link tasks, wherein the execution processing logs include data flow information within the target data warehouse during the execution of the business link tasks; Based on the inter-data flow information, statistics are generated on the ratio between the amount of data referenced across layers in the target data warehouse and the amount of all referenced data in the target data warehouse, wherein the cross-layer reference refers to cross-layer references between the ODS layer, DWD layer, DWS layer, ADS layer, DIM layer, and TMP layer of the target data warehouse; Determining whether the proportional relationship meets a preset cross-layer reference threshold; If the proportional relationship meets the cross-layer reference threshold, data in the layer table that meets the cross-layer reference threshold is obtained.

4. The data warehouse refinement and optimization method according to claim 3, characterized in that: The step of determining whether the proportional relationship meets a preset cross-layer reference threshold specifically includes: Identifying, by comparison, a magnitude relationship between the proportional relationship and the cross-layer reference threshold; If the proportional relationship is less than the cross-layer reference threshold, the proportional relationship satisfies the preset cross-layer reference threshold; otherwise, the proportional relationship does not satisfy the preset cross-layer reference threshold.

5. The data warehouse refinement and optimization method according to claim 3, characterized in that: The execution processing log also includes the form call information in the target data warehouse during the execution of the business link task, and the step of counting the forms in the ODS layer whose reference counts exceed a preset form reference threshold specifically includes: Based on the form call information, count the number of times each change order in the ODS layer is called; Identify, by comparison, the relationship between the number of times each change order in the ODS layer is called and the form reference threshold; If the number of times the current form is called is greater than the form reference threshold, the current form is the target form; otherwise, the current form is not the target form; All target forms are obtained as forms in the ODS layer whose reference times exceed a preset form reference threshold.

6. The data warehouse refinement and optimization method according to claim 5, characterized in that: The step of obtaining the data in the form whose number of citations exceeds a preset form citation threshold specifically includes: According to the form name information of the target form, perform a full coverage query on all target forms; A full coverage query result of data corresponding to all target forms is obtained as the data in the form whose number of citations exceeds a preset form citation threshold.

7. The data warehouse refinement and optimization method according to any one of claims 1 to 6, characterized in that: The step of performing data indicator standardization processing on the data to be reorganized and developed to obtain the unified processing result specifically includes: Naming the data indicators to be reorganized and developed in a unified naming method; Build a unified data indicator calculation method; Build a unified statistical dimension for data indicators; Constructing a unified data indicator display format, wherein constructing a unified data indicator display format includes setting a unified number of decimal display digits if the data to be reorganized and developed involves decimal value display; Set the update time for data refinement optimization.

8. A data warehouse refining and optimization device, characterized in that: include: The deep processing judgment module is used to judge whether there are ODS layer tables exceeding the preset table quantity in the target data warehouse that are subject to physical deep processing based on the preset judgment conditions; The first acquisition module is used to analyze the business scenarios and form design logic corresponding to all ODS layer tables if more than a preset number of forms have been physically deep-processed, and obtain data from all ODS layer tables that have been physically deep-processed based on the analysis results; The second acquisition module is used to count and obtain data in layer tables that meet a preset cross-layer reference threshold based on all business link tasks in the business scenario, and to count forms in the ODS layer whose number of references exceeds a preset form reference threshold, and obtain data in the forms whose number of references exceeds the preset form reference threshold; A target data determination module is used to take all the data in the ODS layer tables that have been physically deep-processed, the data in the layer tables that meet the preset cross-layer reference threshold, and the data in the forms that have been referenced more than the preset form reference threshold as data to be reorganized and developed; A data indicator caliber unification module is used to perform data indicator caliber unification processing on the data to be reorganized and developed, and obtain a unified processing result; The refinement and optimization module is used to migrate the data to be reorganized and developed into the public dimension table pre-built in the DIM layer according to the unified processing result, so as to complete the refinement and optimization of the target data warehouse.

9. A computer device comprising a memory and a processor, wherein the memory stores computer-readable instructions, and the processor implements the steps of the data warehouse refinement optimization method according to any one of claims 1 to 7 when executing the computer-readable instructions.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-readable instructions, which, when executed by a processor, implement the steps of the data warehouse refinement optimization method according to any one of claims 1 to 7.

Images