Apparatus and method for performing data transformations in data warehousing

• US 6,339,775 B1
• Filed: 11/16/1999
• Issued: 01/15/2002
• Est. Priority Date: 11/07/1997
• Status: Expired due to Term

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Nesamoney, Diaz, Zamanian, Kiumarse
• Primary Examiner(s): Shah, Sanjiv
• Application Number:
  US09/442,060
• Time in Patent Office: 791 Days
• Field of Search: 707/1, 707/101, 707/100, 707/201, 707/3, 707/4, 707/7, 707/103
• 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/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A transformation description language (TDL) for specifying how data is to be manipulated in a data warehousing application. The TDL is comprised of a source for storing raw data, one or more transformation objects for processing the raw data according to predefined instructions, and a target for storing the processed data. A mapping is used for directing the data flow between the I/O ports corresponding to the source, the plurality of transformation objects, and the target. The mapping specifies the connectivity between the source, transformation, and target objects as well as the order of these connections. There are a number of different transformations which can be performed to manipulate the data. Some such transformations include: an aggregator transformation, an expression transformation, a filter transformation, a lookup transformation, a query transformation, a sequence transformation, a stored procedure transformation, and an update strategy transformation.

Claims

1. A computer implemented method for analyzing data in an application, comprising the steps of:

specifying at least one source system containing source data;

storing metadata corresponding to a plurality of transformation objects including an aggregator transformation objects, an expression transformation objects, a filter transformation objects, a lookup transformation objects, a query transformation objects and a sequence transformation objects which transform data according to the metadata corresponding to a particular transformation object, wherein different transformation objects perform different, unique functions;

specifying a target system for storing transformed data;

selecting at least one of the transformation objects;

mapping data from the source system to a first selected transformation object;

transforming the data according to the metadata corresponding to the first selected transformation object;

mapping the transformed data from the first selected transformation object to the target system.

2. The computer implemented method of claim 1, wherein the application is comprised of a balance score card application.

3. The computer implemented method of claim 1, wherein the application is comprised of an enterprise performance measurement application.

4. The computer implemented method of claim 1, wherein the application is comprised of a marketing application.

5. The computer implemented method of claim 1, wherein the application is comprised of a profiling application.

6. The computer implemented method of claim 1, wherein the application is comprised of a segmentation application.

7. The computer implemented method of claim 1, wherein the application is comprised of a data mining application.

8. The computer implemented method of claim 1, wherein the application includes an embedded data mart.

9. A computer implemented method for transferring data having a first format from a first system to a second system having a second format, comprising the steps of:

identifying a set of source data having the first format stored on the first system;

storing a set of transformation objects including an aggregator transformation object, an expression transformation objects, a filter transformation objects, a lookup transformation objects, a query transformation objects and a sequence transformation objects, wherein the transformation objects have corresponding metadata which defines how data is to be transformed and wherein different transformation objects perform different, unique functions;

mapping data from the first system to one or more of the transformation objects;

transforming the data having the first format to the second format through at least one of the transformation objects;

exporting transformed data having the second format to the second system.

10. The computer implemented method of claim 9, wherein data having the first format is copied from the first system and stored on the second system in the second format.

11. A computer implemented method for transforming data, comprising the steps of:

specifying at least one source system containing source data;

storing metadata corresponding to a plurality of transformation objects including an aggregator transformation object, an expression transformation objects, a filter transformation objects, a lookup transformation objects, a query transformation objects and a sequence transformation objects which transform data according to the metadata corresponding to a particular transformation object, wherein different transformation objects perform different, unique functions;

specifying a target system for storing transformed data;

selecting at least one of the transformation objects;

mapping data from the source system to a first selected transformation object;

transforming the data according to the metadata corresponding to the first selected transformation object;

mapping the transformed data from the first selected transformation object to the target system.

12. The computer implemented method of claim 11, wherein the source system is comprised of an application.

13. The computer implemented method of claim 12, wherein the application includes a database embedded within the application.