Method and device for optimizing data warehouse, electronic equipment and storage medium

Abstract

The application relates to the technical field of big data, and provides an optimization method and device of a data warehouse, electronic equipment and a storage medium, the method comprising the following steps: in response to an abnormality monitoring request received, obtaining a data warehouse to be monitored; according to each business line, performing health analysis on a corresponding sub-data warehouse by using multiple preset indexes; calculating the health degree of the corresponding sub-data warehouse according to multiple health analysis results of the multiple preset indexes; monitoring the corresponding sub-data warehouse according to the health degree of each sub-data warehouse calculated; and optimizing the data warehouse according to multiple monitoring results returned by multiple sub-data warehouses. The health of the sub-data warehouses of each business line in the data warehouse is analyzed by using multiple preset indexes, unified management of the data warehouse is realized, and the optimization efficiency of the data warehouse is improved.

Description

Technical Field

[0001] This invention relates to the field of big data technology, and specifically to a data warehouse optimization method, apparatus, electronic device, and storage medium. Background Technology

[0002] With the rapid development of the information age, enterprises have an increasing demand for building data warehouses, but the quality of the built data warehouses varies greatly.

[0003] Currently, the data warehouses of various business lines within enterprises lack unified management and standardization, making it impossible to uniformly manage the quality of data warehouses and promptly clean up or repair unhealthy data warehouses, resulting in low efficiency in data warehouse optimization.

[0004] Therefore, it is necessary to propose a method that can quickly optimize data warehouses. Summary of the Invention

[0005] In view of the above, it is necessary to propose a data warehouse optimization method, device, electronic equipment and storage medium. By using multiple preset indicators to perform health analysis on the sub-data warehouses of various business lines in the data warehouse, unified management of the data warehouse is achieved and the optimization efficiency of the data warehouse is improved.

[0006] A first aspect of the present invention provides a method for optimizing a data warehouse, the method comprising:

[0007] In response to a received anomaly monitoring request, the data warehouse to be monitored is obtained, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line;

[0008] For each business line, multiple preset indicators are used to conduct health analysis on the corresponding sub-data warehouse;

[0009] Based on the health analysis results of the multiple preset indicators, the health of the corresponding sub-data warehouse is calculated;

[0010] Monitor the corresponding sub-data warehouse based on the calculated health status of each sub-data warehouse;

[0011] The data warehouse is optimized based on multiple monitoring results returned by multiple sub-data warehouses.

[0012] Optionally, the step of performing health analysis on the corresponding sub-data warehouse using multiple preset indicators according to each business line includes:

[0013] Retrieve the corresponding sub-data warehouse based on the theme name of each business line;

[0014] According to the table name normalization requirements, perform a health analysis on the table names of the data tables in the sub-data warehouse; and / or

[0015] Based on the number of times the data tables are referenced, perform a health analysis on the number of references to the data tables in the common data layer of the sub-data warehouse; and / or

[0016] Based on the reference information of the data tables, perform a health analysis on the reference information of the data tables in the sub-data warehouse; and / or

[0017] Based on the task link depth, perform a health analysis on the task link depth in the sub-data warehouse; and / or

[0018] Based on the single-task runtime, perform a health analysis on the single-task execution status of the sub-data warehouse; and / or

[0019] Based on the task execution failure rate, perform a health analysis on the task execution status of the sub-data warehouse; and / or

[0020] A health analysis is performed on the repair status of failed tasks in the sub-data warehouse based on the repair time of failed tasks.

[0021] Optionally, the health analysis of the table names in the sub-data warehouse according to the table name normalization requirements includes:

[0022] Identify the table names of the data tables in the sub-data warehouse;

[0023] Determine whether the table name of the data table meets the preset normalization requirements;

[0024] When the table name of the data table meets the preset normalization requirements, the table name of the data table is determined to be healthy;

[0025] Calculate the first total number of healthy data tables in the sub-data warehouse;

[0026] Calculate the second total number of data tables in the sub-data warehouse;

[0027] The quotient obtained by dividing the first total by the second total is determined as the health analysis result corresponding to the table name.

[0028] Optionally, the health analysis of the reference counts of data tables in the common data layer of the sub-data warehouse based on the reference counts of the data tables includes:

[0029] Record the number of fields in the data tables of the data common layer that are referenced by the application layer;

[0030] The number of fields is determined as the number of times the data table is referenced;

[0031] When the number of times the data table is referenced is greater than or equal to a preset first threshold, the number of times the data table is referenced is determined to be healthy.

[0032] Calculate the third total number of referenced healthy data tables in the data public layer;

[0033] Calculate the fourth total number of data tables in the aforementioned data common layer;

[0034] The quotient obtained by dividing the third total by the fourth total is determined as the health analysis result corresponding to the number of times the data table is referenced.

[0035] Optionally, the step of performing a health analysis on the reference status of data tables in the sub-data warehouse based on the reference status of the data tables includes:

[0036] Identify whether the data tables in the sub-data warehouse meet the preset referencing specifications, wherein the preset referencing specifications include cross-level referencing or reverse-level referencing;

[0037] If the data table meets the preset referencing rules, it is determined that the references to the data table are unhealthy;

[0038] Calculate the fifth total number of references to unhealthy data tables in the sub-data warehouse;

[0039] The quotient obtained by dividing the fifth total by the second total is determined as the health analysis result corresponding to the reference status of the data table.

[0040] Optionally, calculating the health of the corresponding sub-data warehouse based on the health analysis results of the plurality of preset indicators includes:

[0041] Obtain the weight coefficient of each preset indicator, and extract the quotient of the corresponding preset indicator from the analysis results of each preset indicator;

[0042] The health status of the corresponding preset indicator is obtained by multiplying the weight coefficient of each preset indicator by the quotient of the preset indicator.

[0043] The health score of the sub-data warehouse is obtained by summing the health scores of the multiple preset indicators.

[0044] Optionally, optimizing the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses includes:

[0045] Extract the cause of the anomaly from the abnormal data table for each monitoring result;

[0046] Determine the corresponding optimization strategy based on the cause of the anomaly;

[0047] The data warehouse is optimized based on the optimization strategy described above.

[0048] A second aspect of the present invention provides a data warehouse optimization apparatus, the apparatus comprising:

[0049] The acquisition module is used to acquire the data warehouse to be monitored in response to the received anomaly monitoring request, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line;

[0050] The analysis module is used to perform health analysis on the corresponding sub-data warehouse according to each business line, using multiple preset indicators;

[0051] The calculation module is used to calculate the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators.

[0052] The monitoring module is used to monitor the corresponding sub-data warehouse based on the calculated health status of each sub-data warehouse;

[0053] An optimization module is used to optimize the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses.

[0054] A third aspect of the present invention provides an electronic device comprising a processor and a memory, the processor being configured to implement the data warehouse optimization method when executing a computer program stored in the memory.

[0055] A fourth aspect of the present invention provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the data warehouse optimization method.

