Elastic dimension modeling method for intensive storage and rapid retrieval

Abstract

The invention discloses an elastic dimension modeling method oriented to intensive storage and rapid retrieval, and relates to the technical field of data warehouses and big data, a hierarchical dimension architecture is adopted, dimensions are divided into core dimensions and extension dimensions, access attributes can be dynamically loaded according to service requirements, the extension dimensions are dynamically loaded through metadata driving, and the service quality is improved. The redundancy of core dimension tables is reduced, the storage efficiency is improved, a dimension merging strategy is adopted, the core dimension tables are merged into composite dimensions, the number of the dimension tables is reduced, the query performance is improved, meanwhile, stored data are efficiently compressed, the occupied storage space is reduced, time partition and Hash bucket dividing strategies are adopted for optimizing fact tables, time range query is accelerated, and the query efficiency is improved. The high-frequency access dimension table is loaded to a memory database such as Redis for caching, query response is accelerated, parallel processing is conducted on large-scale data through a distributed query engine, and query efficiency is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of data warehouse and big data technology, and particularly relates to an elastic dimension modeling method for intensive storage and fast retrieval. BACKGROUND

[0002] With the rapid development of information technology, big data is becoming an important force to promote social progress and economic development. However, with the rapid growth of big data, a series of problems and challenges have also arisen. How to effectively organize and manage massive data has become a problem that needs to be solved.

[0003] Most enterprises will build data warehouses to store and manage data. The data modeling theory related to data warehouses has been fully practiced in the industry, and the application has also been relatively mature. The most commonly used dimension modeling technology can be well applied to analytical database, data warehouse and data mart modeling methods. In the dimension modeling theory, the model is generally divided into fact table and dimension table. The fact table carries the measurement of the analysis theme, and contains the foreign key associated with each dimension table. The dimension table is the carrier of the dimension, and the dimension is used for the analysis of the required multi-environment of the fact.

[0004] However, the traditional dimension modeling method often has problems such as low storage efficiency and query performance bottleneck when facing large-scale data sets. Especially when dealing with complex analysis queries, multiple table join operations are required, which significantly increases the query delay. In addition, the traditional method is difficult to adapt to rapidly changing business needs, and the cost of dimension expansion and maintenance is high.

[0005] In view of the above problems, the present application provides an elastic dimension modeling method for intensive storage and fast retrieval. SUMMARY

[0006] The purpose of the present application is to solve the storage and retrieval performance bottleneck in the traditional dimension modeling method by the comprehensive application of dynamic dimension design, storage optimization and retrieval acceleration technology, and to improve the flexibility and scalability of the data warehouse.

[0007] In order to achieve the above purpose, the present application adopts the following technical scheme: an elastic dimension modeling method for intensive storage and fast retrieval, comprising the following steps: Step 1, obtain business requirements and general data in the industry, batch clean and model preprocess the general data, and prestore them in the memory database. According to the business requirements, the structure of the dimension table and the fact table is constructed, and the general data in the industry is respectively entered into the corresponding dimension table and fact table according to its attributes. Step two, for specific business analysis scenarios, combine the dimension table and the fact table structure to build a flexible dimension model, and plan and construct the data in the in-memory database in layers, domains and markets; Step three, optimize the flexible dimension model using a hierarchical dimension architecture composed of core dimensions and extended dimensions and a metadata-driven dynamic dimension expansion strategy; Step four, select the corresponding storage format and compression algorithm to optimize the general data in the in-memory database; The storage format includes Parquet, ORC and other columnar storage formats, and the compression algorithm includes Snappy, Zstd and other efficient compression algorithms.

[0008] Step five, optimize the fact table according to the data characteristics using time partitioning and hash bucketing strategies; Step six, configure index bitmap index and inverted index for the flexible dimension model to speed up query performance; Step seven, load high-frequency access dimension tables into the in-memory database for caching to improve query response speed; Step eight, precompute and store common aggregation indicators as materialized views to speed up query response.

[0009] Further, the general data includes product basic information, customer information, product sales information and product inventory information, the basic information and customer information are entered into the dimension table, and the product sales information and product sales information are entered into the fact table; The product basic information includes product ID, product name, product category, product brand and production date; The customer information includes customer ID, name, gender, address and contact information; The product sales information includes product sales, sales quantity and sales date; The inventory information includes the number of products in, the number of products out and the inventory balance.

[0010] Further, the core dimension dynamically loads high-frequency access attributes according to business requirements, specifically product basic information and product sales information, and the extended dimension dynamically loads low-frequency access attributes according to business requirements, specifically customer information and inventory information.

