A method and system for automated construction of data warehouses based on dimensional modeling

Abstract

This disclosure proposes an automated data warehouse construction method and system based on dimensional modeling. The method includes: constructing a detailed layer logical model, establishing relationships between the detailed layer logical models, and generating detailed layer physical tables; receiving predefined atomic indicators, business constraints, and derived indicators through a visual interface; receiving first configuration metadata through a visual interface and generating a first summary layer logical model based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators; responding to physicalization instructions, automatically generating a first structured query language (SQL) script for creating physical tables and processing data according to the first summary layer logical model; and automatically executing the first SQL script to generate the first summary layer physical tables. This method provides a data warehouse solution that can reduce data modeling complexity, improve development efficiency, and support standardized and automated construction.

Description

Technical Field

[0001] This disclosure relates to the field of data warehouse modeling applications, specifically to an automated data warehouse construction method and system based on dimensional modeling. Background Technology

[0002] Currently, with the rapid development and widespread application of big data technology, the effective organization and management of massive amounts of data has become a core challenge for enterprises and society. Therefore, data warehouses, as a key infrastructure for systematically storing, integrating, and managing enterprise data, are widely adopted in practice. Among data warehouse construction methodologies, dimensional modeling has become one of the mainstream modeling techniques due to its clear structure, ease of understanding, and high query performance. This technique divides data into fact tables and dimension tables, which respectively carry business metrics and analytical contexts to support complex analytical queries.

[0003] However, in the actual construction of large-scale data warehouses, especially against the backdrop of continuously growing data volume and frequent business changes, traditional dimensional modeling methods based on manual coding face many bottlenecks:

[0004] 1. Low development efficiency and insufficient automation: The construction of each layer of the data warehouse (such as the detailed data layer, the data summary layer, and the application data layer) relies heavily on developers to manually write and maintain SQL / ETL code. The process is cumbersome, involves a lot of repetitive work, and lacks full-process automated support from modeling and design to code generation.

[0005] 2. Lack of Standardization and Significant Governance Challenges: In traditional development models, data model design, layered specifications, and business indicator definitions heavily rely on the personal experience and understanding of developers, lacking platform-based and standardized constraint mechanisms. Indicator definitions are often maintained in isolation through offline documents (such as Word documents), and data table asset relationships depend on manual recording and word-of-mouth communication, making it difficult to form a unified, interconnected, and traceable digital management system. This problem directly leads to a series of governance challenges, including poor data model scalability, inconsistent indicator definitions, and high data asset maintenance costs. Further details on the current state of related technologies can be found in Chinese patent documents CN112328706A and CN113254544A.

[0006] Therefore, there is an urgent need for a data warehouse solution that can reduce the complexity of data modeling, improve development efficiency, and support standardized and automated construction. Summary of the Invention

[0007] This disclosure provides a method and system for automated construction of data warehouses based on dimensional modeling. This system provides a data warehouse solution that can reduce the complexity of data modeling, improve development efficiency, and support standardized and automated construction.

[0008] Firstly, this disclosure provides an automated data warehouse construction method based on dimensional modeling, including:

[0009] In the detailed data layer construction module, a detailed layer logical model is constructed, the relationship between the detailed layer logical models is established, and a detailed layer physical table is generated.

[0010] In the indicator management module, predefined atomic indicators, business constraints, and derived indicators are received through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints.

[0011] In the data aggregation layer construction module, the first configuration metadata is received through a visual interface, and a first aggregation layer logical model is generated based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, and the first business indicators are selected from the predefined atomic indicators and / or derived indicators;

[0012] In response to physicalization instructions, based on the first aggregation layer logical model, a first structured query language (SQL) script for creating physical tables and processing data is automatically generated;

[0013] Automatically execute the first structured query language (SQL) script to generate the first summary layer physical table.

[0014] In some embodiments, the first model relationship information is determined based on the detailed layer logical model and its associated relationships.

[0015] In some embodiments, the first model relationship information includes: a driving table, an association table, attribute fields, and a first association mapping between the driving table and the association table, wherein the first association mapping is determined based on the association relationship between the detailed layer logical models.

[0016] In some embodiments, the first association mapping includes association type, relationship cardinality, and association key.

[0017] In some embodiments, the source logic models associated with the first business metric are all selected from the logic models contained in the first model relationship information.

