Supporting set-level slice and dice in data warehouses

• US 8,326,797 B2
• Filed: 11/11/2009
• Issued: 12/04/2012
• Est. Priority Date: 11/11/2009
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: He, Bin, Chen, Ying
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): GRAHAM, ALAN G
• Application Number:
  US12/616,612
• Publication Number:
  US 20110113005A1
• Time in Patent Office: 1,119 Days
• Field of Search: 707/602, 707/719, 707/759
• US Class Current: 707/602
• CPC Class Codes: G06F 16/24528 Standardisation; Simplification
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A method and system for coping with slice and dice operations in data warehouses is disclosed. An external approach may be utilized, creating queries using structured query language on a computer. An algorithm may be used to rewrite the queries. The resulting predicates may be joined to dimension tables corresponding to fact tables. An internal approach may be utilized, using aggregation functions with early aggregation for creating the queries. The results of the slice and dice operations may be outputted to a user on a computer monitor.

Claims

1. A method for implementing on-line analytical processing (OLAP) set-level slice and dice operations, comprising:

creating queries using structured query language on a computer and storing the queries on a storage medium;

utilizing an algorithm to rewrite the queries and implement OLAP slice and dice operations, wherein the algorithm;

rewrites the queries as ANDED set-level predicates with a remaining expression;

for at least one of the ANDED set-level predicates in the queries;

utilizes at least one selected from the group consisting of INTERSECT and UNION functions for query expressions with multiple set-level predicates;

utilizes at least one selected from the group consisting of CONTAINS, NOT CONTAINS, CONTAINED BY, NOT CONTAINED BY, EQUALS, and NOT EQUALS functions for rewriting the queries;

utilizes a DISTINCT function in combination with the UNION function on a group key; and

inserts the group key into a first temporary table;

for the remaining expression

inserts the group key into a second temporary table;

joins query expressions using the group key; and

uses an IN function with the group key; and

joins the set-level predicates from the queries to dimension tables corresponding to fact tables;

applying the algorithm to the queries to generate a result of the queries; and

outputting the result of the queries on a computer user interface.

2. The method of claim 1, wherein the method rewrites queries having a single set-level operator.

3. The method of claim 1, wherein the method rewrites queries having more than one set-level operator.

4. The method of claim 1, wherein the method includes using at least one selected from the group consisting of INTERSECT and EXCEPT operators to create queries for set-level operators.

5. A system for implementing on-line analytical processing (OLAP) set-level slice and dice operations in a data warehouse, comprising:

a data warehouse user interface, including an input device, and computer monitor, permitting a user to create queries using structured query language on the data warehouse user interface and store the queries on a storage medium;

a data warehouse query engine hardware device including a computer processor, that utilizes an algorithm to rewrite queries, and implement OLAP slice and dice operations, wherein the algorithm

rewrites the queries as ANDED set-level predicates with a remaining expression;

for at least one of the ANDED set-level predicates in the queries in a plurality of predicates;

utilizes at least one selected from the group consisting of CONTAINS, NOT CONTAINS, CONTAINED BY, NOT CONTAINED BY, EQUALS, and NOT EQUALS functions for rewriting the queries;

utilizes at least one selected from the group consisting of INTERSECT and UNION functions for query expressions with multiple set-level predicates;

utilizes a DISTINCT function in combination with the UNION function on a group key; and

inserts the group key into a first temporary table;

for the remaining expression, the algorithm;

utilizes at least one selected from the group consisting of CONTAINS, NOT CONTAINS, CONTAINED BY, NOT CONTAINED BY, EQUALS, and NOT EQUALS functions for rewriting the queries;

inserts the group key into a second temporary table;

joins query expressions using the group key; and

uses an IN function with the group key; and

joins the set-level predicates from the queries to dimension tables corresponding to fact tables;

applying the algorithm to the queries to generate a result of the queries; and

a computer user interface outputting the result of the queries to a computer monitor.

6. The system of claim 5, wherein the data warehouse query engine uses at least one selected from the group consisting of INTERSECT and EXCEPT operators to create queries for set-level operators.