AUTO-GENERATION OF CODE FOR PERFORMING A TRANSFORM IN AN EXTRACT, TRANSFORM, AND LOAD PROCESS

• US 20110125705A1
• Filed: 11/25/2009
• Published: 05/26/2011
• Est. Priority Date: 11/25/2009
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chen, Danny, Aski, Vijaykumar K.
• Granted Patent: US 8,504,513 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
• CPC Class Codes: G06F 8/30 Creation or generation of source code

Abstract

A mapping is received and stored that maps elements of a data warehouse to types of a type system implemented by a data source. Program code is generated that performs a transform of data retrieved from a data source based on the mapping. Generation of the program code may include generating program code for performing a dimension transform based on the mapping, generating program code for performing a fact transform based on the mapping, and generating program code for performing an outrigger transform based on the mapping. The generated program code may then be executed to transform the data retrieved from the data source prior to loading into the data warehouse.

Claims

1. A computer-implemented method for performing a transform in an extract, transform, and load process, the computer-implemented method comprising performing computer-implemented operations for:

storing a mapping that maps types within a type system implemented by one or more data sources to elements within a data warehouse; and

generating program code that performs a transform of data retrieved from the data sources based on the mapping.

2. The computer-implemented method of claim 1, further comprising executing the generated program code to transform the data retrieved from the data sources prior to loading into the data warehouse.

3. The computer-implemented method of claim 2, wherein generating program code that performs a transform of data retrieved from the data sources based on the mapping comprises generating program code for performing a dimension transform based on the mapping.

4. The computer-implemented method of claim 3, wherein generating program code that performs a transform of data retrieved from the data sources based on the mapping further comprises generating program code for performing a fact transform based on the mapping.

5. The computer-implemented method of claim 4, wherein generating program code that performs a transform of data retrieved from the data sources based on the mapping further comprises generating program code for performing an outrigger transform based on the mapping.

6. The computer-implemented method of claim 5, wherein the mapping comprises data defining one or more properties that are processed in the dimension transform.

7. The computer-implemented method of claim 6, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that only properties specified in the mapping for a dimension should be processed.

8. The computer-implemented method of claim 7, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that new properties added to a dimension when the dimension is extended should also be processed.

9. The computer-implemented method of claim 8, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that properties for a target class and a parent of the target class should be processed.

10. The computer-implemented method of claim 9, wherein the program code comprises structured query language (SQL) script.

11. A computer-readable storage medium having computer-readable instructions stored thereupon which, when executed by a computer, cause the computer to:

store a mapping that maps types within a type system implemented by a data source to elements within a data warehouse; and

generate program code that performs a transform of data retrieved from the data source based on the mapping.

12. The computer-readable storage medium of claim 11, having further computer-executable instructions stored thereupon which, when executed by the computer, cause the computer to execute the generated program code to transform the data retrieved from the data source prior to loading into the data warehouse.

13. The computer-readable storage medium of claim 12, wherein generating program code that performs a transform of data retrieved from the data source based on the mapping comprises generating program code for performing a dimension transform based on the mapping.

14. The computer-readable storage medium of claim 13, wherein generating program code that performs a transform of data retrieved from the data source based on the mapping further comprises generating program code for performing a fact transform based on the mapping.

15. The computer-readable storage medium of claim 14, wherein generating program code that performs a transform of data retrieved from the data source based on the mapping further comprises generating program code for performing an outrigger transform based on the mapping.

16. The computer-readable storage medium of claim 15, wherein the mapping comprises data defining one or more properties that are processed in the dimension transform.

17. The computer-readable storage medium of claim 16, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that only properties specified in the mapping for a dimension should be processed.

18. The computer-readable storage medium of claim 17, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that new properties added to a dimension when the dimension is extended should also be processed.

19. The computer-readable storage medium of claim 18, wherein the data defining one or more properties that are processed in the dimension transform comprises data specifying that properties for a target class and a parent of the target class should be processed.

20. A computer system for performing a transform in an extract, transform, and load process, the computer system comprising:

a central processing unit; and

a memory storing program code executable on the central processing unit which, when executed, causes the central processing unit to store a mapping in the memory, the mapping comprising data mapping types within a type system implemented by a data source to elements within a data warehouse and data defining one or more properties that are processed in a dimension transform, to generate program code based on the mapping that performs a dimension transform, a fact transform, and an outrigger transform of data retrieved from the data source, and to execute the generated program code on the central processing unit to transform the data retrieved from the data source prior to loading into the data warehouse.