[0018] In some embodiments, after automatically executing the first Structured Query Language (SQL) script to generate the first summary layer physical table, the method further includes:

[0019] Based on the first summary layer logical model, the dependency relationship between each logical model used to construct the first summary layer logical model and the first summary layer logical model is automatically extracted. After deduplication of duplicate dependencies, the data lineage relationship of the first summary layer logical model is generated.

[0020] In some embodiments, after generating the data lineage of the first aggregation layer logical model, the method further includes:

[0021] In the indicator management module, predefined derivative indicators are received through a visual interface. These derivative indicators are created based on predefined atomic indicators and / or derived indicators through calculation rules. The predefined derivative indicators include the source logic model.

[0022] In the data aggregation layer construction module, the second configuration metadata is received through a visual interface, and a second aggregation layer logical model is generated based on the second configuration metadata. The second configuration metadata includes second model relationship information and second business indicators, and the second business indicators are selected from the predefined derived indicators.

[0023] In response to the physicalization instruction, based on the second aggregation layer logical model, the second structured query language SQL script is automatically generated and executed to generate the second aggregation layer physical table.

[0024] Secondly, this disclosure provides an automated data warehouse construction system based on dimensional modeling, used to run the aforementioned automated data warehouse construction method based on dimensional modeling, including:

[0025] The detailed data layer construction module is used to construct the detailed layer logical model, establish the association between the detailed layer logical models, and generate the detailed layer physical table;

[0026] The indicator management module is used to receive predefined atomic indicators, business constraints, and derived indicators through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints.

[0027] The data aggregation layer construction module is used to receive first configuration metadata through a visual interface and generate a first aggregation layer logical model based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, the first business indicators being selected from the predefined atomic indicators and / or derived indicators;

[0028] In response to physicalization instructions, based on the first aggregation layer logical model, a first structured query language (SQL) script for creating physical tables and processing data is automatically generated;

[0029] Automatically execute the first structured query language (SQL) script to generate the first summary layer physical table.

[0030] Thirdly, this disclosure provides an electronic device, including:

[0031] A memory is used to store computer programs; a processor is used to execute the programs stored in the memory to implement the steps of the method described.

[0032] Fourthly, this disclosure provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the method described.

[0033] The beneficial effects of this disclosure are that, compared with the prior art, this disclosure has the following advantages:

[0034] 1. This solution automates the entire data warehouse modeling process, significantly improving development efficiency and standardization: While ensuring high-quality data in the Detailed Data Layer (DWD), it completely changes the traditional model that relies on manual coding for the Data Summary Layer (DWS) and Application Layer (ADS). Through visual metadata configuration, the system can automatically complete the entire process from model association and indicator calculation to physical table generation and task scheduling, realizing "configuration as development," greatly improving construction efficiency and ensuring the consistency of model output.

[0035] 2. A unified indicator management system has been established to fundamentally solve the problem of inconsistent definitions: Through an independent indicator management module, atomic indicators, business constraints, and their combinations (derived / produced indicators) are centrally predefined. All upper-level models reference this unified indicator library during construction, ensuring the uniqueness and comparability of indicator definitions across models and analysis scenarios, thus eradicating the pain points of scattered definitions and difficult management in the traditional model.

[0036] 3. Automated construction and maintenance of data lineage, enhancing the governability of data assets: Based on the definition and physicalization process of the logical model, the system can automatically generate and record complete data lineage relationships at the table and field levels. This replaces the high-cost and error-prone traditional method of relying entirely on manual lineage sorting and maintenance, providing a reliable and automated foundation for data traceability, impact analysis, and asset governance.

[0037] 4. Significantly lowers the barrier to entry through visual interaction: The entire modeling process is completed through an intuitive visual interface, transforming complex SQL writing into simple drag-and-drop and configuration operations. This not only reduces the operational complexity for data developers but also enables business personnel to understand and directly participate in the definition of the data model, effectively improving the efficiency of bridging business needs and technical implementation. Attached Figure Description

[0038] The accompanying drawings, which are incorporated in and form a part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure.

[0039] Figure 1A schematic diagram of a method for automated construction of a data warehouse based on dimensional modeling, provided in this embodiment of the disclosure;

[0040] Figure 2 A schematic diagram of the table structure for creating a data summary table based on a detail layer logic model, provided for a disclosed embodiment;

