TRANSFORMING TIMESERIES AND NON-RELATIONAL DATA TO RELATIONAL FOR COMPLEX AND ANALYTICAL QUERY PROCESSING

• US 20170075968A1
• Filed: 11/29/2016
• Published: 03/16/2017
• Est. Priority Date: 01/13/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: BROWN, Kevin, HO, Frederick C., Murthy, Raghupathi K., OSTNER, Karl
• Granted Patent: US 10,073,897 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A system for transforming time series data into data that is accessible by a data warehouse identifies a data table comprising the time series data. The system creates a virtual view of the data table where the time series data is represented as at least one standard relational table in the virtual view, where the virtual view is presented as a virtual table. The system partitions the virtual table into a plurality of virtual partitions according to a time interval. The virtual table is partitioned across a data time range, where the data time range comprises at least one time interval, and where each of the plurality of virtual partitions has a respective partition time range that spans the time interval. The virtual partitions are created to optimize loading of the data into the data warehouse by incrementally refreshing the data according to the respective partition time range.

Claims

1. A method for transforming time series data into data that is accessible by a data warehouse, implemented by a computing processor, the method comprising:

identifying, by the computing processor, a data table comprising the time series data;

creating, by the computing processor, a virtual view of the data table wherein the time series data is represented as at least one standard relational table in the virtual view, the virtual view presented as a virtual table; and

partitioning, by the computing processor, the virtual table into a plurality of virtual partitions according to a time interval, the virtual table partitioned across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the time interval, the virtual partitions created to optimize loading of the data into the data warehouse by incrementally refreshing the data according to the respective partition time range.

2. The method of claim 1 comprising:

providing, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

3. The method of claim 1 wherein partitioning, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval comprises:

incrementally refreshing the data by extracting new time series data from the data table, according to the time interval;

creating a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

adding the new virtual partition to the plurality of virtual partitions.

4. The method of claim 1 wherein partitioning, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval comprises:

incrementally refreshing the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has the respective partition time range that is within the data time range.

5. The method of claim 4 comprising:

detecting at least one virtual partition, within the subset of the plurality of virtual partitions, having the respective partition time range outside of the data time range; and

removing the at least one virtual partition from the subset of the plurality of virtual partitions, wherein the at least one virtual partition is no longer represented within the chosen view.

6. The method of claim 4 comprising:

detecting at least one virtual partition having the respective partition time range within the data time range, wherein the at least one virtual partition is not in the subset of the plurality of virtual partitions; and

adding the at least one virtual partition to the subset of the plurality of virtual partitions, wherein the at least one virtual partition is now represented within the chosen view.

7. The method of claim 1 wherein partitioning, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval comprises:

creating a partitioning calendar;

associating the time interval to the partitioning calendar;

assigning the partitioning calendar to the virtual table; and

partitioning the virtual table, using the partitioning calendar, wherein each partition spans the time interval.

8. The method of claim 1 wherein partitioning, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval comprises:

defining a time window selected to optimize an amount of relevant data that is loaded into the data warehouse;

associating the time interval to the time window; and

applying the time window to the virtual table to partition the virtual table into the plurality of virtual partitions according to the time interval.

9. A computer program product for transforming time series data into data that is accessible by a data warehouse, the computer program product comprising:

a computer readable storage medium having computer readable program code embodied therewith, the program code executable by a processor to;

identify, by the computing processor, a data table comprising the time series data;

create, by the computing processor, a virtual view of the data table wherein the time series data is represented as at least one standard relational table in the virtual view, the virtual view presented as a virtual table; and

partition, by the computing processor, the virtual table into a plurality of virtual partitions according to a time interval, the virtual table partitioned across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the time interval, the virtual partitions created to optimize loading of the data into the data warehouse by incrementally refreshing the data according to the respective partition time range.

10. The computer program product of claim 9 wherein the computer readable program code is further configured to:

provide, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

11. The computer program product of claim 9 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by extracting new time series data from the data table, according to the time interval;

create a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

add the new virtual partition to the plurality of virtual partitions.

12. The computer program product of claim 9 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has the respective partition time range that is within the data time range.

13. The computer program product of claim 12 wherein the computer readable program code is further configured to:

detect at least one virtual partition, within the subset of the plurality of virtual partitions, having the respective partition time range outside of the data time range; and

remove the at least one virtual partition from the subset of the plurality of virtual partitions, wherein the at least one virtual partition is no longer represented within the chosen view.

14. The computer program product of claim 12 wherein the computer readable program code is further configured to:

detect at least one virtual partition having the respective partition time range within the data time range, wherein the at least one virtual partition is not in the subset of the plurality of virtual partitions; and

add the at least one virtual partition to the subset of the plurality of virtual partitions, wherein the at least one virtual partition is now represented within the chosen view.

15. The computer program product of claim 9 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

create a partitioning calendar;

associate the time interval to the partitioning calendar;

assign the partitioning calendar to the virtual table; and

partition the virtual table, using the partitioning calendar, wherein each partition spans the time interval.

16. The computer program product of claim 9 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

define a time window selected to optimize an amount of relevant data that is loaded into the data warehouse;

associate the time interval to the time window; and

apply the time window to the virtual table to partition the virtual table into the plurality of virtual partitions according to the time interval.

17. A system comprising:

a processor; and

a computer readable storage medium operationally coupled to the processor, the computer readable storage medium having computer readable program code embodied therewith to be executed by the processor, the computer readable program code configured to;

identify, by the computing processor, a data table comprising the time series data;

create, by the computing processor, a virtual view of the data table wherein the time series data is represented as at least one standard relational table in the virtual view, the virtual view presented as a virtual table; and

partition, by the computing processor, the virtual table into a plurality of virtual partitions according to a time interval, the virtual table partitioned across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the time interval, the virtual partitions created to optimize loading of the data into the data warehouse by incrementally refreshing the data according to the respective partition time range.

18. The system of claim 17 wherein the computer readable program code is further configured to:

provide, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

19. The system of claim 17 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by extracting new time series data from the data table, according to the time interval;

create a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

add the new virtual partition to the plurality of virtual partitions.

20. The system of claim 17 wherein the computer readable program code configured to partition, by the computing processor, the virtual table into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has the respective partition time range that is within the data time range.