A data warehouse optimization management system

Abstract

The present invention discloses a data warehouse optimization management system. By setting a data analysis module, a data warehouse construction module and a data warehouse optimization module in the system, the ETL operation of collecting, converting and loading the input data of the business system is performed. The distributed ETL calculation divides the unprocessed big data into several small data sets of equal size, and multiple computing nodes are used to simultaneously calculate each small data set, which can effectively use the computing power of multiple computers, solve the problem of long ETL process time consumption, and improve the data update rate. The appropriate system data granularity is determined by roughly estimating the data level of the data warehouse to be built, different data granularity strategies are determined according to the estimated data level scale, and the table segmentation strategy is determined according to the data granularity used, thereby effectively realizing the performance optimization of the data warehouse, improving the data quality and high credibility, and minimizing the risk of ETL errors on the subsequent data processing of the data warehouse.

Description

Technical Field

[0001] The present invention belongs to the technical field of data warehouses, and in particular relates to a data warehouse optimization management system. Background Art

[0002] The widespread adoption of the internet has ushered in a new era of development for businesses, industry, and education, but this has also resulted in the generation of vast amounts of data. With the advent of the mobile internet era, people are spending increasing amounts of time on various apps, generating massive amounts of user behavior data. Continuous advancements in data collection and storage technologies are also increasing the amount of data accessible to enterprises. However, traditional databases cannot meet the performance requirements for massive data analysis and querying. This has led to the emergence of data warehouse technology. Data warehouses are subject-oriented, non-updatable, integrated, and time-varying. They not only store data but also analyze it and output valuable insights. The sheer volume of data in a data warehouse and the complexity of its queries make its performance a crucial application metric. In practice, data warehouse performance suffers from the following main issues: low data quality, resulting in unreliable analytical results; slow query response times; and unpredictable demand, leading to poor availability. Therefore, optimizing data warehouse performance is essential to better facilitate data access and massive data analysis. Summary of the Invention

[0003] In view of this, the present invention provides a data warehouse optimization management system that improves system access efficiency, improves data warehouse utilization and enhances data warehouse performance to solve the above-mentioned technical problems, which is specifically implemented using the following technical solutions.

[0004] The present invention provides a data warehouse optimization management system, comprising:

[0005] The data analysis module is used to respond to and integrate business system data to form a basic data layer, an intermediate data layer, and a data mart layer. The basic data layer synchronizes the collected data to the data warehouse, performs incremental or full synchronization on structured data, structures unstructured data, and stores the data in a distributed file system. The intermediate data layer is used to store detailed fact data, dimension table data, and public indicator summary data. Dimension data is constructed separately to obtain dimension table data. Detailed data is generated based on data from the basic data layer, and public indicator summary data is generated based on dimension table data and detailed fact data. The data mart layer is used to store personalized statistical indicators, which are generated based on data from the basic data layer and the intermediate data layer.

[0006] The data warehouse construction module is used to extract, clean, and transform data from the business system and load it into the data analysis module to obtain a data warehouse. The data extraction unit determines the data source, and the data conversion unit processes incomplete, erroneous, and duplicate data, and converts the data format and content. The data extraction unit and the data conversion unit belong to the data warehouse construction module.

[0007] The data warehouse optimization module is used to roughly estimate the data level of the data warehouse to be built to determine the system data granularity, determine different data granularity strategies based on the estimated data level, and determine the table segmentation strategy in the data warehouse based on the data granularity used to obtain the data warehouse relationship table corresponding to each business data system.

[0008] As a further improvement to the above technical solution, the process of estimating the order of magnitude of the data warehouse includes:

[0009] Assuming that the number of tables in the conceptual model is N, calculate the size S of each table i (0≤i≤N) i and its primary key size K i , and then estimate the maximum number of records D per table i per unit time max and the minimum number of records D min , and calculate the rough data volume range of the data warehouse through the following expression:

[0010] Where T represents the data existence period. The period of storing comprehensive data in the data warehouse is 5 to 10 years. α is the redundancy factor that estimates the increase in data size due to data indexing and redundancy. α is 1.2 to 2. The maximum number of records in table i per unit time is Estimates can be made based on the specific data of the industry and institution.