[0041] Figure 3 A schematic diagram of another automated data warehouse construction method based on dimensional modeling provided in this disclosure embodiment;

[0042] Figure 4 A schematic diagram of another automated data warehouse construction method based on dimensional modeling provided in this disclosure embodiment;

[0043] Figure 5 An example of a DDL statement for an SQL script provided in this disclosure embodiment;

[0044] Figure 6 An example of an Insert Into SQL statement for an SQL script provided in this disclosure.

[0045] The accompanying drawings have illustrated specific embodiments of this disclosure, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concepts of this disclosure to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0046] The present disclosure will be further described below with reference to the accompanying drawings. The following embodiments are only used to illustrate the technical solutions of the present disclosure more clearly, and should not be used to limit the scope of protection of the present disclosure.

[0047] Terminology Explanation:

[0048] A data warehouse is a subject-oriented, integrated, stable, and historically reflective collection of data used to support management decisions and analytical applications. It typically employs a four-tier architecture, from bottom to top:

[0049] The Operational Data Store (also known as the Staging Area or Landing Zone) directly accesses and temporarily stores raw business data, maintaining the same granularity as the source system.

[0050] The Data Warehouse Detail (also known as the Base Layer or Core Layer) is responsible for cleaning, standardizing, and normalizing the source data to form high-quality, fine-grained core detail data.

[0051] The Data Warehouse Summary / Service (also known as Data Mart or SummaryLayer) performs light summarization and aggregation of detailed data according to the analysis topic, forming a reusable common summary model.

[0052] The Application Data Store (also known as the Presentation Layer or ReportLayer) is highly customized for specific business scenarios, directly driving reports, analysis, or data products.

[0053] The summary layer logical model is a novel data model concept proposed in this disclosure. Unlike the detailed layer logical models in existing technologies that only describe the finest-grained facts and dimensional relationships, this model is used to explicitly define the standardized design specifications for the data entity structure, multi-table association rules, and embedded business indicator calculations in the data summary layer. As an intermediary connecting business intent and physical implementation, it is the core metadata carrier for achieving full automation from the "configuration" stage to "code" generation, providing a solid framework support for efficient data processing and accurate application.

[0054] Application Layer Logical Model: This is a novel data model concept proposed in this disclosure. This model defines the data entity structure, data filtering rules, and scenario-oriented indicator assembly logic that directly support reports, data products, or analytical applications. It serves as the final design hub connecting analytically ready data with specific business deliverables, providing a standardized basis for scenario-based and personalized data provision based on the underlying model, and offering a reusable semantic layer framework for the agile generation and consistent management of data products and services.

[0055] Driver table: The core data model table or main analytical entity table specified when building the summary layer or application layer model. It determines the data granularity, primary key basis, and main analytical perspective of the target model, and is the starting point and backbone of the model relationships. When generating SQL, the driver table usually corresponds to the main table in the query statement.

[0056] Related tables: When building summary or application layer models, these are one or more dimension tables, auxiliary fact tables, or other related data model tables that are connected to the driving table based on business association rules. Their role is to provide descriptive attributes, filtering conditions, or supplementary measures to the driving table. Related tables are connected to the driving table through predefined association keys and association types.

[0057] The data warehouse automated construction method and system based on dimensional modeling disclosed herein are mainly aimed at core technical personnel in the data warehouse construction and operation process, including but not limited to data development engineers, data modelers (or data architects) and data operation and maintenance engineers.

[0058] like Figure 1 As shown, the first embodiment of this disclosure provides an automated data warehouse construction method based on dimensional modeling, including the following steps:

[0059] S1, In the detailed data layer construction module, construct the detailed layer logical model, establish the association relationship between the detailed layer logical models, and generate the detailed layer physical table;

[0060] S2, In the indicator management module, predefined atomic indicators, business constraints, and derived indicators are received through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints.

[0061] Preferably, the atomic indicator information includes the indicator name, indicator code, and source logic model; the business restriction information includes the business restriction name, business restriction code, and source logic model.

[0062] Preferably, the atomic indicator information specifically includes the subject domain, indicator name, indicator code, source logical model, data type, unit, indicator scope, description, and expression.

[0063] The data types include strings, numbers, dates, datetimes, booleans, and binary.

