Dynamic Query Stored Procedure for Data Aggregation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database systems face inefficiencies in aggregating and transforming data from diverse management systems used by client entities, as they often require numerous sequential stored procedures, leading to increased processing time and resource utilization.

Innovation Solution

A database system that utilizes a single stored procedure to identify schemas of various data sources, determine client-specific criteria for dynamic queries, and execute these queries in parallel to retrieve and transform raw data into a unified format compatible with a content management system.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Manufacturing precision

If numerous sequential stored procedures are used to aggregate and transform data from diverse management systems, then data compatibility and transformation accuracy are improved, but processing time and resource consumption increase

Engineering Contradiction:

Improvedata transformation accuracyVSAvoidprocessing time

Core Design Contradiction:

Manufacturing precisionVSLoss of time

Solution Approach 1:

The patent combines multiple sequential stored procedures into a single stored procedure that handles data aggregation and transformation for diverse management systems. This single procedure integrates schema identification, client-specific criteria determination, dynamic query generation, and parallel query execution, thereby reducing processing time while maintaining transformation accuracy through unified control logic

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent implements dynamic query generation that adapts to different client entities and management systems. The stored procedure dynamically determines schemas, identifies client-specific criteria, and generates appropriate queries based on the target data warehouse schema and source system characteristics, enabling accurate transformation without fixed sequential steps

Inventive Principle:

Principle #15Dynamics

2Adaptability or versatility

If numerous sequential stored procedures are used to aggregate and transform data from diverse management systems, then data compatibility with content management systems is improved, but resource utilization increases

Engineering Contradiction:

Improvedata compatibilityVSAvoidresource utilization

Core Design Contradiction:

Adaptability or versatilityVSUse of energy by moving object

Solution Approach 1:

The patent creates a universal stored procedure that can handle multiple types of management systems and data formats through a single implementation. The procedure universally identifies schemas, determines client-specific criteria, and generates appropriate transformation queries for diverse sources, reducing the need for multiple specialized procedures and thereby lowering resource utilization

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The patent segments the data transformation process into distinct logical components within a single stored procedure: schema identification, client criteria determination, dynamic query generation, and parallel execution. This segmentation allows efficient resource management while maintaining compatibility across different management systems through modular processing steps

Inventive Principle:

Principle #1Segmentation

3Productivity

If parallel queries are executed to retrieve and transform raw data, then processing efficiency is improved, but system complexity increases

Engineering Contradiction:

Improvedata processing efficiencyVSAvoidsystem complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent introduces an intermediary layer within the stored procedure that manages parallel query execution. This intermediary component handles schema identification, generates client-specific dynamic queries, and coordinates parallel processing without requiring complex external orchestration, thereby improving efficiency while containing system complexity within a single manageable procedure

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS12222933B2System and method for management system data aggregation and transformation using client-specific criteria

Publication Date: 2025.02.11 OPTX SOLUTIONS LLC

AI summary

A method may include receiving, at a first time, an instruction to execute a stored procedure at a database system, retrieving a schema of a structure of a database used by a management system of a client entity, determining an identity of a management system and a type of data to retrieve for the client entity. The method may also include, responsive to determining the identity and the type of data to retrieve, determining a criteria to use in a dynamic query based on the identity and the type of data to retrieve. The method may also include, responsive to determining the criteria, generating, in the stored procedure, the dynamic query comprising the criteria, and executing the stored procedure to retrieve raw data from the database used by the management system and transform the raw data to transformed data using the dynamic query.