[0056] In summary, the data warehouse optimization method, apparatus, electronic device, and storage medium described in this invention can promote the construction of smart cities and be applied to fields such as smart buildings, smart security, smart communities, smart living, and the Internet of Things. By performing health analysis on corresponding sub-data warehouses according to each business line using multiple preset indicators, and calculating the health level of the corresponding sub-data warehouse based on the multiple health analysis results of these preset indicators, the accuracy of the calculated health level is improved by considering the multiple preset indicators and their impact on the health level during the health level calculation process. Based on the calculated health level of each sub-data warehouse, the corresponding sub-data warehouse is monitored, and the data warehouse is optimized based on the multiple monitoring results returned by multiple sub-data warehouses. Using a unified logic for health level optimization solves the problems of data redundancy and non-standardization of data warehouses across different business lines. It also provides unified control over the quality of sub-data warehouses across different business lines and promptly cleans up or repairs unhealthy data warehouses, thus improving the optimization efficiency and accuracy of the data warehouse. Attached Figure Description

[0057] Figure 1 This is a flowchart of the data warehouse optimization method provided in Embodiment 1 of the present invention.

[0058] Figure 2 This is a schematic diagram of the task chain provided in Embodiment 1 of the present invention.

[0059] Figure 3 This is a structural diagram of the data warehouse optimization device provided in Embodiment 2 of the present invention.

[0060] Figure 4 This is a schematic diagram of the structure of the electronic device provided in Embodiment 3 of the present invention. Detailed Implementation

[0061] To better understand the above-mentioned objects, features, and advantages of the present invention, the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments. It should be noted that, unless otherwise specified, the embodiments of the present invention and the features thereof can be combined with each other.

[0062] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.

[0063] Example 1

[0064] Figure 1 This is a flowchart of the data warehouse optimization method provided in Embodiment 1 of the present invention.

[0065] In this embodiment, the data warehouse optimization method can be applied to electronic devices. For electronic devices that require data warehouse optimization, the functionality of the data warehouse optimization method provided by the present invention can be directly integrated into the electronic device, or it can run in the electronic device in the form of a software development kit (SDK).

[0066] The embodiments of this invention can acquire and process relevant data based on artificial intelligence technology. Artificial intelligence (AI) refers to the theories, methods, technologies, and application systems that utilize digital computers or machines controlled by digital computers to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use that knowledge to obtain optimal results.

[0067] Foundational technologies for artificial intelligence generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing, operating / interactive systems, and mechatronics. AI software technologies mainly encompass computer vision, robotics, biometrics, speech processing, natural language processing, as well as machine learning and deep learning.

[0068] like Figure 1 As shown, the optimization method for the data warehouse specifically includes the following steps. Depending on different needs, the order of the steps in this flowchart can be changed, and some steps can be omitted.

[0069] 101. In response to the received anomaly monitoring request, obtain the data warehouse to be monitored, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line.

[0070] In this embodiment, the anomaly monitoring request is used to request the operation of the monitoring data warehouse. When a single device receives the anomaly monitoring request, it parses the anomaly monitoring request, obtains the request message, obtains the warehouse identification code of the data warehouse to be monitored from the message information, obtains the interface of the data warehouse to be monitored according to the warehouse identification code, and calls the interface to obtain the data warehouse to be monitored. The data warehouse contains multiple business lines and sub-data warehouses of each business line.

[0071] In this embodiment, the sub-data warehouse is divided into three layers, from top to bottom: a base layer, a common data layer, and an application layer. The base layer stores unprocessed raw data in the data warehouse system, and its structure is consistent with the source system. The common data layer mainly completes data processing and integration, establishes consistent dimensions, constructs data tables, and summarizes common data indicators. The application layer is used to process indicators with the same business process and the same dimensions into the same wide table, which facilitates the use of data by the business.

[0072] For example, for a transaction business line of a transaction theme, a sub-data warehouse of the transaction business line is obtained, the data of the transaction data system is integrated into the base layer, synchronized to the data common layer of the data warehouse, a data table is built in the data common layer, and common summaries are performed by dimensions such as product, price, and payment method. After processing, it is synchronized to the application layer for further processing.

[0073] 102. For each business line, use multiple preset indicators to perform health analysis on the corresponding sub-data warehouse.

[0074] In this embodiment, most enterprises' data warehouses contain multiple business lines, and different business lines use different specifications and standards to build sub-data warehouses, resulting in inconsistent quality among the built sub-data warehouses.

[0075] In this embodiment, for each business line, a health analysis of the corresponding sub-data warehouse is performed using multiple preset indicators, which realizes unified management of multiple business conditions in the data warehouse. It eliminates the need to use different indicators for health analysis for each business line, thereby improving the efficiency of health analysis of the sub-data warehouse.

[0076] In this embodiment, multiple common indicators, i.e. multiple preset indicators, can be pre-set for multiple business lines of the data warehouse. Based on the multiple preset indicators, health analysis is performed on the sub-data warehouse of each business line, and the business dimension is unified for the sub-data warehouses of each business line, which improves the health analysis efficiency of the data warehouse and thus improves the optimization efficiency of the data warehouse.

[0077] In this embodiment, the multiple preset indicators may include one or more of the following combinations: table name normalization requirements; number of times the data table is referenced; data table referencing status; task chain depth; single task runtime; task execution failure rate; and repair time for failed tasks.

[0078] In other optional embodiments, the preset metric may also be the number of times the data warehouse is optimized, etc., which is not limited in this embodiment.

[0079] In an optional embodiment, the step of performing health analysis on the corresponding sub-data warehouse using multiple preset indicators according to each business line includes:

[0080] Retrieve the corresponding sub-data warehouse based on the theme name of each business line;

[0081] According to the table name normalization requirements, perform a health analysis on the table names of the data tables in the sub-data warehouse; and / or

[0082] Based on the number of times the data tables are referenced, perform a health analysis on the number of references to the data tables in the common data layer of the sub-data warehouse; and / or

[0083] Based on the reference information of the data tables, perform a health analysis on the reference information of the data tables in the sub-data warehouse; and / or

[0084] Based on the task link depth, perform a health analysis on the task link depth in the sub-data warehouse; and / or

[0085] Based on the single-task runtime, perform a health analysis on the single-task execution status of the sub-data warehouse; and / or

[0086] Based on the task execution failure rate, perform a health analysis on the task execution status of the sub-data warehouse; and / or

[0087] A health analysis is performed on the repair status of failed tasks in the sub-data warehouse based on the repair time of failed tasks.

[0088] In this embodiment, each business line corresponds to a topic name, and each topic name is used to uniquely identify the sub-data warehouse of the corresponding business line.

[0089] In an optional embodiment, the health analysis of the table names in the sub-data warehouse according to the table name normalization requirements includes:

[0090] Identify the table names of the data tables in the sub-data warehouse;

[0091] Determine whether the table name of the data table meets the preset normalization requirements;

[0092] When the table name of the data table meets the preset normalization requirements, the table name of the data table is determined to be healthy;

[0093] When the table name of the data table does not meet the preset normalization requirements, the table name of the data table is determined to be unhealthy.

[0094] Calculate the first total number of healthy data tables in the sub-data warehouse;

[0095] Calculate the second total number of data tables in the sub-data warehouse;

[0096] The quotient obtained by dividing the first total by the second total is determined as the health analysis result corresponding to the table name.

[0097] In this embodiment, for table names, pre-set normalization requirements can be implemented. These pre-set requirements may include table names starting with ods / dwd / dws / dim / ads / tmp, etc., to ensure the table names meet these requirements; otherwise, they do not. The larger the quotient obtained by dividing the first total by the second total, the higher the health score of the sub-data warehouse.

[0098] In an optional embodiment, the health analysis of the reference counts of data tables in the common data layer of the sub-data warehouse based on the number of references to the data tables includes:

[0099] Record the number of fields in the data tables of the data common layer that are referenced by the application layer;

[0100] The number of fields is determined as the number of times the data table is referenced;

[0101] When the number of times the data table is referenced is greater than or equal to a preset first threshold, the number of times the data table is referenced is determined to be healthy.

[0102] When the number of references to the data table is less than the preset second threshold, the number of references to the data table is determined to be unhealthy.

[0103] Calculate the third total number of referenced healthy data tables in the data public layer;

[0104] Calculate the fourth total number of data tables in the aforementioned data common layer;

[0105] The quotient obtained by dividing the third total by the fourth total is determined as the health analysis result corresponding to the number of times the data table is referenced.

[0106] In this embodiment, the number of references is used to characterize the number of times each data table in the data common layer is referenced downstream, i.e., the referenced layer.

[0107] Specifically, a first threshold can be preset, which can be set to 5. If the number of times a data table is referenced is greater than or equal to 5, the data table is determined to be highly referenced, that is, the number of times the data table is referenced is determined to be healthy.

[0108] Specifically, a second threshold can be preset, which can be set to 2. If the number of references to a data table is less than 2, the data table is determined to be low-referenced, meaning its reference count is unhealthy. Furthermore, the higher the quotient obtained by dividing the third total by the fourth total, the higher the health score of the sub-data warehouse.

[0109] In this embodiment, health analysis is not performed on data tables that fall within the range of the preset first threshold and the preset second threshold. This reduces the number of data tables to be analyzed for health and avoids interference from regular data tables, thereby improving the efficiency and accuracy of the health analysis based on the number of times the data table is referenced.

[0110] In an optional embodiment, the health analysis of the reference status of the data tables in the sub-data warehouse based on the reference status of the data tables includes:

[0111] Identify whether the data tables in the sub-data warehouse meet the preset referencing specifications, wherein the preset referencing specifications include cross-level referencing or reverse-level referencing;

[0112] If the data table meets the preset referencing rules, it is determined that the references to the data table are unhealthy;

[0113] If the data table does not meet the preset referencing specifications, the referencing health of the data table is determined.

[0114] Calculate the fifth total number of references to unhealthy data tables in the sub-data warehouse;

[0115] The quotient obtained by dividing the fifth total by the second total is determined as the health analysis result corresponding to the reference status of the data table.

[0116] In this embodiment, the cross-layer reference means that the application layer uses the data table of the base layer as the data source; the reverse layer reference means that the data common layer uses the data table of the application layer as the data source.

[0117] In this embodiment, the lower the quotient obtained by dividing the fifth total by the second total, the higher the health score of the sub-data warehouse is determined.

[0118] In an optional embodiment, the step of performing a health analysis on the task link depth in the sub-data warehouse based on the task link depth includes:

[0119] Obtain multiple task nodes of the sub-data warehouse from the task scheduling platform;

[0120] Based on the multiple task nodes, calculate the sixth total number of task links from the base layer to the application layer;

[0121] Simultaneously, the depth value of each task link is calculated;

[0122] Task links whose depth value is greater than or equal to a preset first depth threshold are identified as healthy task links;

[0123] Task links whose depth value is less than a preset second depth threshold are identified as unhealthy task links.

[0124] The seventh total number of health-related task chains;

[0125] The quotient obtained by dividing the seventh total by the sixth total is determined as the health analysis result corresponding to the task link depth.

[0126] In this embodiment, the task scheduling platform refers to the platform responsible for task management and task allocation in the data warehouse. The task scheduling platform can run on a smart terminal, which includes smart devices such as computers, tablets, and mobile phones.

[0127] In this embodiment, multiple task nodes of the sub-data warehouse are obtained from the task scheduling platform, and the number of task links from the base layer to the application layer is calculated based on the dependencies between the multiple task nodes.

[0128] In this embodiment, see Figure 2 As shown, there are three task links from the base layer to the application layer, namely ABCEFG, ABDEFG, and ABDH, with task link depth values ​​of 6, 6, and 4, respectively. The health status of each task link can be determined based on the depth value of each link.

[0129] In this embodiment, a first depth threshold can be preset, which can be set to 6; a second depth threshold can be preset, which can be set to 3. The depth thresholds can be set according to the actual business situation.

[0130] In this embodiment, the higher the quotient obtained by dividing the seventh total by the sixth total, the higher the health score of the sub-data warehouse is determined.

[0131] In an optional embodiment, the health analysis of the single-task execution status of the sub-data warehouse based on the single-task runtime includes:

[0132] Obtain the runtime of a single task in the sub-data warehouse from the task scheduling platform;

[0133] Identify the execution type of the single task;

[0134] Obtain the corresponding preset runtime conditions based on the execution type of the single task;

[0135] When the runtime of a single task does not meet the preset runtime condition, the single task is determined to be unhealthy.

[0136] When the runtime of a single task meets the preset runtime condition, the single task is determined to be running healthily.

[0137] The eighth total number of single tasks that are running healthily;

[0138] The quotient obtained by dividing the eighth total by the sixth total is determined as the health analysis result corresponding to the single task runtime.

[0139] Specifically, obtaining the runtime of a single task in the sub-data warehouse from the task scheduling platform includes: obtaining the task identification code of each single task, and obtaining the runtime of the single task in the sub-data warehouse from the task scheduling platform based on the task identification code of the single task, wherein the single task can be one or more single tasks.

[0140] In this embodiment, the execution type of a single task may include: hourly task, daily task, weekly task, monthly task, etc.

[0141] In this embodiment, execution duration conditions can be preset for each execution type. For example, the preset runtime conditions for hourly tasks are: runtime not exceeding 2 hours; for daily tasks, the preset runtime conditions are: runtime not exceeding 4 hours; for weekly tasks, the preset runtime conditions are: runtime not exceeding 8 hours; and for monthly tasks, the preset runtime conditions are: runtime not exceeding 12 hours. Under the premise of ensuring stable operation of the data warehouse and accurate data, the lower the runtime, the higher the health score of the data warehouse.

[0142] In an optional embodiment, the step of performing a health analysis on the task execution status of the sub-data warehouse based on the task execution failure rate includes:

[0143] Obtain the total number of first failed tasks within a preset time period from the task scheduling platform, and the total number of tasks within the preset time period;

[0144] The quotient obtained by dividing the total number of the first failed tasks by the total number of tasks is determined as the health analysis result corresponding to the task execution failure rate.

[0145] In this embodiment, a preset time period can be set in advance, and the preset time period can be set to 24 hours.

[0146] In this embodiment, the stability of the sub-data warehouse operation can be determined by the task execution failure rate. For example, the task execution failure rate of the sub-data warehouse can be obtained by dividing the total number of first failed tasks of the day by the total number of tasks run on the scheduling platform. The lower the task execution failure rate, the higher the health score of the sub-data warehouse.

[0147] In an optional embodiment, the health analysis of the failure task repair status of the sub-data warehouse based on the repair time of the failed tasks includes:

[0148] Obtain the repair time for failed tasks from the task scheduling platform;

[0149] When the repair time of the failed task is greater than or equal to a preset repair time threshold, the repair time of the failed task is determined to be unhealthy.

[0150] When the repair time of the failed task is less than the preset repair time threshold, the repair time of the failed task is determined to be healthy.

[0151] Calculate the total number of second failed tasks with healthy repair times within the preset time period;