[0011] As a further improvement to the above technical solution, different data granularity strategies are determined based on the estimated data magnitude, including:

[0012] If the data size is less than or equal to the preset threshold, the detailed data is directly stored using a single data granularity, and data integration is periodically performed on the stored detailed data;

[0013] If the data size is larger than the preset threshold, double granularity is used. The data warehouse retains recent detailed data. When the data retention period of the industry or organization is reached, the data with the largest time difference is exported to the disk to optimize the storage space of the data warehouse.

[0014] As a further improvement to the above technical solution, the table segmentation strategy is to segment the table according to time, add a suitable time field to each table, and adjust the table or define a new table according to the determined data granularity and segmentation strategy.

[0015] As a further improvement of the above technical solution, the extraction methods of the data extraction unit include full extraction and incremental extraction of data. Full extraction is when the data warehouse is initially established and data already exists in the business system, all the data in the business system is extracted into the constructed data warehouse to ensure that the data in the data warehouse is complete; incremental extraction is when some data in the business system already exists in the data warehouse, some data that is not in the data warehouse is extracted, and the upper limit of the last extraction is recorded, and the time of inserting the record and the update time in the business system are recorded, and the data record or the record's auto-increment primary key is used for recording.

[0016] As a further improvement to the above technical solution, the data analysis module calculates the similarity of business concepts and when it determines that the similarity is greater than a preset similarity, it performs data fusion and also fuses the relationship between the business concepts. The process of calculating the similarity of business concepts includes:

[0017] Business concepts are segmented, and then part-of-speech tagging is performed for syntactic analysis. The text similarity is calculated by combining the word vector model of the vocabulary database.

[0018] An optimized training model is used to express words in corresponding vector form. The word similarity is calculated by calculating the cosine value of the vector angle in the constructed word vector space. In the semantically annotated corpus, the annotated business concepts contain multiple parts of speech. When the part of speech is a phrase, the similarity is directly calculated. When the part of speech is a long text, the labeled business concept is converted into a form of a modifier plus a central word. The similarity between long texts is converted into the similarity between two central words and two modified phrases.

[0019] As a further improvement of the above technical solution, when calculating the similarity of text, the central word is used as the core and the modifying phrase is used as a reference. The annotated text T is preset, and its central word is represented by H. M(T) = {m1, m2...m n}, then the annotated business concept can be expressed as a bigram (H, M(T)), and the weight of the center word similarity is adjusted in the entire calculation by the β value;

[0020] The expression for calculating the similarity between texts T1 and T2 is Sim(T1, T2) = Sim(H1, H2)*[β+(1-β)*Sim(M(T1), M(T2))], where H1 and H2 are the central words of T1 and T2, M(T1) and M(T2) are the modifiers of T1 and T2, Sim(H1, H2) is calculated by optimizing the training model, and β represents the weight of the central word similarity.

[0021] As a further improvement to the above technical solution, when calculating the similarity of modified phrases, if there is a modifier in two long texts, the calculation is similar to the calculation of the central word similarity, that is, the similarity is pre-estimated from the perspective of word vectors;

[0022] If there are multiple modifiers in the two long texts, combined with the weighted geotropism between the phrase elements, assuming that M(T1) has one word and M(T2) has J words, the constructed expression is The similarity between M(T1) and M(T2) can be obtained by If I>J, I / J is changed to J / I.

[0023] As a further improvement of the above technical solution, the data warehouse optimization module includes a data loading unit. The execution process of the data loading unit includes:

[0024] Create a first temporary table of the original data warehouse table. The content of the first temporary table is empty and has no primary key or foreign key. Continuously integrate data based on the first temporary table. Execute a packaging routine that updates the original data warehouse schema table with the records in the temporary table and recreates the first temporary table without content to rebuild the index of the original table.

[0025] As a further improvement of the above technical solution, the data warehouse optimization module includes a data update unit, and the execution process of the data update unit includes:

