Data reconciliation system oriented to service control

Abstract

The invention relates to the field of data management, and discloses a data reconciliation system oriented to service management and control, which is used for solving the problem of poor universality due to different grammar of statements compiled by different personnel when the data quantity is increased, and comprises a data element construction module, a data element association module, a statement generation module and a statement modification module, the method comprises the following steps: configuring basic data, forming standard data elements, collecting standard data element data, associating the standard data elements through a JOI N operator according to a data element priority association index, generating new data elements, associating through an account checking operator according to the new data elements, generating a complete account checking statement, and storing basic information of the data elements into a database; according to the data reconciliation statement generation method and device, the reconciliation statement information composed of the data element and the reconciliation operator is stored, the data element and the reconciliation operator are combined in the configuration file according to the new reconciliation requirement, the new reconciliation statement is generated, the universality of the data reconciliation statement is effectively improved, and the situation of omission is avoided.

Description

Technical Field

[0001] The present invention relates to the field of data management, and more specifically to a data reconciliation system oriented to service management and control. Background Art

[0002] The data reconciliation system for service management and control refers to a system used to manage and monitor transactions and service usage between service providers and customers. This system can track and record the details of various service transactions, aiming to ensure consistency in transaction records between service providers and customers, and provide transparency and accuracy to reduce errors and disputes.

[0003] The existing data reconciliation system uses the SQL language to write the data to be reconciled and the reconciliation logic into one statement for data reconciliation. However, this data reconciliation method will result in the rapid expansion of reconciliation data information and calculation logic information as the amount of data increases and the data reconciliation logic increases. The SQL statements written by different people have different syntaxes, poor universality and readability, are difficult to maintain uniformly, and may cause omissions.

[0004] In view of the above problems, the present invention proposes a solution. Summary of the invention

[0005] In order to overcome the above-mentioned defects of the prior art, the present invention provides a data reconciliation system for service management and control to solve the problems existing in the above-mentioned background technology.

[0006] To achieve the above object, the present invention provides the following technical solutions:

[0007] A data reconciliation system for service management and control includes a data element construction module, a data element association module, a statement generation module, and a statement modification module. The modules are connected by signals. The steps of data processing between the modules are as follows:

[0008] A data element construction module is used to configure basic data to form standard data elements. The basic data includes data source, latitude, measurement, aggregation operator and screening condition;

[0009] The data element association module collects standard data metadata, calculates the data element priority association index based on the standard data metadata, and associates the standard data elements through the JOI N operator based on the data element priority association index to generate new data elements;

[0010] The statement generation module is used to associate the new data element through the reconciliation operator to generate a complete reconciliation statement;

[0011] The information storage module is used to store the basic information of the data element and the reconciliation statement information composed of the data element and the reconciliation operator through a configuration file in JSON format;

[0012] The statement modification module is used to combine data elements and reconciliation operators in the configuration file according to new reconciliation requirements to generate new reconciliation statements.

[0013] Preferably, the step of calculating the data element priority association index is:

[0014] Count the names, data types and other information of all data elements, cluster the data elements with the same name together to form data element clusters, and understand the type information of each data element cluster;

[0015] Query the information of the data element in each cluster, and calculate the data volume coefficient based on the information of the data element;

[0016] The number of indexes in the data element in each cluster is queried through the database management system, and the number of indexes in the data element in each cluster is recorded as the index coefficient;

[0017] The usage of data elements is obtained by querying the database log, and the data element query demand coefficient is obtained according to the usage of data elements;

[0018] The data element priority association index is calculated by the data volume coefficient, index coefficient and data element query requirement coefficient, and its calculation formula is: Among them, PA represents the data element priority association index, TD represents the data volume coefficient, IE represents the index coefficient, QR represents the data element query requirement coefficient, and a1, a2, and a3 represent weight coefficients.

[0019] Preferably, the data volume coefficient calculation step is:

[0020] Count the number of data elements in each cluster and record it as the total data volume;

[0021] Use SQL query statements to query the number of null values ​​in data elements;

[0022] Use SQL query statements to query the number of different values ​​in each cluster data element;

[0023] The data element quantity coefficient is calculated based on the total data volume of the data element, the number of null values ​​of the data element, and the number of different values ​​in each cluster data element. The calculation formula is TD = (T 总 -T 空 )×VL, where TD represents the data metadata coefficient, T 总 Expressed as the total data volume, T 空It is represented by the number of null values ​​of the data element, and VL is represented by the number of different values ​​in each cluster data element.

