Dynamic dimension modeling method and system for data warehouse and computer equipment

Abstract

The invention discloses a dynamic dimension modeling method and system for a data warehouse and computer equipment. The method comprises the following steps: receiving a data model generation request or a data model modification request created by a user on a visual interface; selecting a corresponding data model according to the data model generation request or the data model modification request; selecting a target table from a data source library according to the selected data model; configuring field mapping according to the target table, and storing the field mapping of the target table and the configuration; and according to the configured field mapping, dynamically establishing a table corresponding to the selected data model in a local library, configuring a synchronization rule, and creating a timing synchronization task. According to the dynamic dimension modeling method and system for the data warehouse and the computer equipment provided by the invention, the adjustment time of the data model can be shortened, the maintenance cost is reduced, and the flexibility of the data warehouse is remarkably improved.

Description

Technical Field

[0001] The present application relates to the technical field of data warehouses, and in particular to a dynamic dimensional modeling method, system and computer equipment for data warehouses. Background Art

[0002] A data warehouse is a subject-oriented, integrated, stable database that reflects historical changes and is designed to support management decisions. Through a data warehouse, an enterprise can integrate data from different business systems and provide data from a global and historical perspective for decision makers to conduct multi-dimensional analysis. Multi-dimensional analysis can include multiple fields such as sales, finance, and marketing, support complex queries and generate reports, and help managers make more informed business decisions.

[0003] Typically, data warehouses are designed using dimensional modeling, which includes fact tables and dimension tables. Fact tables contain measurement data for business transactions, while dimension tables store attributes that describe these transactions, such as time, location, and product. In order to support flexible data analysis, data warehouses use multidimensional models such as star models or snowflake models. However, the construction process of data warehouses is cumbersome and complex, especially when dealing with different dimensions. How to define the hierarchical relationship of dimensions, deal with time changes, deal with geographic regions and organizational structures, etc., are all major challenges.

[0004] The traditional way of building a data warehouse has the following major defects:

[0005] 1. Long construction period: The design and implementation of data warehouses involve a lot of analysis, modeling, development and testing, which often takes months or even years to complete. In the process of building data warehouses, enterprises often encounter changes in business needs, which leads to the need to adjust the modeling plan, thus further extending the construction period.

[0006] 2. Limitations of dimensional design: When building a data warehouse, the dimensional model usually needs to clearly list possible analysis dimensions, such as time, organizational structure, products, etc., at the beginning of the design. However, the business conditions and analysis needs of an enterprise are changing dynamically, and managers' analysis angles often change over time. Once a traditional data warehouse is finalized, adding new dimensions or modifying existing dimensions often requires redesigning the database model, modifying the data table structure, and modifying the corresponding query code. This is not only time-consuming and labor-intensive, but also increases the complexity and maintenance cost of the system.

[0007] 3. Poor flexibility in dimension expansion: When the business department proposes new dimension analysis requirements, such as adding a new market area or product line as an analysis dimension in the existing sales analysis, the traditional method requires technical personnel to modify the database model and reload the data. The lag of this method makes it difficult for enterprises to obtain accurate analysis results in a timely manner, limiting the flexibility and adaptability of the data warehouse. Summary of the invention

[0008] To this end, the present application provides a dynamic dimensional modeling method, system and computer device for a data warehouse to solve the problems of long data warehouse construction cycle, high maintenance cost and poor dimensional expansion flexibility existing in the prior art.

[0009] In order to achieve the above objectives, this application provides the following technical solutions:

[0010] In a first aspect, a dynamic dimensional modeling method for a data warehouse includes:

[0011] Step 1: Receive a request for generating a data model or a request for modifying a data model created by a user in a visualization interface;

[0012] Step 2: Selecting a corresponding data model according to the request to generate a data model or the request to modify a data model;

[0013] Step 3: Select the target table from the data source library according to the selected data model;

[0014] Step 4: Configure field mapping according to the target table, and save the target table and the configured field mapping;

