Data warehousing systems and methods having reusable user transforms

• US 7,676,478 B2
• Filed: 08/02/2006
• Issued: 03/09/2010
• Est. Priority Date: 01/10/2006
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Rausch, Nancy A.
• Primary Examiner(s): Breene; John E
• Assistant Examiner(s): COLAN, GIOVANNA B
• Application Number:
  US11/497,782
• Publication Number:
  US 20070174308A1
• Time in Patent Office: 1,315 Days
• Field of Search: 717/136, 717/106, 717/148, 707/203, 707/101, 707/202, 707/106, 707/102, 707/103.R, 707/100, 707/6, 707/200, 719/315, 709/217, 709/201, 709/315
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Data warehousing systems and methods are described herein. In one example methodology, a plurality of transformation templates are stored in a transformation library, each transformation template including software objects that describe how to transform extracted data prior to loading the data into the data warehouse. A unique identifier may be associated with each of the plurality of transformation templates. Using this unique identifier, an instance of at least one of the plurality of transformation templates may be instantiated into a data transformation process by mapping the software objects of the transformation template into the instance of the transform using code segments. The code segments of the data transformation process are then executed to transform and load the extracted data into the data warehouse.

Claims

1. A data warehousing method, comprising:

executing software instructions on a computer system to store a plurality of transformation templates in a plurality of transformation libraries, each transformation template including metadata objects that describe how to transform extracted source data prior to loading the source data into a data warehouse within a data warehousing system;

executing software instructions on the computer system to create a unique link that corresponds to a particular transformation template, wherein the link is unique to a particular data warehousing system housing one or more transformation libraries within one or more data warehouses;

executing software instructions on the computer system to associate the unique link with the particular transformation template, such that the particular transformation template is identified by the unique link in each transformation library in the plurality of transformation libraries within a particular data warehouse;

executing software instructions on the computer system to instantiate an instance of the particular transformation template into a data transformation process using the unique link associated with the particular transformation template, wherein the metadata objects of the particular transformation template are mapped into an instance of a transform using executable code segments; and

executing software instructions on the computer system to execute the data transformation process to transform and load the extracted source data into the particular data warehouse using the particular transformation template.

2. The data warehousing method of claim 1, further comprising:

executing software instructions on the computer system to export the executable code segments to a file.

3. The data warehousing method of claim 1, wherein the metadata objects of the particular transformation template include a main object that describes the name and purpose of the transform, a code object that supplies the generated code for the transform, and an input/output object that describes the inputs and outputs of the transform.

4. The data warehousing method of claim 3, further comprising:

a version object that tracks modifications to the particular transformation template.

5. The data warehousing method of claim 3, further comprising:

an options object that describes an interface to a user of the transform, the interface including one or more variable fields that are supplied by the user when customizing the instantiated transform.

6. The data warehousing method of claim 3, wherein the unique link associated with each transformation template is stored along with the template as a metadata object.

7. The data warehousing method of claim 1, wherein the executable code segments comprise XML code segments.

8. The data warehousing method of claim 1, further comprising:

executing software instructions on the computer system to modify the particular transformation template stored in the transformation library;

executing software instructions on the computer system to use a graphical user interface to perform a visual impact analysis on the modified particular transformation template corresponding to the unique link; and

executing software instructions on the computer system to determine, based upon the visual impact analysis, whether any data transformation processes will be affected by the modifications.

9. The data warehousing method of claim 8, further comprising:

executing software instructions on the computer system to accept the modifications to the particular transformation template and to save the modified template in the transformation library.

10. The data warehousing method of claim 9, further comprising:

executing software instructions on the computer system to propagate the modified template to any data transformation process linked to the modified template via the unique link; and

executing software instructions on the computer system to automatically update the data transformation process using the modified template.

11. The data warehousing method of claim 9, further comprising:

executing software instructions on the computer system to propagate the modified template to any data transformation process that is linked to the template via the unique link; and

executing software instructions on the computer system to prompt a user of the data transformation process to accept or reject the modified template.

12. The data warehousing method of claim 11, further comprising:

if the user accepts the modified template, then executing software instructions on the computer system to update the data transformation process using the modified template; and

if the user rejects the modified template, then executing software instructions on the computer system to discard the modified template.

13. The data warehousing method of claim 4, further comprising:

executing software instructions on the computer system to compare the version object of the particular transformation template with an associated version object of the instance of the transformation in the data transformation process; and

automatically executing software instructions on the computer system to update the instance of the transformation in the data transformation process with a current version of the particular transformation template if the version object of the particular transformation template indicates a newer version than the version object of the instance.

14. A data warehousing system, comprising

a processor;

a computer-readable storage medium containing instructions operable to cause the processor to perform operations including;

storing a plurality of transformation templates in a plurality of transformation libraries, each transformation template including metadata objects that describe how to transform extracted source data prior to loading the source data into a data warehouse within a data warehousing system;

creating a unique link that corresponds to a particular transformation template, wherein the link is unique to a particular data warehousing system housing one or more transformation libraries within one or more data warehouses;

associating a unique link with the particular transformation template, such that the particular transformation template is identified by the unique link in each transformation library in the plurality of transformation libraries within a particular data warehouse;

instantiating an instance of the particular transformation template into a data transformation process using the unique link associated with the particular transformation template, wherein the metadata objects of the particular transformation template are mapped into an instance of a transform using executable code segments; and

executing the data transformation process to transform and load the extracted source data into the particular data warehouse using the particular transformation template.

15. A computer-readable storage medium encoded with instructions that when executed, cause a computer to perform a data warehousing method, comprising:

storing a plurality of transformation templates in a plurality of transformation libraries, each transformation template including metadata objects that describe how to transform extracted source data prior to loading the source data into a data warehouse within a data warehousing system;

creating a unique link that corresponds to a particular transformation template, wherein the link is unique to a particular data warehousing system housing one or more transformation libraries within one or more data warehouses;

associating a unique link with the particular transformation template, such that the particular transformation template is identified by the unique link in each transformation library in the plurality of transformation libraries within a particular data warehouse;

instantiating an instance of the particular transformation template into a data transformation process using the unique link associated with the particular transformation template, wherein the metadata objects of the particular transformation template are mapped into an instance of a transform using executable code segments; and

executing the data transformation process to transform and load the extracted source data into the particular data warehouse using the particular transformation template.