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

• US 7,499,944 B2
• Filed: 05/15/2006
• Issued: 03/03/2009
• Est. Priority Date: 05/16/2005
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Higuchi, Masaya
• Primary Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US11/434,271
• Publication Number:
  US 20060259507A1
• Time in Patent Office: 1,023 Days
• Field of Search: 707100-1041
• 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 residing on a data warehouse as first information to an administrator;

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 to an administrator;

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 to an administrator;

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

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 to an administrator.

2. A dimension table processing method comprising:

presenting information on a plurality of fact tables included in a multidimensional database residing on a data warehouse as first information to an administrator;

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 to an administrator;

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;

presenting the derived dimension hierarchy in said dimension table to be processed as third information to an administrator; and

receiving dimension hierarchy approved based on the third information as approved dimension hierarchy;

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 receiving step when it is denormalized, and said dimension table to be processed is received by a normalizing step when it is normalized;

the normalizing step 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

presenting the dimension hierarchy that the

normalizing step has derived from the dimension tables as third information to an administrator.

3. A computer-readable medium containing a program which, when executed, performs dimension table processing. comprising the steps of:

presenting information on a plurality of fact tables included in a multidimensional database residing on a data warehouse as first information to an administrator;

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 to an administrator;

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;

presenting the derived dimension hierarchy in said dimension table to be processed as third information to an administrator; and

receiving dimension hierarchy approved based on the third information as approved dimension hierarchy;

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 receiving step when it is denormalized, and said dimension table to be processed is received by a normalizing step when it is normalized;

the normalizing step 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

presenting the dimension hierarchy that the normalizing step has derived from the dimension tables as third information to an administrator.