[0064] In this embodiment, the creation of the highest temperature atomic index is taken as an example, and the specific information is shown in Table 1:

[0065] Table 1 Atomic Index Information

[0066] Subject area Indicator Name Indicator coding Relational factual logic model Data types unit Indicator caliber describe expression Pumped storage data / equipment operation domain highest temperature max_temp dwd_temperature number ℃ / / max(dwd_detetion_data.val)

[0067] Preferably, the business restriction information specifically includes the subject domain, business restriction name, business restriction code, source logic model, description, and business restriction rules.

[0068] Taking the creation of a maximum business limit for monthly statistical indicators of generator motors as an example, the specific information is shown in Table 2:

[0069] Table 2 Business Restriction Information

[0070] Subject area Business-restricted name Business-restricted coding Relational logic model Business restriction rules Pumped storage data / equipment operation domain The largest monthly statistical indicator month_max_identifier_first dwd_temperature dwd_temperature.month_max_identifier_rank = 1 Pumped storage data / equipment operation domain Generator motor dg dim_device dim_device.device_name='Generator Motor'

[0071] Specifically, derived metrics are generated by combining atomic metrics and business constraints.

[0072] Preferably, the source logic model of the atomic indicator selected by the derived indicator is the same as or related to the source logic model defined by the business.

[0073] Preferably, the derived indicator information includes the subject domain, indicator name, indicator code, data type, business limitation, atomic indicator, unit, indicator scope, and description.

[0074] In this embodiment, users can customize the combination of atomic indicators and business constraints through a visual interface to generate derived indicators. Taking the creation of the maximum derived indicator for monthly statistical indicators of generator motors as an example, specific information is shown in Table 3:

[0075] Table 3 Derived Indicator Information

[0076] Subject area Indicator Name Indicator coding Data types Business restrictions Atomic index unit Indicator caliber describe Pumped storage data / equipment operation domain Maximum monthly temperature of generator motors dg_temp_month_max_1st_val numerical values The largest monthly statistical indicator is generator motors. highest temperature ℃ / /

[0077] S3, in the data aggregation layer construction module, the first configuration metadata is received through a visual interface, and a first aggregation layer logical model is generated based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, and the first business indicators are selected from the predefined atomic indicators and / or derived indicators;

[0078] In one alternative implementation, the first model relationship information is determined based on the detailed layer logical model and its associated relationships.

[0079] Preferably, the first model relationship information includes: a driving table, an association table, attribute fields, and a first association mapping between the driving table and the association table, wherein the first association mapping is determined based on the association relationship between the detailed layer logical models.

[0080] The first association mapping includes association type, relation cardinality, and association key:

[0081] Association types are used to define how data is combined, including inner join and left outer join.

[0082] The relation cardinality is used to define the business correspondence rules between tables, including one-to-one, one-to-many, and many-to-many relationships;

[0083] Association keys are used to define the technical basis for data connections between tables, including one or more corresponding fields from the driving table and the associated table.

[0084] Preferably, in the data aggregation layer construction module, the method for configuring the first model relationship information specifically includes:

[0085] S3.1, Receive the user's specification of the driving table, wherein the driving table is selected from the detail layer logic model;

[0086] S3.2, in response to the specification of the driving table, based on the pre-established association relationship between the detailed layer logical models, automatically obtain and recommend one or more candidate association tables to the user, as well as the corresponding default association type, default relationship cardinality and default association key;

[0087] S3.3 Receive the user's selection and confirmation of the candidate association table, and receive the user's confirmation or adjustment operation of the default association type, default relationship cardinality and default association key, and generate the first association mapping between the driving table and each association table.

[0088] like Figure 2 The diagram illustrates the table structure of a data summary table created based on the detail layer logic model. Taking the monthly statistical summary table of the largest data of generator motors as an example, the model relationship information and selected indicator information are shown in Tables 4 and 5, respectively:

[0089] Table 4 Model Relationship Information

[0090] driving table Association Key Related Tables Select attribute field Temperature Fact Table Test point code Device Dimension Table Measurement point code, equipment ID, operating condition code, unit code, month

[0091] Table 5 Selection Indicator Information

