Method and system for configuring metadata of data warehouse by using EXCEL

Abstract

The invention discloses a method and system for configuring and managing metadata of a data warehouse through an Excel table, definition, modification and management of the metadata are achieved through the Excel table, the complexity and technical threshold of metadata management are remarkably reduced, and the method comprises the steps that 1, an Excel template containing target table configuration and an Excel template containing target-source mapping algorithm configuration are designed through a template design module; step 2, analyzing the Excel file and generating a structured metadata object by utilizing an analysis engine; 3, verifying the integrity and logic consistency of the metadata by using a verification engine; step 4, synchronizing the metadata passing the verification to a data warehouse by using a synchronization module, and generating or updating a data structure; 5, analyzing direct dependency relationships among the data models through the imported metadata by utilizing a blood relationship analysis module, and displaying the dependency relationships through a tree diagram; and step 6, importing the downloaded data into a local database by utilizing a calculation engine, and calculating the data to generate data of the target table.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and in particular to a method and system for configuring and managing data warehouse metadata through Excel tables. Background Art

[0002] Data warehouses are core components of enterprise data management, used to store, integrate, and analyze large amounts of data. Metadata is key information that describes the data structure, relationships, and rules within a data warehouse. It typically includes table structures, field definitions, data sources, and transformation rules. Traditional metadata management methods often rely on specialized ETL tools or programming scripts.

[0003] Regarding ETL tool integration with metadata management, the following tools are used: Informatica PowerCenter, Talend, and Kettle. The implementation is as follows: (1) Configure the ETL process through a graphical interface to automatically generate metadata. (2) Metadata is stored in a dedicated metadata database, supporting data lineage tracking and version management. Its limitations are as follows: (1) High learning cost: Professional technicians are required to operate, and non-technical personnel are difficult to participate. (2) Poor flexibility: Modifying metadata requires re-adjusting tool configuration, which is inefficient. (3) Strong platform dependence: Metadata management is bound to specific tools and difficult to use across platforms.

[0004] Regarding low-code / no-code platforms, example tools include Alteryx, Knime, and Microsoft PowerBI. Their implementation methods are as follows: (1) Configure data processes through a visual interface and automatically generate metadata. (2) Provide data lineage tracking, version management, and collaboration capabilities. Their limitations are as follows: (1) Expensive: Platform licensing fees are high, making them unsuitable for small and medium-sized enterprises. (2) Complex functions: Although the technical threshold has been lowered, a certain learning cost is still required. (3) Closed: Metadata management is tied to the platform and difficult to integrate with other tools.

[0005] Regarding manual scripts and configuration files, the following example tools are available: SQL scripts and JSON / XML configuration files. The implementation is as follows: (1) Define metadata by manually writing SQL scripts or configuration files. (2) Use version control tools (such as Git) to manage metadata changes. The limitations are as follows: (1) Inefficiency: Manual scripting is time-consuming and error-prone. (2) Difficulty in collaboration: Non-technical personnel cannot directly participate in metadata management. (3) Lack of standardization: Metadata definitions are scattered, making it difficult to ensure consistency.

[0006] In summary, the problems existing in the related existing technologies are as follows:

[0007] Regarding the high technical barrier to entry: Traditional metadata management relies on specialized ETL tools (such as Informatica and Talend) or manual scripting, requiring extensive technical involvement. This makes it difficult for non-technical personnel to directly operate, hindering collaboration between business and IT departments. Regarding the impact of the high technical barrier to entry: It increases labor costs for enterprises and reduces metadata management efficiency.

[0008] Problem Description of Lack of Flexibility: Modifying metadata requires reconfiguring tool configurations or scripts, a cumbersome and inefficient process. Existing tools are often closed and difficult to quickly adapt to changing business needs. Impact of Lack of Flexibility: Metadata update cycles are long, making it difficult to support rapidly iterating business scenarios.

[0009] Problem description regarding collaboration difficulties: Non-technical personnel are unable to directly participate in metadata management, resulting in a gap between business requirements and IT implementation. Existing tools lack user-friendly interfaces, making it difficult to support multi-departmental collaboration. Impact of collaboration difficulties: Inaccurate communication of business requirements leads to mismatch between metadata configuration and actual needs.