[0024] Preferably, the data element query requirement coefficient calculation step is:

[0025] The frequency of use of each cluster data element in the query is counted within the detection time T. The more frequently used a data source is, the higher its query demand is.

[0026] Analyze the conditions in the query statement within the detection time T, and count the frequency of occurrence of each cluster data element in the query condition. The more frequent the data element, the higher its query demand;

[0027] The data element query demand coefficient is calculated based on the usage frequency of each cluster data element in the query and the frequency of occurrence in the query condition. The calculation formula is QR=UE+AE, where QR represents the data element query demand coefficient, UE represents the usage frequency of each cluster data element in the query, and AE represents the frequency of occurrence in the query condition.

[0028] Preferably, the step of counting the usage frequency of each cluster data element in the query is:

[0029] Obtain database query logs or audit logs, which record all query operations performed on the database. Query logs contain detailed information about the query, including the tables and columns involved, and the execution time of the query;

[0030] Filter out query operations that contain target data elements from the query log, and determine which queries involve the target data elements by analyzing the column names or conditions in the query statements.

[0031] Count the number of times the target data element appears in the filtered query, that is, its usage frequency. Use the query times in the query log to calculate the usage frequency of the data element, or group the query operations in the query log according to the target data element for statistics.

[0032] Preferably, the step of generating a complete reconciliation statement by associating the new data element through the reconciliation operator is:

[0033] According to the reconciliation operator, determine the data elements that need to be associated. These data elements may come from different data sources or different database tables and need to be associated through JOIN operations.

[0034] According to the association relationship of data elements and reconciliation operators, write SQL query statements to implement reconciliation operations. The query statements should include information such as association conditions, matching rules, and output fields;

[0035] After writing, debug and test the SQL query statement to ensure that the query results meet the expected reconciliation rules and logic. You can test by executing the query statement and checking the output results.

[0036] According to the test results, the query statement is optimized and adjusted by adding indexes, adjusting the query order, etc., to improve query performance and efficiency, and obtain a complete reconciliation statement.

[0037] Technical effects and advantages of the present invention:

[0038] Configure basic data to form standard data elements, collect standard data metadata, associate standard data elements through JOI N operators according to data element priority association index, generate new data elements, associate new data elements through reconciliation operators, generate a complete reconciliation statement, store basic information of data elements and reconciliation statement information composed of data elements and reconciliation operators, combine data elements and reconciliation operators in configuration files according to new reconciliation requirements, and generate new reconciliation statements, which effectively improves the versatility of data reconciliation statements and avoids omissions. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 It is the overall flow chart of the present invention.

[0040] Figure 2 The figure is a flow chart of calculating the priority association index of data elements of the present invention. DETAILED DESCRIPTION

[0041] The technical solution of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the present invention. In addition, the forms of the various structures recorded in the following implementation modes are merely illustrative. The data reconciliation system for service management and control involved in the present invention is not limited to the various structures recorded in the following implementation modes. All other implementation modes obtained by ordinary technicians in this field without making creative work belong to the scope of protection of the present invention.

[0042] The present invention provides a data reconciliation system for service management and control, including a data element construction module, a data element association module, a statement generation module and a statement modification module. The modules are connected by signals, and the data processing steps between the modules are as follows:

[0043] A data element construction module is used to configure basic data to form standard data elements. The basic data includes data source, latitude, measurement, aggregation operator and screening condition;

[0044] The data element association module collects standard data metadata, calculates the data element priority association index based on the standard data metadata, and associates the standard data elements through the JOIN operator based on the data element priority association index to generate new data elements;

[0045] The statement generation module is used to associate the new data element through the reconciliation operator to generate a complete reconciliation statement;

[0046] The information storage module is used to store the basic information of the data element and the reconciliation statement information composed of the data element and the reconciliation operator through a configuration file in JSON format;

[0047] The statement modification module is used to combine the data element with the reconciliation operator in the configuration file according to the new reconciliation requirement to generate a new reconciliation statement. The modification of the data element will directly modify all reconciliation statements containing the data element.

[0048] In this embodiment, it should be specifically explained that the steps of configuring the basic data to form standard data elements are:

[0049] Determine the basic data that needs to be configured, that is, identify the data element. The data element is the smallest unit in the data model and represents a specific data item or attribute in the business;

[0050] For each data element, a detailed definition and description is provided, including the data element's name, data type, length, description, value range, and business meaning information;

