Layering and domain-dividing design method for data warehouse

Abstract

The invention discloses a data warehouse layering and domain dividing design method, and belongs to the technical field of data warehouses, and the method comprises the steps: constructing a data warehouse, and carrying out the layering of the data warehouse; extracting traffic flow related data from a source end service system in a data operation layer, and establishing a data model in a zoning manner; in the data detail layer, standardizing and cleaning the data of the data operation layer, and establishing a data model based on the processed data; preprocessing the data of the detail layer in the data intermediate layer, establishing a fact table and performing light summarization; exchanging the data to a Dores database for processing; in the data summarization layer, summarization processing is provided for the theme library based on the data of the middle layer, and business indexes are developed according to different business dimensions; in the data mart layer, the fact table and the index data are subjected to domain processing according to business requirements and are docked to an application thematic library. According to the method, the problems of high data processing delay, long period and analysis singleness are effectively solved, and data redundancy and delay are remarkably reduced.

Description

Technical Field

[0001] The present application relates to the technical field of data warehouses, and in particular to a design method for layering and domaining a data warehouse. Background Art

[0002] In traditional data warehouse (DW) design and application scenarios, especially in the field of intelligent traffic management on highways, existing technologies generally have the following key problems: chaotic and redundant data management: the lack of a unified layered architecture and standardized specifications leads to inefficient data storage and processing; raw data and cleaned data are stored mixedly, without a clear division between the operation layer (ODS) and the detail layer (SDM), resulting in a data redundancy rate of up to 30%-40%, which increases the complexity of the data cleaning process; the naming rules are not unified, table names and field names are arbitrarily defined, and there is a lack of business and technical classification suffixes, resulting in poor data readability and difficulty in cross-team collaboration. These problems seriously restrict the efficiency of data-driven decision-making and system scalability.

[0003] Apache Spark is an open-source big data processing framework developed by AMPLab. It provides an integrated, scalable data processing tool suitable for various data analyses. Currently, traffic flow calculations, entrance and exit traffic data, and ETC gantry data are all processed using Apache Spark. This data is extracted into Hive using an offline T-1 process and directly processed to generate usable data.

[0004] However, data processing based on Apache Spark suffers from high latency, with a T-1 (every other day) data cycle, preventing hourly or even shorter data response times. This prevents management from timely adjusting highway dynamic strategies based on traffic flow analysis. Furthermore, the entire data processing process is time-consuming, resulting in slow data response times, impacting the timeliness of business decisions. Furthermore, existing technologies only generate data analysis results for the current cycle and lack correlation and comparative analysis with historical data. This inability to identify trends or cyclical patterns limits the comprehensiveness of decision-making. Summary of the Invention

[0005] In response to the above-mentioned deficiencies in the prior art, the present application provides a data warehouse hierarchical and domain-based design method that solves the problems of high data processing time delay, long processing cycle and single analysis results.

[0006] In order to achieve the above-mentioned invention objectives, the technical solutions adopted in this application are: This application provides a method for designing a data warehouse with layers and domains, including: S1: Build a data warehouse and divide the data warehouse into layers, wherein the data warehouse includes a data operation layer, a data detail layer, a data intermediate layer, a data summary layer, and a data mart layer; S2: Extracts traffic flow data from the source data of the source business system through the data operation layer and models the relevant data using the data partitioning method; S3: At the data detail layer, the partitioned data obtained from the data operation layer is standardized and cleaned, and a data model is built based on the standardized and cleaned data. S4: Preprocess the data model of the data detail layer through the data middle layer to obtain fact tables and lightly summarized data divided by business subject domains; S5: Exchange the data in the middle layer to the Doris database and process the data based on the Doris database; S6: In the data aggregation layer, derivative aggregation processing is performed based on the lightly aggregated data in the data middle layer, and business indicator data is developed according to different business dimensions; S7: Through the data mart layer, fact table data and business indicator data are processed in different domains according to business needs and connected to the application subject library.