[0010] Regarding the high cost issue: Professional ETL tools and low-code platforms are expensive and unsuitable for small and medium-sized enterprises. Manual scripting, while cost-effective, is inefficient and error-prone. Regarding the impact of high costs: It increases operational costs for enterprises and limits the adoption of metadata management.

[0011] Problem Description of Incomplete Version Management: Existing tools have limited version management capabilities, making it difficult to compare and roll back metadata versions. Manual version management is error-prone and lacks historical records. Impact of Incomplete Version Management: Metadata changes are difficult to track, which can easily lead to data inconsistencies.

[0012] There is a need to solve the above problems. Summary of the Invention

[0013] In view of the defects or deficiencies in the prior art, the present invention provides a method and system for configuring and managing data warehouse metadata through Excel tables.

[0014] The technical solutions of the present invention are as follows:

[0015] A method for configuring and managing data warehouse metadata through an Excel spreadsheet, characterized by comprising the following steps:

[0016] Step 1: Use the template design module to design an Excel template that includes the target table configuration and the target-source mapping algorithm configuration.

[0017] Step 2: parse the Excel file using the parsing engine and generate structured metadata objects;

[0018] Step 3: Use the verification engine to verify the integrity and logical consistency of the metadata;

[0019] Step 4: Use the synchronization module to synchronize the verified metadata to the data warehouse and generate or update the data structure;

[0020] Step 5: Use the lineage analysis module to analyze the direct dependencies between data models through the imported metadata and display these dependencies in a tree diagram;

[0021] Step 6: Use the calculation engine to import the downloaded data into the local database, calculate the data, and generate the data of the target table.

[0022] The target table configuration in step 1 includes the target table name, source table name, time granularity enumeration value, and resource table associated fields.

[0023] The target-source mapping algorithm configuration in step 1 includes the target table fields, field types, spatial granularity algorithms, and temporal granularity algorithms.

[0024] Generating the target table data in step 6 includes the following steps:

[0025] Step 6.1: Create and store data in the original table;

[0026] Step 6.2: Create and store data in the intermediate table;

[0027] Step 6.3, create an intermediate temporary table and perform data association processing;

[0028] Step 6.4, insert the main table data into the intermediate temporary table;

[0029] Step 6.5: Associate the data in the intermediate temporary table with the secondary table and insert the data into the new temporary table.

[0030] Step 6.6: Remove duplicate data from the new temporary table and keep only the unique data.

[0031] Step 6.7: Generate the target table data using the new temporary table data.

[0032] Including step 7, using the version management module to record each metadata change and support rollback.

[0033] A system for configuring and managing data warehouse metadata through Excel spreadsheets, characterized in that it includes a combined architecture formed by executing the above-mentioned method for configuring and managing data warehouse metadata through Excel spreadsheets, the combined architecture including a Web interface management module, the Web interface management module being respectively connected to a product library, a configuration center service module, a task distribution module, a metadata service module, a model service and a storage model management module, the task distribution module being respectively connected to a platform library, a summary trigger service module and a task execution module, the task execution module being respectively connected to a distributed file system and a distributed message system, the platform library being respectively connected to a metadata service module and a model service module, the distributed message system being respectively connected to a summary trigger service module and an warehousing service module, and the warehousing service module being respectively connected to a data storage library, a distributed file system and a model service module.

[0034] The technical effects of the present invention are as follows: The present invention provides a method and system for configuring and managing data warehouse metadata through Excel tables, which realizes the definition, modification and management of metadata through Excel tables, significantly reduces the complexity and technical threshold of metadata management, and is a simple, flexible and easy-to-use metadata configuration method.

[0035] The present invention has the following characteristics:

[0036] In terms of lowering the technical threshold, metadata configuration is implemented through Excel spreadsheets, allowing non-technical personnel to directly participate in metadata management, thereby reducing dependence on technical personnel and lowering the company's labor costs.

[0037] In terms of improving flexibility, by providing standardized Excel templates, it supports rapid modification and updating of metadata, thereby shortening the metadata update cycle and adapting to rapidly changing business needs.