[0011] Further, the specific process of building a flexible dimension model is as follows: S101, for specific business analysis scenarios, metadata definition of the flexible dimension model, including the design of dimension primary key, attribute and level, the combination of fact table dimension primary key and measure design, and the dimension model metadata are obtained; S102, define the logical structure of the dimension model, define the dimension table structure on the semantic layer, according to the business analysis scene to cut or extend the dimension attribute, define the dimension hierarchy relationship and the summary granularity; S103, generate a feature importance ranking pedigree, and dynamically optimize the dimension space based on a Markov decision process, while recording the dimension evolution path in the historical modeling process.

[0012] Further, the specific process of optimizing the elastic dimension model is as follows: S201, divide the memory data into multiple levels, including an operation data storage layer, a data warehouse summary layer and an application data service layer; The operation data storage layer is used to store raw industry general data to ensure data integrity and history; The data warehouse summary layer is used for cross-domain data integration, providing aggregated and summarized theme data to meet business needs; The application data service layer is used to provide special data for specific business scenarios, perform pre-computation and cache optimization, and improve data query efficiency.

[0013] S202, define and classify the general data stored in the dimension table and the fact table, and the classification result includes business metadata, technical metadata and operation metadata. The business metadata is used to describe the business meaning, rules and the like of the data, such as business definition, term explanation, index name and calculation range, etc.

[0014] The technical metadata is specifically structured data for easy computer recognition, storage, transmission and exchange, such as physical database table name, column name, field length, field type, constraint information, data dependency relationship, etc.

[0015] The operation metadata is used to describe the operation attributes of the data, such as management department, management person in charge, data access method, access time, access restriction, etc.

[0016] S203, establish a metadata database, uniformly store and maintain the collected metadata, and automatically adjust the elastic dimension model structure to accommodate new dimensions based on metadata definition.

[0017] Further, the specific process of optimizing the fact table is as follows: S301, based on the fact table, partition the general data in the fact table according to the time node of the data source, use data variance to obtain the first vector of the general data; S302, for the first vector, a hash bucket strategy is used to construct an index for each partition, and the first vector is converted into multiple different hash tables; Specifically comprising: simultaneously multiple local sensitive hash functions, obtaining a multi-dimensional joint hash function, inputting the first vector in each partition into the multi-dimensional joint hash function, and obtaining the hash table; Wherein each of the hash table includes a plurality of hash buckets, and a (K, L) bucket construction strategy is used to construct hash buckets for the general data, in the (K, L) bucket construction strategy, K*L hash functions are used for each partition to construct an index.

[0018] Further, the performance indicators are periodically monitored, and when the performance indicators do not reach the preset indicator threshold, the elastic dimension model design and storage optimization strategy is iteratively adjusted to optimize the overall performance.

[0019] In summary, due to the adoption of the above technical solutions, the beneficial effects of the present application are: The elastic dimension modeling method for intensive storage and fast retrieval adopts a hierarchical dimension architecture, divides the dimensions into core dimensions and extended dimensions, can dynamically load access attributes according to business requirements, dynamically loads extended dimensions through metadata driving, reduces the redundancy of core dimension tables, improves storage efficiency, adopts a dimension merging strategy to merge them into composite dimensions, reduces the number of dimension tables, improves query performance, simultaneously performs efficient compression on stored data, reduces storage space occupation, and adopts time partitioning and hash bucketing strategies to optimize fact tables, accelerates time range queries, loads high-frequency access dimension tables into Redis and other in-memory databases for caching, accelerates query response, and uses a distributed query engine to perform parallel processing on large-scale data, improving query efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0020] Figure 1 The overall method flowchart of the present application is shown. DETAILED DESCRIPTION

[0021] The technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0022] Embodiment 1:

[0023] As shown in Figure 1 The elastic dimension modeling method for intensive storage and fast retrieval includes the following steps: Step one, obtain business requirements and industry general data, batch clean and model preprocess general data, and prestore them in the in-memory database, construct dimension table and fact table structure according to business requirements, and enter industry general data into corresponding dimension table and fact table according to its attributes; The general data includes product basic information, customer information, product sales information and product inventory information, the basic information and customer information are entered into the dimension table, and the product sales information and product sales information are entered into the fact table; The product basic information includes product ID, product name, product category, product brand and production date; The customer information includes customer ID, name, gender, address and contact information; The product sales information includes product sales, sales quantity and sales date; The inventory information includes the quantity of products into the warehouse, the quantity of products out of the warehouse and the inventory balance.

[0024] The core dimension dynamically loads high-frequency access attributes according to business requirements, specifically product basic information and product sales information, and the extended dimension dynamically loads low-frequency access attributes according to business requirements, specifically customer information and inventory information.