[0026] Create a second temporary table for all tables in the data warehouse. The second temporary table is used to receive new data. The second temporary table has no index, primary key, or foreign key. An additional attribute is created based on the second temporary table to store a unique sequential identifier related to the insertion of each row in the second temporary table. The unique sequential identifier is used to mark the association between the data warehouse table and the second temporary table.

[0027] The present invention provides a data warehouse optimization management system. By setting a data analysis module, a data warehouse construction module and a data warehouse optimization module in the system, the system performs ETL operations on the collection, conversion and loading of business system input data. Distributed ETL calculation divides unprocessed big data into several small data sets of equal size. Multiple computing nodes are used to simultaneously calculate each small data set, which can effectively use the computing power of multiple computers, solve the problem of long ETL process time consumption, and improve the data update rate. The appropriate system data granularity is determined by roughly estimating the data level of the data warehouse to be built, different data granularity strategies are determined according to the estimated data level, and the table segmentation strategy is determined according to the data granularity used, thereby effectively achieving data warehouse performance optimization, improving data quality and high credibility, and at the same time minimizing the risk of ETL errors in the subsequent data processing of the data warehouse. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. It should be understood that the following drawings only illustrate certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other relevant drawings can be obtained based on these drawings without paying any creative work.

[0029] Figure 1 This is a structural diagram of the data warehouse optimization management system provided by the present invention;

[0030] Figure 2 This is a process diagram for calculating the similarity of business concepts provided by the present invention. DETAILED DESCRIPTION

[0031] The following describes embodiments of the present invention in detail. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended only to explain the present invention and are not to be construed as limiting the present invention.

[0032] See Figure 1 The present invention provides a data warehouse optimization management system, comprising:

[0033] The data analysis module is used to respond to and integrate business system data to form a basic data layer, an intermediate data layer, and a data mart layer. The basic data layer synchronizes the collected data to the data warehouse, performs incremental or full synchronization on structured data, structures unstructured data, and stores the data in a distributed file system. The intermediate data layer is used to store detailed fact data, dimension table data, and public indicator summary data. Dimension data is constructed separately to obtain dimension table data. Detailed data is generated based on data from the basic data layer, and public indicator summary data is generated based on dimension table data and detailed fact data. The data mart layer is used to store personalized statistical indicators, which are generated based on data from the basic data layer and the intermediate data layer.

[0034] The data warehouse construction module is used to extract, clean, and transform data from the business system and load it into the data analysis module to obtain a data warehouse. The data extraction unit determines the data source, and the data conversion unit processes incomplete, erroneous, and duplicate data, and converts the data format and content. The data extraction unit and the data conversion unit belong to the data warehouse construction module.

[0035] The data warehouse optimization module is used to roughly estimate the data level of the data warehouse to be built to determine the system data granularity, determine different data granularity strategies based on the estimated data level, and determine the table segmentation strategy in the data warehouse based on the data granularity used to obtain the data warehouse relationship table corresponding to each business data system.

[0036] In this embodiment, the data source within the data warehouse is the foundation of the data warehouse. Data warehouses typically consist of multiple different types of data sources, containing both internal and external information. Data may be structured, semi-structured, or in other formats. Storage and management are the core and key to data manipulation and determine the form in which the data warehouse provides data. This requires specific processes for extraction, cleaning, conversion, and integration based on the actual data characteristics. Data warehouses can also be categorized into enterprise-level data warehouses and departmental internal data warehouses based on their coverage. The completion of a data warehouse doesn't mark the end of the work; corresponding monitoring is required to help developers promptly identify and quickly respond to data warehouse issues. Monitoring functions include data monitoring, table monitoring, and task monitoring. Data monitoring involves monitoring whether hourly data volume experiences sudden spikes or decreases, the success rate of field reporting, the vacancy rate of important fields, the matching rate of events that should appear in pairs, and the duplication rate. Table monitoring involves monitoring whether the table naming format complies with specifications, whether required information is accurate, and whether the lifecycle is set (i.e., metadata-level monitoring). Task monitoring involves monitoring whether ETL tasks run successfully and whether there are delays in task completion.