[0092] Serial Number Attribute Name Attribute Encoding Indicator Type Data types character length decimal point 1 Maximum monthly temperature of generator motors dg_temp_month_max_1st_val Derived Indicators decimal / / 2 Starting time of monthly statistical maximum temperature for generator motors dg_temp_month_max_1st_start_time Derived Indicators datetime / / 3 End time of monthly statistical maximum temperature for generator motors dg_temp_month_max_1st_end_time Derived Indicators datetime / / 4 Monthly statistics of maximum temperature measurement points for generator motors dg_temp_month_max_1st_detection Derived Indicators varchar / / 5 The time of maximum monthly temperature for generator motors dg_temp_month_max_1st_ts Derived Indicators varchar / /

[0093] In one optional implementation, the source logic models associated with the first business indicator are all selected from the logic models contained in the first model relationship information.

[0094] In this embodiment, the range of business metrics that the system can select is limited to the logical model contained in the first model relationship information, so as to ensure that the selected business metrics have a usable data source when automatically generating SQL in the future.

[0095] S4, in response to the physicalization instruction, automatically generates a first structured query language (SQL) script for creating physical tables and processing data based on the first aggregation layer logical model;

[0096] In one optional implementation, step S4, in response to a physicalization instruction, automatically generates a first structured query language (SQL) script for creating physical tables and processing data based on the first aggregation layer logical model, specifically including:

[0097] Based on the preset code template and the metadata definition of the first summary layer logical model, the template engine renders and automatically generates a structured query language SQL script containing physical table creation statements (DDL) and data processing statements (DML) as well as a task scheduling configuration file.

[0098] In one alternative implementation, the method further includes: receiving user configuration of the target database type via a visual interface before triggering the physicalization command. The target database type may include MySQL, Hadoop Hive, Doris, etc.

[0099] In this embodiment, taking the monthly statistical summary table of generator motors as an example, its DDL statements and InsertInto SQL statements are as follows: Figure 5 , Figure 6 As shown.

[0100] S5 automatically executes the first structured query language SQL script to generate the first summary layer physical table.

[0101] In one alternative implementation, after generating the first summary layer physical table in step S5, the method further includes a step of constructing the data lineage of the first summary layer logical model:

[0102] S6. Based on the first summary layer logical model, automatically extract the dependency relationship between each logical model used to construct the first summary layer logical model and the first summary layer logical model. After deduplication of duplicate dependencies, generate the data lineage relationship of the first summary layer logical model.

[0103] In this embodiment, the deduplication process is as follows: The system identifies the dependencies between each detailed layer logical model (covering driving tables and related tables) and the first summary layer logical model to determine if there are any duplicates. If duplicate dependencies are found, they are merged. In this way, it is ensured that in the final generated data lineage, only one dependency record is retained between the first summary layer logical model and any detailed layer logical model.

[0104] like Figure 3 As shown, in one optional implementation, after constructing the data lineage of the first aggregation layer logical model in step S6, the method further includes:

[0105] S7, in the indicator management module, the predefined derivative indicators are received through a visual interface. The derivative indicators are created based on the predefined atomic indicators and / or derived indicators through calculation rules. The predefined derivative indicators include the source logic model.

[0106] S8, in the data aggregation layer construction module, the second configuration metadata is received through a visual interface, and a second aggregation layer logical model is generated based on the second configuration metadata. The second configuration metadata includes second model relationship information and second business indicators. The second business indicators are selected from the predefined derived indicators.

[0107] S9, in response to the physicalization instruction, automatically generates and executes a second structured query language (SQL) script based on the second aggregation layer logical model to generate a second aggregation layer physical table.

[0108] In one alternative implementation, the second model relationship information is determined based on the detailed layer logical model and its association relationships and / or the first summary layer logical model and its data lineage relationships.

[0109] Preferably, the second model relationship information includes: a driving table, an associated table, attribute fields, and a second association mapping between the driving table and the associated table. The second association mapping is determined based on the pre-established association relationship between the detail layer logical models and / or the data lineage relationship of the first summary layer logical models.

[0110] The second association mapping includes association type, relation cardinality, and association key.

[0111] In practical applications, once the driving table is specified, the system automatically filters and pushes one or more candidate related tables to the user based on the pre-established relationships between the detailed layer logical models and the data lineage of the first summary layer logical model. Simultaneously, the system matches each candidate related table with a corresponding default related type, default relationship cardinality, and default related key. Subsequently, the system receives confirmation or adjustment operations from the user regarding these default related types, default relationship cardinality, and default related keys, thereby generating a second related mapping between the driving table and each related table.