[0007] Furthermore, the data operation layer in S2 includes partition design; The partition design is to extract relevant data on vehicle flow from the source data of the source business system and partition them by year, month and day, with each partition storing a copy of the data for that day.

[0008] Furthermore, the traffic flow related data includes traffic flow entrance data, traffic flow exit data and ETC gantry data; Extract the incremental data and historical data from the relevant data into the data operation layer by hour and day, overwriting the partition data of the current day.

[0009] Furthermore, the S3 specifically includes: S301: By exploring the original data, the original fields of the partitioned data obtained from the data operation layer are split according to the national standard, the enterprise standard and the industry standard to form standardized fields; S302: combining standardized fields to obtain new fields that include original fields, thereby forming standard fields and original fields with standard names; S303: Map the standard fields and the original fields with standard names using SQL, and clean the dirty data detected during the mapping process; S304: Based on the cleaned fields, data modeling is performed using a partitioning method.

[0010] Furthermore, the preprocessing in S4 is to establish a fact table divided into domains by business subject according to a dimensional modeling approach, and perform light aggregation based on the fact table.

[0011] Furthermore, the establishment of a fact table divided into domains by business subject and light aggregation based on the fact table include: A1: Implement modeling methods and processed data based on dimensions, divide the data into the smallest granularity, and create a fact table with detailed data granularity. A2: Coarsen the granularity of detailed data, select common dimensions, retain the business metrics of events, and perform light data aggregation.

[0012] Furthermore, the light aggregation specifically includes: Calculate fact data of different dimensions, spread them evenly over days, and summarize them with days as the granularity.

[0013] Furthermore, the data warehouse further includes a dimension layer, wherein the dimension tables of the dimension layer store attributes and detailed description information of business entities in the fact table; The dimension layer uses a zipper table to record slowly changing dimension information.

[0014] The beneficial effects of this application are: This application provides a method for designing a hierarchical and domain-based data warehouse. This method ensures orderly and efficient data flow through a unified hierarchical architecture design and standardized naming conventions, effectively reducing data redundancy and management complexity, improving data readability and maintainability, and partitioning data by business theme, reducing the frequency of full table scans and lowering data latency. Furthermore, a data middle layer is added to design fact tables with detailed data granularity, allowing for light aggregation based on the fact tables, improving computational efficiency. Furthermore, exchanging data to the Doris database facilitates subsequent computations and queries, improving data processing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0016] Figure 1 A flowchart of a method for designing a hierarchical and domain-based data warehouse provided in an embodiment of the present application.

[0017] Figure 2 A design architecture diagram of a data system provided in an embodiment of the present application. DETAILED DESCRIPTION

[0018] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field based on this application are within the scope of protection of this application.

[0019] Based on the current problem of low efficiency in the use of traffic flow data and backwardness in the field of intelligent traffic management, this embodiment of the application provides a design method for layered and domain-based data warehouse. Figure 1 , Figure 1 The figure shows a flow chart of a method for designing a data warehouse with layers and domains according to an embodiment of the present application, including: S1: Build a data warehouse and divide the data warehouse into layers. The data warehouse includes a data operation layer, a data detail layer, a data intermediate layer, a data summary layer, and a data mart layer.