[0015] Step 5: Dynamically create a table corresponding to the selected data model in the local database based on the configured field mapping, configure synchronization rules, and create a scheduled synchronization task.

[0016] Preferably, in step 1, the user creates a data model request or modifies a data model request in a visual interface by dragging and dropping or configuring the interface.

[0017] Preferably, in step 2, the data model includes a fact model and a dimensional model.

[0018] Preferably, when the data model is a dimensional model and has multiple dimensions, it is necessary to select multiple target tables from the data source library, and configure field mapping and multi-dimensional linkage SQL.

[0019] Preferably, the multi-level dimensions include a time dimension, an organizational structure dimension and a resource dimension.

[0020] In a second aspect, a dynamic dimensional modeling system for a data warehouse includes:

[0021] A dynamic dimensional modeling module is used to receive a request for generating a data model or a request for modifying a data model created by a user in a visual interface;

[0022] and selecting a corresponding data model according to the request to generate a data model or the request to modify a data model;

[0023] Automatic library table generation and modification module, used to select the target table from the data source library according to the selected data model;

[0024] and configuring field mapping according to the target table, and saving the target table and the configured field mapping;

[0025] The data loading and updating module is used to dynamically create the table corresponding to the selected data model in the local database according to the configured field mapping, configure the synchronization rules, and create a scheduled synchronization task.

[0026] As a preference, it also includes:

[0027] The multi-dimensional analysis support module allows users to use newly added dimensions for analysis and query, and to cross-analyze business data through different dimensions.

[0028] In a third aspect, a computer device includes a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of a dynamic dimensional modeling method for a data warehouse when executing the computer program.

[0029] In a fourth aspect, a computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of a dynamic dimensional modeling method for a data warehouse.

[0030] In a fifth aspect, a computer program product includes a computer program or instructions, which, when executed by a processor, implements the steps of a dynamic dimensional modeling method for a data warehouse.

[0031] Compared with the prior art, this application has at least the following beneficial effects:

[0032] The present application provides a method, system and computer device for dynamic dimensional modeling of a data warehouse, which receives a request for generating a data model or modifying a data model created by a user in a visual interface; selects a corresponding data model according to the request for generating a data model or modifying a data model; selects a target table from a data source library according to the selected data model; configures field mapping according to the target table, and saves the target table and the configured field mapping; dynamically establishes a table corresponding to the selected data model in a local library according to the configured field mapping, configures synchronization rules, and creates a scheduled synchronization task. The method, system and computer device for dynamic dimensional modeling of a data warehouse provided by the present application can shorten the adjustment time of the data model, reduce maintenance costs, and significantly improve the flexibility of the data warehouse. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more intuitively illustrate the prior art and the present application, exemplary drawings are given below. It should be understood that the specific shapes and structures shown in the drawings should not generally be regarded as limiting conditions for implementing the present application; for example, those skilled in the art are capable of easily making conventional adjustments or further optimizations to the addition / reduction / attribution division, specific shapes, positional relationships, connection methods, dimensional ratios, etc. of certain units (components) based on the technical concepts and exemplary drawings disclosed in the present application.

[0034] Figure 1 A basic flow chart of a dynamic dimensional modeling method for a data warehouse provided in Example 1 of the present application;

[0035] Figure 2 A determination flow chart of a dynamic dimensional modeling method for a data warehouse provided in Embodiment 1 of the present application;

[0036] Figure 3 A schematic diagram of a multi-dimensional structure provided in Example 1 of the present application;

[0037] Figure 4 The automatic library table generation and modification module execution flow chart provided in the second embodiment of the present application;

[0038] Figure 5 This is a flowchart of the data loading and updating module execution provided in Example 2 of the present application. DETAILED DESCRIPTION

[0039] The present application is further described below in detail through specific embodiments in conjunction with the accompanying drawings.

