Method, device and equipment for drop analysis of master data model in database

Abstract

This application relates to a method, apparatus, and equipment for analyzing the misalignment of master data models in a database. The method includes reading column information from all master data models and all tables in the database to obtain key column information for each master data model and sampled column data from the database. Based on the key column information and sampled column data, the similarity between the key columns of the master data models and all columns in the database is calculated row by row. This generates a matching relationship between the master data models and all columns in the database and confirms the misalignment positions. Based on the matching relationship, the master data model data is compared with the full column data at the corresponding misalignment positions to obtain a detailed breakdown of the misalignment status of the master data models in the database. This application can improve data quality and consistency, promote data sharing and utilization, and enhance enterprise operational efficiency and decision-making capabilities.

Description

Technical Field

[0001] This application relates to the field of data governance technology, and in particular to a method, apparatus and equipment for analyzing the misalignment of master data models in a database. Background Technology

[0002] A database is a large, organized, and shared collection of data stored permanently in a computer. It can be shared by various users, possessing minimal redundancy and high data independence. A database management system (DBMS) provides unified control over the database during its creation, use, and maintenance to ensure data integrity and security, performs concurrency control when multiple users access the database simultaneously, and recovers the database after a failure.

[0003] Master data (MD Master Data) refers to data shared between systems, such as customer, supplier, account, and organizational department-related data. Compared to transaction data, which records business activities and fluctuates significantly, master data (also known as baseline data) changes slowly. In a formal relational data model, transaction records (such as order line items) can be retrieved from master data using keywords (such as order headers or invoice numbers and product codes). It is called master data because the entity information recorded in the data needs to be accurately matched as it flows through various systems within an enterprise.

[0004] There are four main reasons for managing master data. First, it improves data quality: Master data management tools improve the overall quality of enterprise data by centrally managing data from key business entities, ensuring accuracy, consistency, and completeness. Second, it enhances decision-making efficiency: Accurate master data provides decision-makers with a reliable information foundation, enabling them to make more informed and rapid business decisions. Third, it optimizes business processes: Master data management tools support data sharing across departments and systems, helping to simplify business processes and improve operational efficiency. Fourth, it reduces operating costs: By reducing data redundancy and eliminating errors caused by data inconsistencies, master data management tools help reduce enterprise operating costs.

[0005] Data standardization management involves standardizing and integrating internal enterprise data according to certain standards to facilitate data sharing and utilization. The purpose of data standardization management is to improve data quality and consistency, reduce duplication of work and errors, and enhance operational efficiency and decision-making capabilities.

[0006] Information on the location and degree of misalignment of master data model data within the database allows data governance engineers to precisely focus on the data requiring governance, identifying which data conforms to master data standards and which does not, thus enabling targeted quality improvements. In enterprise digitalization projects, the master data system serves as a unified enterprise data standard. However, numerous external procurement business information systems exist within enterprises, and their databases are not designed and planned in sync with master data standards. The location and degree of misalignment of master data model standard data within the database often hinder data governance efforts, limiting in-depth master data governance within business systems. Traditionally, this has relied on manual comparison by business system database experts according to master data standards, requiring a significant workload. Summary of the Invention

[0007] This application provides a method for index-based analysis of a master data model in a database, characterized by comprising: By reading column information from all master data models and all data tables in the database, we can obtain key column information for each master data model and column sample data from the database. Based on the key column information and column sampling data, calculate the similarity between the key columns of the master data model and all columns in the database, generate the matching relationship between the master data model and all columns in the database, and confirm the location of the index. Based on the matching relationship, compare the master data model data with the column data at the corresponding index position to obtain the index details of the master data model in the database.

[0008] Optionally, the method for index-based analysis of the master data model in the database is characterized by: The key column of the master data model is a field that uniquely identifies the master data record; The column sampling data of the database consists of N non-empty data samples taken from each column of the database, where N is greater than or equal to 2.