[0038] In terms of supporting multi-person collaboration, seamless collaboration among multiple people is achieved through Excel templates, providing a user-friendly interface and error feedback mechanism, thereby improving the accuracy of business demand delivery and ensuring that metadata configuration is consistent with actual needs.

[0039] In terms of cost reduction, metadata management based on Excel eliminates the need to purchase additional professional tools, thereby reducing the company's operating costs and is particularly suitable for small and medium-sized enterprises.

[0040] In terms of improving version management, metadata version control and rollback functions are provided to record the history of each change, thereby ensuring that metadata changes are traceable and avoiding data inconsistency issues. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1The present invention is a schematic diagram of a system structure for configuring data warehouse metadata based on Excel. Figure 1 It includes a Web interface management module, which is respectively connected to the product library ams, the configuration center service module config-service, the task distribution module task-dispatch, the metadata service module metadata-service, the model service module model-service and the storage model management module metads-sto-mgr. The task distribution module is respectively connected to the platform library Mysql, the summary trigger service module sum-trigger and the task execution module task-worker. The task execution module is respectively connected to the distributed file system HDFS / MINIO and the distributed messaging system KAFKA. The platform library is respectively connected to the metadata service module and the model service module. The distributed messaging system KAFKA is respectively connected to the summary trigger service module and the warehousing service module metads-data2db. The warehousing service module is respectively connected to the data repository, the distributed file system HDFS / MINIO and the model service module.

[0042] Figure 2 The present invention is a schematic diagram of an Excel general metadata template structure involved in implementing a method for configuring data warehouse metadata based on Excel. Figure 2 It includes destTable: the name of the target table; level: data level, which is divided into cache, ods, dw, and dm; timeType: granularity; objType: object alias, which takes the key part of the table name; bucketColumn: the field based on which data is cleaned. If not filled in, the default value is obj_id; incrUpdateFlag: whether to enable increment, the default value is false; filterCondition: filtering requirements.

[0043] Figure 3 This is a schematic diagram of target table configuration in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 3 It includes 7 columns represented by AG, and the column names are destTable, level, timeType, objType, bucketColumn, incrUpdateFlag and filterCondition.

[0044] Figure 4 This is a schematic diagram of target table configuration in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention.

[0045] Figure 5This is a schematic diagram of source table configuration in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel in the present invention. Figure 5 The column names from left to right are: sourceTable - source table name; level - data warehouse level, divided into cache, ods, dw, dm; timeType - granularity, enumeration value is the same as above; groupName - group table name, the source table is filled with data after executing SQL through filter condition (filterCondition) and group field (groupBy); condition - the association condition between the auxiliary table and the main table; isMain - whether it is the main table, single main table and multiple main tables are supported; groupBy - source table group field, multiple fields are separated by commas; filterCondition - filter condition, the source table data is filtered according to this condition; interfaceType - DB or FILE; sourceTableAlias - source table alias, which can be filled in if and only if interfaceType is DB. If not filled in, the default alias is the source table name.

[0046] Figure 6 This is a schematic diagram of source table configuration in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel in the present invention.

[0047] Figure 7 The present invention is a schematic diagram of a target-source mapping algorithm configuration method for configuring data warehouse metadata based on Excel. Figure 7 The column names from left to right are destField - target table field; sourceArith - source table algorithm; type - the attribute of the field, dest (belonging to the target table, must exist in the model), grouptemp (field used to associate with the main table), bigtemp (intermediate transition field, not reflected in the target table); groupName - which group table the field belongs to. When it is empty, it must be ensured that the field is in bigtemp or a constant.

[0048] Figure 8 It is a schematic diagram of version page configuration involved in implementing a method of configuring data warehouse metadata based on Excel in the present invention. Figure 8 The columns in the first row from left to right are metadata name and metadata type. The columns in the fourth row from left to right are version, developer, email, release date, and version description.

