DATA EXTRACTION AND TESTING METHOD AND SYSTEM

• US 20120290527A1
• Filed: 05/12/2011
• Published: 11/15/2012
• Est. Priority Date: 05/12/2011
• Status: Active Grant

• Current Assignee: Narendar Yalamanchilli
• Original Assignee: Narendar Yalamanchilli
• Inventors: Yalamanchilli, Narendar
• Granted Patent: US 8,386,419 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• 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

The present method and apparatus provides for automated testing of data integration and business intelligence projects using Extract, Load and Validate (ELV) architecture. The method and computer program product provides a testing framework that automates the querying, extraction and loading of test data into a test result database from plurality of data sources and application interfaces using source specific adaptors. The test data available for extraction using the adaptors include metadata such as the database query generated by the OLAP Tools that are critical to validate the changes in business intelligence systems. A validation module helps define validation rules for verifying the test data loaded into the test result database. The validation module further provides a framework for comparing the test data with previously archived test data as well as benchmark test data.

Claims

1. A computerized method for testing of data integration and business intelligence projects, the method comprising:

generating and storing test queries for extracting test data, the test queries can be run on plurality of computing data sources, the computing data sources include at least one of;

data stores and application interfaces;

extracting test data and test query execution metadata from a plurality of computing data sources,

loading the test data and the test query execution metadata into a test result data store;

performing a plurality of validation operation on the test data and the test query execution metadata stored in the result data store based on an existing data set, including electronically comparing the test data to the existing data set to determine test data discrepancies and query execution metadata discrepancies; and

creating a validation report, identifying the test data discrepancies and the query execution metadata discrepancies.

2. The method of claim 1 further comprising:

loading a plurality of benchmark data in the test result data store; and

wherein the plurality of validations includes comparing the test data from the plurality of data sources with the benchmark data.

3. The method of claim 1 further comprising:

loading previous test data in the test result data store; and

wherein the plurality of validations includes comparing the test data from the plurality of data sources with the previous test data.

4. The method of claim 3, wherein the validation includes a determination that the test data is within a tolerance range of the previous test data.

5. The method of claim 1 wherein the validation includes comparing the test data of a first source from the plurality of sources with the test data from a second source from the plurality of sources.

6. The method of claim 5 wherein the first source is a source database, the second source is a data warehouse, and a third source is an OLAP reporting tool.

7. The method of claim 1 further comprising:

loading a plurality of benchmark data in the test result data store; and

wherein the validation includes comparing a plurality of test query execution metadata used by the plurality of data sources with the benchmark data.

8. The method of claim 1 further comprising:

loading previous test query execution metadata in the test result data store; and

wherein the validation includes comparing at least a portion of the test query execution metadata used by the plurality of data sources with the previous test query execution metadata.

9. The method of claim 1 wherein the computing data source is a database and the test query execution metadata is a query execution plan.

10. The method of claim 1 wherein the computing data source is an OLAP reporting tool and the test query execution metadata is an SQL query generated by a query engine.

11. The method of claim 1 wherein the source is a database and the database metadata is used for test query generation.

12. The method of claim 1 wherein the source is an OLAP reporting tool and the existing reports and test queries are generated using OLAP project metadata.

13. The method of claim 1 wherein the validations include a plurality of validation operations for each validation of the test data.

14. The method of claim 1 wherein the plurality of validations are performed in parallel operations.

15. The method of claim 1 wherein the validations are in the form of a high-level programming language.

16. The method of claim 1 wherein the validations are in the form of SQL code.

17. The method of claim 1 wherein the validation includes a test execution plan, the test execution plan including a plurality of data format components allowing for the extraction of test data from a plurality of corresponding data formats.

18. A processing apparatus for testing of data integration, the apparatus comprising:

a memory device having executable instructions stored therein; and

a processing device, in response to the executable instructions, operative to;

generate and store test queries for extracting test data, the test queries can be run on plurality of computing data sources, the computing data sources include at least one of;

data stores and application interfaces;

extract test data and test query execution metadata from a plurality of computing data sources,

load the test data and the test query execution metadata into a test result data store;

perform a plurality of validation operation on the test data and the test query execution metadata stored in the result data store based on an existing data set, including electronically comparing the test data to the existing data set to determine test data discrepancies and query execution metadata discrepancies; and

create a validation report, identifying the test data discrepancies and the query execution metadata discrepancies.

19. The apparatus of claim 18, the processing device further operative to:

load a plurality of benchmark data in the test result data store; and

wherein the plurality of validations includes comparing the test data from the plurality of data sources with the benchmark data.

20. The apparatus of claim 18, the processing device further operative to:

load previous test data in the test result data store; and

wherein the plurality of validations includes comparing the test data from the plurality of data sources with the previous test data.