Column-Oriented Database Data Model for Measurement Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for storing and analyzing large sets of measurement data are inefficient, as they require loading entire files into memory for comparison, which is cumbersome and often impossible due to CPU and memory limitations, especially when dealing with millions of data points.
Innovation Solution
A system and method using horizontally scalable column-oriented databases to store and analyze measurement data, with a data model that includes a metadata table and a measurement data table, where data is stored in a columnar format, enabling efficient storage and quick retrieval of data points through a query mechanism that links metadata to corresponding data sets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If entire measurement data files are loaded into memory for comparison and analysis, then complete data access is achieved, but memory usage and processing time increase significantly
Solution Approach 1:
The patent extracts and separates metadata from the main measurement data, storing metadata in a database while keeping the actual measurement data in compressed form or on disk. This allows the system to work with only the essential identifying information in memory, dramatically reducing memory requirements while maintaining the ability to access and compare complete measurement datasets through their metadata identifiers.
Solution Approach 2:
The patent segments the measurement data system into two distinct components: a metadata layer stored in a database that enables efficient querying and comparison, and the actual measurement data stored in compressed format or on disk. This segmentation allows independent optimization of each layer, with the metadata layer providing fast access without requiring the full measurement data to be loaded into memory.
2Productivity
If multiple large measurement data files are loaded simultaneously for comparison, then comprehensive analysis is enabled, but CPU and memory limitations prevent processing
Solution Approach 1:
The patent extracts the essential identifying and descriptive information (metadata) from each measurement data file and stores it in a database. This extracted metadata serves as a surrogate that enables comparison and analysis operations without requiring the actual large measurement data files to be loaded into memory, thus bypassing CPU and memory limitations while maintaining comprehensive analysis capability.
Solution Approach 2:
The patent introduces metadata as an intermediary layer between the user/query system and the actual measurement data. The metadata acts as a mediator that enables filtering, searching, and comparison operations without direct access to the full measurement data, thereby reducing processing resource requirements while maintaining the ability to perform comprehensive analysis on demand.
3Ease of operation
If row-oriented database structures are used to store measurement data metadata, then traditional data access methods are maintained, but query efficiency and retrieval speed decrease
Solution Approach 1:
The patent changes the fundamental parameter of data organization from row-oriented to column-oriented storage. In column-oriented storage, data is organized by columns rather than rows, which dramatically improves query efficiency for analytical operations. This parameter change enables the database to retrieve only the specific columns needed for a query rather than entire rows, thereby increasing data retrieval speed while maintaining ease of operation through standard SQL interfaces.
Data Source
AI summary
A non-transitory computer-readable memory medium may store a first table comprising rows, wherein each row comprises a first data set identification (ID) field which stores a measurement data set identifier value identifying a measurement data set, and one or more fields for storing measurement data metadata associated with the identified data set. The medium may also store a second table comprising rows, wherein each row comprises a second data set identification (ID) field which stores a measurement data set identifier value present in the first data set ID field. The second table may also store a datapoints field for storing individual data set datapoints and a data set index field corresponding to an ordering of the individual data set datapoints. At least a portion of each of the fields of both the first and second tables may be stored in a columnar format in contiguous memory.