[0025] Step two, for specific business analysis scenarios, combine the dimension table and fact table structure to build a flexible dimension model, and plan and build the data in the in-memory database in layers, domains and markets; The specific process of building a flexible dimension model is as follows: S101, for specific business analysis scenarios, define the metadata of the flexible dimension model, including the design of dimension primary key, attribute and hierarchy, the combination of fact table dimension primary key and measure, and obtain the dimension model metadata; S102, define the logical structure of the dimension model, define the dimension table structure on the semantic layer, according to the business analysis scenario, cut or extend the dimension attribute, define the dimension hierarchy relationship and summary granularity; S103, generate feature importance ranking pedigree, and dynamically optimize the dimension space based on Markov decision process, and record the dimension evolution path in the historical modeling process.

[0026] Step three, optimize the flexible dimension model using the hierarchical dimension architecture composed of core dimension and extended dimension and the metadata-driven dynamic dimension expansion strategy; The specific process of optimizing the flexible dimension model is as follows: S201, divide the in-memory data into multiple levels, including operation data storage layer, data warehouse summary layer and application data service layer; The operation data storage layer is used to store the original industry general data to ensure data integrity and history; The data warehouse summary layer is used for cross-domain data integration, provides aggregated and summarized subject data, and meets business requirements. The application data service layer is used for providing special data for specific business scenarios, pre-computing, and cache optimization to improve data query efficiency.

[0027] S202, define and classify the general data stored in the dimension table and the fact table, and the classification result includes business metadata, technical metadata, and operation metadata. The business metadata is used to describe the business meaning and rules of the data, such as business definition, term explanation, index name, and calculation range.

[0028] The technical metadata is specifically structured data processed, which is convenient for computer recognition, storage, transmission, and exchange, such as physical database table name, column name, field length, field type, constraint information, and data dependency relationship.

[0029] The operation metadata is used to describe the operation attributes of the data, such as management department, management person in charge, data access method, access time, and access restriction.

[0030] S203, establish a metadata database, uniformly store and maintain the collected metadata, and automatically adjust the elastic dimension model structure based on the metadata definition to accommodate new dimensions.

[0031] Step four, select the corresponding storage format and compression algorithm to optimize the general data in the in-memory database; The storage format includes Parquet, ORC, and other columnar storage formats, and the compression algorithm includes Snappy, Zstd, and other efficient compression algorithms, which can efficiently compress the stored data and reduce the storage space occupation. At the same time, dictionary encoding is used for high-repetition value attributes in the dimension table to further compress the storage.

[0032] Step five, according to the data characteristics, use time partitioning and hash bucketing strategies to optimize the fact table; The specific process of optimizing the fact table is as follows: S301, based on the fact table, partition the general data in the fact table according to the time node of the data source, and use the data variable to obtain the first vector of the general data; S302, for the first vector, use the hash bucketing strategy to construct an index for each partition, and convert the first vector into multiple different hash tables; Specifically, it includes: combining multiple local sensitive hash functions to obtain a multi-dimensional joint hash function, inputting the first vector in each partition into the multi-dimensional joint hash function to obtain a hash table; Each hash table includes a plurality of hash buckets, a (K, L) bucket construction strategy is used to construct hash buckets for general data, in the (K, L) bucket construction strategy, K*L hash functions are used to construct indexes for each partition; The fact table is optimized by using a time partitioning and hash bucketing strategy, the fact table is partitioned according to days / months by time partitioning to speed up time range queries, and the high-cardinality dimensions are bucketed by hash bucketing to optimize the efficiency of Join operations.

[0033] Step six, configuring index bitmap indexes and inverted indexes for the elastic dimension model to speed up query performance; Step seven, loading the high-frequency access dimension table into an in-memory database for caching to improve query response speed; Step eight, precomputing and storing commonly used aggregate indicators as materialized views to speed up query response.

[0034] Further comprising periodically monitoring query performance indicators, and when the performance indicators do not reach preset indicator thresholds, iteratively adjusting the elastic dimension model design and storage optimization strategy to optimize overall performance. By using a predicate pushdown and parallel query strategy, the filtering conditions are pushed down to the storage layer to reduce data scanning volume, and distributed query engines such as Spark and Presto are used to perform parallel processing on large-scale data to improve query efficiency.

[0035] The application adopts a hierarchical dimension architecture, divides the dimensions into core dimensions and extended dimensions, dynamically loads access attributes according to business requirements, dynamically loads extended dimensions through metadata driving, reduces the redundancy of core dimension tables, improves storage efficiency, adopts a dimension merging strategy to merge them into composite dimensions, reduces the number of dimension tables, improves query performance, simultaneously performs efficient compression on stored data to reduce storage space occupation, uses a time partitioning and hash bucketing strategy to optimize the fact table, speeds up time range queries, loads the high-frequency access dimension table into a Redis in-memory database for caching to speed up query response, and uses distributed query engines such as Spark and Presto to perform parallel processing on large-scale data to improve query efficiency.

