ETL tool utilizing dimension trees

• US 8,832,601 B2
• Filed: 05/31/2008
• Issued: 09/09/2014
• Est. Priority Date: 05/31/2008
• Status: Active Grant

• Current Assignee: Red Hat, Inc. (International Business Machines Corporation)
• Original Assignee: Red Hat, Inc. (International Business Machines Corporation)
• Inventors: Williamson, Eric J.
• Primary Examiner(s): PITARO, RYAN F
• Application Number:
  US12/156,309
• Publication Number:
  US 20090300533A1
• Time in Patent Office: 2,292 Days
• Field of Search: 707/602, 715/853
• US Class Current: 715/853
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases

Abstract

A method and apparatus for an ETL tool utilizing dimension trees. The method may include identifying various dimensions associated with one or more databases, and creating dimension trees based on these dimensions. The method may further include creating an ETL pyramid having multiple levels, linking the dimension trees to corresponding levels of the ETL pyramid, and creating a table for each level of the ETL pyramid to store a subset of the dimension trees that is linked to a corresponding level of the ETL pyramid.

Claims

1. A computer-implemented method comprising:

identifying, by a computer system, a plurality of dimensions associated with one or more databases;

creating a plurality of dimension trees based on the plurality of dimensions;

creating an ETL (Extract, Transform and Load) pyramid having a number of levels;

providing a user interface facilitating user input specifying dimensions and dimension trees for individual levels within the number of levels of the ETL pyramid;

linking the plurality of dimensions and the plurality of dimension trees to the number of levels of the ETL pyramid based on the user input, wherein the levels of the ETL pyramid include components comprising one or more of the plurality of dimension trees, one or more dimensions from the plurality of dimension trees and one or more dimensions that are not part of any of the plurality of dimension trees; and

creating a table for each level of the ETL pyramid to store a subset of the plurality of dimension trees that is linked to a corresponding level of the ETL pyramid.

2. The method of claim 1 wherein one of the plurality of dimension trees comprises at least one other dimension tree.

3. The method of claim 2 wherein the one of the plurality of dimension trees further comprises at least one of the plurality of dimensions.

4. The method of claim 2 wherein the one of the plurality of dimension trees further comprises at least one element unique for the one of the plurality of dimension trees.

5. The method of claim 1 wherein creating the plurality of dimension trees comprises:

providing a user interface allowing a user to select a plurality of components for a new dimension tree;

graphically illustrating the new dimension tree to the user; and

storing an identifier of the new dimension tree with identifiers of the selected components in a dimension tree table.

6. The method of claim 5 wherein the plurality of components corresponds to one or more groups, the groups comprising at least one of an existing dimension group, an existing dimension tree group, a new element group, or a new consolidator group.

7. The method of claim 1 wherein creating an ETL (Extract, Transform and Load) pyramid comprises:

receiving user input specifying the number of levels for the ETL pyramid; and

displaying the ETL pyramid using the number of levels.

8. The method of claim 1, wherein linking the plurality of dimension trees to corresponding levels of the ETL pyramid comprises:

allowing a user to select any of the plurality of dimension trees and the plurality of dimensions for association with one of the levels of the ETL pyramid; and

storing an identifier of the selected dimension tree or dimension in a linkage table.

9. The method of claim 8 further comprising:

upon receiving a user selection of a dimension tree from the list, graphically illustrating the selected dimension tree; and

allowing the user to associate the selected dimension tree or any dimension from the selected dimension tree with one of the levels of the ETL pyramid.

10. The method of claim 1, further comprising:

receiving user input specifying a level of the ETL pyramid; and

displaying components linked to the specified level of the ETL pyramid, the components corresponding to at least one of a dimension tree group or a dimension group.

11. A system comprising:

a memory; and

a processor, coupled to the memory, to cause;

a dimension and tree creator to identify a plurality of dimensions associated with one or more databases, and to create a plurality of dimension trees based on the plurality of dimensions; and