[0152] The quotient obtained by dividing the total number of the second failed tasks by the total number of the first failed tasks is determined as the health analysis result corresponding to the repair time of the failed tasks.

[0153] In this embodiment, a repair time threshold can be preset. For example, the repair time threshold can be set to a maximum repair time of 2h, 4h, 8h or 12h according to the task priority or scheduling cycle level.

[0154] In this embodiment, the stability of the data warehouse operation also depends on maintenance. When a task fails, it must be investigated and repaired promptly to ensure the stable operation of the task flow, timely delivery of application metrics, and the timeliness of downstream tasks and applications. The shorter the repair time, the higher the health score of the data warehouse.

[0155] In this embodiment, multiple preset indicators are used to perform health analysis on the sub-data warehouses of each business line in the data warehouse. In the subsequent health calculation process of each sub-data warehouse, the dimensions of the multiple preset indicators are taken into consideration, which improves the accuracy of the health calculated in the subsequent process.

[0156] 103. Calculate the health status of the corresponding sub-data warehouse based on the multiple health analysis results of the multiple preset indicators.

[0157] In this embodiment, a weighting coefficient is pre-set for each preset indicator based on the degree of influence of each indicator on health, and the sum of the multiple weighting coefficients of the multiple preset indicators is 1.

[0158] In this embodiment, when analyzing the health of the sub-data warehouse, one or more of the health analysis results of the multiple preset indicators can be randomly used, and this embodiment does not impose any restrictions.

[0159] In an optional embodiment, calculating the health of the corresponding sub-data warehouse based on the health analysis results of the plurality of preset indicators includes:

[0160] Obtain the weight coefficient of each preset indicator, and extract the quotient of the corresponding preset indicator from the analysis results of each preset indicator;

[0161] The health status of the corresponding preset indicator is obtained by multiplying the weight coefficient of each preset indicator by the quotient of the preset indicator.

[0162] The health score of the sub-data warehouse is obtained by summing the health scores of the multiple preset indicators.

[0163] In this embodiment, when calculating the health of each sub-data warehouse, multiple preset indicators and the degree of influence of each preset indicator on the health are considered, thereby realizing unified management of multiple business lines and improving the management efficiency of the data warehouse.

[0164] In this embodiment, after calculating the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators, the method further includes:

[0165] Sort multiple health scores for multiple sub-data warehouses and display the sorting results.

[0166] In this embodiment, by displaying multiple health levels of multiple sub-data warehouses, it can help operation and maintenance personnel quickly identify abnormal sub-data warehouses, thereby improving the optimization efficiency of data warehouses.

[0167] 104. Based on the calculated health status of each sub-data warehouse, monitor the corresponding sub-data warehouse.

[0168] In this embodiment, after obtaining the health status of each sub-data warehouse, the corresponding monitoring device is activated to perform anomaly monitoring on the corresponding sub-data warehouse. Each sub-data warehouse corresponds to one monitoring device, and the monitoring device is communicatively connected to the corresponding sub-data warehouse. The monitoring device is used to monitor abnormal information in the corresponding sub-data warehouse. When abnormal information is detected, the abnormal data table corresponding to the abnormal information is extracted, and the extracted abnormal data table and the abnormal information are used as the monitoring result.

[0169] In this embodiment, the abnormal information includes the abnormal reason for the abnormal data table. For example, the abnormal reason may be that the table name is unhealthy and does not meet the preset normalization requirements; or the number of times the data table is referenced is unhealthy.

[0170] In this embodiment, since each sub-data warehouse corresponds to a monitoring device, multiple monitoring devices can be triggered simultaneously to monitor multiple sub-data warehouses, which improves the efficiency of obtaining abnormal information and thus improves the monitoring efficiency of the data warehouse.

[0171] 105. Optimize the data warehouse based on the multiple monitoring results returned by the multiple sub-data warehouses.

[0172] In an optional embodiment, optimizing the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses includes:

[0173] Extract the cause of the anomaly from the abnormal data table for each monitoring result;

[0174] Determine the corresponding optimization strategy based on the cause of the anomaly;

[0175] The data warehouse is optimized based on the optimization strategy described above.

[0176] In this embodiment, the optimization strategy is preset based on the cause of the anomaly. For example, if the cause of the anomaly is that the table name of the data table is unhealthy, the optimization strategy is to correct the non-standard table name according to the preset standardization requirements; if the cause of the anomaly is that the number of references to the data table is unhealthy, the optimization strategy is to design a new data table to replace the data table with the unhealthy number of references.

[0177] In this embodiment, the optimization strategy can be stored in the task scheduling platform. When an abnormal cause is received, an optimization strategy retrieval instruction is sent to the task scheduling platform. The task scheduling platform responds to the optimization strategy retrieval instruction and returns the optimization strategy corresponding to the abnormal cause.

[0178] In this embodiment, the data warehouse is optimized through an optimization strategy, which improves the optimization efficiency and accuracy of the data warehouse in a targeted manner.

[0179] In this embodiment, for each business line, a health analysis is performed on the corresponding sub-data warehouse using multiple preset indicators. The health score of each sub-data warehouse is calculated, and the data warehouse is monitored based on the calculated health score. Optimization is then performed based on the monitoring results. By using a unified logic for health score optimization, the issues of data redundancy and non-standardization of data warehouses across different business lines are resolved. At the same time, the quality of sub-data warehouses across different business lines is uniformly controlled, and unhealthy data warehouses are cleaned up or repaired in a timely manner, thereby improving the optimization efficiency and accuracy of the data warehouse.

[0180] In summary, the data warehouse optimization method described in this embodiment performs health analysis on corresponding sub-data warehouses for each business line using multiple preset indicators. Based on the health analysis results of these preset indicators, the health score of each sub-data warehouse is calculated. The calculation process for each sub-data warehouse considers the multiple preset indicators and their impact on the health score, thus improving the accuracy of the calculated health score. Based on the calculated health score of each sub-data warehouse, the corresponding sub-data warehouse is monitored, and optimization is performed based on the multiple monitoring results returned by the sub-data warehouses. Using a unified logical health score for data warehouse optimization resolves data redundancy and non-standardization of data warehouses across different business lines. It also provides unified control over the quality of sub-data warehouses across different business lines and promptly cleans up or repairs unhealthy data warehouses, thereby improving the optimization efficiency and accuracy of the data warehouse.

[0181] Example 2

[0182] Figure 3 This is a structural diagram of the data warehouse optimization device provided in Embodiment 2 of the present invention.

[0183] In some embodiments, the data warehouse optimization apparatus 20 may include multiple functional modules composed of program code segments. The program code of each program segment in the data warehouse optimization apparatus 20 may be stored in the memory of an electronic device and executed by the at least one processor to perform (see details). Figure 1 (Description) Functionality of data warehouse optimization methods.

[0184] In this embodiment, the data warehouse optimization device 20 can be divided into multiple functional modules according to the functions it performs. These functional modules may include: an acquisition module 201, an analysis module 202, a calculation module 203, a monitoring module 204, and an optimization module 205. The term "module" in this invention refers to a series of computer-readable instruction segments that can be executed by at least one processor and perform a fixed function, stored in memory. In this embodiment, the functions of each module will be detailed in subsequent embodiments.

[0185] The acquisition module 201 is used to acquire the data warehouse to be monitored in response to the received anomaly monitoring request, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line.