[0040] In the description of this application: unless otherwise specified, "plurality" means two or more. The terms "first", "second", "third", etc. in this application are intended to distinguish the objects referred to, and do not have special meanings in terms of technical connotations (for example, they should not be understood as emphasizing the importance or order, etc.). Expressions such as "including", "comprising", "having", etc. also mean "not limited to" (certain units, components, materials, steps, etc.).

[0041] The terms such as "upper", "lower", "left", "right", "middle", etc. cited in this application are usually used to indicate the general relative position relationship for the purpose of intuitive understanding by referring to the drawings, and are not absolute limitations on the position relationship in the actual product.

[0042] Embodiment 1

[0043] This embodiment provides a dynamic dimension modeling method for a data warehouse, which can quickly respond to changes in business needs and allow users to dynamically create or adjust analysis dimensions through a visual interface without redesigning the data model or writing code. Through this technology, enterprises can significantly shorten the dimension expansion and adjustment cycle of data warehouses, respond to managers' analysis needs in a short time, and enhance the flexibility of data warehouses.

[0044] See also Figure 1 and Figure 2 This embodiment provides a dynamic dimensional modeling method for a data warehouse, including:

[0045] S1: receiving a request for generating a data model or a request for modifying a data model created by a user in a visualization interface;

[0046] Specifically, this embodiment uses data visualization tools or front-end frameworks (such as React, Vue, Angular, etc.) to provide users with a user interaction interface. Users can create and adjust dimension tables in the data warehouse in a visual way. Users can dynamically define new dimensions, such as time dimensions, organizational structure dimensions, and resource dimensions, through drag-and-drop operations or configuration interfaces without writing SQL codes or redesigning data models. The engine can automatically generate database table structures based on user input and seamlessly integrate the dimension model into the existing data warehouse.

[0047] S2: selecting a corresponding data model according to a request to generate a data model or a request to modify a data model;

[0048] Specifically, the data model includes a fact model and a dimensional model. When the data model is a dimensional model and has multiple dimensions, you need to select multiple target tables from the data source library and configure field mapping and multi-dimensional linkage SQL.

[0049] See also Figure 3 , the multi-level dimensions include time dimension, organizational structure dimension and resource dimension:

[0050] Time dimension: For example, time levels such as day, month, quarter, and year, supporting time rolling window analysis;

[0051] Organizational structure dimensions: such as company departments, regions, etc., supporting cross-level analysis;

[0052] Resource dimension: static classification data such as product classification and customer type.

[0053] S3: Select the target table from the data source library according to the selected data model;

[0054] Specifically, this embodiment connects to the corresponding data source library (such as a relational database, NoSQL database, etc.) through a database driver, JDBC, ODBC, etc. interface according to the selected data model, and selects the corresponding target table from the data source library. If the target table involves multiple data sources or systems, cross-library query is also required.

[0055] S4: Configure field mapping according to the target table, and save the target table and the configured field mapping;

[0056] Specifically, during the process of creating or modifying a data model, you need to configure the mapping relationship between the source table fields and the target table fields, and based on these configurations, generate field mapping rules to verify the configured fields to ensure that the data types, constraints, lengths, etc. of the source and target fields are compatible and can be mapped successfully.

[0057] When you save the target table and configured field mapping, these field mapping rules are stored in a database or configuration management system. The storage format can be JSON, XML, or a database table to ensure that the field mapping rules are reusable and extensible.

[0058] S5: Dynamically create a table corresponding to the selected data model in the local database according to the configured field mapping, configure synchronization rules, and create a scheduled synchronization task.

[0059] Specifically, this embodiment will dynamically create a table corresponding to the data model in the local database (in the data warehouse) according to the configured field mapping relationship, and automatically generate and execute the corresponding SQL DDL statement (such as CREATE TABLE) according to the table structure and field definition, thereby ensuring that the structure of the target table is consistent with that of the data source table.

[0060] Synchronization rule configuration includes selecting the data synchronization method (such as incremental synchronization, full synchronization), synchronization time interval (such as minutes, hours, days, weeks, etc.), and synchronization conditions (such as based on timestamp, ID, status, etc.). These synchronization rules can be defined through the graphical interface or scripts.

