Delta Handling in Star and Snowflake Schemas
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data warehouse systems, such as SAP's Business Information Warehouse (BW), face challenges in efficiently handling changes to multidimensional data stored in star and snowflake schemas, particularly in determining and writing deltas without indicating changes between the received data and the data already stored, which can lead to computational burdens and performance issues.
Innovation Solution
A delta handler is implemented to receive absolute data, create identifiers, write it into a temporary table, perform a join with the fact table to determine deltas, and post these deltas to the repository, thereby efficiently managing changes in data structures like star and snowflake schemas.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a data warehouse system stores complete absolute data in fact tables for star and snowflake schemas, then data consistency is maintained, but computational load increases significantly when determining changes
Solution Approach 1:
The patent segments the data storage approach by separating absolute data storage from change detection. Instead of processing complete absolute data to determine changes, the system segments the process into: (1) storing only delta information in the fact table, and (2) using a separate temporary table for change detection. This segmentation reduces the volume of data processed for change determination while maintaining data consistency through the join operation between the temporary table and fact table.
2Measurement precision
If the system performs complex operations to determine deltas between received data and stored data, then data accuracy is improved, but processing time increases
Solution Approach 1:
The patent introduces a temporary table as an intermediary structure between the source data and the fact table. This temporary table serves as a mediator that holds received absolute data with generated identifiers before the join operation. The intermediary temporary table simplifies the delta determination process by providing a structured intermediate state, enabling accurate change detection through the join operation while optimizing processing time through efficient query execution.
3Measurement precision
If identifiers are generated and temporary tables are used for delta determination, then delta accuracy is improved, but device complexity increases
Solution Approach 1:
The system implements self-service functionality by automatically generating identifiers for received absolute data before storing it in the temporary table. This automatic identifier generation eliminates the need for manual key management and simplifies the overall system architecture. The self-service approach to identifier creation ensures unique mapping of data records while reducing the complexity of external key management mechanisms.
4Productivity
If all received data is written to the fact table without delta determination, then productivity is improved, but data management efficiency deteriorates
Solution Approach 1:
The patent applies preliminary action by performing delta determination before writing data to the fact table. The system first loads data into a temporary table, determines the deltas through a join operation with the fact table, and then writes only the necessary changes. This preliminary delta determination prevents unnecessary data writes, optimizing both productivity by avoiding redundant operations and data management efficiency by maintaining an up-to-date delta indicator in the fact table.
Data Source
AI summary
In one aspect there is provided a method for handling deltas being posted to a repository, database, or other Business Information Warehouse (BW). The method includes receiving, at a delta handler, absolute data without an indication of a change between the received absolute data and data posted to a repository including a fact table. Identifiers are created for the received absolute data. The absolute data including the created identifier is written into a temporary table. One or more deltas are determined by performing a join of the temporary table and the fact table of the repository. The deltas correspond to the indication of the change between the received absolute data and the data posted to the repository. The delta handler provides the one or more deltas to be posted to the fact table of the repository. Related systems, apparatus, methods, and/or articles are also described.