[0186] In this embodiment, the anomaly monitoring request is used to request the operation of the monitoring data warehouse. When a single device receives the anomaly monitoring request, it parses the anomaly monitoring request, obtains the request message, obtains the warehouse identification code of the data warehouse to be monitored from the message information, obtains the interface of the data warehouse to be monitored according to the warehouse identification code, and calls the interface to obtain the data warehouse to be monitored. The data warehouse contains multiple business lines and sub-data warehouses of each business line.

[0187] In this embodiment, the sub-data warehouse is divided into three layers, from top to bottom: a base layer, a common data layer, and an application layer. The base layer stores unprocessed raw data in the data warehouse system, and its structure is consistent with the source system. The common data layer mainly completes data processing and integration, establishes consistent dimensions, constructs data tables, and summarizes common data indicators. The application layer is used to process indicators with the same business process and the same dimensions into the same wide table, which facilitates the use of data by the business.

[0188] For example, for a transaction business line of a transaction theme, a sub-data warehouse of the transaction business line is obtained, the data of the transaction data system is integrated into the base layer, synchronized to the data common layer of the data warehouse, a data table is built in the data common layer, and common summaries are performed by dimensions such as product, price, and payment method. After processing, it is synchronized to the application layer for further processing.

[0189] Analysis module 202 is used to perform health analysis on the corresponding sub-data warehouse according to each business line using multiple preset indicators.

[0190] In this embodiment, most enterprises' data warehouses contain multiple business lines, and different business lines use different specifications and standards to build sub-data warehouses, resulting in inconsistent quality among the built sub-data warehouses.

[0191] In this embodiment, for each business line, a health analysis of the corresponding sub-data warehouse is performed using multiple preset indicators, which realizes unified management of multiple business conditions in the data warehouse. It eliminates the need to use different indicators for health analysis for each business line, thereby improving the efficiency of health analysis of the sub-data warehouse.

[0192] In this embodiment, multiple common indicators, i.e. multiple preset indicators, can be pre-set for multiple business lines of the data warehouse. Based on the multiple preset indicators, health analysis is performed on the sub-data warehouse of each business line, and the business dimension is unified for the sub-data warehouses of each business line, which improves the health analysis efficiency of the data warehouse and thus improves the optimization efficiency of the data warehouse.

[0193] In this embodiment, the multiple preset indicators may include one or more of the following combinations: table name normalization requirements; number of times the data table is referenced; data table referencing status; task chain depth; single task runtime; task execution failure rate; and repair time for failed tasks.

[0194] In other optional embodiments, the preset metric may also be the number of times the data warehouse is optimized, etc., which is not limited in this embodiment.

[0195] In an optional embodiment, the analysis module 202 performs health analysis on the corresponding sub-data warehouse according to each business line, using multiple preset indicators, including:

[0196] Retrieve the corresponding sub-data warehouse based on the theme name of each business line;

[0197] According to the table name normalization requirements, perform a health analysis on the table names of the data tables in the sub-data warehouse; and / or

[0198] Based on the number of times the data tables are referenced, perform a health analysis on the number of references to the data tables in the common data layer of the sub-data warehouse; and / or

[0199] Based on the reference information of the data tables, perform a health analysis on the reference information of the data tables in the sub-data warehouse; and / or

[0200] Based on the task link depth, perform a health analysis on the task link depth in the sub-data warehouse; and / or

[0201] Based on the single-task runtime, perform a health analysis on the single-task execution status of the sub-data warehouse; and / or

[0202] Based on the task execution failure rate, perform a health analysis on the task execution status of the sub-data warehouse; and / or

[0203] A health analysis is performed on the repair status of failed tasks in the sub-data warehouse based on the repair time of failed tasks.

[0204] In this embodiment, each business line corresponds to a topic name, and each topic name is used to uniquely identify the sub-data warehouse of the corresponding business line.

[0205] In an optional embodiment, the health analysis of the table names in the sub-data warehouse according to the table name normalization requirements includes:

[0206] Identify the table names of the data tables in the sub-data warehouse;

[0207] Determine whether the table name of the data table meets the preset normalization requirements;

[0208] When the table name of the data table meets the preset normalization requirements, the table name of the data table is determined to be healthy;

[0209] When the table name of the data table does not meet the preset normalization requirements, the table name of the data table is determined to be unhealthy.

[0210] Calculate the first total number of healthy data tables in the sub-data warehouse;

[0211] Calculate the second total number of data tables in the sub-data warehouse;

[0212] The quotient obtained by dividing the first total by the second total is determined as the health analysis result corresponding to the table name.

[0213] In this embodiment, for table names, pre-set normalization requirements can be implemented. These pre-set requirements may include table names starting with ods / dwd / dws / dim / ads / tmp, etc., to ensure the table names meet these requirements; otherwise, they do not. The larger the quotient obtained by dividing the first total by the second total, the higher the health score of the sub-data warehouse.

[0214] In an optional embodiment, the health analysis of the reference counts of data tables in the common data layer of the sub-data warehouse based on the number of references to the data tables includes:

[0215] Record the number of fields in the data tables of the data common layer that are referenced by the application layer;

[0216] The number of fields is determined as the number of times the data table is referenced;

[0217] When the number of times the data table is referenced is greater than or equal to a preset first threshold, the number of times the data table is referenced is determined to be healthy.

[0218] When the number of references to the data table is less than the preset second threshold, the number of references to the data table is determined to be unhealthy.

[0219] Calculate the third total number of referenced healthy data tables in the data public layer;

[0220] Calculate the fourth total number of data tables in the aforementioned data common layer;

[0221] The quotient obtained by dividing the third total by the fourth total is determined as the health analysis result corresponding to the number of times the data table is referenced.

[0222] In this embodiment, the number of references is used to characterize the number of times each data table in the data common layer is referenced downstream, i.e., by the referenced layer.

[0223] Specifically, a first threshold can be preset, which can be set to 5. If the number of times a data table is referenced is greater than or equal to 5, the data table is determined to be highly referenced, that is, the number of times the data table is referenced is determined to be healthy.

[0224] Specifically, a second threshold can be preset, which can be set to 2. If the number of references to a data table is less than 2, the data table is determined to be low-referenced, meaning its reference count is unhealthy. Furthermore, the higher the quotient obtained by dividing the third total by the fourth total, the higher the health score of the sub-data warehouse.

[0225] In this embodiment, health analysis is not performed on data tables that fall within the range of the preset first threshold and the preset second threshold. This reduces the number of data tables to be analyzed for health and avoids interference from regular data tables, thereby improving the efficiency and accuracy of the health analysis based on the number of times the data table is referenced.

[0226] In an optional embodiment, the health analysis of the reference status of the data tables in the sub-data warehouse based on the reference status of the data tables includes:

[0227] Identify whether the data tables in the sub-data warehouse meet the preset referencing specifications, wherein the preset referencing specifications include cross-level referencing or reverse-level referencing;

[0228] If the data table meets the preset referencing rules, it is determined that the references to the data table are unhealthy;

[0229] If the data table does not meet the preset referencing specifications, the referencing health of the data table is determined.

[0230] Calculate the fifth total number of references to unhealthy data tables in the sub-data warehouse;

[0231] The quotient obtained by dividing the fifth total by the second total is determined as the health analysis result corresponding to the reference status of the data table.

