AUTOMATED DATA WAREHOUSE MIGRATION

• US 20120265726A1
• Filed: 04/18/2011
• Published: 10/18/2012
• Est. Priority Date: 04/18/2011
• Status: Active Grant

• Current Assignee: Infosys Limited
• Original Assignee: Infosys Limited
• Inventors: Padmanabhan, Rajan, Patki, Asha Uday
• Granted Patent: US 9,430,505 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/214 Database migration support

Abstract

Systems, apparatus, computer-readable storage media, and methods are disclosed for allowing analysis, migration, and validation of data from a source environment (such as an RDBMS system) to a target environment (such as a data warehouse (DW) appliance). In one example, a method comprises analyzing a source database, a source ETL environment, a target database, and a target ETL environment to produce configuration data, the configuration data being used for generating a mapping of the source database to a target database in the target database environment, a mapping of the source DDL code to target DDL code in the target database environment, and a mapping of source ETL code to target ETL code for the target database environment, and migrating at least one table from the source database, at least a portion of the source DDL code, and at least a portion of the source ETL code to the target database environment, where the migrating is based at least in part on the mapping generated using the configuration data.

Claims

1. A method of migrating data from a source database environment to a target database environment, the source database environment including at least one source database, the method comprising:

analyzing the source database environment and the target database environment to produce configuration data for generating a mapping for converting at least one table in the source database to a format compliant with a target database in the target database environment;

generating a target-compliant mapping based on the configuration data; and

with a computer, migrating the at least one table from the source database to the target database environment to produce migrated data in the target database environment, wherein the migrating is performed based at least in part on the target-compliant mapping.

2. The method of claim 1, wherein the source database environment comprises a relational database management system and the target database environment comprises a data warehouse appliance.

3. The method of claim 1, further comprising searching the source database environment for structured query language (SQL) statements and based on the SQL statements and the target-compliant mapping, generating SQL statements compliant with the target database environment.

4. The method of claim 1, wherein:

the analyzing comprises extracting one or more column lists from the source database environment;

the generating the target-compliant mapping comprises generating one or more SQL statements based on the extracted column lists and generating an SQL script with translation functions and conversion functions based on the generated SQL statements; and

the migrating comprises executing the SQL script in the target database environment.

5. The method of claim 1, further comprising:

persisting output of a target database environment script in a computer-readable storage device to produce persisted output; and

based on the persisted output, repeating the migrating, wherein the at least one table is selected to be migrated to the target database environment based on the persisted output.

6. The method of claim 1, further comprising validating the migrated data using one or more sets of computer-executable instructions, the computer-executable instructions being generated based at least in part on the configuration data or the target-compliant mapping.

7. The method of claim 1, wherein:

the analyzing comprises extracting at least one or more of the following from the source database environment to produce extracted data;

a column list, data associated with a column list, a date range, or a validation criteria list;

the generating the target-compliant mapping comprises mapping one or more validation SQL statements based on the extracted data; and

the migrating comprises executing at least a portion of the validation SQL statements in the target database environment.

8. The method of claim 1, further comprising storing at least a portion of the migrated data in a computer-readable storage medium.

9. The method of claim 1, wherein the target-compliant mapping includes mappings for converting at least one or more of the following to a form compliant with the target environment:

DB-specific functions, custom SQL usage patterns, custom target loads, DB-specific join syntax, reserved syntax, DB constraints, datatypes, or DDL code.

10. One or more computer-readable media storing computer-readable instructions that when executed by a computer, cause the computer to perform the method of claim 1.

22. One or more computer-readable media storing computer-readable instructions that when executed by a computer, cause the computer to perform the method of claim 10.

11. A method of migrating source extraction, transformation, and loading (ETL) code from a source environment to a target environment as target ETL code compliant with the target environment, the method comprising, with a computer:

analyzing the source ETL code and the source environment to produce a conversion inventory; and

converting at least a portion of the source ETL code to the target ETL code using at least one mapping from the source environment to target environment, wherein the mapping is based at least in part on the conversion inventory, and wherein at least a portion of the target ETL code is executable in the target database environment.

12. The method of claim 11, wherein the source ETL code comprises tool-based ETL code.