[0020] In one embodiment of the present application, the data operation layer (ODS) is the raw data acquisition layer of the data warehouse. Its data structure is consistent with the source system data structure. It is equivalent to a data preparation area and is also responsible for recording basic data and historical changes. The data detail layer (SDM) is the detailed data layer of the data warehouse. It is responsible for standardizing, adding and transferring full data, and other cleaning operations on the data in the data operation layer, and storing the full amount of data for a long period of time. The main features of this layer are standardization, adding and transferring full data, and cleaning operations. The data middle layer (DWM) is the analytical detail data sedimentation layer of the data warehouse. Above the data detail layer, it revolves around the core business processes of the enterprise and focuses on the core business events and business entities in the business process. It mainly realizes the basic association within and across domains, including the association of models within the same source system domain and different source system domains, forming facts by subject domain and light-grained summary data models. The main features of this layer are: intra-domain / cross-domain association, light-grained summary, and redundancy avoidance. The Data Aggregation Layer (DWA) is the derived aggregated data layer of the data warehouse. It sits above the data intermediate layer and aggregates data across multiple topics, including intra-domain and cross-domain model associations or higher-level model associations within a domain. Business indicator data is solidified and precipitated based on application requirements, with dimensionality degradation as needed. Key features of this layer include intra-domain and cross-domain associations or high-level aggregation within a domain, a focus on topics, and partial redundancy. The Data Mart Layer (FDM) is the data layer of the data warehouse for application delivery. Above the Data Aggregation Layer, it is divided into domains by topic and outputs application-specific database wide tables as needed.

[0021] like Figure 2 As shown, Figure 2A design architecture diagram of a data system provided in an embodiment of the present application. The data source of the data operation layer is the vehicle flow entry data, vehicle flow exit data and ETC gantry data in the source business system. It is configured according to the specific business type and business needs, and provides basic data for other logical layers of the data warehouse.

[0022] S2: Extract traffic flow related data from the source data of the source business system through the data operation layer, and use the data partitioning method to model the related data.

[0023] Furthermore, the data operation layer in S2 includes partition design; The partition design is to extract relevant data on vehicle flow from the source data of the source business system and partition them by year, month and day, with each partition storing a copy of the data for that day.

[0024] The traffic flow related data includes traffic flow entrance data, traffic flow exit data and ETC gantry data; Extract the incremental data and historical data from the relevant data into the data operation layer by hour and day, overwriting the partition data of the current day.

[0025] In one embodiment of the present application, the data integration framework is responsible for data access and transmission. The data integration framework is a secondary encapsulation based on Apache Seatunnel and is a tool for data exchange. Data modeling and partitioning are structural design, optimization and storage management operations performed inside the data warehouse after the data enters the data warehouse. Data modeling is based on the original data and business-specific model design. Each layer of the data warehouse has its own unique design concept, and the model design of each layer is different; data partitioning is partitioning processing for large quantities of data on HDFS, which is divided into yearly partitions, monthly partitions, daily partitions and hourly partitions. Since the data exchange tool is not the main application point of this application, it will not be described here.

[0026] In one embodiment of the present application, a data model is established at the data operation layer. The data structure at this layer remains consistent with the source system data structure, but the concept of partitioning is added to the original table. Partition fields are added to the original table, and partitioning is performed according to year (Y_PART), month (M_PART), and day (D_PART). SQL statements are then used to create a data partition table in the Hive database, storing data in different partitions, with each partition containing a copy of the current day's data. A data integration framework is also introduced: it is a secondary encapsulation based on Apache Seatunnel, enabling connectivity to hundreds of data sources, integrated stream and batch data processing, and ensuring high performance while also providing low latency, automatic recovery from task failures, visual data integration content, and data order and consistency. Leveraging the high fault tolerance and low latency of data integration, incremental data is extracted hourly into partitions at the ODS layer, overwriting the current day's partition data each time to prevent data loss. Historical data is also supplemented by daily partitioning, capturing the latest data while also collecting historical data into the cluster.

[0027] S3: At the data detail layer, the partitioned data obtained from the data operation layer is standardized and cleaned, and a data model is established based on the standardized and cleaned data.

[0028] Furthermore, the S3 specifically includes: S301: By exploring the original data, the original fields of the partitioned data obtained from the data operation layer are split according to the national standard, the enterprise standard and the industry standard to form standardized fields; S302: combining standardized fields to obtain new fields that include original fields, thereby forming standard fields and original fields with standard names; S303: Map the standard fields and the original fields with standard names using SQL, and clean the dirty data detected during the mapping process; S304: Based on the cleaned fields, data modeling is performed using a partitioning method.

