System to generate a structured query language query by using a dimensional hierarchy

• US 8,229,966 B2
• Filed: 09/07/2007
• Issued: 07/24/2012
• Est. Priority Date: 09/07/2007
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Nolan, Ellen
• Primary Examiner(s): Ali, Mohammad
• Assistant Examiner(s): WILLIS, AMANDA LYNN
• Application Number:
  US11/851,417
• Publication Number:
  US 20090070359A1
• Time in Patent Office: 1,782 Days
• Field of Search: 707791-801, 707/956, 707/958, 707/999.101, 707/E17.008
• US Class Current: 707/797
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A tree-structured database is collapsed into a two-column table. The two-column table includes a characteristic key value for a given leaf node in the first column and the system identification (SID) relating to the characteristic key value in the second column and also identifying the node that leads to the characteristic key value. Data is presented by coupling the characteristic key value with a transaction table. A query that is formulated in an infocube may be translated to a relational data base query, collapsed, and reformulated into an infocube result set, or vice versa.

Claims

1. A machine-implemented method of collapsing a data tree hierarchy, the data tree hierarchy including leaf nodes and at least one root node, and each node including a system identification (SID), the method comprising:

for each leaf node,

at a leaf-node row;

writing a leaf-node key value to a first column in a data table; and

writing a corresponding leaf-node SID value to a second column in the data table; and

at a root-node row;

writing the leaf-node key value to the first column; and

writing a corresponding root-node SID value to the second column, the entire data tree hierarchy collapsed into the first column and the second column, the second column having the leaf-node SID values representing a trail of traversal of the tree for each leaf node through intermediate nodes and to the at least one root node.

2. The method of claim 1, the data-tree hierarchy including at least one internal node, the method further including:

at an internal-node row;

writing the leaf-node key value to the first column; and

writing a corresponding internal-node SID value to the second column.

3. The method of claim 1, further including:

at a subsequent leaf-node row;

writing a subsequent leaf-node key value to the first column; and

writing a corresponding subsequent leaf-node SID value to the second column; and

at a root-node row;

writing the subsequent leaf-node key value to the first column; and

writing a corresponding root-node SID value to the second column.

4. The method of claim 1, further including:

at a subsequent leaf-node row;

writing a subsequent leaf-node key value to the first column; and

writing a corresponding subsequent leaf-node SID value to the second column;

at a root-node row;

writing the subsequent leaf-node key value to the first column; and

writing a corresponding root-node SID value to the second column; and

aggregating the SID values.

5. The method of claim 1, further including:

receiving a search criteria via an infocube interface from a user;

automatically transitioning from the infocube interface to a relational database interface;

using the relational database interface to search the relational database and to acquire an answer set that includes at least the first leaf-node row and the root-node row; and

automatically translating the answer set to a data format used and recognized by the infocube interface and presenting to the user within the infocube interface.

6. The method of claim 5, wherein automatically transitioning further includes evaluating the search query for key values and filtering criteria defined therein and querying metadata tables of the relational database to map infocube constructs found in the search criteria to relational database constructs.

7. The system of claim 6, wherein using further includes assembling the relational database constructs into a SQL query and processing the SQL query as the search against the relational database.

8. The method of claim 1, further including joining each leaf node key value to a transaction table.

9. A machine-implemented method, comprising:

collapsing an entire data tree hierarchy into a two-column data table, the data tree hierarchy includes leaf nodes, at least one intermediate node, and at least one root node, and each node including a system identification (SID);

collapsing includes;

for each leaf node, writing a leaf-node key value to only the first column in the data table, and writing a corresponding leaf-node SID value to only the second column in the data table, the second column representing a trail of traversal through the data tree hierarchy for each leaf node though the at least one intermediate node and to the at least one root node;

at a next row, writing the leaf-node key value to the first column, and writing a corresponding internal-node SID value to the second column;

at yet a next row, writing the leaf-node key value to the first column, and writing a corresponding root-node SID value to the second column;

at still yet a next row, writing a subsequent leaf-node key value to the first column, and writing a corresponding subsequent leaf-node SID value to the second column; and

aggregating the SID values.

10. The method of claim 9, further including:

receiving a search criteria via an infocube interface from a user;

automatically transitioning from the infocube interface to a relational database interface;

using the relational database interface to search the relational database and to acquire an answer set that includes at least the first leaf-node row and the root-node row; and

automatically translating the answer set to a data format used and recognized by the infocube interface and presenting to the user within the infocube interface.

11. The method of claim 10, wherein automatically transitioning further includes evaluating the search query for key values and filtering criteria defined therein and querying metadata tables of the relational database to map infocube constructs found in the search criteria to relational database constructs.

12. The system of claim 11, wherein using further includes assembling the relational database constructs into a SQL query and processing the SQL query as the search against the relational database.

13. The method of claim 9, further including joining each leaf node key value to a transaction table.

14. The method of claim 9, further including:

joining each leaf node key value to a transaction table;

aggregating data using SID values; and

presenting the SID value for the requested node with the aggregated data.