13. The method of claim 11, wherein the source ETL code comprises script-based ETL code, database-based ETL code, or script-based ETL code and database-based ETL code.

14. The method of claim 11, wherein the converting further comprises:

generating one or more input files for SQL statement conversion, the input files being generated based on at least one or more of the following;

datatype usage patterns in the source database environment, database-specific function usage patterns in the source database environment, or custom SQL statement usage patterns in the source environment; and

executing SQL statement conversion code in the target environment, the SQL statement conversion code being based at least in part on the input files for SQL statement conversion.

15. The method of claim 14, wherein the executing the SQL statement conversion code includes at least one or more of the following conversions:

converting database-specific join syntax to ANSI-standard join conventions, converting inline outer join queries to set queries, or converting syntax and/or keywords that are reserved in the target environment to a target environment-specific syntax.

16. The method of claim 11, further comprising:

generating XML data describing a mapping of the source ETL code to the target database environment;

exporting the XML data to the target environment;

searching and replacing one or more tags from the source environment with target environment-specific metadata tags in the target environment;

replacing source database-specific SQL source system properties with target database-specific SQL source system properties in the target environment; and

compiling and validating the target ETL code in the target environment, wherein the compiling and the validating is based at least in part on the XML data.

17. The method of claim 11, further comprising:

persisting output of a script for the converting the ETL source code in a computer-readable storage device to produce persisted output; and

based on the persisted output, repeating the migrating, wherein at least one table is migrated to the target environment based on the persisted output.

18. The method of claim 11, further comprising generating validation SQL code for validating the target environment based on a criteria list and a date range, the validation SQL being executable in the target environment to validate at least a portion of the target ETL source code.

19. The method of claim 11, wherein the at least one mapping includes mappings for converting at least one or more of the following:

DB-specific functions, custom SQL code, custom target load techniques, DB-specific syntax, datatypes, metadata tags, or extraction SQL code.

20. The method of claim 11, wherein the at least one mapping is based on mapping XML code generated based on a target ETL dictionary.

21. The method of claim 11, further comprising storing at least a portion of the target ETL code in a computer-readable storage medium.

23. A system for migrating a source database environment to a target environment comprising a data warehouse appliance, the source database environment including a source database and source ETL code, the system comprising:

a computer-implemented database analysis workbench for analyzing the source database environment and the target environment to produce configuration data for migrating data from the source database environment to the data warehouse appliance;

a computer-implemented database migration workbench for migrating at least a portion of the data from the source database environment to the data warehouse appliance using one or more mappings based at least in part on the configuration data; and

a computer-implemented database quality assurance workbench for validating data migrated to the data warehouse appliance by the database migration workbench.

24. The system of claim 23, further comprising:

a computer-implemented ETL analysis workbench for analyzing the source database environment and the target environment to produce ETL configuration data for migrating at least a portion of the source ETL code from the source database environment to the target database environment;

a computer-implemented ETL migration workbench for migrating at least a portion of the data from the source ETL code to the target database environment using one or more mappings based at least in part on the ETL configuration data; and

a computer-implemented ETL quality assurance workbench for validating ETL code migrated to the data warehouse appliance by the ETL migration workbench.

25. A system for migrating data from a source environment to a target environment, the system comprising:

means for analyzing the source environment and the target environment to produce configuration data for generating one or more mappings for data in the source environment to the target environment;

means for migrating at least a portion of the data to the target environment, wherein the migrating is performed based at least in part on the mappings generated using the configuration data, wherein the migrating produces migrated data in the target environment.

26. The system of claim 25, wherein:

the means for analyzing comprise means for analyzing data stored in a source database in the source environment; and

the means for migrating comprise means for migrating data from the source database to a target database in the target environment.

27. The system of claim 25, wherein:

the means for analyzing comprise means for analyzing ETL data stored in the source environment; and

the means for migrating comprise means for migrating ETL data from the source environment to the target environment.

28. The system of claim 25, further comprising:

means for validating at least a portion of the migrated data.

29. The system of claim 25, further comprising:

means for validating at least a portion of the migrated data to produce validation data; and

means for repeating the analyzing and the migrating, wherein the analyzing and the migrating are based at least in part on the validation data.