[0051] Classify and categorize data elements according to their attributes and business logic;

[0052] Use database management tools to configure each data element according to the definition and classification of the data element, including determining the attribute values, default values, constraints and associations of the data element to obtain standard data elements.

[0053] By configuring basic data elements in a standardized form, you can ensure that data has a consistent definition and format throughout the organization. This helps eliminate data confusion and inconsistency, improves data quality and reliability, and standard data elements make data easier to manage and maintain. You can record the definition, attributes, and relationships of data elements through a data dictionary or data model, so that the structure and meaning of data are clearly visible, easy to understand and operate, and standard data elements make data easier to share and exchange. When different systems or organizations need to share data, standard data elements can ensure the compatibility and consistency of data between different systems, reduce the complexity and cost of data integration, and standard data elements make data more suitable for analysis and mining. Through unified data standards, it is easier to perform statistical analysis, data mining, and business intelligence on data. Standard data elements can improve work efficiency. Data standardization makes data easier to find, understand, and use, reduces the complexity of data processing and the possibility of errors, and improves work efficiency and accuracy.

[0054] In this embodiment, it should be specifically explained that Figure 2 As shown, the steps for calculating the data element priority association index are:

[0055] Count the names, data types and other information of all data elements, cluster the data elements with the same name together to form data element clusters, and understand the type information of each data element cluster;

[0056] Query the information of the data element in each cluster, and calculate the data volume coefficient based on the information of the data element;

[0057] The number of indexes in the data element in each cluster is queried through a database management system, and the number of indexes in the data element in each cluster is recorded as an index coefficient. The database management system is a software used to manage and organize the creation, maintenance, operation and control of a database, and provides a structured data storage system so that users can conveniently store, retrieve, update and manage data;

[0058] The usage of data elements is obtained by querying the database log, and the data element query demand coefficient is obtained according to the usage of the data elements. The database log is a mechanism for the database management system to record database operations and transaction execution processes. It is a persistent log that records database system activities and is used to record database changes, fault recovery, and transaction status;

[0059] The data element priority association index is calculated by the data volume coefficient, index coefficient and data element query requirement coefficient, and its calculation formula is: Among them, PA represents the data element priority association index, TD represents the data volume coefficient, and the table with smaller data volume is placed in the front for JION operation to reduce the data volume of subsequent JOIN operation and improve query efficiency. The higher the data volume coefficient, the higher the data volume, so the lower the data element priority association index. IE represents the index coefficient. The index can speed up the data search and matching process and reduce the time required for query. In the JOIN operation, if the connected data elements have indexes, the database engine can locate and match the corresponding data rows faster, thereby improving query performance. The indexed data elements can reduce the amount of data that the database engine needs to scan. Through the index, the database engine can directly locate the data rows that meet the conditions without scanning the entire table, thereby reducing the time complexity of the query. Therefore, the higher the index coefficient, the higher the data element priority association index. QR represents the data element query demand coefficient. Data elements with high query demand are frequently used for query. Associating them with other data elements through JOIN operation can reduce multiple queries, thereby improving query performance. The JOIN operation can combine related data in multiple tables, avoiding multiple separate queries and reducing the number of database accesses. Through the JOIN operation, data can be associated at the database level, reducing the overhead of data transmission and processing, which can reduce the load on the database and improve the overall performance and stability of the system. Therefore, the higher the data element query demand coefficient, the higher the data element priority association index. a1, a2, and a3 are expressed as weight coefficients of the data volume coefficient, index coefficient, and data element query demand coefficient, and this embodiment does not perform specific calculations on the specific values ​​of a1, a2, and a3.

[0060] In this embodiment, it should be specifically explained that the steps for calculating the data volume coefficient are:

[0061] Count the number of data elements in each cluster and record it as the total data volume;

[0062] Use SQL query statements to query the number of null values ​​in data elements;

[0063] Use SQL query statements to query the number of different values ​​in each cluster data element;

[0064] The data element quantity coefficient is calculated based on the total data volume of the data element, the number of null values ​​of the data element, and the number of different values ​​in each cluster data element. The calculation formula is TD = (T 总 -T 空 )×VL, where TD represents the data metadata coefficient, T 总 Expressed as the total data volume, T 空 It is represented by the number of null values ​​of the data element, and VL is represented by the number of different values ​​in each cluster data element.