[0037] It should be noted that the basic data layer is also used to preserve historical data for data needs and audit requirements. The intermediate data layer is internally divided into a detailed data layer and a summary data layer. The intermediate data layer uses a dimensional model to degenerate some dimensional information into the fact table through dimensionality regression. This effectively reduces joins between fact tables and dimension tables and improves the usability of detailed tables. The summary data layer strengthens dimensionality regression for indicators. By constructing a public indicator data layer using more wide tables, it improves indicator reusability, reduces data duplication, mitigates the risk of inconsistent calculation calibers, and establishes consistent dimensions. Indicators at the data mart layer are not public, and the calculation logic for indicators is relatively complex. Indicators are also assembled based on application data. Data call services prioritize using data from the intermediate data layer. When the public layer does not have the required data, it is necessary to evaluate whether the need is temporary or long-term. If the need is long-term, the corresponding calculation logic needs to be developed in the public layer. Data warehouse models include ER models, dimensional models, and Data Vault models. By selecting dimensional modeling in the construction of user behavior data warehouses, high-performance retrieval, good scalability, ease of use, and rapid response can be achieved. Query performance can be improved by using redundant data. With the current decline in hard drive prices, storage costs are constantly decreasing. Compared with storage overhead, high performance will bring higher value. When new business emerges, the new model will not impact the existing model, and new model data can be generated without any impact. The model is easier to understand, which is conducive to the promotion and use of data, and users do not need to write lengthy SQL code when querying. In the data warehouse, the data structure of each table must comply with regulations before physical modeling can be performed. Otherwise, the output data fields may not match the table structure fields, resulting in data storage failure.

[0038] It should be understood that ETL involves the extraction, transformation, and loading of data. Data warehouses contain large amounts of data, necessitating a parallel storage structure. When building a data warehouse, factors such as data read / write speed, storage efficiency, system reliability and maintainability, and system cost must be weighed to determine a storage structure that meets the system's data volume and business needs. B-Tree indexes use a tree to provide an access path to the data blocks being searched. Index pointers are used to read disk blocks and ultimately locate the required data. While B-Tree indexes provide a convenient method for rapid data searches in OLTP systems, they have certain limitations for handling complex interactive queries within data warehouses. B-Tree indexes often require indexes on highly selective fields. While B-Tree indexes are more suitable for querying a small number of records from large tables, they lack performance for the large number of complex set queries in data warehouse environments. When querying data in a data warehouse, a single query often involves a series of tables. For two tables that are commonly accessed simultaneously, records from the two tables can be physically merged by grouping related records together using common keywords, thereby improving I / O efficiency. Optimizing data access with strong access dependencies can also be achieved by adding redundant data. This provides similar performance benefits to table merges, but increases the amount of data stored. If this redundancy yields better access efficiency with a minimal increase in data volume, it's worthwhile. Redundant data should also be updated when data is updated, but data is typically not modified in a data warehouse environment. Pre-join technology can be used to perform certain join operations for frequently used queries and store the results, using storage space in exchange for improved OLAP performance.

[0039] Optionally, the process of estimating the order of magnitude of the data warehouse includes:

[0040] Assuming that the number of tables in the conceptual model is N, calculate the size S of each table i (0≤i≤N) i and its primary key size K i , and then estimate the maximum number of records D per table i per unit time max and the minimum number of records D min , and calculate the rough data volume range of the data warehouse through the following expression:

[0041] Where T represents the data existence period. The period of storing comprehensive data in the data warehouse is 5 to 10 years. α is the redundancy factor that estimates the increase in data size due to data indexing and redundancy. α is 1.2 to 2. The maximum number of records in table i per unit time is Estimates can be made based on the specific data of the industry and institution.

[0042] In this embodiment, different data granularity strategies are determined based on the estimated data volume. These strategies include: if the data size is less than or equal to a preset threshold, a single data granularity is used to directly store detailed data, with periodic data aggregation performed on the stored detailed data. If the data size exceeds the preset threshold, a dual granularity is used, with the data warehouse retaining recent detailed data and, upon reaching the industry or organization's data retention period, exporting data with the largest time difference to disk to optimize data warehouse storage space. The table partitioning strategy partitions tables by time, adding appropriate time fields to each table. Tables are then adjusted or new tables are defined based on the determined data granularity and partitioning strategy.