[0049] Figure 9 This is a schematic diagram of configuring an aggregated metadata table in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel of the present invention. Figure 9 The column names in the table include: sumName - aggregation name; destTable - target table name; sourceTable - source table name, supporting multiple source tables (separated by commas), generally referring to performance tables; sumType - aggregation type (e.g., unify indicates resource and performance association, space indicates spatial granularity aggregation, and time indicates temporal granularity aggregation); sourceTimeType - source table time granularity (e.g., 5 minutes, hours, days); destTimeType - target table time granularity (e.g., 5 minutes, hours, days); sourceObjType - source table resource type; dimTable - resource table name; DestObjTypes - target table resource type; sumCondition - source table filter condition (e.g., amount>0); isLeftJoin - resource and performance association method (1 indicates left join, full join by default); dimRelaField - resource table association field (default ca_obj_id); sourceRelaField - performance table association field (default obj_id).

[0050] Figure 10 This is a schematic diagram of configuring the aggregate metadata table in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel of the present invention.

[0051] Figure 11 The present invention is a schematic diagram of a target-source mapping algorithm configuration method for configuring data warehouse metadata based on Excel. Figure 11 The following column names are included: destColumn - the name of the target table field; columnType - the field type (such as VARCHAR, INT); unifyArith - the source of the field for generating the temporary table (such as the field name); spaceArith - the algorithm of spatial granularity (such as sum(amount)); timeArith - the algorithm of time granularity (such as avg(amount)); hourArith - the algorithm of hour granularity (optional); dayArith - the algorithm of day granularity (optional).

[0052] Figure 12 This is another version page configuration diagram involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 12This includes record metadata such as name, type (such as "aggregate"), version number (consistent with the file name), developer, email address, release date, and release notes. It also includes built-in data validation (such as a field type drop-down menu and enumerated value selection for time granularity); supports dynamic default value generation by formulas (such as automatically filling in timestamps and selecting built-in system variables); and provides a template generation wizard to guide users in quickly generating initial templates.

[0053] Figure 13 The present invention is a schematic diagram of a computing engine structure involved in implementing a method for configuring data warehouse metadata based on Excel. Figure 13 It includes an FTP server (File Transfer Protocol Server), a data sharing platform acquisition adapter (acquisition module) and a distributed file system MINIO, a general metadata / aggregate metadata module (aggregate module, downloading files from MINIO and uploading files to MINIO), a computing engine (work) = dc-converge and a clickhouse table engine, which are connected in sequence. The general metadata / aggregate metadata module is connected to a data storage module (including a dw data warehouse, data warehouse; a dm data mart, data mart; an ods operational data store, operational data store).

[0054] Figure 14 It is a schematic diagram of the target table structure involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 14 The metadata name included is pm_s_o_cm_smf_project, and the source tables used are o_cm_gtob_co_go_nssi_ne (network element and sub-slice relationship table) and o_cm_gtob_project (project table). The project_name_ori field in the project table is associated with the project_name_ori field in the network element and sub-slice relationship table and stored in the target table.

[0055] Figure 15 It is a schematic diagram of an execution process page involved in implementing a method for configuring data warehouse metadata based on Excel of the present invention. Figure 15 It includes test items, and the following columns are included under the test items: process name; current metadata; current version; test process instance; time granularity; time. It also includes a run button and a clear cache button; a task button; a data query button; view the executed SQL; a refresh button; the following items are refreshed under the task item: time granularity; data time; task status; task ID; execution container; execution start time; execution end time; operation (download execution log). Figure 15 The page shown can analyze the execution log, including: (1) creating and storing data in the original tables o_cm_gtob_co_go_nssi_ne and o_cm_gtob_project. (2) creating and storing data in the intermediate tables c_gtob and c_project. (3) creating the intermediate temporary table bigtemp and performing data association processing. (4) inserting the main table data into the intermediate temporary table. (5) associating the data in the intermediate temporary table with the secondary table. Inserting the data into the new temporary table bigtemp0. (6) using obj_id to group, eliminate duplicate data, and retain only unique data. (7) generating data for the target table. DETAILED DESCRIPTION

[0056] Below is the attached figure ( Figures 1-15 ) and Examples illustrate the present invention.

