Method and apparatus for import, transform and export of data

• US 6,356,901 B1
• Filed: 12/16/1998
• Issued: 03/12/2002
• Est. Priority Date: 12/16/1998
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: MacLeod, Stewart P., Kiernan, Casey L.
• Primary Examiner(s): MIZRAHI, DIANE D
• Application Number:
  US09/212,989
• Time in Patent Office: 1,182 Days
• Field of Search: 709/217, 709/218, 707/315, 707/6, 707/10, 707/204, 707/102, 707/104, 707/501, 707/513, 707/7, 707/201
• 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
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A user specifies source data, optional transformations and defines a destination database, as well as its location. The user specification creates a package. A package consists of steps of associated tasks, with each step optionally having one or more precedence constraints. Execution of the package causes a data pump to import the user-specified data, conform the data in accordance with the user'"'"'s definition of the destination database and export the data to that database. Processing occurs on a streaming, contiguous basis. As each row is pulled from source database into data pump, the user-defined transform is optionally applied and data lineage information is bound to the data.

Claims

1. A computer system including a processing unit, the computer system also including:

a user interface element comprising instructions executed by the processing unit, the user interface element prompting a user to specify source data and a destination database; and

a data pump element comprising instructions executed by the processing unit, the data pump element causing extraction of the source data from a first storage area, conforming of the extracted source data into a format corresponding to the destination database and storage of the extracted source data in the destination database wherein the source data is buffered such that the extraction of the source data and the storage of the extracted source data in the destination database occur simultaneously.

2. The computer system of claim 1 wherein data pump element processes are initiated by a package.

3. The computer system of claim 2 wherein the package incorporates language script commands.

4. The computer system of claim 1 wherein, prior to the extraction of source data from a first storage area, source data is converted into a tabular format.

5. The computer system of claim 4 wherein the source data is spread sheet data.

6. The computer system of claim 4 wherein the source data is text data.

7. The computer system of claim 1 wherein the conforming of the extracted source data into a format corresponding to the destination database comprises reordering the extracted source data to correspond to an ordering of data in the destination database.

8. The computer system of claim 1 wherein the data pump element causes transformation of the extracted source data.

9. The computer system of claim 1 wherein the data pump element is incorporated into a package.

10. The computer system of claim 9 wherein the extraction of the source data from a first storage area, the conforming of the extracted source data into a format corresponding to the destination database and the storage of the extracted source data in the destination database each comprise a package task.

11. The computer based method of claim 10 wherein the task has an associated precedence constraint.

12. A method for constructing a database, the method comprising the steps of:

a. accepting a source data specification from the user;

b. accepting a destination storage location specification from the user;

c. extracting data corresponding to the source data specification;

d. transforming the extracted data;

e. conforming the extracted data in accordance with a format corresponding to the destination storage location; and

f. storing the extracted data in a storage area corresponding to the destination storage location;

g. buffering the extracted data such that steps c and f occur simultaneously.

13. The method of claim 12 also comprising the step of converting source data into tabular format.

14. The method of claim 13 wherein the source data is spread sheet data.

15. The method of claim 13 wherein the source data is text data.

16. The method of claim 12 wherein step e comprises reordering the extracted data to correspond to an ordering of data in the destination storage location.

17. The method of claim 12 wherein steps a-f are incorporated into a plurality of a package tasks.