[0009] Optionally, the step of calculating the similarity between the key columns of the master data model and all columns in the database, based on the key column information and column sampling data, generating the matching relationship between the master data model and all columns in the database, and confirming the target position, includes: Similarity is obtained by calculating the similarity between column data in the database and key column data in the master data model; The similarity is determined by setting a threshold value to assess the matching relationship between the master data model and the database. By determining the matching relationship, the columns that match successfully are found, and the position of the master data model in the database is obtained.

[0010] Optionally, the method for index-based analysis of the master data model in the database is characterized by: The database can be any relational or non-relational database, including but not limited to Oracle, MySQL, Hive, or a data warehouse or data lake. The similarity calculation method can use similarity algorithms for two sequences, including but not limited to Jaccard similarity, cosine similarity, and inclusion metric.

[0011] Optionally, the step of comparing the master data model data and the column data at the corresponding index positions based on the matching relationship to obtain the index details of the master data model in the database includes: The column data at the marked position is considered consistent if it is included in the key column data set of the master data model. The storage of the label details includes a database or a file.

[0012] Optionally, the key columns of the master data model are fields that uniquely identify the master data, including: The key columns of the master data model can be single or multiple, depending on the master data model.

[0013] Optionally, the column sampling data of the database consists of N non-empty data entries sampled from each column of the database, including: The sampling method is random sampling or sequential sampling.

[0014] This application also provides an apparatus for index-drop analysis of a master data model in a database, characterized in that the apparatus includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the method for index-drop analysis of a master data model in a database as described in any one of claims 1 to 7.

[0015] Optionally, the apparatus for analyzing the missing data of the master data model in the database is characterized in that the storage medium is a computer-readable storage medium, and the storage medium stores a computer program, which, when executed by a processor, implements the steps of the method for analyzing the missing data of the master data model in the database as described in any one of claims 1 to 7.

[0016] This application also provides an electronic device, characterized in that it includes: a memory and a processor, wherein the memory stores a computer program, and wherein the processor executes the computer program to implement the method for indexing analysis of the master data model in a database as described in any one of claims 1 to 7.

[0017] The beneficial effects of this application are: it can improve the quality and consistency of data, promote data sharing and utilization, and improve the operational efficiency and decision-making capabilities of enterprises. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the accompanying drawings required in the description of the embodiments or the prior art are briefly introduced below. Obviously, the accompanying drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0019] Figure 1 This application discloses a method for index-based analysis of master data models in a database, which is illustrated in the flowchart below. Figure 2 This application discloses a flowchart for a marker drop analysis. Detailed Implementation

[0020] Various exemplary embodiments, features, and aspects of this application will now be described in detail with reference to the accompanying drawings. The same reference numerals in the drawings denote elements that have the same or similar functions. Although various aspects of the embodiments are shown in the drawings, they are not necessarily drawn to scale unless specifically indicated otherwise.

[0021] The terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this application, "multiple" means two or more, unless otherwise explicitly specified.

[0022] The term “exemplary” as used herein means “serving as an example, embodiment, or illustration.” Any embodiment illustrated herein as “exemplary” is not necessarily to be construed as superior to or better than other embodiments.

[0023] Furthermore, to better illustrate this application, numerous specific details are provided in the following detailed embodiments. Those skilled in the art should understand that this application can be implemented without certain specific details. In some instances, methods, means, components, and circuits well-known to those skilled in the art have not been described in detail in order to highlight the main points of this application.

[0024] This application is a method for index drop analysis. In this method, the system calculates the similarity between different master data models and columns of all tables in the database to obtain the index drop position of the master data model in the database. Then, the index drop position is compared with the master data model data one by one to generate an index drop status detail, which can be viewed by the user on the software.

[0025] like Figure 1 The diagram illustrates a method for index-based analysis of a master data model in a database, according to an embodiment of this application. The method specifically includes the following steps: S100 obtains key column information for each master data model and column sample data for the database by reading column information from all master data models and all data tables in the database. This step involves finding fields that can uniquely identify the master data for different master data models, obtaining the key data columns of the master data, and reading column information from all data tables in the database, and randomly sampling each column.