[0065] The number of different values ​​in the data element refers to the number of different values ​​contained in the column, which can also be called the number of different values ​​or the number of unique values. In the database, a data element column may contain multiple values, and each value may correspond to one record or multiple records. Therefore, the number of different values ​​indicates how many different values ​​there are in the data element column. For example, suppose there is a table containing a "gender" column, which may contain values ​​such as "male" and "female". If the column contains 1,000 records, but there are only two different values, "male" and "female", then the number of different values ​​of the column is 2.

[0066] In this embodiment, it should be specifically explained that the steps for calculating the data element query requirement coefficient are:

[0067] The frequency of use of each cluster data element in the query is counted within the detection time T. The more frequently used a data source is, the higher its query demand is.

[0068] Analyze the conditions in the query statement within the detection time T, and count the frequency of occurrence of each cluster data element in the query condition. The more frequent the data element, the higher its query demand;

[0069] The data element query demand coefficient is calculated based on the usage frequency of each cluster data element in the query and the frequency of occurrence in the query condition. The calculation formula is QR=UE+AE, where QR represents the data element query demand coefficient, UE represents the usage frequency of each cluster data element in the query, and AE represents the frequency of occurrence in the query condition.

[0070] In this embodiment, it should be specifically explained that the step of associating standard data elements through the JOIN operator according to the data element priority association index is to arrange the priority association index of each cluster data element from large to small, and associate the standard data elements through the JOIN operator according to the arrangement order.

[0071] In this embodiment, it should be specifically explained that the step of counting the usage frequency of each cluster data element in the query is:

[0072] Obtain database query logs or audit logs. These logs record all query operations performed by the database. Query logs usually contain detailed information about the query, including the tables and columns involved, and the execution time of the query;

[0073] Filter out query operations that contain target data elements from the query log, and determine which queries involve the target data elements by analyzing the column names or conditions in the query statements.

[0074] Count the number of times the target data element appears in the filtered query, that is, its usage frequency. Use the query times in the query log to calculate the usage frequency of the data element, or group the query operations in the query log according to the target data element for statistics.

[0075] In this embodiment, it should be specifically explained that the steps of generating a complete reconciliation statement by associating the new data element through the reconciliation operator are as follows:

[0076] According to the reconciliation operator, determine the data elements that need to be associated. These data elements may come from different data sources or different database tables and need to be associated through JOIN operations.

[0077] According to the association relationship of data elements and reconciliation operators, write SQL query statements to implement reconciliation operations. The query statements should include information such as association conditions, matching rules, and output fields;

[0078] After writing, debug and test the SQL query statement to ensure that the query results meet the expected reconciliation rules and logic. You can test by executing the query statement and checking the output results.

[0079] According to the test results, the query statement is optimized and adjusted by adding indexes, adjusting the query order, etc. to improve query performance and efficiency and obtain a complete reconciliation statement.

[0080] Reconciliation statements can be used to verify the data consistency between two or more data sources. By associating new data elements with the reconciliation operator, you can check the matching of the corresponding data in the two data sources to ensure data consistency. Reconciliation statements can be used to check the integrity of data. By associating new data elements with the reconciliation operator, you can check whether there is missing, duplicate or inconsistent data in the data source, and whether the logical relationship between the data elements is as expected. Reconciliation statements can be used to detect abnormal data.

[0081] By associating new data elements with reconciliation operators, you can check abnormal data or abnormal situations in the data source, such as out-of-range values, illegal formats, duplicate records, etc. Reconciliation statements can be used to ensure data quality. By associating new data elements with reconciliation operators, you can verify and check data to ensure data accuracy, completeness and consistency, and improve data quality. Reconciliation statements can be used to execute business rules. By associating new data elements with reconciliation operators, you can match, calculate, filter, and perform other operations on data according to business rules to meet business needs and requirements.

[0082] Through the configuration files in JSON format, the configuration information of reconciliation statements and data elements can be flexibly modified and adjusted without modifying the application code, which can reduce the cost of system maintenance and improve the configurability and maintainability of the system. The configuration files in JSON format have good readability and are easy to understand and edit. The structure and attributes in the configuration files can clearly express the definition and parameters of the reconciliation statements and data elements. Through the configuration files in JSON format, new reconciliation statements and data elements can be easily added to meet the ever-changing business needs. JSON is a universal data exchange format that supports multiple programming languages ​​and platforms. Therefore, by storing reconciliation statements and data elements in configuration files in JSON format, cross-platform configuration information sharing and exchange can be achieved.

[0083] Finally: The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the protection scope of the present invention.

