Method and apparatus for processing a dimension table and deriving a hierarchy therefrom

• US 20060259507A1
• Filed: 05/15/2006
• Published: 11/16/2006
• Est. Priority Date: 05/16/2005
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Higuchi, Masaya
• Granted Patent: US 7,499,944 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

The present invention provides a dimension table processing apparatus that efficiently derives a dimension hierarchy for a denormalized dimension table. The first information presenting means presents information on a plurality of fact tables included in a multidimensional database to an administrator as first information. The second information presenting means receives a fact table selected by the administrator from among the fact tables related to the first information, and presents information on dimension tables associated with the selected fact table in a star schema to the administrator as second information. The dimension hierarchy deriving means receives a dimension table selected by the administrator from among the dimension tables related to the second information as the one to be processed, calculates a predetermined evaluation value for each column in the dimension table to be processed based on data in each cell belonging to that column, and derives a dimension hierarchy of the columns, which consists of a plurality of hierarchical levels of the respective columns, based on the evaluation values thereof. The third information presenting means presents the dimension hierarchy for the dimension table to be processed derived by the dimension hierarchy deriving means to the administrator as third information. The approval receiving means receives a dimension hierarchy approved by the administrator based on the third information as an approved dimension hierarchy.

Claims

1. A dimension table processing apparatus comprising:

first information presenting means for presenting information on a plurality of fact tables included in a multidimensional database as first information;

second information presenting means for receiving a fact table selected from among the fact tables related to the first information and presenting information on dimension tables associated with said fact table in a star schema as second information;

dimension hierarchy deriving means for receiving a dimension table selected from among the dimension tables related to the second information as the one to be processed, calculating a predetermined evaluation value for each column in the dimension table to be processed based on data in each cell belonging to that column, and deriving dimension hierarchy of the columns based on the evaluation values thereof;

third information presenting means for presenting the derived dimension hierarchy for said dimension table to be processed as third information; and

approval receiving means for receiving dimension hierarchy approved based on the third information as approved dimension hierarchy.

2. The dimension table processing apparatus according to claim 1, further comprising:

switching means for determining whether said dimension table to be processed is either denormalized or normalized, and performing switching so that said dimension table to be processed is received by said dimension deriving means when it is denormalized, and said dimension table to be processed is received by normalized dimension table processing means when it is normalized;

the normalized dimension table processing means for employing the received dimension table to be processed as a starting dimension table to sequentially trace dimension tables from one having a referencing column to one having a column referenced by the referencing column under the referential integrity constraint, and deriving a dimension hierarchy of the referenced columns in the dimension tables by assigning a lowest hierarchical level to the referenced column in the starting dimension table with higher hierarchical levels assigned to the referenced columns in the dimension tables following the starting dimension table in order of the tracing; and

third information presenting means for presenting the dimension hierarchy that the normalized dimension table processing means has derived from the dimension tables as third information.

3. The dimension table processing apparatus according to claim 1, wherein said dimension hierarchy deriving means processes all the dimension tables included in the second information, instead of a dimension table selected from among the dimension tables related to the second information.

4. The dimension table processing apparatus according to claim 1, wherein said approval receiving means receives said approved dimension hierarchy, which includes a corrected dimension hierarchy that has been corrected and approved based on the third information.

5. The dimension table processing apparatus according to claim 4, wherein said corrected dimension hierarchy includes one or more hierarchical levels including one or more hierarchical levels that remain after deletion of part of a plurality of hierarchical levels presented in the third information, one or more hierarchical levels that remain after deletion of all the hierarchical levels existing on a branch split off from a predetermined hierarchical level in the plurality of hierarchical levels presented in said third information, or one or more hierarchical levels for which a branching point is changed.

6. The dimension table processing apparatus according to claim 1, further comprising saving means for saving an approved dimension hierarchy as metadata of the multidimensional database.

7. The dimension table processing apparatus according to claim 1, wherein said evaluation value calculated by said dimension hierarchy deriving means is a cardinality for each column, or a correlation coefficient for each column relative to a predetermined column.

8. A dimension hierarchy deriving apparatus comprising:

evaluation value calculating means for calculating an evaluation value for each column in a dimension table to be processed based on data of each cell belonging to that column;

ranking means for ranking the columns of said dimension table to be processed based on the evaluation values; and

dimension hierarchy determining means for determining dimension hierarchy of the columns in said dimension table to be processed according to said ranking.

9. The dimension hierarchy deriving apparatus according to claim 8, wherein the evaluation value is a cardinality for each column, or a correlation coefficient relative to a predetermined column.

10. A dimension table processing method comprising:

a first information presenting step of presenting information on a plurality of fact tables included in a multidimensional database as first information;

a second information presenting step of receiving a fact table selected from among the fact tables related to the first information and presenting information on dimension tables associated with said fact table in a star schema as second information;

a dimension hierarchy deriving step of receiving a dimension table selected from among the dimension tables related to the second information as the one to be processed, calculating a predetermined evaluation value for each column in the dimension table to be processed based on data in each cell belonging to that column, and deriving dimension hierarchy of the columns based on the evaluation values thereof;

a third information presenting step of presenting the derived dimension hierarchy in said dimension table to be processed as third information; and

an approval receiving step of receiving dimension hierarchy approved based on the third information as approved dimension hierarchy.

11. The dimension table processing method according to claim 10, further comprising:

a switching step of determining whether said dimension table to be processed is either denormalized or normalized, and performing switching so that said dimension table to be processed is received by said dimension deriving step when it is denormalized, and said dimension table to be processed is received by normalized dimension table processing step when it is normalized;