[0026] S200: Based on the key column information and column sampling data, calculate the similarity between the key columns of the master data model and all columns in the database, generate the matching relationship between the master data model and all columns in the database, and confirm the position of the target. In this step, the key data columns of the master data model obtained in S100 and the column sampling data of the database are used to calculate similarity row by row. A similarity threshold is set; if the calculation result exceeds the threshold, the corresponding column is considered a match; otherwise, it is considered a mismatch. After all calculations are completed, the columns that successfully match are the index positions of the master data model in the database.

[0027] S300: Based on the matching relationship, compare the master data model data with the column data at the corresponding index position to obtain the index details of the master data model in the database.

[0028] In this step, the data from the master data model is compared with the column data at the marked positions obtained in S200. If the column data at the marked positions is included in the key column data set of the master data model, it is considered consistent; otherwise, it is inconsistent. After all comparisons are completed, the database is identified as having which data at the marked positions are consistent with the master data standard and which data are inconsistent, i.e., a detailed list of the marked position status of the master data model in the database.

[0029] The above methods can improve data quality by calculating the similarity between the key columns of the master data model and all columns in the database to identify the index positions, and improve data consistency by comparing the master data model data with the column data at the corresponding index positions. This promotes data sharing and utilization, and enhances the company's operational efficiency and decision-making capabilities.

[0030] like Figure 2 As shown, the flowchart for marker drop analysis includes the following: In this application, firstly, key data columns and column sampling data are extracted from the master data model and the database, respectively.

[0031] Specifically, the key data column refers to the field in the master data model that can uniquely identify the master data. Depending on the master data model, the key data column can be single or multiple. The column sampling data refers to sampling N non-empty data from each column of all data tables in the database, where N is a natural number, such as 1000, 10000, etc. The sampling method can be random sampling or sequential sampling.

[0032] The database can be any relational or non-relational database, including but not limited to Oracle, MySQL, and Hive. The database can also be a data warehouse or a data lake.

[0033] Since this application is more geared towards enterprises, users may use data warehouses or data lakes in addition to various databases. A data warehouse is a single data store created specifically for analytical reporting and decision support, designed to help enterprises that need business intelligence guide business process improvement, monitor time, cost, quality, and control. A data lake is a large-scale unstructured or semi-structured data storage technology used to store raw or unprocessed data of various types and formats.

[0034] Then, based on the key column information and column sampling data, the system calculates the similarity between the key data columns of the master data model and the column sampling data in the database, and obtains a similarity threshold. Next, by calculating the total similarity between the master data and the database, and judging based on the threshold value, the system obtains the position of the master data model in the database.

[0035] Specifically, similarity is the degree of similarity between column data in a database and key column data in a master data model. Similarity calculation methods include Jaccard similarity and cosine similarity. Taking Jaccard similarity as an example, each row in both the key column data of the master data model and the sampled column data of the database should be considered as a set or a group of features; then, the Jaccard similarity formula is applied. Where A represents the key column data of the master data model, and B represents the sampled column data of the database. This represents the size of the intersection of A and B. This indicates the size of the union of A and B; it calculates the Jaccard similarity between key column data in the master data model and sampled data in the database columns.

[0036] Specifically, after the similarity calculation is completed, a similarity threshold is set (e.g., 0.8). When the similarity exceeds the threshold, it is determined that the corresponding column in the master data model matches the corresponding column in the database; otherwise, they do not match.

[0037] Specifically, after setting the threshold, the key data columns of all master data models are looped through sequentially, and similarity calculations are performed with all columns in the database. This yields the similarity between the master data model and all columns in the database. All the obtained similarities are compared with the threshold. When the similarity is greater than the threshold, it is considered a successful match, while when the similarity is less than or equal to the threshold, it is considered a failed match. These successfully matched columns are the index positions of the master data model in the database.

[0038] Finally, the column data at the marked position is compared with the key data columns of the master data model to compare their consistency, and the marked details are generated based on which data is consistent and which data is inconsistent.