[0084] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A data reconciliation system for service management and control, characterized in that: It includes data element construction module, data element association module, statement generation module and statement modification module. The modules are connected by signals. The steps of data processing between modules are as follows: A data element construction module is used to configure basic data to form standard data elements. The basic data includes data source, latitude, measurement, aggregation operator and screening condition; The data element association module is used to collect standard data metadata, calculate the data element priority association index according to the standard data metadata, associate the standard data elements through the JOIN operator according to the data element priority association index, and generate new data elements; The statement generation module is used to associate the new data element through the reconciliation operator to generate a complete reconciliation statement; The information storage module is used to store the basic information of the data element and the reconciliation statement information composed of the data element and the reconciliation operator through a configuration file in JSON format; The statement modification module is used to combine data elements and reconciliation operators in the configuration file according to new reconciliation requirements to generate new reconciliation statements.

2. A data reconciliation system for service management and control according to claim 1, characterized in that: The data element priority association index calculation steps are: Count the names, data types and other information of all data elements, cluster the data elements with the same name together to form data element clusters, and understand the type information of each data element cluster; Query the information of the data element in each cluster, and calculate the data volume coefficient based on the information of the data element; The number of indexes in the data element in each cluster is queried through the database management system, and the number of indexes in the data element in each cluster is recorded as the index coefficient; The usage of data elements is obtained by querying the database log, and the data element query demand coefficient is obtained according to the usage of data elements; The data element priority association index is calculated by the data volume coefficient, index coefficient and data element query requirement coefficient. The calculation formula is: Among them, PA represents the data element priority association index, TD represents the data volume coefficient, IE represents the index coefficient, QR represents the data element query requirement coefficient, and a1, a2, and a3 represent weight coefficients.

3. A data reconciliation system for service management and control according to claim 2, characterized in that: The steps for calculating the data volume coefficient are as follows: Count the number of data elements in each cluster and record it as the total data volume; Use SQL query statements to query the number of null values ​​in data elements; Use SQL query statements to query the number of different values ​​in each cluster data element; The data element quantity coefficient is calculated based on the total data volume of the data element, the number of null values ​​of the data element, and the number of different values ​​in each cluster data element. The calculation formula is TD = (T 总 -T 空 )×VL, where TD represents the data metadata coefficient, T 总 Expressed as the total data volume, T 空 It is represented by the number of null values ​​of the data element, and VL is represented by the number of different values ​​in each cluster data element.

4. A data reconciliation system for service management and control according to claim 2, characterized in that: The steps for calculating the data element query requirement coefficient are as follows: The frequency of use of each cluster data element in the query is counted within the detection time T. The more frequently used a data source is, the higher its query demand is. Analyze the conditions in the query statement within the detection time T, and count the frequency of occurrence of each cluster data element in the query condition. The more frequent the data element, the higher its query demand; The data element query demand coefficient is calculated based on the usage frequency of each cluster data element in the query and the frequency of occurrence in the query condition. The calculation formula is QR=UE+AE, where QR represents the data element query demand coefficient, UE represents the usage frequency of each cluster data element in the query, and AE represents the frequency of occurrence in the query condition.

5. A data reconciliation system for service management and control according to claim 4, characterized in that: The step of counting the usage frequency of each cluster data element in the query is: Obtain database query logs or audit logs, which record all query operations performed on the database. Query logs contain detailed information about the query, including the tables and columns involved, and the execution time of the query; Filter out query operations that contain target data elements from the query log, and determine which queries involve the target data elements by analyzing the column names or conditions in the query statements. Count the number of times the target data element appears in the filtered query, that is, its usage frequency. Use the query times in the query log to calculate the usage frequency of the data element, or group the query operations in the query log according to the target data element for statistics.

6. A data reconciliation system for service management and control according to claim 1, characterized in that: The steps of generating a complete reconciliation statement by associating the new data element through the reconciliation operator are as follows: According to the reconciliation operator, determine the data elements that need to be associated. These data elements may come from different data sources or different database tables and need to be associated through JOIN operations. According to the association relationship of data elements and reconciliation operators, write SQL query statements to implement reconciliation operations. The query statements should include information such as association conditions, matching rules, and output fields; After writing, debug and test the SQL query statement to ensure that the query results meet the expected reconciliation rules and logic. You can test by executing the query statement and checking the output results. According to the test results, the query statement is optimized and adjusted by adding indexes, adjusting the query order, etc., to improve query performance and efficiency, and obtain a complete reconciliation statement.

Images