[0029] In one embodiment of the present application, at the data detail layer, the data obtained from the data operation layer is standardized. The data standards are divided into field standards and naming standards. By exploring the original data, the original fields are split according to the national standard (international standard), enterprise table (enterprise standard), and industry standard (industry standard). For example, the translation is performed according to the translation software and the national standard network, and the fields are split through the standard root tool. The split fields are combined through the root tool to obtain new fields that include the original fields, and the naming standard is performed. The standardized fields are mapped to the system, and the system's data modeling tool is used to bind the field standards, and then data modeling is performed. The data model of the detail layer is the same as the model of the operation layer. Both are modeled in a partitioned manner, that is, partitioned by year, month, and day. This keeps consistent with the data of each partition of the operation layer, and can also perform better calculations. At the same time, at the data detail layer, we also perform data cleaning operations by exploring the original data and mapping the data in SQL form. During the mapping process, we clean the dirty data detected, such as using clear functions or association functions to remove some special characters or perform code conversion, so as to discover special characters and empty fields in the fields and process them, removing dirty and messy data and retaining valuable data.

[0030] Specifically, you can create a mapping dictionary table and use SQL to update the fields: Convert the original vehicle type code to the standard code: SELECT m.standard_code AS vehicle_type_std FROM detail_table d JOIN code_mapping m ON d.vehicle_type_raw = m.raw_code; Use Apache Atlas to manage metadata mapping relationships, or perform batch transformations through ETL tools such as Kettle.

[0031] The naming standard backend vocabulary is combined to form a field standard, including: naming standardization: defining the backend vocabulary library (such as vehicle_type → veh_type, identification → id); splicing the cleaned fields according to business logic: Combine "province code + license plate number" to generate the standard field 'std_plate_no' SELECT CONCAT(province_code, plate_number) AS std_plate_no FROM cleaned_table; Write standardized fields into the detail layer table as the basis for modeling: CREATE TABLE dwd_traffic_detail( std_plate_noSTRING COMMENT 'Standard license plate number', / / combined fields vehicle_type_std STRING COMMENT 'Standard vehicle model' / / Field after mapping )PARTITIONED BY (dt STRING).

[0032] The key points of the SDM layer model design are as follows: 1. The SDM layer model is divided into domains according to the source business system.

[0033] 2. The SDM layer model must comply with the requirements of the data asset catalog standard and perform processing operations such as data cleaning, model and attribute value standardization on the ODS layer.

[0034] Among them, processing operations include data cleaning and data conversion. Data cleaning: identifying and cleaning invalid, useless, and abnormal information to ensure the standardization and validity of data; data conversion: grouping and mapping information with the same meaning but different codes; unifying data content formats (such as date, region, product unified coding, etc.).

[0035] S4: Preprocess the data model of the data detail layer through the data middle layer to obtain fact tables and lightly summarized data divided by business subject domains.

[0036] Furthermore, the preprocessing in S4 is to establish a fact table divided into domains by business subject according to a dimensional modeling approach, and perform light aggregation based on the fact table.

[0037] Among them, establish a fact table and perform light aggregation based on the fact table, including: A1: Implement modeling methods and processed data based on dimensions, divide the data into the smallest granularity, and create a fact table with detailed data granularity. A2: Coarsen the granularity of detailed data, select common dimensions, retain the business metrics of events, and perform light data aggregation.

[0038] Among them, light aggregation refers to calculating fact data of different dimensions, spreading different dimensions to days, and aggregating them with days as the granularity.

[0039] In one embodiment of the present application, domain design refers to dividing the data model according to business subject domains (such as "vehicle traffic domain", "toll collection domain" and "event domain"), wherein the vehicle traffic domain refers to the calculation of the number and distribution of cars passing through the current gantry or intersection, road section per hour and per day based on the passing of cars through the gantry, intersection, road section, etc., and the year-on-year and month-on-month calculations; the toll collection domain refers to the calculation of daily vehicle highway traffic conditions, the calculation of effective tolls, holiday exemption fees, etc.; the event domain is calculated based on the number of various events and accidents on the highway, and the year-on-year and month-on-month calculations are also calculated.

