ETL tool utilizing dimension trees

• US 20090300533A1
• Filed: 05/31/2008
• Published: 12/03/2009
• 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.
• Granted Patent: US 8,832,601 B2
• Time in Patent Office: Days
• US Class Current: 715/771
• 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 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;

linking the plurality of 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 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:

providing a user interface presenting a list of existing dimension trees and dimensions associated with at least one specified source database;

allowing a user to select any of a dimension tree or a dimension from the list 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 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 link the plurality of dimension trees to corresponding levels of the ETL pyramid, 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.

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:

providing a user interface presenting a list of existing dimension trees and dimensions associated with at least one specified source database;

allowing a user to select any of a dimension tree or a dimension from the list 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.

16. The system of claim 15 further comprising:

a pyramid creation user interface to graphically illustrate the selected dimension treeupon 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 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;

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;

linking the plurality of 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 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:

providing a user interface presenting a list of existing dimension trees and dimensions associated with at least one specified source database;

allowing a user to select any of a dimension tree or a dimension from the list 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.