[0061] Once the synchronization rules are configured, scheduled tasks (such as using Cron expressions, task scheduling frameworks such as Quartz, Airflow, etc.) are automatically generated and scheduled to ensure that data is regularly synchronized to the local table. These tasks usually obtain data from the source system in batches or incrementally and update the local data warehouse.

[0062] In order to ensure the successful execution of the synchronization task, this embodiment will implement a real-time monitoring and alarm mechanism. For example, if an error occurs during the synchronization process (such as connection failure, data format error, etc.), the system will automatically trigger an alarm and record an error log to facilitate troubleshooting by operation and maintenance personnel.

[0063] It should be noted that the dynamic dimensional modeling method for a data warehouse provided in this embodiment is particularly suitable for data warehouses that adopt dimensional models such as star models and constellation models. It supports automatic generation or modification of database table structures and seamless integration into existing data warehouses to ensure that the analysis process is not disturbed.

[0064] The dynamic dimensional modeling method for a data warehouse provided in this embodiment has the following characteristics:

[0065] 1. Dynamic dimension creation and adjustment: Provides support for star model and constellation model data warehouses, allowing users to quickly create and adjust dimensions through a visual interface without writing code or performing complex database design.

[0066] 2. Automatic database table generation and modification: It can automatically generate or modify the corresponding database table structure based on the user's dynamic dimension definition, and ensure that the relationship between the dimension and the fact table is correct.

[0067] 3. ETL process automation: It can automatically adjust the ETL process to ensure that the newly created or modified dimension table can load data in time and associate it with the existing fact table to achieve seamless integration.

[0068] 4. Support dynamic creation of multiple dimension types: It can support multiple types of dimensions. Users can dynamically create time dimensions, organizational structure dimensions and resource dimension types according to their needs.

[0069] The dynamic dimensional modeling method of a data warehouse provided in this embodiment can significantly improve the flexibility of the data warehouse, reduce maintenance costs, and shorten the time for adjusting the data model. By allowing users to dynamically adjust the dimensional model without writing code, enterprises can quickly respond to changing business needs and ensure that data analysis can provide support for management decisions in a timely and accurate manner.

[0070] The dynamic dimensional modeling method for a data warehouse provided in this embodiment also reduces the need for the development team to frequently modify the data warehouse, thereby reducing the complexity of system maintenance. Through automated ETL process adjustment and data loading, the data warehouse is ensured to be always in a consistent and efficient state.

[0071] Embodiment 2

[0072] This embodiment provides a dynamic dimensional modeling system for a data warehouse, including:

[0073] A dynamic dimensional modeling module is used to receive a request for generating a data model or a request for modifying a data model created by a user in a visual interface;

[0074] And selecting a corresponding data model according to the request to generate a data model or the request to modify a data model.

[0075] Specifically, the dynamic dimension modeling module allows users to create and adjust dimension tables in the data warehouse in a visual way. Users can dynamically define new dimensions, such as time dimensions and organizational structure dimensions, by dragging and dropping or configuring the interface, without having to write SQL code or redesign the data model. The system can automatically generate database table structures based on user input and seamlessly integrate the dimension model into the existing data warehouse.

[0076] Automatic library table generation and modification module, used to select the target table from the data source library according to the selected data model;

[0077] And configuring field mapping according to the target table, and saving the target table and the configured field mapping.

[0078] Specifically, this module is responsible for automatically generating or modifying the table structure of the data warehouse according to the changes in the dynamic dimension model. The system can automatically create corresponding dimension tables for new dimensions and associate them with fact tables. For modifications to existing dimensions, such as adding new fields and adjusting levels, the system can ensure the compatibility of the data table structure to avoid affecting existing data and query logic. Figure 4 shown.

[0079] The data loading and updating module is used to dynamically create the table corresponding to the selected data model in the local database according to the configured field mapping, configure the synchronization rules, and create a scheduled synchronization task.