[0057] Figure 1 The present invention is a schematic diagram of a system structure for configuring data warehouse metadata based on Excel. Figure 2 The present invention is a schematic diagram of an Excel general metadata template structure involved in implementing a method for configuring data warehouse metadata based on Excel. Figure 3 This is a schematic diagram of target table configuration in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 4 This is a schematic diagram of target table configuration in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 5 This is a schematic diagram of source table configuration in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel in the present invention. Figure 6 This is a schematic diagram of source table configuration in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel in the present invention. Figure 7 The present invention is a schematic diagram of a target-source mapping algorithm configuration method for configuring data warehouse metadata based on Excel. Figure 8 It is a schematic diagram of version page configuration involved in implementing a method of configuring data warehouse metadata based on Excel in the present invention. Figure 9 This is a schematic diagram of configuring an aggregated metadata table in Example 1 involved in implementing a method for configuring data warehouse metadata based on Excel of the present invention. Figure 10 This is a schematic diagram of configuring the aggregate metadata table in Example 2 involved in implementing a method for configuring data warehouse metadata based on Excel of the present invention. Figure 11 The present invention is a schematic diagram of a target-source mapping algorithm configuration method for configuring data warehouse metadata based on Excel. Figure 12This is another version page configuration diagram involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 13 The present invention is a schematic diagram of a computing engine structure involved in implementing a method for configuring data warehouse metadata based on Excel. Figure 14 It is a schematic diagram of the target table structure involved in implementing a method for configuring data warehouse metadata based on Excel according to the present invention. Figure 15 This is a schematic diagram of the execution process page involved in implementing the method of configuring data warehouse metadata based on Excel in the present invention. Figures 1 to 15 As shown, a method for configuring and managing data warehouse metadata through Excel tables includes the following steps: Step 1, using a template design module to design an Excel template including a target table configuration and an Excel template for a target-source mapping algorithm configuration; Step 2, using a parsing engine to parse the Excel file and generate a structured metadata object; Step 3, using a verification engine to verify the integrity and logical consistency of the metadata; Step 4, using a synchronization module to synchronize the verified metadata to the data warehouse and generate or update the data structure; Step 5, using a lineage analysis module to analyze the direct dependencies between data models through the imported metadata, and display these dependencies through a tree diagram; Step 6, using a calculation engine to import the downloaded data into a local database, calculate the data, and generate data for the target table.

[0058] The target table configuration in step 1 includes the target table name, source table name, time granularity enumeration value, and resource table associated fields. The target-source mapping algorithm configuration in step 1 includes the target table fields, field type, spatial granularity algorithm, and time granularity algorithm.

[0059] Generating the target table data in step 6 includes the following steps: step 6.1, creating and storing data in the original table; step 6.2, creating and storing data in the intermediate table; step 6.3, creating an intermediate temporary table and performing data association processing; step 6.4, inserting the main table data into the intermediate temporary table; step 6.5, associating the data in the intermediate temporary table with the secondary table and inserting the data into the new temporary table; step 6.6, removing duplicate data in the new temporary table, and retaining only the unique data for the duplicate data; step 6.7, generating the target table data using the new temporary table data.

[0060] Including step 7, using the version management module to record each metadata change and support rollback.

[0061] A system for configuring and managing data warehouse metadata through Excel spreadsheets includes a combined architecture formed by executing the above-mentioned method for configuring and managing data warehouse metadata through Excel spreadsheets, the combined architecture including a Web interface management module, the Web interface management module being respectively connected to a product library, a configuration center service module, a task distribution module, a metadata service module, a model service, and a storage model management module, the task distribution module being respectively connected to a platform library, a summary trigger service module, and a task execution module, the task execution module being respectively connected to a distributed file system and a distributed message system, the platform library being respectively connected to a metadata service module and a model service module, the distributed message system being respectively connected to a summary trigger service module and an warehousing service module, and the warehousing service module being respectively connected to a data storage library, a distributed file system, and a model service module.

[0062] The present invention provides a method and system for configuring data warehouse metadata based on Excel, which realizes the definition, modification and management of metadata through Excel tables, significantly reducing the complexity and technical threshold of metadata management.

[0063] The present invention is a simple, flexible and easy-to-use metadata configuration method to lower technical barriers and improve management efficiency.