[0112] In one optional implementation, the source logic models associated with the second business indicator are all selected from the logic models contained in the second model relationship information.

[0113] Preferably, the derived indicator information specifically includes the subject domain, indicator name, indicator code, source logical model, data type, unit, indicator scope, description, and expression.

[0114] In this embodiment, the TOP2 difference derived index for one month is used as an example. The specific information is shown in Table 6:

[0115] Table 6. Derivative Indicator Information

[0116] Subject area Indicator Name Indicator coding Source logic model Data types unit Indicator caliber describe expression Pumped storage data / equipment operation domain Monthly statistical difference of maximum temperature of generator motor dg_temp_month_max_1st_val_diff dws_dg_month_temp_df number / / / dws_dg_month_temp_df.dg_temp_month_max_1st_val-dws_dg_month_temp_df.dg_temp_last_month_max_1st_val

[0117] In one alternative implementation, after generating the second summary layer physical table in step S9, the method further includes a step of constructing the data lineage of the second summary layer logical model:

[0118] S10, based on the second summary layer logical model, automatically extract the dependency relationship between each logical model used to construct the second summary layer logical model and the second summary layer logical model. After deduplication of duplicate dependencies, the data lineage relationship of the second summary layer logical model is generated.

[0119] In one optional implementation, step S1, in the detailed data layer construction module, involves constructing a detailed layer logical model, establishing the relationships between the detailed layer logical models, and generating a detailed layer physical table, specifically including:

[0120] S1.1 In the detailed data layer construction module, the user's input under the selected topic domain is received through a visual interface, and a conceptual model is created, which includes business objects and business activities;

[0121] S1.2, the conceptual model is automatically converted into a detailed layer logical model, wherein business objects are converted into detailed layer dimension models, business activities are converted into detailed layer fact models, and the configuration and modification functions of the attribute fields of the detailed layer logical model are provided through the visualization interface.

[0122] S1.3, Receive the association relationships between the logical models of each detail layer through the visualization interface.

[0123] S1.4 Based on the configured detail layer logical model, data processing and cleaning are performed using ETL tools to generate a well-structured detail layer physical table.

[0124] Specifically, after optimizing and improving the logical model of the Detailed Data Layer (DWD), the definition and maintenance of entity relationships within it are carried out through a visual interface. Specifically, the definition and maintenance operations include: specifying association keys through drag-and-drop operations, and setting the relationship cardinality and association type. The relationship cardinality includes one-to-one (1:1), one-to-many (1:N), and many-to-many (N:N) relationships, and the association type includes inner join and left outer join. Finally, the system generates relationship information, forming an entity relationship graph (ER graph), providing a solid data lineage and dependency foundation for the subsequent construction of business constraints, derived indicators, and derivative indicators.

[0125] It should be noted that, in order to ensure the data quality of the core foundation of the data warehouse—the Detailed Data Layer (DWD), this embodiment adopts the following design: In view of the inherent complexity of the Operational Data Storage Layer (ODS) in terms of business rules, logical relationships and multi-source heterogeneity, based on the unified defined DWD logical model, the raw data is cleaned, transformed and integrated through ETL tools or manually written processing code, thereby achieving standardization and 100% accuracy in data granularity, format, encoding and business logic.

[0126] Based on this high-quality DWD data, this embodiment achieves fully automated modeling of the Data Summarizing Layer (DWS). Specifically, while ensuring reliable input of DWD layer data, the traditional DWS development model, which relied on manually writing ETL scripts and complex SQL, is reshaped into a "one-stop" automated generation and execution of First Structured Query Language (SQL) scripts for creating physical tables and processing data, based on visual metadata configuration. This completely changes the inefficient traditional approach of manually performing table joins, indicator calculation coding, and task scheduling in stages at the DWS layer, greatly improving the efficiency and standardization of data model construction.

[0127] like Figure 4 As shown, in one optional implementation, after constructing the data lineage of the first aggregation layer logical model in step S6, or after constructing the data lineage of the second aggregation layer logical model in step S10, the following is further included:

[0128] S11, in the application data layer construction module, the target configuration metadata is received through a visual interface, and a target application layer logical model is generated based on the target configuration metadata. The target configuration metadata includes target model relationship information and target business indicators. The target business indicators are selected from at least one of predefined atomic indicators, derived indicators and derived indicators.

