OPTIMIZED BULK COMPUTATIONS IN DATA WAREHOUSE ENVIRONMENTS

• US 20100023477A1
• Filed: 07/23/2008
• Published: 01/28/2010
• Est. Priority Date: 07/23/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Thiebaut-George, Eric
• Granted Patent: US 8,195,645 B2
• Time in Patent Office: Days
• US Class Current: N/A
• 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;

if needed to perform the 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.

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 accessed data 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 interpolating the results of the computations if needed to derive missing result items based on the common interval.

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

7. 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.

8. The method of claim 7, 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.

9. A system, comprising:

table accessor logic configured 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, and configured to create an array of each column of data that is extracted;

column accessor logic in communication with the table accessor logic and configured to access each array as a standardized programmatic interface to manipulate the data;

column normalizer logic in communication with the column accessor logic and configured to, if needed to perform the computations, normalize the accessed data to a common interval or interpolate the accessed data to derive missing items based on the common interval; and

computation logic in communication with the column normalizer logic and configured to perform the computations on the accessed data and output results of the computations to a storage medium or an output device.

10. The system of claim 9, wherein the computational interval is a primary key interval or a time span interval.

11. The system of claim 9, wherein the column normalizer is configured to normalize 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.

12. The system of claim 9, wherein the column normalizer is configured to interpolate the accessed data by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation.

13. The system of claim 9, wherein the column normalizer is further configured to interpolate the results of the computations if needed to derive missing results items based on the common interval.

14. The system of claim 13, wherein the column normalizer is configured to interpolate the results of the computations by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation.

15. The system of claim 9, further comprising logic configured 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 table accessor logic is configured to extract the data by extracting the data groups.

16. The system of claim 15, wherein the logic is configured to group 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.

17. A computer program product, comprising a 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;

if needed to perform the computations, normalize the accessed data to a common interval or interpolate the accessed data to derive missing items based on the common interval;

perform the computations on the accessed data; and

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

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

19. The computer program product of claim 17, 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.

20. The computer program product of claim 17, wherein the computer interpolates the accessed data by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation.

21. The computer program product of claim 17, wherein the computer readable program, when executed on the computer, further causes the computer to interpolate the results of the computations if needed to derive missing results items based on the common interval.

22. The computer program product of claim 21, wherein the computer interpolates the results of the computations by performing at least one of linear interpolation, polynomial interpolation, logarithmic interpolation, exponential interpolation, or geometric interpolation.

23. The computer program product of claim 17, 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.

24. The computer program product of claim 23, 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.