Dense Data Structures for Dimensional Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in processing and analyzing multidimensional data due to the high cost and complexity of managing large fact tables with numerous foreign key columns, especially when executing star queries, which often require scanning vast portions of the fact table to find relevant data.
Innovation Solution
The creation of dense data structures that compress and aggregate dimensional data by identifying actual value combinations in dimension tables, allowing for efficient processing of fact tables by using dense set identifiers that directly reference relevant data, reducing the need for extensive scanning and resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional star schema with fact tables and dimension tables is used, then data can be stored and queried using standard SQL, but query processing requires scanning large portions of fact tables leading to high computational cost and slow performance
Solution Approach 1:
The patent pre-computes and stores aggregate data in compressed format before queries are executed. Dense data structures are built in advance by aggregating dimensional data and storing it in a compressed state, so that query processing can directly utilize these pre-computed results without scanning the entire fact table, thereby reducing computational resources and improving query speed
Solution Approach 2:
The patent extracts and separates dimensional data from the fact table structure. By creating dense data structures that contain only the relevant dimensional data and aggregates, the system removes the need to scan irrelevant portions of the fact table during query processing, reducing computational overhead while maintaining query capability
2Reliability
If all possible value combinations are considered in dimension tables, then complete data coverage is achieved, but data storage and processing complexity increases significantly
Solution Approach 1:
The patent applies different levels of detail to different parts of the data structure. Dense data structures store highly aggregated and compressed data for common dimensional values, while maintaining the ability to access more detailed information when needed. This local differentiation in data representation reduces overall complexity while preserving data completeness
Solution Approach 2:
The patent segments the dimensional data into dense data structures that group related dimensional values together. By organizing data into segmented dense containers rather than storing all possible combinations, the system reduces data structure complexity while maintaining the ability to represent complete dimensional information through hierarchical aggregation
3Productivity
If dense data structures are created to compress dimensional data, then query processing efficiency improves, but additional storage space is required for the dense data structures
Solution Approach 1:
The patent creates composite data structures that combine multiple types of information (dimensional data, aggregates, and compressed representations) into a single dense data structure. This composite approach allows the system to store multiple layers of information in a space-efficient manner, improving query processing efficiency while minimizing the additional storage space required compared to storing separate structures for each data type
Data Source
AI summary
Methods, computer systems, and stored instructions are described herein for densely grouping dimensional data and/or aggregating data using a data structure, such as one that is constructed based on dimensional data. When smaller tables are joined with a larger table, a server may analyze the smaller tables first to determine actual value combinations that occur in the smaller tables, and these actual value combinations are used to more efficiently process the larger table. A dense data structure may be generated by processing dimensional data before processing data from fact table. The dense data structure may be generated by compressing ranges of values that are possible in dimensions into a range of values that actually occurs in the dimensions. The compressed range of values may be represented by dense set identifiers rather than the actual compressed range of values.