Optimized bulk computations in data warehouse environments

• US 8,195,645 B2
• Filed: 07/23/2008
• Issued: 06/05/2012
• Est. Priority Date: 07/23/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Thiebaut-George, Eric
• Primary Examiner(s): ALAM, SHAHID AL
• Application Number:
  US12/178,247
• Publication Number:
  US 20100023477A1
• Time in Patent Office: 1,413 Days
• Field of Search: 707/715, 707/716, 707/638, 707/639, 707/640, 707/660, 707/686, 706/45, 706/46, 706/50, 706/61
• US Class Current: 707/715
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

A method including extracting data spanning a computational interval from a plurality of database tables in an optimized manner, creating an array of each column of data that is extracted, accessing each array via a standardized programmatic interface to manipulate the data, if needed to perform computations, normalizing the accessed data to a common interval or interpolating the accessed data to derive missing items based on the common interval, performing the computations on the accessed data, and outputting results of the computations to a storage medium or an output device. A corresponding system and computer program product.

Claims

1. A method, comprising:

extracting data spanning a computational interval from a plurality of database tables in an optimized manner comprising one of a table scan, a partition scan, a covering index scan, or a structured query language scan, wherein the data is extracted to perform computations to respond to computation requests;

creating an array of each column of data that is extracted;

accessing each array via a standardized programmatic interface to manipulate the data;

normalizing the accessed data to a common interval;

interpolating the accessed data to derive missing items based on the common interval by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation;

and

outputting results of the computations to a storage medium or an output device.

2. The method of claim 1, wherein extracting data spanning a computational interval comprises extracting data spanning a primary key interval or a time span interval.

3. The method of claim 1, wherein normalizing the accessed data comprises performing at least one of a function of summing, subtracting, multiplying, dividing, computing a minimum, computing a maximum, or copying on the accessed data to rebase the data to a common primary key interval or a common time span interval.

4. The method of claim 1, wherein interpolating the results of the computations comprises performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation.

5. The method of claim 1, further comprising:

analyzing the computations to be performed on the data to respond to the computation requests;

sorting the computations into computational clusters based on their inter-computation dependencies;

identifying data groups to be extracted for each computational cluster, wherein the data groups comprise a data subset of a combination of the database tables over the computational interval; and

grouping the computations into computational groups within each computational cluster to minimize needed extractions of the data groups to perform the computations;

wherein extracting data spanning a computational interval from a plurality of database tables comprises extracting the data groups.

6. The method of claim 5, wherein grouping the computations into computational groups comprises grouping the computations to limit the needed extractions of the data groups to a maximum number of extractions to minimize memory usage for each computational group.

7. A computer program product, comprising a non-transitory computer usable medium having a computer readable program, wherein the computer readable program, when executed on a computer, causes the computer to:

extract data spanning a computational interval from a plurality of database tables in an optimized manner comprising one of a table scan, a partition scan, a covering index scan, or a structured query language scan, wherein the data is extracted to perform computations to respond to computation requests;

create an array of each column of data that is extracted;

access each array via a standardized programmatic interface to manipulate the data;

normalize the accessed data to a common interval;

interpolate the accessed data to derive missing items based on the common interval by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation;

and

store results of the computations to a storage medium.

8. The computer program product of claim 7, wherein the computer extracts data spanning a computational interval by extracting data spanning a primary key interval or a time span interval.

9. The computer program product of claim 7, wherein the computer normalizes the accessed data by performing at least one of a function of summing, subtracting, multiplying, dividing, computing a minimum, computing a maximum, or copying on the accessed data to rebase the data to a common primary key interval or a common time span interval.

10. The computer program product of claim 7, wherein the computer readable program, when executed on the computer, further causes the computer to:

analyze the computations to be performed on the data to respond to the computation requests;

sort the computations into computational clusters based on their inter-computation dependencies;

identify data groups to be extracted for each computational cluster, wherein the data groups comprise a data subset of a combination of the database tables over the computational interval; and

group the computations into computational groups within each computational cluster to minimize needed extractions of the data groups to perform the computations;

wherein the computer extracts the data by extracting the data groups.

11. The computer program product of claim 10, wherein the computer groups the computations into computational groups by grouping the computations to limit the needed extractions of the data groups to a maximum number of extractions to minimize memory usage for each computational group.