[0040] In one embodiment of the present application, the DWM layer preprocesses the SDM layer data, namely, by implementing a dimensionally implemented modeling method to design a fact table with detailed data granularity. Based on the fact table, a light aggregation is performed. The light aggregation is mainly carried out around core events with large data volumes. The data granularity changes from fine to coarse, and commonly used dimensions are selected while retaining the business metrics of the events. Light aggregation and sedimentation of data are performed. The core entities of the SDM library and the relationships between the core entities are no longer stored in the DWM library due to data redundancy.

[0041] The DWM layer fact dimension model has the following characteristics: 1. Fact tables are divided and stored according to business subject domains.

[0042] 2. In order to improve the query speed, there is a certain degree of redundancy in the fields in the dimension table.

[0043] Fact table: At the DWM layer, we divide data into the smallest granularity. Each vehicle's entrance and exit record and each gantry that a vehicle passes through are considered a small granularity. In the data intermediate layer, each useful record is considered 1. The latest granularity is then used for subsequent aggregation and calculation. case VEHICLE_CNT when 1 then 1 else 0 end AS EXIT_FLOW Lightly aggregated tables: Due to the massive amount of data, to reduce computing pressure and improve efficiency, we also performed light aggregation on the fact table in the data middle layer. This granularity was changed from 1 to daily aggregation, and fact data of different dimensions was calculated for each day, such as daily traffic volume, daily transaction amount, and daily vehicle type. By amortizing different dimensions to a daily basis, we reduced the massive amount of data in the millions to tens of thousands, accelerating the overall efficiency of the entire data warehouse.

[0044] S5: The data in the middle layer is transferred to the Doris database through data exchange, and data processing is performed based on the Doris database.

[0045] In one embodiment of the present application, DWM layer data is transferred to an MPP database (Doris) through data exchange, and subsequent data processing is performed in Doris. Apache Doris is a high-performance, real-time analytical database based on the MPP architecture. It is well-known for its extremely fast and easy-to-use features. It can return query results for massive amounts of data with sub-second response times, supporting not only high-concurrency point query scenarios but also high-throughput complex analysis scenarios.

[0046] Features of MPP database: 1) The MPP cluster directly supports the digital workbench, providing data analysis and visualization services.

[0047] 2) Generate data API interfaces based on the MPP cluster to provide data services to applications.

[0048] Data is exchanged from the Hadoop cluster to the MPP cluster. The entire exchange process involves exchanging data from the data operation layer and Hive data to the MPP database, namely the Doris database. After the data is exchanged to the Doris database, the powerful computing power and high performance of Doris are used for subsequent indicator development and calculation. Apache Flink data synchronization is also adopted. The Flink CDC or Flink DataStream module is used to write data in batches, and the lightly summarized data is synchronized on an hourly basis. Subsequent operations are continued in the MPP cluster, which not only improves the response speed, but also greatly speeds up the query speed.

[0049] In one embodiment of the present application, Apache Doris is a high-performance, real-time analytical database based on the MPP architecture. It is well-known for its extremely fast and easy-to-use features. It can return query results for massive amounts of data with only sub-second response times. It can support not only high-concurrency query scenarios, but also high-throughput complex analysis scenarios. Based on this, Apache Doris can better meet usage scenarios such as report analysis, instant query, unified data warehouse construction, and data lake federated query acceleration. Users can build applications such as user behavior analysis, AB experimental platforms, log retrieval analysis, user portrait analysis, and order analysis on it.

[0050] S6: In the data aggregation layer, derivative aggregation processing is performed based on the lightly aggregated data in the data middle layer, and business indicator data is developed according to different business dimensions.