a pyramid creator, coupled to the dimension and tree creator, to create an ETL (Extract, Transform and Load) pyramid having a number of levels, to facilitate user input specifying dimensions and dimension trees for individual levels within the number of levels of the ETL pyramid, to link the plurality of dimensions and the plurality of dimension trees to the number of levels of the ETL pyramid based on the user input, and to create a table for each level of the ETL pyramid to store a subset of the plurality of dimension trees that is linked to a corresponding level of the ETL pyramid, wherein the levels of the ETL pyramid include components comprising one or more of the plurality of dimension trees, one or more dimensions from the plurality of dimension trees and one or more dimensions that are not part of any of the plurality of dimension trees.

12. The system of claim 11 wherein one of the plurality of dimension trees comprises at least one of another dimension tree, one or more dimensions, or an element unique for the one of the plurality of dimension trees.

13. The system of claim 11 wherein the dimension and tree creator is to create the plurality of dimension trees by:

providing a user interface allowing a user to select a plurality of components for a new dimension tree;

graphically illustrating the new dimension tree to the user; and

storing an identifier of the new dimension tree with identifiers of the selected components in a dimension tree table.

14. The system of claim 13 wherein the plurality of components corresponds to one or more groups, the groups comprising at least one of an existing dimension group, an existing dimension tree group, a new element group, or a new consolidator group.

15. The system of claim 11, wherein the pyramid creator is to link the plurality of dimension trees to corresponding levels of the ETL pyramid by:

allowing a user to select any of the plurality of dimension trees and the plurality of dimensions; and

storing an identifier of the selected dimension tree or dimension in a linkage table.

16. The system of claim 15 further comprising:

a pyramid creation user interface to graphically illustrate the selected dimension tree upon receiving a user selection of a dimension tree from the list, graphically illustrating the selected dimension tree, and to allow the user to associate the selected dimension tree or any dimension from the selected dimension tree with one of the levels of the ETL pyramid.

17. A non-transitory computer readable storage medium including instructions that, when executed by a processing system, cause the processing system to perform a method comprising:

identifying a plurality of dimensions associated with one or more databases comprising at least one production database;

creating a plurality of dimension trees based on the plurality of dimensions;

creating an ETL (Extract, Transform and Load) pyramid having a number of levels;

providing a user interface facilitating user input specifying dimensions and dimension trees for individual levels within the number of levels of the ETL pyramid;

linking the plurality of dimensions and the plurality of dimension trees to the number of levels of the ETL pyramid based on the user input, wherein the levels of the ETL pyramid include components comprising one or more of the plurality of dimension trees, one or more dimensions from the plurality of dimension trees and one or more dimensions that are not part of any of the plurality of dimension trees; and

creating a table for each level of the ETL pyramid to store a subset of the plurality of dimension trees that is linked to a corresponding level of the ETL pyramid.

18. The computer readable storage medium of claim 17 wherein creating the plurality of dimension trees comprises:

providing a user interface allowing a user to select a plurality of components for a new dimension tree;

graphically illustrating the new dimension tree to the user; and

storing an identifier of the new dimension tree with identifiers of the selected components in a dimension tree table.

19. The computer readable storage medium of claim 18 wherein the plurality of components corresponds to one or more groups, the groups comprising at least one of an existing dimension group, an existing dimension tree group, a new element group, or a new consolidator group.

20. The computer readable storage medium of claim 17, wherein linking the plurality of dimension trees to corresponding levels of the ETL pyramid comprises:

allowing a user to select any of the plurality of dimension trees and the plurality of dimensions for association with one of the levels of the ETL pyramid; and

storing an identifier of the selected dimension tree or dimension in a linkage table.

21. The computer readable storage medium of claim 20, wherein the method further comprises:

upon receiving a user selection of a dimension tree from the list, graphically illustrating the selected dimension tree; and

allowing the user to associate the selected dimension tree or any dimension from the selected dimension tree with one of the levels of the ETL pyramid.