[0232] In this embodiment, the cross-layer reference means that the application layer uses the data table of the base layer as the data source; the reverse layer reference means that the data common layer uses the data table of the application layer as the data source.

[0233] In this embodiment, the lower the quotient obtained by dividing the fifth total by the second total, the higher the health score of the sub-data warehouse is determined.

[0234] In an optional embodiment, the step of performing a health analysis on the task link depth in the sub-data warehouse based on the task link depth includes:

[0235] Obtain multiple task nodes of the sub-data warehouse from the task scheduling platform;

[0236] Based on the multiple task nodes, calculate the sixth total number of task links from the base layer to the application layer;

[0237] Simultaneously, the depth value of each task link is calculated;

[0238] Task links whose depth value is greater than or equal to a preset first depth threshold are identified as healthy task links;

[0239] Task links whose depth value is less than a preset second depth threshold are identified as unhealthy task links.

[0240] The seventh total number of health-related task chains;

[0241] The quotient obtained by dividing the seventh total by the sixth total is determined as the health analysis result corresponding to the task link depth.

[0242] In this embodiment, the task scheduling platform refers to the platform responsible for task management and task allocation in the data warehouse. The task scheduling platform can run on a smart terminal, which includes smart devices such as computers, tablets, and mobile phones.

[0243] In this embodiment, multiple task nodes of the sub-data warehouse are obtained from the task scheduling platform, and the number of task links from the base layer to the application layer is calculated based on the dependencies between the multiple task nodes.

[0244] In this embodiment, see Figure 2 As shown, there are three task links from the base layer to the application layer, namely ABCEFG, ABDEFG, and ABDH, with task link depth values ​​of 6, 6, and 4, respectively. The health status of each task link can be determined based on the depth value of each link.

[0245] In this embodiment, a first depth threshold can be preset, which can be set to 6; a second depth threshold can be preset, which can be set to 3. The depth thresholds can be set according to the actual business situation.

[0246] In this embodiment, the higher the quotient obtained by dividing the seventh total by the sixth total, the higher the health score of the sub-data warehouse is determined.

[0247] In an optional embodiment, the health analysis of the single-task execution status of the sub-data warehouse based on the single-task runtime includes:

[0248] Obtain the runtime of a single task in the sub-data warehouse from the task scheduling platform;

[0249] Identify the execution type of the single task;

[0250] Obtain the corresponding preset runtime conditions based on the execution type of the single task;

[0251] When the runtime of a single task does not meet the preset runtime condition, the single task is determined to be unhealthy.

[0252] When the runtime of a single task meets the preset runtime condition, the single task is determined to be running healthily.

[0253] The eighth total number of single tasks that are running healthily;

[0254] The quotient obtained by dividing the eighth total by the sixth total is determined as the health analysis result corresponding to the single task runtime.

[0255] Specifically, obtaining the runtime of a single task in the sub-data warehouse from the task scheduling platform includes: obtaining the task identification code of each single task, and obtaining the runtime of the single task in the sub-data warehouse from the task scheduling platform based on the task identification code of the single task, wherein the single task can be one or more single tasks.

[0256] In this embodiment, the execution type of a single task may include: hourly task, daily task, weekly task, monthly task, etc.

[0257] In this embodiment, execution duration conditions can be preset for each execution type. For example, the preset runtime conditions for hourly tasks are: runtime not exceeding 2 hours; for daily tasks, the preset runtime conditions are: runtime not exceeding 4 hours; for weekly tasks, the preset runtime conditions are: runtime not exceeding 8 hours; and for monthly tasks, the preset runtime conditions are: runtime not exceeding 12 hours. Under the premise of ensuring stable operation of the data warehouse and accurate data, the lower the runtime, the higher the health score of the data warehouse.

[0258] In an optional embodiment, the step of performing a health analysis on the task execution status of the sub-data warehouse based on the task execution failure rate includes:

[0259] Obtain the total number of first failed tasks within a preset time period from the task scheduling platform, and the total number of tasks within the preset time period;

[0260] The quotient obtained by dividing the total number of the first failed tasks by the total number of tasks is determined as the health analysis result corresponding to the task execution failure rate.

[0261] In this embodiment, a preset time period can be set in advance, and the preset time period can be set to 24 hours.

[0262] In this embodiment, the stability of the sub-data warehouse operation can be determined by the task execution failure rate. For example, the task execution failure rate of the sub-data warehouse can be obtained by dividing the total number of first failed tasks of the day by the total number of tasks run on the scheduling platform. The lower the task execution failure rate, the higher the health score of the sub-data warehouse.

[0263] In an optional embodiment, the health analysis of the failure task repair status of the sub-data warehouse based on the repair time of the failed tasks includes:

[0264] Obtain the repair time for failed tasks from the task scheduling platform;

[0265] When the repair time of the failed task is greater than or equal to a preset repair time threshold, the repair time of the failed task is determined to be unhealthy.

[0266] When the repair time of the failed task is less than the preset repair time threshold, the repair time of the failed task is determined to be healthy.

[0267] Calculate the total number of second failed tasks with healthy repair times within the preset time period;

[0268] The quotient obtained by dividing the total number of the second failed tasks by the total number of the first failed tasks is determined as the health analysis result corresponding to the repair time of the failed tasks.

[0269] In this embodiment, a repair time threshold can be preset. For example, the repair time threshold can be set to a maximum repair time of 2h, 4h, 8h or 12h according to the task priority or scheduling cycle level.

[0270] In this embodiment, the stability of the data warehouse operation also depends on maintenance. When a task fails, it must be investigated and repaired promptly to ensure the stable operation of the task flow, timely delivery of application metrics, and the timeliness of downstream tasks and applications. The shorter the repair time, the higher the health score of the data warehouse.

[0271] In this embodiment, multiple preset indicators are used to perform health analysis on the sub-data warehouses of each business line in the data warehouse. In the subsequent health calculation process of each sub-data warehouse, the dimensions of the multiple preset indicators are taken into consideration, which improves the accuracy of the health calculated in the subsequent process.

[0272] The calculation module 203 is used to calculate the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators.

[0273] In this embodiment, a weighting coefficient is pre-set for each preset indicator based on the degree of influence of each indicator on health, and the sum of the multiple weighting coefficients of the multiple preset indicators is 1.

[0274] In this embodiment, when analyzing the health of the sub-data warehouse, one or more of the health analysis results of the multiple preset indicators can be randomly used, and this embodiment does not impose any restrictions.

[0275] In an optional embodiment, the calculation module 203 calculates the health of the corresponding sub-data warehouse based on multiple health analysis results of the multiple preset indicators, including:

[0276] Obtain the weight coefficient of each preset indicator, and extract the quotient of the corresponding preset indicator from the analysis results of each preset indicator;

[0277] The health status of the corresponding preset indicator is obtained by multiplying the weight coefficient of each preset indicator by the quotient of the preset indicator.

[0278] The health score of the sub-data warehouse is obtained by summing the health scores of the multiple preset indicators.

[0279] In this embodiment, when calculating the health of each sub-data warehouse, multiple preset indicators and the degree of influence of each preset indicator on the health are considered, thereby realizing unified management of multiple business lines and improving the management efficiency of the data warehouse.

[0280] In this embodiment, after calculating the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators, the method further includes:

[0281] Sort multiple health scores for multiple sub-data warehouses and display the sorting results.