the normalized dimension table processing step of employing the received dimension table to be processed as a starting dimension table to sequentially trace dimension tables from one having a referencing column to one having a column referenced by the referencing column under the referential integrity constraint, and deriving a dimension hierarchy of the referenced columns in the dimension tables by assigning a lowest hierarchical level to the referenced column in the starting dimension table with higher hierarchical levels assigned to the referenced columns in the dimension tables following the starting dimension table in order of the tracing; and

a third information presenting step of presenting the dimension hierarchy that the normalized dimension table processing step has derived from the dimension tables as third information.

12. The dimension table processing method according to claim 10, wherein said dimension hierarchy deriving step processes all the dimension tables included in the second information, instead of a dimension table selected from among the dimension tables related to the second information.

13. The dimension table processing method according to claim 10, wherein said approved dimension hierarchy received by said approval receiving step includes a corrected dimension hierarchy that has been corrected and approved based on the third information.

14. The dimension table processing method according to claim 13, wherein said corrected dimension hierarchy includes one or more hierarchical levels including one or more hierarchical levels that remain after deletion of part of a plurality of hierarchical levels presented in the third information, one or more hierarchical levels that remain after deletion of all the hierarchical levels existing on a branch split off from a predetermined hierarchical level in the plurality of hierarchical levels presented in said third information, or one or more hierarchical levels for which a branching point is changed.

15. The dimension table processing method according to claim 10, further comprising a saving step of saving an approved dimension hierarchy as metadata of the multidimensional database.

16. The dimension table processing method according to claim 10, wherein the evaluation value calculated in said dimension hierarchy deriving step is a cardinality for each column, or a correlation coefficient for each column relative to a predetermined column.

17. A dimension hierarchy deriving method comprising:

an evaluation value calculating step of calculating an evaluation value for each column in a dimension table to be processed based on data of each cell belonging to that column;

a ranking step of ranking the columns of said dimension table to be processed based on the evaluation values; and

a dimension hierarchy determining step of determining dimension hierarchy of the columns in said dimension table to be processed according to said ranking.

18. The dimension hierarchy deriving method according to claim 17, wherein the evaluation value is a cardinality for each column, or a correlation coefficient relative to a predetermined column.

19. A dimension table processing program for causing a computer to execute:

a first information presenting step of presenting information on a plurality of fact tables included in a multidimensional database as first information;

a second information presenting step of receiving a fact table selected from among the fact tables related to the first information and presenting information on dimension tables associated with said fact table in a star schema as second information;

a dimension hierarchy deriving step of receiving a dimension table selected from among the dimension tables related to the second information as the one to be processed, calculating a predetermined evaluation value for each column in the dimension table to be processed based on data in each cell belonging to that column, and deriving dimension hierarchy of the columns based on the evaluation values thereof;

a third information presenting step of presenting the derived dimension hierarchy in said dimension table to be processed as third information; and

an approval receiving step of receiving dimension hierarchy approved based on the third information as approved dimension hierarchy.

20. The dimension table processing program according to claim 19, which causes said computer to further execute:

a switching step of determining whether said dimension table to be processed is either denormalized or normalized, and performing switching so that said dimension table to be processed is received by said dimension deriving step when it is denormalized, and said dimension table to be processed is received by normalized dimension table processing step when it is normalized;

the normalized dimension table processing step of employing the received dimension table to be processed as a starting dimension table to sequentially trace dimension tables from one having a referencing column to one having a column referenced by the referencing column under the referential integrity constraint, and deriving a dimension hierarchy of the referenced columns in the dimension tables by assigning a lowest hierarchical level to the referenced column in the starting dimension table with higher hierarchical levels assigned to the referenced columns in the dimension tables following the starting dimension table in order of the tracing; and

a third information presenting step of presenting the dimension hierarchy that the normalized dimension table processing step has derived from the dimension tables as third information.

21. The dimension table processing program according to claim 19, wherein said dimension hierarchy deriving step processes all the dimension tables included in the second information, instead of a dimension table selected from among the dimension tables related to the second information.

22. The dimension table processing program according to claim 19, wherein said approved dimension hierarchy received by said approval receiving step includes a corrected dimension hierarchy corrected and approved based on the third information.

23. The dimension table processing program according to claim 22, wherein said corrected dimension hierarchy includes one or more hierarchical levels including one or more hierarchical levels that remain after deletion of part of a plurality of hierarchical levels presented in the third information, one or more hierarchical levels that remain after deletion of all the hierarchical levels existing on a branch split off from a predetermined hierarchical level in the plurality of hierarchical levels presented in said third information, or one or more hierarchical levels for which a branching point is changed.

24. The dimension table processing program according to claim 19, which causes said computer to further execute a saving step of saving an approved dimension hierarchy as metadata of the multidimensional database.

25. The dimension table processing program according to claim 19, wherein the evaluation value calculated in said dimension hierarchy deriving step is a cardinality for each column, or a correlation coefficient for each column relative to a predetermined column.

26. A dimension hierarchy deriving program for causing a computer to execute:

an evaluation value calculating step of calculating an evaluation value for each column in a dimension table to be processed based on data of each cell belonging to that column;

a ranking step of ranking the columns of said dimension table to be processed based on the evaluation values; and

a dimension hierarchy determining step of determining dimension hierarchy of the columns in said dimension table to be processed according to said ranking.

27. The dimension hierarchy deriving program according to claim 26, wherein the evaluation value is a cardinality for each column, or a correlation coefficient relative to a predetermined column.