[0064] refer to Figure 1 , the system architecture of the present invention includes the following core modules:

[0065]

[0066]

[0067]

[0068] Regarding Excel metadata template design, metadata templates include two categories: general metadata and aggregate metadata.

[0069] Regarding general metadata, general metadata is closer to native SQL statements. By configuring multi-table associations, data is written to a new model. It supports various SQL operations such as left join, inner join, grouping, etc. General metadata template reference Figure 2 .

[0070] refer to Figure 2 、 Figure 3 and Figure 4,Regarding the target table configuration in the general metadata, it includes: destTable - the target table name; level - the data level, which is divided into cache, ods, dw, and dm; timeType - granularity, the enumeration values are as follows: -99 means no time granularity, -9 means multiple time granularities, 0 means 5 minutes, 1 means 15 minutes, 2 means 30 minutes, 3 means hours, 4 means days, 5 means weeks, 6 means months, 7 means quarters, 8 means half a year, and 9 means years.

[0071] objType: object alias, which is the key part of the table name; bucketColumn: the field on which data cleaning is based. If not filled in, the default value is obj_id; incrUpdateFlag: whether to enable incremental update. The default value is false. For simple needs to filter the data in the target table, you can configure filterCondition according to the last column.

[0072] refer to Figure 5 , regarding the source table configuration in the general metadata, including: sourceTable - the source table name; level - the data warehouse level, divided into cache, ods, dw, dm; timeType - granularity, the enumeration value is the same as above; groupName - the group table name, the source table is populated with data after executing sql through the filter condition (filterCondition) and the group field (groupBy); condition - the association condition between the auxiliary table and the main table; isMain - whether it is the main table, supporting single main table and multiple main tables; groupBy - the source table group field, multiple fields are separated by commas; filterCondition - the filter condition, the source table data is filtered according to this condition; interfaceType - DB or FILE; sourceTableAlias - alias, can be filled in when and only when the interfaceType is DB. If it is not filled in, the default alias is the source table name. When the source table name and the target table name are the same, the alias must be filled in, refer to Figure 6 If the same DB source table is imported multiple times, the alias must be filled in, and the fields imported into each alias table can be different.

[0073] refer to Figure 7Regarding the target-source mapping algorithm configuration in the general metadata, it includes destField - target table field; sourceArith - source table algorithm; type - the attribute of the field, dest (belonging to the target table, must exist in the model), grouptemp (the field used to associate with the main table), bigtemp (the intermediate transition field, not reflected in the target table); groupName - which group table the field belongs to. When it is empty, it must be ensured that the field is in bigtemp or a constant.

[0074] refer to Figure 8 Regarding the version page configuration in the general metadata, including metadata name: keep it consistent with the table name; metadata type: write "general"; version: there must be a record with the same version number as the file name.

[0075] Regarding the aggregation of metadata, the purpose of aggregating metadata is to facilitate and quickly aggregate data.

[0076] refer to Figure 9 The target table configuration in the aggregation metadata includes: destTable - target table name; sourceTable - source table name, supports multiple source tables (separated by commas), generally referring to performance tables; sumType - summary type (e.g., unify indicates resource and performance association, space indicates spatial granularity aggregation, and time indicates temporal granularity aggregation); sourceTimeType - source table time granularity (e.g., 5 minutes, hours, days); destTimeType - target table time granularity (e.g., 5 minutes, hours, days); sourceObjType - source table resource type; dimTable - resource table name; DestObjTypes - target table resource type; sumCondition - source table filter condition (e.g., amount>0); isLeftJoin - resource and performance association method (1 indicates left join, full join by default); dimRelaField - resource table association field (default ca_obj_id); sourceRelaField - performance table association field (default obj_id).

[0077] For special aggregation configurations, refer to Figure 10 , the configured special summary fields will be added to the basic summary for summary.

[0078] refer to Figure 11Regarding the target-source mapping algorithm configuration in the aggregate metadata, it includes: destColumn - the target table field name; columnType - the field type (such as VARCHAR, INT); unifyArith - the field source for generating the temporary table (such as the field name); spaceArith - the algorithm for spatial granularity (such as sum(amount)); timeArith - the algorithm for time granularity (such as avg(amount)); hourArith - the algorithm for hour granularity (optional); dayArith - the algorithm for day granularity (optional).