[0043] Optionally, the extraction methods of the data extraction unit include full extraction and incremental extraction of data. Full extraction is when the data warehouse is initially established and data already exists in the business system, all the data in the business system is extracted into the constructed data warehouse to ensure that the data in the data warehouse is complete; incremental extraction is when some data in the business system already exists in the data warehouse, some data that is not in the data warehouse is extracted, and the upper limit of the last extraction is recorded, as well as the time when the record is inserted and updated in the business system, and the data record or the record's auto-increment primary key is used for recording.

[0044] In this embodiment, incremental extraction is typically used when a data warehouse is initially built, when new services are added to the business system, or when the data warehouse requires new topics. In these cases, the data warehouse may not have any historical data, and relevant historical data already in the business system database needs to be extracted. Incremental extraction significantly reduces ETL processing time and reduces data redundancy in the data warehouse. Incremental extraction is not only a relatively important method, but also a relatively complex data extraction method.

[0045] It should be noted that due to the non-standard development of the business system, there may be duplicate submissions in the database. In this case, the duplicate data needs to be deleted. To delete the duplicate data, only the last submitted data needs to be saved, and other identical data needs to be deleted. There are legacy issues in development. Different business systems are not unified for consistent data. For example, gender issues are often saved as data dictionaries in the database. Some business systems save males as 1, while others save males as 0. Unified coding is required during the data conversion process. In the business system, granularity is often a transaction process. However, for the data warehouse, which is an analysis-oriented system, its granularity is different from that in the business system and needs to be converted during the data conversion process. Data loading is to load the converted data into the business system. The data loading process needs to ensure the data loading speed as much as possible, which solves the data update problem.

[0046] Optionally, the data analysis module calculates the similarity of business concepts and when it determines that the similarity is greater than a preset similarity, performs data fusion and also fuses the relationship between the business concepts. The process of calculating the similarity of business concepts includes:

[0047] S1: Segment the business concepts, perform part-of-speech tagging for syntactic analysis, and calculate text similarity using the word vector model of the lexicon.

[0048] S2: The training model expresses words in corresponding vector form, and calculates the similarity of words by calculating the cosine value of the vector angle in the constructed word vector space. In the semantically annotated corpus, the annotated business concepts contain multiple parts of speech. When the part of speech is a phrase, the similarity is directly calculated. When the part of speech is a long text, the labeled business concept is converted into the form of a modifier plus a central word, and the similarity between long texts is converted into the similarity between two central words and two modified phrases.

[0049] In this embodiment, when calculating the similarity of text, the central word is used as the core and the modified word group is used as a reference. The annotated text T is preset, and its central word is represented by H. M(T) = {m1, m2...m n}, then the annotated business concept can be expressed as a binary pair (H, M(T)), and the weight of the center word similarity is adjusted in the entire calculation by the β value; the expression for calculating the similarity between texts T1 and T2 is Sim(T1, T2) = Sim(H1, H2)*[β+(1-β)*Sim(M(T1),M(T2))], where H1 and H2 are the center words of T1 and T2, M(T1) and M(T2) are the modifiers of T1 and T2, and the calculation of Sim(H1, H2) is obtained by optimizing the training model. β represents the weight of the center word similarity. When calculating the similarity of modified phrases, if there is one modifier in the two long texts, it is similar to the calculation of the center word similarity, that is, the similarity is pre-estimated from the perspective of the word vector; if there are multiple modifiers in the two long texts, combined with the weighted geotropism between the phrase elements, it is assumed that M(T1) has one word and M(T2) has J words, then the expression is constructed as follows: The similarity between M(T1) and M(T2) can be obtained by If I>J, I / J is changed to J / I.

[0050] It should be noted that by analyzing the characteristics of business data, similarity is directly calculated for short texts, and special part-of-speech tagging is performed on business concept data with longer texts. Business concepts are represented as central words and modifiers, and similarity between business concepts is calculated. The metadata obtained after fusion is the metadata required for data warehouse dimensional modeling. This data originates from various processes in the lifecycle. Metadata fusion eliminates semantic heterogeneity between business concepts. After using metadata for data warehouse dimensional modeling, data from different departments throughout the lifecycle can be extracted into the data warehouse metamodel through unified identification, eliminating semantic inconsistencies at the conceptual level.