[0051] In one embodiment of the present application, the DWA aggregation layer primarily performs derivative aggregation based on the DWM layer's fact dimension model or lightly aggregated table, and performs dimensionality degradation to form a common indicator library, thereby improving subsequent data processing and access performance. Derivative aggregation refers to the calculation of derived indicators using atomic indicators, such as calculating daily, monthly, and annual vehicle traffic, year-on-year, and month-on-month vehicle traffic using the atomic indicator of vehicle traffic, specifically through SQL statements. Dimensional degradation refers to the use of codes to represent the meaning of some indicators in the previous data warehouse layer, and the conversion of these codes into corresponding Chinese characters at the marketplace layer for easier business use. Its characteristic is that it is application-oriented but does not directly support applications. It jointly precipitates and processes commonly used information in the application process. The DWA layer, SDM layer, and DWM layer together constitute an enterprise-level data warehouse.

[0052] At the data aggregation layer, summary calculations are provided for each theme library based on the data from the data middle layer, and indicators are developed according to different businesses and dimensions. The theme library is derived from specific business scenarios, and relevant topics are developed based on actual business scenarios, such as traffic flow topics, event topics, accident topics, maintenance topics, etc.; as shown in Table 1, the table shows some indicators of entrance and exit traffic.

[0053] Table 1 Analysis of inlet and outlet flow indicators

[0054] It should be noted that the implementation of each layer of this application is based on code. Part of the code is used here to illustrate. Taking the entrance traffic flow indicator as an example, first, in the middle layer, atomic indicators are generated through the cleaned detailed data: After the atomic indicators are formed, the atomic indicators are used in the summary layer to perform summary calculations: For example: Daily entrance traffic volume: INSERT INTO DWA_LWYY_D_ENTRANCE_FLOW_INDEX_D SELECT DATE_ID AS DATE_ID, MONTH_ID AS MONTH_ID, YEAR_ID AS YEAR_ID, SUM(ENTRANCE_FLOW) AS ENTRANCE_FLOW, current_timestamp() AS ETL_TIME FROM DWM_LWYY_ENTRANCE_FLOW_LS_D AS t1 GROUP BY DATE_ID,MONTH_ID,YEAR_ID At the summary layer, we perform association processing on atomic indicators and dimension tables, and use SQL statements to degenerate the codes in the indicators into Chinese characters: INSERT INTO FDM_GSDN_M_TOLL_STN_TRAFFIC_FLOW_STAT_H SELECT t1.MONTH_ID AS MONTH_ID, t1.YEAR_ID AS YEAR_ID, t1.TOLL_STN_ID AS TOLL_STN_ID, t1.TOLL_STN_NAME AS TOLL_STN_NAME, t1.EXIT_FLOW AS EXIT_FLOW, t2.ENTRANCE_FLOW AS ENTRANCE_FLOW, current_timestamp() AS ETL_TIME FROM DWA_LWYY_M_TOLL_STN_EXIT_FLOW_INDEX_D AS t1 left join DWA_LWYY_M_TOLL_STN_ENTRANCE_FLOW_INDEX_D as t2 ont1.MONTH_ID = t2.MONTH_ID and t1.TOLL_STN_ID = t2.TOLL_STN_ID ORDER BY MONTH_ID S7: Through the data mart layer, fact table data and business indicator data are processed in different domains according to business needs and connected to the application subject library.

[0055] In one embodiment of the present application, the data intermediate layer is the smallest granularity indicator data, also known as atomic indicators. The data aggregation layer is derived indicators that are dimensionally calculated and aggregated based on atomic indicators. The market layer, also known as the application layer, can directly use derived indicators or calculate composite indicators based on derived indicators. Finally, data service applications are performed at the market layer. The market layer is a lightly aggregated data layer for business scenarios, in which each layer performs SQL calculations for business logic. For example, secondary indicator calculations: lightly aggregated according to business needs at the FDM layer (such as aggregating hourly data into daily peaks): INSERT INTO dm_pass.peak_congestion SELECT road_id, MAX(avg_speed) AS min_speed / / Take the minimum speed as the congestion indicator FROM dws_road_hourly WHERE dt='20230724' AND hour BETWEEN '07:00' AND '09:00' GROUP BY road_id.