[0129] S12, in response to the physicalization instruction, automatically generate and execute the target structured query language SQL script according to the target application layer logical model to generate the target application layer physical table.

[0130] In one alternative implementation, the target model relationship information is determined based on the detailed layer logical model and its association relationships and / or the summary layer logical model and its data lineage relationships.

[0131] Preferably, the target model relationship information includes: a driving table, an associated table, attribute fields, and a target association mapping between the driving table and the associated table. The target association mapping is determined based on the pre-established association relationship between the detailed layer logical models and / or the data lineage relationship of the summary layer logical models.

[0132] The target association mapping includes association type, relation cardinality, and association key.

[0133] In one optional implementation, the source logic models associated with the target business metrics are all selected from the logic models contained in the target model relationship information.

[0134] In one alternative implementation, the configuration metadata further includes basic configuration information, which is used to define the basic information of the logical model.

[0135] The basic information includes the subject domain name, model name, data warehouse layer, model code, table type, and description.

[0136] The second embodiment of this disclosure proposes an automated data warehouse construction system based on dimensional modeling, used to run the automated data warehouse construction method based on dimensional modeling, including:

[0137] The detailed data layer construction module is used to construct the detailed layer logical model, establish the association between the detailed layer logical models, and generate the detailed layer physical table;

[0138] The indicator management module is used to receive predefined atomic indicators, business constraints, and derived indicators through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints.

[0139] The data aggregation layer construction module is used to receive first configuration metadata through a visual interface and generate a first aggregation layer logical model based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, the first business indicators being selected from the predefined atomic indicators and / or derived indicators;

[0140] In response to physicalization instructions, based on the first aggregation layer logical model, a first structured query language (SQL) script for creating physical tables and processing data is automatically generated;

[0141] Automatically execute the first structured query language (SQL) script to generate the first summary layer physical table.

[0142] In one alternative implementation, the system further includes:

[0143] The first data lineage generation module is used to automatically extract the dependency relationship between each logical model used to construct the first summary layer logical model and the first summary layer logical model based on the first summary layer logical model. After deduplication of duplicate dependencies, the data lineage relationship of the first summary layer logical model is generated.

[0144] In one alternative implementation, the system further includes:

[0145] The indicator management module is also used to receive predefined derivative indicators through a visual interface. The derivative indicators are created based on predefined atomic indicators and / or derived indicators through calculation rules. The predefined derivative indicators include the source logic model.

[0146] The data aggregation layer construction module is also used to receive the second configuration metadata through a visual interface and generate a second aggregation layer logical model based on the second configuration metadata. The second configuration metadata includes second model relationship information and second business indicators, and the second business indicators are selected from the predefined derived indicators.

[0147] In response to the physicalization instruction, based on the second aggregation layer logical model, the second structured query language SQL script is automatically generated and executed to generate the second aggregation layer physical table.

[0148] In one alternative implementation, the system further includes:

[0149] The second data lineage generation module is used to automatically extract the dependency relationship between each logical model used to construct the second summary layer logical model and the second summary layer logical model based on the second summary layer logical model. After deduplication of duplicate dependencies, the data lineage relationship of the second summary layer logical model is generated.

[0150] In one alternative implementation, the system further includes:

[0151] The application data layer construction module is used to receive target configuration metadata through a visual interface and generate a target application layer logical model based on the target configuration metadata. The target configuration metadata includes target model relationship information and target business indicators. The target business indicators are selected from at least one of predefined atomic indicators, derived indicators, and derived indicators.

[0152] In response to physicalization instructions, based on the target application layer logical model, the target structured query language (SQL) script is automatically generated and executed to generate the target application layer physical table.

[0153] The third embodiment of this disclosure provides an electronic device, including: a memory for storing computer programs;

[0154] The processor is used to execute the program stored in the memory to implement the steps of the above embodiment of the data warehouse automated construction method based on dimensional modeling.

[0155] For details on the specific implementation of each step and related explanations, please refer to the aforementioned embodiment of an automated data warehouse construction method based on dimensional modeling, which will not be repeated here.

[0156] The memory of the electronic device mentioned in the fourth embodiment of this disclosure may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage device.

