Dynamic Calculated Dimensions in Database Query Processing

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional database systems require manual or periodic updates of calculated dimensions, which can lead to inefficiencies and inaccuracies due to the need to store and manage these dimensions within the database, limiting flexibility and data quality.

Innovation Solution

A system that dynamically generates calculated dimensions on demand based on user-defined dimension definitions, combining these dimensions with database data to provide aggregated results, and then destroys the dimensions after use, avoiding the need for storage and allowing for flexible modifications without altering the database structure.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of information

If calculated dimensions are manually or periodically updated and stored within the database, then data availability is improved, but storage requirements increase and data quality deteriorates due to staleness

Engineering Contradiction:

Improvedata availabilityVSAvoidstorage requirements

Core Design Contradiction:

Loss of informationVSQuantity of substance

Solution Approach 1:

The system pre-defines dimension specifications (field names, operations, mappings) in advance, but only performs the actual calculation when queried. This allows the system to have dimension definitions ready without storing calculated results, eliminating storage requirements while maintaining data availability.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

Instead of storing actual calculated dimension values in the database, the system creates virtual copies through dynamic calculation based on stored dimension definitions. These virtual dimensions behave like real stored dimensions but consume no additional storage space.

Inventive Principle:

Principle #26Copying

2Speed

If calculated dimensions are stored within the database, then query performance is improved, but flexibility deteriorates due to rigid database structure

Engineering Contradiction:

Improvequery performanceVSAvoidflexibility

Core Design Contradiction:

SpeedVSAdaptability or versatility

Solution Approach 1:

The system transitions from static stored dimensions to dynamic calculated dimensions. Dimension definitions are stored, but actual dimension values are generated on-demand based on current data, allowing flexible modifications without database structure changes while maintaining query performance.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system introduces dimension definitions as an intermediary layer between the raw data and queries. These definitions act as templates that dynamically generate dimension values during queries, providing flexibility while maintaining performance.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Measurement precision

If calculated dimensions are manually updated, then data accuracy is improved, but productivity deteriorates due to manual intervention

Engineering Contradiction:

Improvedata accuracyVSAvoidupdate efficiency

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The system makes calculated dimensions self-updating by automatically recalculating them based on current underlying data whenever queried. This eliminates manual update intervention while ensuring data accuracy reflects the latest source values.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

Instead of periodic manual updates, the system continuously maintains dimension accuracy by calculating dimensions on-demand from current data. This ensures data accuracy is always up-to-date without interrupting productivity for manual updates.

Inventive Principle:

Principle #20Continuity of useful action

4Quantity of substance

If calculated dimensions are periodically updated, then storage requirements are reduced, but data quality deteriorates due to staleness

Engineering Contradiction:

Improvestorage requirementsVSAvoiddata quality

Core Design Contradiction:

Quantity of substanceVSMeasurement precision

Solution Approach 1:

The system maintains continuous dimension accuracy by calculating dimensions on-demand from current source data during queries. This eliminates data staleness while avoiding storage requirements, as dimensions are never stored but always freshly calculated.

Inventive Principle:

Principle #20Continuity of useful action

Data Source

PatentUS10997213B2Database queries using dynamically calculated dimensions

Publication Date: 2021.05.04 SAP SE

AI summary

Some embodiments provide a non-transitory machine-readable medium that stores a program. The program receives from a client device a request for data stored in a database categorized based on a calculated dimension. The program further generates the calculated dimension based on a subset of the data stored in the database. The program also generates an intermediary data structure by combining the calculated dimension with the data stored in the database. The program further generates results for the request by aggregating the data in the intermediary data structure based on the calculated dimension. The program also provides the results for the request to the client device.