Data warehouse compatibility

• US 9,460,188 B2
• Filed: 06/03/2013
• Issued: 10/04/2016
• Est. Priority Date: 06/03/2013
• Status: Active Grant

• Current Assignee: Bank of America Corp.
• Original Assignee: Bank of America Corp.
• Inventors: Mundlapudi, Bharath, Banala, Karthik, Koneru, Rajesh
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): TESSEMA, AIDA Z
• Application Number:
  US13/908,749
• Publication Number:
  US 20140358845A1
• Time in Patent Office: 1,219 Days
• Field of Search: None
• US Class Current: 1/1
• 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 compatibility processing module, for executing one or more processes to format and manipulate data, such that communication between previously-incompatible data warehouses is facilitated. In particular, a first warehouse is disclosed, wherein the first data warehouse is configured with a compatibility processing module, for receiving a large number of data points, and for executing one or more processes on a stored portion of the received data points such that the resulting processed data points are compatible with formatting conventions of a second data warehouse.

Claims

1. A system for establishing compatibility between an open-source data warehouse and a proprietary data warehouse, the system comprising:

a distribution processing module which receives a set of unprocessed, raw data points;

a distributed file system which stores the set of data points according to a first data format;

a first data warehouse which executes an extract, transform, and load (ETL) operation on the set of data points, the ETL operation comprising an extract process which selects from the set of data points a subset of data points for transformation and loading into a second data warehouse that uses a second data format; and

a workflow scheduler which schedules execution of the ETL operation according to a workflow implemented as a directed acyclic graph comprising a plurality of action nodes that each specify an action to perform for the ETL operation wherein the plurality of action nodes comprise at least one action node that specifies a map and reduce process to perform on the subset of data points;

wherein the ETL operation further comprises one or more transformation processes, each transformation process comprising transforming individual data points in the subset of data points to obtain a set of transformed data points having the second data format;

wherein the one or more transformation processes comprise a group rank process, the group rank process comprising ranking a data subset that comprises a plurality of data points from the set of data points, the ranking based on a plurality of metrics, and the group rank process further comprising storing the data subset in a ranked data table comprising a first column corresponding to a first metric of the plurality of metrics, a second column corresponding to a second metric of the plurality of metrics, and a third column corresponding to a rank; and

wherein the ETL operation further comprises a load process comprising loading the set of transformed data points into the second data warehouse.

2. The system according to claim 1, wherein one of the one or more transformation processes comprises a precision-adjustment process comprising rounding numerical values according to one or more standards of numerical precision associated with the second data format.

3. The system according to claim 1, wherein one of the one or more transformation processes comprises a data type conversion process comprising converting first data having a first data type stored in the first data warehouse into second data having a second data type recognized by the second data warehouse.

4. The system according to claim 1, wherein one of the one or more transformation processes comprises a single rank process comprising ranking a data subset comprising a plurality of data points from the set of data points, wherein the single rank process ranks the data subset based on a single metric.

5. The system according to claim 1, wherein one of the one or more transformation processes comprises a date format process comprising converting first data having a first date format into second data having a second date format recognized by the second data warehouse.

6. The system according to claim 1, further comprising a distributed non-relational database that stores the set of data points.

7. The system according to claim 1, wherein the system is comprising dividing a plurality of processes of the ETL operation among a plurality of distributed computers for parallel processing.

8. A non-transitory computer-readable storage medium having computer-executable program instructions stored thereon that when executed by a processor cause the processor to perform steps establishing compatibility between a first data warehouse and a second data warehouse, the steps comprising:

receiving, from a distribution processing module, a set of unprocessed, raw data points;

storing, in a distributed file system, the set of data points according to a first data format;

executing, using a first data warehouse, an extract, transform, and load (ETL) operation on the set of data points, the ETL operation comprising

an extract process comprising selecting from the set of data points a subset of data points for transformation and loading into a second data warehouse that uses a second data format,

one or more transformations processes each comprising transforming individual data points in the subset of data points to obtain a set of transformed data points having the second data format, and

a load process comprising loading the set of transformed data points into the second data warehouse; and

scheduling execution of the ETL operation according to a workflow implemented as a directed acyclic graph comprising a plurality of action nodes that each specify an action to perform for the ETL operation wherein the plurality of action nodes comprise at least one action node that specifies a map and reduce process to perform on the subset of data points;

wherein the one or more transformation processes comprise a group rank process, the group rank process comprising ranking a data subset that comprises a plurality of data points from the set of data points, the ranking based on a plurality of metrics, and the group rank process further comprising storing the data subset in a ranked data table comprising a first column corresponding to a first metric of the plurality of metrics, a second column corresponding to a second metric of the plurality of metrics, and a third column corresponding to a rank.

9. The computer-readable storage medium of claim 8, wherein one of the one or more transformation processes comprises a precision-adjustment process comprising rounding off numerical values according to one or more standards of numerical precision associated with the second data warehouse.

10. The computer-readable storage medium of claim 8, wherein one of the one or more transformation processes comprises a data type conversion process comprising converting first data having a first data type stored in the first data warehouse into second data having a second data type recognized by the second data warehouse.

11. The computer-readable storage medium of claim 8, wherein one of the one or more transformation processes comprises a single rank process comprising ranking a data subset comprising a plurality of data points from the set of data points, wherein the single rank process ranks the data subset based on a single metric.

12. The computer-readable storage medium of claim 8, wherein one of the one or more transformation processes comprises a date format process comprising converting first data having a first date format into second data having a second date format recognized by the second data warehouse.

13. The computer-readable storage medium of claim 8, wherein the computer-executable instructions, when executed by the processor, cause the processor to perform steps further comprising storing, in a distributed non-relational database, the set of data points.

14. The computer-readable storage medium of claim 8, wherein the computer-executable instructions, when executed by the processor, cause the processor to perform steps further comprising dividing a plurality of processes of the ETL operation among a plurality of distributed computers for parallel processing.

15. A method of establishing compatibility between a first data warehouse and a second data warehouse comprising:

receiving a set of unprocessed, raw data points;

storing the data points in a distributed file system according to a first data format;

executing an extract, transform, and load (ETL) operation on the set of data points, the ETL operation comprising

an extract process comprising selecting from the set of data points a subset of data points for transformation and loading into a second data warehouse that uses a second data format,

one or more transformations processes each comprising transforming individual data points in the subset of data points to obtain a set of transformed data points having the second data format, and

a load process comprising loading the set of transformed data points into the second data warehouse; and

scheduling execution of the ETL operation according to a workflow implemented as a directed acyclic graph comprising a plurality of action nodes that each specify an action to perform for the ETL operation wherein the plurality of action nodes comprise at least one action node that specifies a map and reduce process to perform on the subset of data points;

wherein the one or more transformation processes comprise a group rank process, the group rank process comprising ranking a data subset that comprises a plurality of data points from the set of data points, the ranking based on a plurality of metrics, and the group rank process further comprising storing the data subset in a ranked data table comprising a first column corresponding to a first metric of the plurality of metrics, a second column corresponding to a second metric of the plurality of metrics, and a third column corresponding to a rank.

16. The method of claim 15 wherein:

the first data warehouse is an open-source data warehouse;

the second data warehouse is a commercially available data warehouse; and

the distributed file system is an open-source distributed file system.