[0051] Optionally, the data warehouse optimization module includes a data loading unit, and the execution process of the data loading unit includes: creating a first temporary table of the original data warehouse table, the content of the first temporary table is empty and has no primary key or foreign key, continuously integrating data based on the first temporary table, executing a packaging routine, which will use the records in the temporary table to update the original data warehouse schema table, and recreate the first temporary table without content to rebuild the index of the original table.

[0052] In this embodiment, the data warehouse optimization module includes a data update unit, and the execution process of the data update unit includes: creating a second temporary table for all tables in the data warehouse, the second temporary table is used to receive new data, the second temporary table has no index, primary key and foreign key, and creating an additional attribute based on the second temporary table to store a unique sequential identifier related to the insertion of each row in the second temporary table, and the unique sequential identifier is used to mark the association between the data warehouse table and the second temporary table. In order to refresh the database, the application extracts the OLTP data and converts it into the correct format to load the data area of ​​the data warehouse, inserts the record as a new type into the corresponding temporary table, and increases the unique sequential identifier attribute by one. In order to query the latest data, the query statement needs to be modified, and all the data in the data warehouse table and the temporary table need to be combined for the search.

[0053] It should be noted that data is integrated into tables without any access optimization, such as indexes, which will affect their functionality and reduce performance. Due to the actual size of the space occupied, the performance of the temporary table will decline after a certain number of insertions. To restore performance in the data warehouse, a packaging routine must be executed. This routine will use the records in the temporary table to update the original data warehouse table and recreate the temporary table without content. To update the original data warehouse table, the rows in the temporary table should be summarized by the primary key of the original table. The rows in the temporary table are added to the original table as the latest records in ascending order of sequential identifiers, thereby improving the performance of the data warehouse.

[0054] In all examples shown and described herein, any specific values ​​should be interpreted as merely exemplary and not limiting, and thus other examples of the exemplary embodiments may have different values.

[0055] It should be noted that similar reference numerals and letters denote similar items in the following drawings, and therefore, once an item is defined in one drawing, it does not need to be further defined or explained in subsequent drawings.

[0056] The above-described embodiments merely illustrate several implementations of the present invention, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present invention. It should be noted that variations and modifications are possible without departing from the scope of the present invention, and such variations and modifications are fully within the scope of protection of the present invention.

Claims

1. A data warehouse optimization management system, characterized in that: include: The data analysis module is used to respond to and integrate business system data to form a basic data layer, an intermediate data layer, and a data mart layer. The basic data layer synchronizes the collected data to the data warehouse, performs incremental or full synchronization on structured data, structures unstructured data, and stores the data in a distributed file system. The intermediate data layer is used to store detailed fact data, dimension table data, and public indicator summary data. Dimension data is constructed separately to obtain dimension table data. Detailed data is generated based on data from the basic data layer, and public indicator summary data is generated based on dimension table data and detailed fact data. The data mart layer is used to store personalized statistical indicators, which are generated based on data from the basic data layer and the intermediate data layer. The data warehouse construction module is used to extract, clean, and transform data from the business system and load it into the data analysis module to obtain a data warehouse. The data extraction unit determines the data source, and the data conversion unit processes incomplete, erroneous, and duplicate data, and converts the data format and content. The data extraction unit and the data conversion unit belong to the data warehouse construction module. The data warehouse optimization module is used to roughly estimate the data volume of the data warehouse to be built to determine the system data granularity, determine different data granularity strategies based on the estimated data volume, and determine the partitioning strategy of the tables in the data warehouse based on the data granularity used to obtain the data warehouse relationship table corresponding to each business data system; The process of estimating the order of magnitude of a data warehouse includes: The default number of tables in the conceptual model is , calculate each table Size and its primary key size , and then estimate each table Maximum number of records per unit time and minimum number of records , and calculate the rough data volume range of the data warehouse through the following expression: , where T represents the data existence cycle. The cycle of storing comprehensive data in the data warehouse is 5 to 10 years. is the redundancy factor that estimates the increase in data size due to data indexing and redundancy, The value ranges from 1.2 to 2, which is the maximum number of records in table i per unit time. Estimates can be made based on the specific data of the industry and institution.