[0080] Specifically, this module is responsible for automatically extracting, transforming, and loading (ETL) data based on the newly added or modified dimension tables. After adding a new dimension, the system will automatically extract relevant data from the data source, fill the dimension table, and ensure that this data can be correctly associated with the data in the fact table, supporting cross-dimensional multidimensional analysis, such as Figure 5 shown.

[0081] The present embodiment provides a data warehouse dynamic dimensional modeling system, further comprising:

[0082] The multi-dimensional analysis support module allows users to use newly added dimensions for analysis and query, and to cross-analyze business data through different dimensions.

[0083] Specifically, the system allows users to directly use the newly added dimensions for analysis and query through existing OLAP tools or BI platforms without having to modify the query statements. Users can quickly switch analysis angles and cross-analyze business data through different dimensions to improve decision-making efficiency.

[0084] For the specific implementation content of each module in a dynamic dimensional modeling system of a data warehouse, please refer to the above definition of a dynamic dimensional modeling method of a data warehouse, which will not be repeated here.

[0085] Embodiment 3

[0086] This embodiment provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, steps of a dynamic dimensional modeling method for a data warehouse are implemented.

[0087] Embodiment 4

[0088] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of a dynamic dimensional modeling method for a data warehouse are implemented.

[0089] Embodiment 5

[0090] This embodiment provides a computer program product, including a computer program or instructions, which, when executed by a processor, implements the steps of a dynamic dimensional modeling method for a data warehouse.

[0091] The technical features of the above embodiments may be arbitrarily combined (as long as there is no contradiction in the combination of these technical features). To make the description concise, not all possible combinations of the technical features in the above embodiments are described; these embodiments that are not explicitly written should also be considered to be within the scope of this specification.

Claims

1. A dynamic dimensional modeling method for a data warehouse, characterized in that: include: Step 1: Receive a request for generating a data model or a request for modifying a data model created by a user in a visualization interface; Step 2: Selecting a corresponding data model according to the request to generate a data model or the request to modify a data model; Step 3: Select the target table from the data source library according to the selected data model; Step 4: Configure field mapping according to the target table, and save the target table and the configured field mapping; Step 5: Dynamically create a table corresponding to the selected data model in the local database based on the configured field mapping, configure synchronization rules, and create a scheduled synchronization task.

2. The dynamic dimensional modeling method for data warehouse according to claim 1, characterized in that: In step 1, the user creates a data model request or modifies a data model request in a visual interface by dragging and dropping or configuring the interface.

3. The dynamic dimensional modeling method for data warehouse according to claim 1, characterized in that: In step 2, the data model includes a fact model and a dimensional model.

4. The dynamic dimensional modeling method for data warehouse according to claim 3, characterized in that: When the data model is a dimensional model and has multiple dimensions, it is necessary to select multiple target tables from the data source library and configure field mapping and multi-dimensional linkage SQL.

5. The dynamic dimensional modeling method for data warehouse according to claim 4, characterized in that: The multi-level dimensions include a time dimension, an organizational structure dimension, and a resource dimension.

6. A dynamic dimensional modeling system for a data warehouse, characterized in that: include: A dynamic dimensional modeling module is used to receive a request for generating a data model or a request for modifying a data model created by a user in a visual interface; and selecting a corresponding data model according to the request to generate a data model or the request to modify a data model; Automatic library table generation and modification module, used to select the target table from the data source library according to the selected data model; and configuring field mapping according to the target table, and saving the target table and the configured field mapping; The data loading and updating module is used to dynamically create the table corresponding to the selected data model in the local database according to the configured field mapping, configure the synchronization rules, and create a scheduled synchronization task.

7. The dynamic dimensional modeling system for data warehouse according to claim 6, characterized in that: Also includes: The multi-dimensional analysis support module allows users to use newly added dimensions for analysis and query, and to cross-analyze business data through different dimensions.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 5 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

10. A computer program product comprising a computer program or instructions, characterized in that When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

Images