[0282] In this embodiment, by displaying multiple health levels of multiple sub-data warehouses, it can help operation and maintenance personnel quickly identify abnormal sub-data warehouses, thereby improving the optimization efficiency of data warehouses.

[0283] The monitoring module 204 is used to monitor the corresponding sub-data warehouse based on the calculated health status of each sub-data warehouse.

[0284] In this embodiment, after obtaining the health status of each sub-data warehouse, the corresponding monitoring device is activated to perform anomaly monitoring on the corresponding sub-data warehouse. Each sub-data warehouse corresponds to one monitoring device, and the monitoring device is communicatively connected to the corresponding sub-data warehouse. The monitoring device is used to monitor abnormal information in the corresponding sub-data warehouse. When abnormal information is detected, the abnormal data table corresponding to the abnormal information is extracted, and the extracted abnormal data table and the abnormal information are used as the monitoring result.

[0285] In this embodiment, the abnormal information includes the abnormal reason for the abnormal data table. For example, the abnormal reason may be that the table name is unhealthy and does not meet the preset normalization requirements; or the number of times the data table is referenced is unhealthy.

[0286] In this embodiment, since each sub-data warehouse corresponds to a monitoring device, multiple monitoring devices can be triggered simultaneously to monitor multiple sub-data warehouses, which improves the efficiency of obtaining abnormal information and thus improves the monitoring efficiency of the data warehouse.

[0287] The optimization module 205 is used to optimize the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses.

[0288] In an optional embodiment, the optimization module 205 optimizes the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses, including:

[0289] Extract the cause of the anomaly from the abnormal data table for each monitoring result;

[0290] Determine the corresponding optimization strategy based on the cause of the anomaly;

[0291] The data warehouse is optimized based on the optimization strategy described above.

[0292] In this embodiment, the optimization strategy is preset based on the cause of the anomaly. For example, if the cause of the anomaly is that the table name of the data table is unhealthy, the optimization strategy is to correct the non-standard table name according to the preset standardization requirements; if the cause of the anomaly is that the number of references to the data table is unhealthy, the optimization strategy is to design a new data table to replace the data table with the unhealthy number of references.

[0293] In this embodiment, the optimization strategy can be stored in the task scheduling platform. When an abnormal cause is received, an optimization strategy retrieval instruction is sent to the task scheduling platform. The task scheduling platform responds to the optimization strategy retrieval instruction and returns the optimization strategy corresponding to the abnormal cause.

[0294] In this embodiment, the data warehouse is optimized through an optimization strategy, which improves the optimization efficiency and accuracy of the data warehouse in a targeted manner.

[0295] In this embodiment, for each business line, a health analysis is performed on the corresponding sub-data warehouse using multiple preset indicators. The health score of each sub-data warehouse is calculated, and the data warehouse is monitored based on the calculated health score. Optimization is then performed based on the monitoring results. By using a unified logic for health score optimization, the issues of data redundancy and non-standardization of data warehouses across different business lines are resolved. At the same time, the quality of sub-data warehouses across different business lines is uniformly controlled, and unhealthy data warehouses are cleaned up or repaired in a timely manner, thereby improving the optimization efficiency and accuracy of the data warehouse.

[0296] In summary, the data warehouse optimization device described in this embodiment performs health analysis on corresponding sub-data warehouses according to each business line using multiple preset indicators. Based on the health analysis results of these preset indicators, the health score of each sub-data warehouse is calculated. The calculation process for the health score of each sub-data warehouse considers the multiple preset indicators and their impact on the health score, thus improving the accuracy of the calculated health score. Based on the calculated health score of each sub-data warehouse, the device monitors the corresponding sub-data warehouse and optimizes it based on the multiple monitoring results returned by the sub-data warehouses. By using a unified logic for health score optimization, the device resolves data redundancy and non-standardization of data warehouses across different business lines. It also provides unified control over the quality of sub-data warehouses across different business lines and promptly cleans up or repairs unhealthy data warehouses, thereby improving the optimization efficiency and accuracy of the data warehouse.

[0297] Example 3

[0298] See Figure 4 The diagram shown is a structural schematic of an electronic device provided in Embodiment 3 of the present invention. In a preferred embodiment of the present invention, the electronic device 3 includes a memory 31, at least one processor 32, at least one communication bus 33, and a transceiver 34.

[0299] Those skilled in the art should understand that Figure 4 The structure of the electronic device shown does not constitute a limitation of the embodiments of the present invention. It can be a bus structure or a star structure. The electronic device 3 may also include more or fewer other hardware or software than shown, or different component arrangements.

[0300] In some embodiments, the electronic device 3 is an electronic device capable of automatically performing numerical calculations and / or information processing according to pre-set or stored instructions. Its hardware includes, but is not limited to, microprocessors, application-specific integrated circuits (ASICs), programmable gate arrays (FPGAs), digital processors, and embedded devices. The electronic device 3 may also include client devices, including, but not limited to, any electronic product capable of human-computer interaction with a client via a keyboard, mouse, remote control, touchpad, or voice control device, such as personal computers, tablet computers, smartphones, and digital cameras.

[0301] It should be noted that the electronic device 3 is merely an example. Other existing or future electronic products that are suitable for this invention should also be included within the scope of protection of this invention and are incorporated herein by reference.

[0302] In some embodiments, the memory 31 is used to store program code and various data, such as the data warehouse optimization device 20 installed in the electronic device 3, and to achieve high-speed and automatic access to programs or data during the operation of the electronic device 3. The memory 31 includes read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), one-time programmable read-only memory (OTPROM), electrically-erasable programmable read-only memory (EEPROM), compact disc read-only memory (CD-ROM) or other optical disc storage, disk storage, magnetic tape storage, or any other computer-readable medium capable of carrying or storing data.

[0303] In some embodiments, the at least one processor 32 may be composed of integrated circuits, such as a single packaged integrated circuit or multiple integrated circuits packaged with the same or different functions, including combinations of one or more central processing units (CPUs), microprocessors, digital processing chips, graphics processors, and various control chips. The at least one processor 32 is the control unit of the electronic device 3, connecting various components of the entire electronic device 3 via various interfaces and lines. It executes programs or modules stored in the memory 31 and calls data stored in the memory 31 to perform various functions and process data of the electronic device 3.

[0304] In some embodiments, the at least one communication bus 33 is configured to enable communication between the memory 31 and the at least one processor 32, etc.

[0305] Although not shown, the electronic device 3 may also include a power supply (such as a battery) to power the various components. Optionally, the power supply may be logically connected to the at least one processor 32 via a power management device, thereby enabling functions such as charging, discharging, and power consumption management. The power supply may also include one or more DC or AC power supplies, recharging devices, power fault detection circuits, power converters or inverters, power status indicators, and other arbitrary components. The electronic device 3 may also include various sensors, Bluetooth modules, Wi-Fi modules, etc., which will not be described in detail here.

[0306] It should be understood that the embodiments described are for illustrative purposes only and are not limited to this structure in the scope of the patent application.

[0307] The integrated unit implemented as a software functional module described above can be stored in a computer-readable storage medium. This software functional module, stored in a storage medium, includes several instructions to cause a computer device (which may be a personal computer, electronic device, or network device, etc.) or processor to execute portions of the methods described in the various embodiments of the present invention.

[0308] In a further embodiment, combined with Figure 3 The at least one processor 32 can execute the operating device of the electronic device 3 and various installed applications (such as the data warehouse optimization device 20), program code, etc., for example, the various modules mentioned above.