2. The data warehouse optimization management system according to claim 1, characterized in that: Determine different data granularity strategies based on the estimated data volume, including: If the data size is less than or equal to the preset threshold, the detailed data is directly stored using a single data granularity, and data integration is periodically performed on the stored detailed data; If the data size is larger than the preset threshold, double granularity is used. The data warehouse retains recent detailed data. When the data retention period of the industry or organization is reached, the data with the largest time difference is exported to the disk to optimize the storage space of the data warehouse.

3. The data warehouse optimization management system according to claim 2, characterized in that: The table segmentation strategy segments the table according to time, adds an appropriate time field to each table, and adjusts the table or defines a new table according to the determined data granularity and segmentation strategy.

4. The data warehouse optimization management system according to claim 1, characterized in that: The data extraction unit can extract data in two ways: full extraction and incremental extraction. Full extraction is to extract all the data in the business system into the built data warehouse when the data warehouse is initially established. This ensures that the data in the data warehouse is complete. Incremental extraction is to extract some data that is not in the data warehouse if some data in the business system already exists in the data warehouse, record the upper limit of the last extraction, the time when the record was inserted and updated in the business system, and use the data record or the record's auto-increment primary key to record it.

5. The data warehouse optimization management system according to claim 1, characterized in that: The data analysis module calculates the similarity of business concepts and when it determines that the similarity is greater than the preset similarity, it performs data fusion and also fuses the relationship between business concepts. The process of calculating the similarity of business concepts includes: Business concepts are segmented, and then part-of-speech tagging is performed for syntactic analysis. The text similarity is calculated by combining the word vector model of the vocabulary database. An optimized training model is used to express words in corresponding vector form. The word similarity is calculated by calculating the cosine value of the vector angle in the constructed word vector space. In the semantically annotated corpus, the annotated business concepts contain multiple parts of speech. When the part of speech is a phrase, the similarity is directly calculated. When the part of speech is a long text, the labeled business concept is converted into a form of a modifier plus a central word. The similarity between long texts is converted into the similarity between two central words and two modified phrases.

6. The data warehouse optimization management system according to claim 5, characterized in that: When calculating the similarity of text, we take the central word as the core and use the modified phrases as a reference. For the pre-annotated text T, we use H to represent its central word. , then the annotated business concept can be expressed as a binary tuple: ,pass The value adjusts the weight of the center word similarity in the entire calculation; Calculated Text and The similarity expression is ,in and yes and The central word, and yes and Modifiers of The calculation is obtained by optimizing the training model. Represents the weight of the center word similarity.

7. The data warehouse optimization management system according to claim 5, characterized in that: When calculating the similarity of modified phrases, if there is a modifier in two long texts, the calculation is similar to the central word similarity, that is, the similarity is pre-estimated from the perspective of word vectors; If there are multiple modifiers in two long texts, combined with the weighted similarity between the phrase elements, the preset There is a word and There are J words, so the constructed expression is , and The similarity between Calculate, if ,but Modified to .

8. The data warehouse optimization management system according to claim 1, characterized in that: The data warehouse optimization module includes a data loading unit. The execution process of the data loading unit includes: Create a first temporary table of the original data warehouse table. The content of the first temporary table is empty and has no primary key or foreign key. Continuously integrate data based on the first temporary table. Execute a packaging routine that updates the original data warehouse schema table with the records in the temporary table and recreates the first temporary table without content to rebuild the index of the original table.

9. The data warehouse optimization management system according to claim 8, characterized in that: The data warehouse optimization module includes a data update unit. The execution process of the data update unit includes: Create a second temporary table for all tables in the data warehouse. The second temporary table is used to receive new data. The second temporary table has no index, primary key, or foreign key. An additional attribute is created based on the second temporary table to store a unique sequential identifier related to the insertion of each row in the second temporary table. The unique sequential identifier is used to mark the association between the data warehouse table and the second temporary table.

Images