[0039] Specifically, each column of data at the marked position is compared line by line with the key data columns of the master data model. If the column data at the marked position is included in the key data columns of the master data model, it is considered consistent; otherwise, it is considered inconsistent. The analysis identifies which data at the marked positions in the database is consistent with the master data model and which is inconsistent, thus providing a detailed breakdown of the marked position status in the master data model. This breakdown is then sent to the user through the system.

[0040] The above steps describe an apparatus for analyzing the missing data of a master data model in a database, characterized in that the apparatus includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the storage medium is a computer-readable storage medium, and the computer program, when executed by the processor, implements the steps of the method for analyzing the missing data of a master data model in a database as described above.

[0041] The above steps utilize an electronic device characterized by including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the method for indexing analysis of any of the master data models in the database.

[0042] The various embodiments of this application have been described above. These descriptions are exemplary and not exhaustive, nor are they limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terminology used herein is chosen to best explain the principles, practical application, or improvement of the technology in the market, or to enable others skilled in the art to understand the embodiments disclosed herein.

Claims

1. A method for index-based analysis of a master data model in a database, characterized in that, include: By reading column information from all master data models and all data tables in the database, we can obtain key column information for each master data model and column sample data from the database. Based on the key column information and column sampling data, calculate the similarity between the key columns of the master data model and all columns in the database, generate the matching relationship between the master data model and all columns in the database, and confirm the location of the index. Based on the matching relationship, compare the master data model data with the column data at the corresponding index position to obtain the index details of the master data model in the database.

2. The method for index-based analysis of the master data model in a database as described in claim 1, characterized in that: The key column of the master data model is a field that uniquely identifies the master data record; The column sampling data of the database consists of N non-empty data samples taken from each column of the database, where N is greater than or equal to 2.

3. The method for index-based analysis of the master data model in a database as described in claim 1, characterized in that, The step of calculating the similarity between the key columns of the master data model and all columns in the database, based on key column information and column sampling data, generating the matching relationship between the master data model and all columns in the database, and confirming the target position includes: Similarity is obtained by calculating the similarity between column data in the database and key column data in the master data model; The similarity is determined by setting a threshold value to assess the matching relationship between the master data model and the database. By determining the matching relationship, the columns that match successfully are found, and the position of the master data model in the database is obtained.

4. The method for index-based analysis of the master data model in a database as described in claim 1, characterized in that: The database can be any relational or non-relational database, including but not limited to Oracle, MySQL, Hive, or a data warehouse or data lake. The similarity calculation method can use similarity algorithms for two sequences, including but not limited to Jaccard similarity, cosine similarity, and inclusion metric.

5. The method for index-based analysis of the master data model in a database as described in claim 1, characterized in that, The step of comparing the master data model data with the column data at the corresponding indexed position based on the matching relationship to obtain the indexed details of the master data model in the database includes: The column data at the marked position is considered consistent if it is included in the key column data set of the master data model. The storage of the label details includes a database or a file.

6. The method for index-based analysis of the master data model in a database as described in claim 2, characterized in that, The key columns of the master data model are fields that uniquely identify the master data record, including: The key columns of the master data model can be single or multiple, depending on the master data model.

7. The method for index-based analysis of the master data model in a database as described in claim 2, characterized in that, The column sampling data of the database consists of N non-empty data entries sampled from each column of the database, including: The sampling method is random sampling or sequential sampling.

8. An apparatus for index-based analysis of a master data model in a database, characterized in that, The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the method for performing index-based analysis of the master data model in a database as described in any one of claims 1 to 7.

9. The apparatus for analyzing the misalignment of the master data model in a database according to claim 8, characterized in that... The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, it implements the steps of the method for analyzing the master data model in the database as described in any one of claims 1 to 7.

10. An electronic device, characterized in that, include: A memory and a processor, wherein the memory stores a computer program, and wherein the processor executes the computer program to implement the method for indexing analysis of the master data model in a database as described in any one of claims 1 to 7.

Images