[0309] The memory 31 stores program code, and the at least one processor 32 can call the program code stored in the memory 31 to execute related functions. For example, Figure 3 The modules described herein are program codes stored in the memory 31 and executed by the at least one processor 32, thereby realizing the functions of the modules to achieve the purpose of the data warehouse optimization method.

[0310] For example, the program code can be divided into one or more modules / units, which are stored in the memory 31 and executed by the processor 32 to complete this application. The one or more modules / units can be a series of computer-readable instruction segments capable of performing a specific function, which describe the execution process of the program code in the electronic device 3. For example, the program code can be divided into an acquisition module 201, an analysis module 202, a calculation module 203, a monitoring module 204, and an optimization module 205.

[0311] In one embodiment of the present invention, the memory 31 stores a plurality of computer-readable instructions, which are executed by the at least one processor 32 to implement the functionality of the data warehouse optimization method.

[0312] Specifically, the specific implementation method of the above instructions by the at least one processor 32 can be referred to Figure 1 and Figure 2 The descriptions of the relevant steps in the corresponding embodiments are not repeated here.

[0313] In the several embodiments provided by this invention, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of modules is only a logical functional division, and other division methods may be used in actual implementation.

[0314] The modules described as separate components may or may not be physically separate. The components shown as modules may or may not be physical units; they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0315] Furthermore, the functional modules in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or in the form of hardware plus software functional modules.

[0316] It will be apparent to those skilled in the art that the present invention is not limited to the details of the exemplary embodiments described above, and that the invention can be implemented in other specific forms without departing from the spirit or essential characteristics of the invention. Therefore, the embodiments should be considered illustrative and non-limiting in all respects, and the scope of the invention is defined by the appended claims rather than the foregoing description. Thus, all variations falling within the meaning and scope of equivalents of the claims are intended to be embraced within the present invention. No reference numerals in the claims should be construed as limiting the scope of the claims. Furthermore, it is clear that the word "comprising" does not exclude other elements, and the singular does not exclude the plural. Multiple elements or devices recited in the present invention may also be implemented by a single element or device in software or hardware. The terms "first," "second," etc., are used to denote names and do not indicate any particular order.

Claims

1. A method for optimizing a data warehouse, characterized in that, The method includes: In response to a received anomaly monitoring request, the data warehouse to be monitored is obtained, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line; For each business line, a health analysis is performed on the corresponding sub-data warehouse using multiple preset indicators, including: obtaining the corresponding sub-data warehouse based on the theme name of each business line; performing a health analysis on the table names in the sub-data warehouse according to table name normalization requirements; performing a health analysis on the number of times the data tables in the common data layer of the sub-data warehouse are referenced; performing a health analysis on the reference status of the data tables in the sub-data warehouse based on the reference status of the data tables; and performing a health analysis on the task link depth in the sub-data warehouse based on the task link depth. Based on the health analysis results of the multiple preset indicators, the health of the corresponding sub-data warehouse is calculated; Monitor the corresponding sub-data warehouse based on the calculated health status of each sub-data warehouse; The data warehouse is optimized based on multiple monitoring results returned by multiple sub-data warehouses.

2. The data warehouse optimization method as described in claim 1, characterized in that, The method of performing health analysis on the corresponding sub-data warehouse using multiple preset indicators according to each business line also includes: Based on the single-task runtime, perform a health analysis on the single-task execution status of the sub-data warehouse; and / or Based on the task execution failure rate, perform a health analysis on the task execution status of the sub-data warehouse; and / or A health analysis is performed on the repair status of failed tasks in the sub-data warehouse based on the repair time of failed tasks.

3. The data warehouse optimization method as described in claim 2, characterized in that, The health analysis of table names in the sub-data warehouse according to table name normalization requirements includes: Identify the table names of the data tables in the sub-data warehouse; Determine whether the table name of the data table meets the preset normalization requirements; When the table name of the data table meets the preset normalization requirements, the table name of the data table is determined to be healthy; Calculate the first total number of healthy data tables in the sub-data warehouse; Calculate the second total number of data tables in the sub-data warehouse; The quotient obtained by dividing the first total by the second total is determined as the health analysis result corresponding to the table name.

4. The data warehouse optimization method as described in claim 2, characterized in that, The health analysis of the reference counts of data tables in the common data layer of the sub-data warehouse based on the number of references to the data tables includes: Record the number of fields in the data tables of the data common layer that are referenced by the application layer; The number of fields is determined as the number of times the data table is referenced; When the number of times the data table is referenced is greater than or equal to a preset first threshold, the number of times the data table is referenced is determined to be healthy. Calculate the third total number of referenced healthy data tables in the data public layer; Calculate the fourth total number of data tables in the aforementioned data common layer; The quotient obtained by dividing the third total by the fourth total is determined as the health analysis result corresponding to the number of times the data table is referenced.

5. The data warehouse optimization method as described in claim 3, characterized in that, The health analysis of the reference status of data tables in the sub-data warehouse based on the reference status of the data tables includes: Identify whether the data tables in the sub-data warehouse meet the preset referencing specifications, wherein the preset referencing specifications include cross-level referencing or reverse-level referencing; If the data table meets the preset referencing rules, it is determined that the references to the data table are unhealthy; Calculate the fifth total number of references to unhealthy data tables in the sub-data warehouse; The quotient obtained by dividing the fifth total by the second total is determined as the health analysis result corresponding to the reference status of the data table.

6. The data warehouse optimization method as described in claim 1, characterized in that, The step of calculating the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators includes: Obtain the weight coefficient of each preset indicator, and extract the quotient of the corresponding preset indicator from the analysis results of each preset indicator; The health status of the corresponding preset indicator is obtained by multiplying the weight coefficient of each preset indicator by the quotient of the preset indicator. The health score of the sub-data warehouse is obtained by summing the health scores of the multiple preset indicators.

7. The data warehouse optimization method as described in claim 1, characterized in that, The optimization of the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses includes: Extract the cause of the anomaly from the abnormal data table for each monitoring result; Determine the corresponding optimization strategy based on the cause of the anomaly; The data warehouse is optimized based on the optimization strategy described above.

8. An optimization device for a data warehouse, characterized in that, The data warehouse optimization apparatus is used to implement the data warehouse optimization method as described in any one of claims 1 to 7, the apparatus comprising: The acquisition module is used to acquire the data warehouse to be monitored in response to the received anomaly monitoring request, wherein the data warehouse contains multiple business lines and sub-data warehouses of each business line; The analysis module is used to perform health analysis on the corresponding sub-data warehouse according to each business line, using multiple preset indicators; The calculation module is used to calculate the health of the corresponding sub-data warehouse based on the health analysis results of the multiple preset indicators. The monitoring module is used to monitor the corresponding sub-data warehouse based on the calculated health status of each sub-data warehouse; An optimization module is used to optimize the data warehouse based on multiple monitoring results returned by multiple sub-data warehouses.

9. An electronic device, characterized in that, The electronic device includes a processor and a memory, the processor being configured to implement the data warehouse optimization method as described in any one of claims 1 to 7 when executing a computer program stored in the memory.

10. A computer-readable storage medium storing a computer program thereon, characterized in that, When the computer program is executed by a processor, it implements the data warehouse optimization method as described in any one of claims 1 to 7.

Images