[0079] refer to Figure 12 , regarding the version page configuration in the aggregated metadata, including: recording the metadata name, type (such as "aggregation"), and version number (consistent with the file name).

[0080] Template functions include: built-in data validation (such as a field type drop-down menu and time granularity enumeration value selection); support for dynamic generation of default values using formulas (such as automatically filling in timestamps and selecting system built-in variables); and a template generation wizard to guide users to quickly generate initial templates.

[0081] Regarding metadata parsing and verification, it includes a parsing module and a verification module. The parsing module is used to read the content of Excel files, parse the target table configuration, the target-source mapping algorithm configuration and the version page, and convert the parsing results into structured metadata objects (such as JSON or XML). Through metadata parsing, model and field level traceability can be performed. The verification module is used for integrity checking: ensuring that required fields (such as table name, field type) are not empty. Consistency check: check the legitimacy of time granularity enumeration values and the validity of resource table associated fields. Logical check: verify the legitimacy of algorithm syntax (such as SQL expression verification). Data correctness verification: verify whether the data model and hierarchy used are correct, etc.

[0082] Metadata import and version management include synchronization and version management modules. The synchronization module synchronizes metadata into the system, supporting Excel import and visual interface operations. It supports incremental synchronization, updating only modified parts to reduce system load, and provides a synchronization log to record detailed information about each synchronization. The version management module includes important information such as the timestamp, operator, and change summary for each new upgrade. By viewing historical versions, you can compare and roll back supported versions (if an upgrade fails, you can roll back to a previous version).

[0083] refer to Figure 13Regarding the computing engine, the system collects data into minio through the acquisition module, notifies the work through the task distribution service to call the computing engine to process the data and generate a new target file, and then uploads the target file to the minio server and stores it in the business database at the same time.

[0084] Regarding user interaction and collaboration, the system includes a template generation wizard and an error feedback mechanism. The template generation wizard guides users to select preset templates (such as the general metadata template and the aggregated metadata template), provides template examples and instructions, and helps users get started quickly. The error feedback mechanism highlights error cells in Excel and provides correction suggestions when parsing fails. A detailed error log is also provided to facilitate user troubleshooting.

[0085] refer to Figure 14 In the example scenario, the relationship between SMF (a type of network element in the 5G core network) and the project is analyzed through the network element and service sub-slice relationship table and the project table. The metadata design is as follows:

[0086] The metadata name is: pm_s_o_cm_smf_project.

[0087] The source tables used are: o_cm_gtob_co_go_nssi_ne (network element and sub-slice relationship table) and o_cm_gtob_project (project table).

[0088] The project_name_ori field in the "project table" is associated with the project_name_ori field in the "network element and sub-slice relationship table" and stored in the target table.

[0089] refer to Figure 15 The execution process in the example scenario in the following example is analyzed and the execution log is as follows:

[0090] (1) Create and store the original data in the tables o_cm_gtob_co_go_nssi_ne and o_cm_gtob_project.

[0091] (2) Create and store data in the intermediate tables c_gtob and c_project.

[0092] (3) Create an intermediate temporary table bigtemp to perform data association processing.

[0093] (4) Insert the main table data into the intermediate temporary table.

[0094] (5) Associate the data in the intermediate temporary table with the secondary table and insert the data into the new temporary table bigtemp0.

[0095] (6) Use obj_id to group, remove duplicate data, and keep only one unique data.

[0096] (7) Generate data for the target table.

[0097] A method for configuring data warehouse metadata based on Excel, comprising:

[0098] a. Design an Excel template that includes the target table configuration and the target-source mapping algorithm configuration;

[0099] b. Parse the Excel file and generate structured metadata objects;

[0100] c. Verify the integrity and logical consistency of metadata;

[0101] d. Synchronize the verified metadata to the data warehouse and generate or update the data structure.

[0102] e. Lineage analysis capability: Analyze the direct dependencies of models using imported metadata and display these dependencies in a tree diagram.