[0036] The above is only a preferred specific embodiment of the application, but the protection scope of the application is not limited thereto, any person skilled in the art can make equivalent replacements or changes to the technical solution and the inventive concept of the application within the technical range disclosed by the application, which should be covered within the protection scope of the application.

Claims

1. A flexible dimensional modeling method for intensive storage and fast retrieval, characterized by: The following steps are involved: Step 1: Obtain business requirements and common industry data, perform batch cleaning and model preprocessing on the common data, and then store it in an in-memory database. Build dimension tables and fact table structures based on business requirements, and enter common industry data into corresponding dimension tables and fact tables according to their attributes. Step 2: Based on specific business analysis scenarios, we build a flexible dimensional model by combining the dimension table and fact table structure, and plan and build the data in the in-memory database by layer, domain, and market. Step 3: Optimize the elastic dimension model using a hierarchical dimension architecture consisting of core dimensions and extended dimensions and a metadata-driven dynamic dimension expansion strategy. Step 4: Select the corresponding storage format and compression algorithm to optimize the general data in the memory database; Step 5: Optimize the fact table using time partitioning and hash bucketing strategies based on data characteristics; Step 6: Configure bitmap indexes and inverted indexes for the elastic dimension model to accelerate query performance. Step 7: Load frequently accessed dimension tables into the in-memory database for caching to improve query response speed; Step 8: Pre-calculate commonly used aggregate indicators and store them as materialized views to speed up query responses.

2. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: The general data includes basic product information, customer information, product sales information and product inventory information. The basic information and customer information are entered into the dimension table, and the product sales information and product inventory information are entered into the fact table. The basic product information includes product ID, product name, product category, product brand and production date; The customer information includes customer ID, name, gender, address and contact information; The product sales information includes the sales amount, sales quantity and sales date of the product; The inventory information includes the product's incoming quantity, outgoing quantity, and inventory balance.

3. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: The core dimension dynamically loads high-frequency access attributes according to business needs, specifically product basic information and product sales information, and the extended dimension dynamically loads low-frequency access attributes according to business needs, specifically customer information and inventory information.

4. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: The specific process of building an elastic dimensional model is as follows: S101. Define metadata for the elastic dimensional model based on specific business analysis scenarios, including the design of dimension primary keys, attributes, and hierarchies, and the design of fact table combined dimension primary keys and measures, to obtain dimensional model metadata. S102: Define the logical structure of the dimensional model, define the dimension table structure at the semantic layer, tailor or expand dimension attributes based on business analysis scenarios, and define the dimension hierarchical relationship and aggregation granularity. S103. Generate a feature importance ranking pedigree and dynamically optimize the dimensional space based on the Markov decision process, while recording the dimensional evolution path during the historical modeling process.

5. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: The specific process of optimizing the elastic dimensional model is as follows: S201, dividing the memory data into multiple layers, including an operational data storage layer, a data warehouse aggregation layer, and an application data service layer; The operational data storage layer is used to store original common industry data to ensure data integrity and history; The data warehouse summary layer is used to integrate cross-domain data and provide aggregated and summarized subject data to meet business needs; The application data service layer is used to provide dedicated data for specific business scenarios, perform pre-calculation and cache optimization, and improve data query efficiency.

6. S202: Define and classify the common data stored in the dimension tables and fact tables. The classification results include business metadata, technical metadata, and operational metadata. S203: Establish a metadata database to uniformly store and maintain the collected metadata, and automatically adjust the elastic dimension model structure to accommodate new dimensions based on the metadata definition.

7. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: The specific process of optimizing the fact table is as follows: S301: Based on a fact table, partition the common data in the fact table according to the time node of the data source, and use data variables to obtain a first vector of the common data; S302: For the first vector, use a hash bucketing strategy to build an index for each partition, and convert the first vector into multiple different hash tables; Specifically comprising: combining multiple locality-sensitive hash functions to obtain a multidimensional joint hash function, inputting the first vector in each partition into the multidimensional joint hash function to obtain the hash table; Each of the hash tables includes multiple hash buckets, and a (K, L) bucketing strategy is used to construct hash buckets for the common data. In the (K, L) bucketing strategy, K*L hash functions are used to construct an index for each partition.

8. The elastic dimensional modeling method for intensive storage and rapid retrieval according to claim 1, characterized in that: It also includes periodically monitoring query performance indicators. When the performance indicators do not reach the preset thresholds, iterative adjustments are made to the elastic dimension model design and storage optimization strategy to optimize overall performance.

Images