Temporal Data Warehouse Loading via Partitioned Pre-Load Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current methods for loading data into temporal data warehouses are inefficient, particularly for high-volume data, as they often rely on sequential processing, require extensive preprocessing, and are costly due to storage needs, leading to long run-times and network stress, and are not adaptable to all data types or interfaces.
Innovation Solution
A system and method that divides incoming data into partitions, imports them into pre-load tables, and applies these tables to a temporal data warehouse using a metadata-driven approach, optimizing performance by using ANSI SQL and leveraging database management system power for scalable, non-intrusive near-real-time loading.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If sequential processing methods are used to load data into temporal data warehouse, then data loading can be performed with simple design, but loading efficiency is low and run-time is long
Solution Approach 1:
The incoming data set is divided into multiple partitions, allowing parallel processing of different data segments. This segmentation enables simultaneous loading operations across multiple partitions, dramatically improving data loading efficiency and reducing overall run-time compared to sequential processing.
Solution Approach 2:
The system creates pre-load tables before the actual data loading process. These pre-load tables are prepared in advance with the necessary structure and indexes, allowing the incoming data partitions to be quickly imported and applied without performing extensive preprocessing during the loading phase itself.
2Reliability
If extensive preprocessing is performed to detect changes and ensure unique valid time periods, then data quality and validity are improved, but processing complexity and time consumption increase
Solution Approach 1:
The system performs preliminary preparation by creating pre-load tables with the appropriate schema and structure before data loading. This preliminary action ensures that data validity and unique time periods are maintained without requiring complex preprocessing logic during the actual loading operation.
Solution Approach 2:
The pre-load table serves as an intermediary structure between the incoming data and the final temporal data warehouse. It handles the complexity of change detection and valid time period validation in a separate, manageable layer, simplifying the main loading process while ensuring data quality.
3Productivity
If external application servers are used to process and load data, then data loading capability is achieved, but network traffic increases and database stress intensifies
Solution Approach 1:
The system extracts the data loading functionality from external application servers and performs it directly within the database environment using pre-load tables. This eliminates the need for extensive network traffic between external servers and the database, reducing network energy consumption and database stress while maintaining full data loading capability.
4Adaptability or versatility
If data is loaded row-at-a-time using ETL tools, then data transformation flexibility is achieved, but processing efficiency decreases significantly
Solution Approach 1:
The system merges multiple row-level data loading operations into bulk partition-based loading. By combining data from multiple partitions into pre-load tables and applying them together, the system achieves both the flexibility of individual row transformation and the efficiency of bulk processing, eliminating the inefficiency of sequential row-at-a-time loading.
5Productivity
If proprietary database tools are used for data loading, then platform-specific optimization is achieved, but portability to other platforms is lost
Solution Approach 1:
The system uses universal SQL standards and database-agnostic pre-load table structures that can operate across different database platforms. This universal approach maintains platform portability while still achieving efficient data loading, as the core mechanism of partition-based loading with pre-load tables is applicable to any relational database system without requiring proprietary tools.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system disclosed includes a temporal data warehouse and a platform independent temporal data warehouse load application operable to run on the system. The load application uses timestamp data from incoming data in conjunction with a relational algebra of set operators to identify and sequence net changes between the incoming data and data previously stored within the data warehouse. The load application loads the identified and sequenced net changes into the data warehouse with relatively little intrusion into normal operation of the data warehouse. Optimizations, including but not limited to, distinct partitioning of the workload into parallel streams are selectable via metadata.