[0157] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0158] This disclosure also proposes a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the above-described embodiment of the dimensional modeling-based automated data warehouse construction method. For details on the specific implementation and related explanations of each step of the method, please refer to the foregoing method embodiments, which will not be repeated here.

[0159] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0160] It should be understood that the above embodiments are only used to illustrate the technical solutions of this disclosure, and not to limit them; although this disclosure has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or make equivalent substitutions for some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this disclosure.

Claims

1. A method for automatically constructing a data warehouse based on dimensional modeling, characterized in that, include: In the detailed data layer construction module, a detailed layer logical model is constructed, the relationship between the detailed layer logical models is established, and a detailed layer physical table is generated. In the indicator management module, predefined atomic indicators, business constraints, and derived indicators are received through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints. In the data aggregation layer construction module, the first configuration metadata is received through a visual interface, and a first aggregation layer logical model is generated based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, and the first business indicators are selected from the predefined atomic indicators and / or derived indicators; In response to physicalization instructions, based on the first aggregation layer logical model, a first structured query language (SQL) script for creating physical tables and processing data is automatically generated; Automatically execute the first structured query language (SQL) script to generate the first summary layer physical table.

2. The automated data warehouse construction method based on dimensional modeling according to claim 1, characterized in that, The first model relationship information is determined based on the detailed layer logical model and its associated relationships.

3. The automated data warehouse construction method based on dimensional modeling according to claim 2, characterized in that, The first model relationship information includes: a driving table, an associated table, attribute fields, and a first association mapping between the driving table and the associated table, the first association mapping being determined based on the association relationship between the detailed layer logical models.

4. The automated data warehouse construction method based on dimensional modeling according to claim 3, characterized in that, The first association mapping includes association type, relation cardinality, and association key.

5. The automated data warehouse construction method based on dimensional modeling according to claim 3, characterized in that, The source logic models associated with the first business indicator are all selected from the logic models contained in the first model relationship information.

6. The automated data warehouse construction method based on dimensional modeling according to claim 1, characterized in that, After automatically executing the first Structured Query Language (SQL) script to generate the first summary layer physical table, the process further includes: Based on the first summary layer logical model, the dependency relationship between each logical model used to construct the first summary layer logical model and the first summary layer logical model is automatically extracted. After deduplication of duplicate dependencies, the data lineage relationship of the first summary layer logical model is generated.

7. The automated data warehouse construction method based on dimensional modeling according to claim 6, characterized in that, After generating the data lineage of the first summary layer logical model, the method further includes: In the indicator management module, predefined derivative indicators are received through a visual interface. These derivative indicators are created based on predefined atomic indicators and / or derived indicators through calculation rules. The predefined derivative indicators include the source logic model. In the data aggregation layer construction module, the second configuration metadata is received through a visual interface, and a second aggregation layer logical model is generated based on the second configuration metadata. The second configuration metadata includes second model relationship information and second business indicators, and the second business indicators are selected from the predefined derived indicators. In response to the physicalization instruction, based on the second aggregation layer logical model, the second structured query language SQL script is automatically generated and executed to generate the second aggregation layer physical table.

8. A data warehouse automated construction system based on dimensional modeling, used to run the data warehouse automated construction method based on dimensional modeling as described in any one of claims 1-7, characterized in that, include: The detailed data layer construction module is used to construct the detailed layer logical model, establish the association between the detailed layer logical models, and generate the detailed layer physical table; The indicator management module is used to receive predefined atomic indicators, business constraints, and derived indicators through a visual interface. The atomic indicators and business constraints are directly associated with the source logic model, and the derived indicators are generated by combining the predefined atomic indicators and business constraints. The data aggregation layer construction module is used to receive first configuration metadata through a visual interface and generate a first aggregation layer logical model based on the first configuration metadata; the first configuration metadata includes first model relationship information and first business indicators, the first business indicators being selected from the predefined atomic indicators and / or derived indicators; In response to physicalization instructions, based on the first aggregation layer logical model, a first structured query language (SQL) script for creating physical tables and processing data is automatically generated; Automatically execute the first structured query language (SQL) script to generate the first summary layer physical table.

9. An electronic device, characterized in that, include: Memory, used to store computer programs; A processor for executing a program stored in memory to implement the steps of the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the steps of the method described in any one of claims 1-7.

Images