Aggregate UDF Multi-Regression Processing

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional regression analysis in relational databases is inefficient due to the need to pack large amounts of data into a single row, leading to server and user load issues, and the overhead of varbyte packing complicates data input and processing.

Innovation Solution

An aggregate User Defined Function (UDF) is introduced, which initializes an intermediate storage structure on each node, distributes data by grouped columns, merges year-week data across nodes for a specific SKU, and processes regression analysis independently on each node, eliminating the need for varbyte packing and oversized row formation.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If traditional tabular UDF is used to pack all data into a single row for regression analysis, then the regression analysis can be performed, but the server load increases and processing becomes inefficient

Engineering Contradiction:

Improveregression analysis processing speedVSAvoidserver load

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent divides the regression analysis process into multiple segments executed in parallel across different threads or processors. Each segment processes a portion of the input data independently, avoiding the need to load all data into a single row. This segmentation reduces server load while maintaining or improving processing speed through parallel execution.

Inventive Principle:

Principle #1Segmentation

2Ease of operation

If varbyte packing is used to input data into tabular UDF, then data can be processed, but the overhead of packing complicates data input and reduces efficiency

Engineering Contradiction:

Improvedata input simplicityVSAvoidprocessing efficiency

Core Design Contradiction:

Ease of operationVSProductivity

Solution Approach 1:

The patent extracts the data packing overhead from the regression analysis process by using a different data input mechanism that does not require varbyte packing. The system directly processes data in a structured format without the intermediate packing step, simplifying data input while improving processing efficiency by eliminating the packing overhead.

Inventive Principle:

Principle #2Taking out (Extraction)

3Reliability

If all regression data is packed into a single row, then traditional tabular UDF can process it, but I/O overhead increases significantly

Engineering Contradiction:

Improveregression analysis accuracyVSAvoidI/O overhead

Core Design Contradiction:

ReliabilityVSLoss of energy

Solution Approach 1:

The patent transitions from a single-row data structure to a multi-row or array-based data structure, effectively adding a dimension to the data organization. This dimensional change allows the system to process regression data without packing it into a single row, thereby reducing I/O overhead while maintaining the integrity and accuracy of the regression analysis through proper data structuring.

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

Data Source

PatentUS7856382B2Aggregate user defined function (UDF) processing for multi-regression

Publication Date: 2010.12.21 TERADATA CORP

AI summary

An aggregate User Defined Function (UDF) processing used for multi-regression is provided. The aggregate UDF initializes storage space for multiple nodes of a database environment. Data is then extracted from a relational database and populated according to groupings on each of the nodes. Multiple rows or records are then processed to create a merge and multi-regression processed.