[0056] In one embodiment of this application, domain-based processing involves organizing different business requirements into distinct directories. Each directory at each layer in the data warehouse is independent and unique. The FDM marketplace layer acts as a thematic repository for applications, connecting detailed data and metrics required by applications to the thematic repository as needed. This serves as the data layer of the data warehouse for application delivery.

[0057] The FDM marketplace layer is primarily composed of data results integrated and processed based on various data models, and is stored by topic library. This can directly support various application needs, such as data reporting, data analysis and statistics. Based on the application, a corresponding topic is created. Different applications will apply the same indicators, but to distinguish them, tasks are created under each application that requires data to meet different needs. For example, an application marketplace table for traffic flow-related indicators is created. The specific code is as follows: INSERT INTO FDM_GSDN_M_TRAFFIC_FLOW_STAT_H SELECT t1.MONTH_ID AS MONTH_ID, t1.YEAR_ID AS YEAR_ID, t1.EXIT_FLOW AS EXIT_FLOW, t2.ENTRANCE_FLOW as ENTRANCE_FLOW, current_timestamp() AS ETL_TIME FROM DWA_LWYY_M_EXIT_FLOW_INDEX_D AS t1 left join DWA_LWYY_M_ENTRANCE_FLOW_INDEX_D AS t2 on t1.MONTH_ID =t2.MONTH_ID In principle, the marketplace layer is only oriented towards application topics and only allows reuse within the same application system. If reuse occurs between application systems, it must be moved down to the aggregation layer to achieve the effect of one-point processing and multiple reuse to form general capabilities.

[0058] Furthermore, the data warehouse further includes a dimension layer, wherein the dimension tables of the dimension layer store attributes and detailed description information of business entities in the fact table; The dimension layer uses a zipper table to record slowly changing dimension information.

[0059] In one embodiment of this application, the dimension layer (DIM) is the dimension table of the dimensional model, used for dimension association. Relevant dimensional information is obtained through primary keys. Establishing consistent dimension tables can reduce the risk of inconsistent data calculation calibers and algorithms. Using dimensions as the modeling driver, based on the business meaning of each dimension, the calculation logic is defined by defining dimensions and their primary keys, adding dimension attributes, and associating dimensions. This completes the attribute definition process and establishes a consistent dimension table.

[0060] In principle, the DIM layer should generate as many dimension attributes as possible, provide as many meaningful textual descriptions as possible, and try to precipitate common dimension attributes. Its specific functions include: descriptive attribute storage: storing detailed descriptions of business entities (such as product name, color, size, etc.); hierarchy maintenance: maintaining natural business hierarchies (such as year-quarter-month-day); slowly changing processing: tracking changes in dimension attributes over time; multilingual support: providing translated text in a multilingual environment; degenerate dimension processing: processing fact table attributes that do not have independent dimension tables. Design features include: typically using a star or snowflake schema design, including surrogate keys and natural keys, supporting type 1, type 2, and type 3 slowly changing dimensions, and possibly including special designs such as miscellaneous dimensions or micro dimensions.

[0061] Slowly changing dimensions are uniformly implemented using zipper tables, which can more efficiently save the historical status of dimension information.

[0062] The data warehouse design method provided in this application is hierarchical and domain-specific. Through a unified hierarchical architecture design, the data warehouse is clearly divided into a data operation layer, a data detail layer, a data middle layer, a data summary layer, and a data mart layer, ensuring orderly and efficient data flow. Unified data object naming standards and field type design standards are formulated to improve data readability and maintainability. A data middle layer is added to design fact tables with detailed data granularity, and light aggregation is performed based on the fact tables, improving computational efficiency. Data is exchanged to the Doris database to facilitate subsequent calculations and queries, improving data processing efficiency. Furthermore, the design of data analysis dimension tables is strengthened, and zipper tables are used to record slowly changing dimension information, which can more efficiently preserve the historical status of dimension information.