[0103] f. The computing engine downloads data from Mino and imports it into the local Climaxhouse database to perform calculations on the data.

[0104] The target table configuration includes the target table name, source table name, time granularity enumeration value, and resource table associated fields.

[0105] The target-source mapping algorithm configuration includes target table fields, field types, spatial granularity algorithms, and temporal granularity algorithms.

[0106] It further includes version management functions, records each metadata change and supports rollback.

[0107] A system for configuring data warehouse metadata based on Excel, comprising:

[0108] a. Template design module, used to generate and edit standardized Excel templates;

[0109] b. Parsing engine, converting Excel files into structured metadata;

[0110] c. Verification engine, checking metadata integrity, consistency and logical legitimacy;

[0111] d. Synchronization module, updates metadata to the data warehouse and updates the data structure.

[0112] By combining the flexibility of Excel and the requirements of data warehouse metadata management, the present invention proposes a method and system for configuring data warehouse metadata based on Excel, which significantly reduces the technical threshold and cost, improves flexibility, collaboration efficiency and scalability, and improves version management and user-friendliness. It is suitable for various data warehouse scenarios.

[0113] Any content not described in detail in this specification is prior art known to those skilled in the art. It should be noted that the above description is intended to help those skilled in the art understand the present invention, but does not limit the scope of protection of the present invention. Any equivalent substitution, modification, improvement, and / or simplification of the above description that does not depart from the essence of the present invention shall fall within the scope of protection of the present invention.

Claims

1. A method for configuring and managing data warehouse metadata through Excel spreadsheets, characterized in that: The following steps are involved: Step 1: Use the template design module to design an Excel template that includes the target table configuration and the target-source mapping algorithm configuration. Step 2: parse the Excel file using the parsing engine and generate structured metadata objects; Step 3: Use the verification engine to verify the integrity and logical consistency of the metadata; Step 4: Use the synchronization module to synchronize the verified metadata to the data warehouse and generate or update the data structure; Step 5: Use the lineage analysis module to analyze the direct dependencies between data models through the imported metadata and display these dependencies in a tree diagram; Step 6: Use the calculation engine to import the downloaded data into the local database, calculate the data, and generate the data of the target table.

2. The method for configuring and managing data warehouse metadata through Excel tables according to claim 1, characterized in that: The target table configuration in step 1 includes the target table name, source table name, time granularity enumeration value, and resource table associated fields.

3. The method for configuring and managing data warehouse metadata through Excel tables according to claim 1, characterized in that: The target-source mapping algorithm configuration in step 1 includes the target table fields, field types, spatial granularity algorithms, and temporal granularity algorithms.

4. The method for configuring and managing data warehouse metadata through Excel tables according to claim 1, characterized in that: Generating the target table data in step 6 includes the following steps: Step 6.1: Create and store data in the original table; Step 6.2: Create and store data in the intermediate table; Step 6.3, create an intermediate temporary table and perform data association processing; Step 6.4, insert the main table data into the intermediate temporary table; Step 6.5: Associate the data in the intermediate temporary table with the secondary table and insert the data into the new temporary table. Step 6.6: Remove duplicate data from the new temporary table and keep only the unique data. Step 6.7: Generate the target table data using the new temporary table data.

5. The method for configuring and managing data warehouse metadata through Excel tables according to claim 1, characterized in that: Including step 7, using the version management module to record each metadata change and support rollback.

6. A system for configuring and managing data warehouse metadata through Excel spreadsheets, characterized in that: It includes a combined architecture formed by executing the method of configuring and managing data warehouse metadata through Excel spreadsheets as described in one of claims 1 to 5 above, and the combined architecture includes a Web interface management module, the Web interface management module is respectively connected to the product library, the configuration center service module, the task distribution module, the metadata service module, the model service and the storage model management module, the task distribution module is respectively connected to the platform library, the summary trigger service module and the task execution module, the task execution module is respectively connected to the distributed file system and the distributed message system, the platform library is respectively connected to the metadata service module and the model service module, the distributed message system is respectively connected to the summary trigger service module and the warehousing service module, and the warehousing service module is respectively connected to the data storage library, the distributed file system and the model service module.

Images