[0063] It should be noted that those skilled in the art will appreciate that the embodiments described herein are intended to help readers understand the principles of this application, and it should be understood that the scope of protection of this application is not limited to such specific descriptions and embodiments. Those skilled in the art can make various other specific variations and combinations based on the technical teachings disclosed in this application without departing from the essence of this application, and such variations and combinations are still within the scope of protection of this application.

Claims

1. A method for designing a data warehouse by layering and domaining, characterized in that: include: S1: Build a data warehouse and divide the data warehouse into layers, wherein the data warehouse includes a data operation layer, a data detail layer, a data intermediate layer, a data summary layer, and a data mart layer; S2: Extracts traffic flow data from the source data of the source business system through the data operation layer and models the relevant data using the data partitioning method; S3: At the data detail layer, the partitioned data obtained from the data operation layer is standardized and cleaned, and a data model is built based on the standardized and cleaned data. S4: Preprocess the data model of the data detail layer through the data middle layer to obtain fact tables and lightly summarized data divided by business subject domains; S5: Exchange the data in the middle layer to the Doris database and process the data based on the Doris database; S6: In the data aggregation layer, derivative aggregation processing is performed based on the lightly aggregated data in the data middle layer, and business indicator data is developed according to different business dimensions; S7: Through the data mart layer, fact table data and business indicator data are processed in different domains according to business needs and connected to the application subject library.

2. The method for designing a data warehouse by layering and domaining according to claim 1, characterized in that: The data operation layer in S2 includes partition design; The partition design is to extract relevant data on vehicle flow from the source data of the source business system and partition them by year, month and day, with each partition storing a copy of the data for that day.

3. The method for designing a data warehouse by layering and domaining according to claim 2, characterized in that: The traffic flow related data includes traffic flow entrance data, traffic flow exit data and ETC gantry data; Extract the incremental data and historical data from the relevant data into the data operation layer by hour and day, overwriting the partition data of the current day.

4. The method for designing a data warehouse by layering and domaining according to claim 1, characterized in that: The S3 specifically includes: S301: By exploring the original data, the original fields of the partitioned data obtained from the data operation layer are split according to the national standard, the enterprise standard and the industry standard to form standardized fields; S302: combining standardized fields to obtain new fields that include original fields, thereby forming standard fields and original fields with standard names; S303: Map the standard fields and the original fields with standard names using SQL, and clean the dirty data detected during the mapping process; S304: Based on the cleaned fields, data modeling is performed using a partitioning method.

5. The method for designing a data warehouse by layering and domaining according to claim 1, characterized in that: The preprocessing in S4 is to establish a fact table divided into domains by business subject according to the dimensional modeling method, and perform light aggregation based on the fact table.

6. The method for designing a data warehouse by layering and domaining according to claim 5, characterized in that: The establishment of fact tables divided by business subject domains and light aggregation based on the fact tables include: A1: Implement modeling methods and processed data based on dimensions, divide the data into the smallest granularity, and create a fact table with detailed data granularity. A2: Coarsen the granularity of detailed data, select common dimensions, retain the business metrics of events, and perform light data aggregation.

7. The method for designing a data warehouse by layering and domaining according to claim 6, characterized in that: The light aggregation specifically includes: Calculate fact data of different dimensions, spread them evenly over days, and summarize them with days as the granularity.

8. The method for designing a data warehouse by layering and domaining according to claim 1, characterized in that: The data warehouse further comprises a dimension layer, wherein the dimension tables of the dimension layer store attributes and detailed description information of business entities in the fact table; The dimension layer uses a zipper table to record slowly changing dimension information.

Images