SUPPORTING SET-LEVEL SLICE AND DICE IN DATA WAREHOUSES

• US 20110113005A1
• Filed: 11/11/2009
• Published: 05/12/2011
• Est. Priority Date: 11/11/2009
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: HE, BIN, CHEN, YING
• Granted Patent: US 8,326,797 B2
• Time in Patent Office: Days
• 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 the 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 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 a DISTINCT function on a group key; and

inserts the group key into a first temporary table;

for the remaining expression in at least one of the set-level predicates from one of 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

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

2. The method of claim 1, wherein the method utilizes at least one selected from the group consisting of CONTAINS and NOT CONTAINS functions for rewriting the queries.

3. The method of claim 1, wherein the method utilizes at least one selected from the group consisting of CONTAINED BY and NOT CONTAINED BY functions for rewriting the queries.

4. The method of claim 1, wherein the method utilizes at least one selected from the group consisting of EQUALS and NOT EQUALS functions for rewriting the queries.

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

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

7. 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.

8. A method for implementing set-level slice and dice operations in a data warehouse, comprising:

using aggregation functions to create queries for slice and dice operations on a computer;

utilizing early aggregation for creating the queries, generating intermediate aggregation results;

utilizing at least one selected from the group consisting of CONTAINS, NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, EQUALS, and NOT_EQUALS functions in creating the queries with an algorithm, wherein the algorithm

utilizes at least one of;

a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets a bit map to the object and the Boolean variable based on existence of a contained object;

combines two groups of bitmap variables with a UNION operator;

combines groups of Boolean variables with an AND operator; and

sets a predicate value based on at least one of;

the values of the bitmap value and the Boolean variable values;

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

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

9. The method of claim 8, wherein the algorithm for utilizing a CONTAINS function:

utilizes a bitmap variable, mapping the contained object to a bit;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing a specified object;

combines two groups of bitmap variables with a UNION operator; and

sets the function predicate to true for a bitmap variable with no zero bit; and

sets the function predicate to false for a bitmap variable with a zero bit.

10. The method of claim 8, wherein the algorithm for utilizing a NOT CONTAINS function:

utilizes a bitmap variable, mapping the contained object to a bit;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing a specified object;

combines two groups of bitmap variables with a UNION operator; and

sets the function predicate to true for a bitmap variable with a zero bit; and

sets the function predicate to false for a bitmap variable with no zero bit.

11. The method of claim 8, wherein the algorithm for utilizing a CONTAINED_BY function:

utilizes a Boolean variable;

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to TRUE; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to FALSE.

12. The method of claim 8, wherein the algorithm for utilizing a NOT CONTAINED_BY function:

utilizes a Boolean variable;

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to FALSE; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to TRUE.

13. The method of claim 8, wherein the algorithm for utilizing an EQUALS function:

utilizes a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 0 for a single tuple not containing a specified object;

combines a plurality of bitmap variables with a UNION operator; and

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for Boolean variables evaluating to TRUE, and bitmap variables with no zero bit; and

sets the function predicate to FALSE for Boolean variables evaluating to FALSE, or bitmap variables with a zero bit.

14. The method of claim 8, wherein the algorithm for utilizing a NOT EQUALS function:

utilizes a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 0 for a single tuple not containing a specified object;

combines a plurality of bitmap variables with a UNION operator; and

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for Boolean variables evaluating to FALSE, or bitmap variables with a zero bit; and

sets the function predicate to FALSE for Boolean variables evaluating to TRUE, and bitmap variables with no zero bit.

15. 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 the 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 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 on a group key; and

inserts the group key into a first temporary table;

for at least one of the set-level predicates in the remaining expression from one of the queries, 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.

16. The system of claim 15, 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.

17. 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 for inputting aggregation functions to create queries for slice and dice operations on a computer;

a data warehouse query engine hardware device including a computer processor, which utilizes early aggregation for creating the queries, generating intermediate aggregation results;

the data warehouse query engine further utilizing at least one selected from the group consisting of CONTAINS, NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, EQUALS, and NOT_EQUALS functions in creating the queries with an algorithm, wherein the algorithm;

utilizes at least one of;

a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets a bit map to the object and the Boolean variable based on existence of a contained object;

combines two groups of bitmap variables with a UNION operator;

combines groups of Boolean variables with an AND operator; and

sets a predicate value based on at least one of;

the values of the bitmap value and the Boolean variable values; and

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

the data warehouse user interface outputting the results of algorithm to a computer monitor.

18. The system of claim 17, wherein the algorithm for utilizing a CONTAINS function:

utilizes a bitmap variable, mapping the contained object to a bit;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing a specified object;

combines two groups of bitmap variables with a UNION operator; and

sets the function predicate to true for a bitmap variable with no zero bit; and

sets the function predicate to false for a bitmap variable with a zero bit.

19. The system of claim 17, wherein the algorithm for utilizing a NOT CONTAINS function:

utilizes a bitmap variable, mapping the contained object to a bit;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing a specified object;

combines two groups of bitmap variables with a UNION operator; and

sets the function predicate to true for a bitmap variable with a zero bit; and

sets the function predicate to false for a bitmap variable with no zero bit.

20. The system of claim 17, wherein the algorithm for utilizing a CONTAINED_BY function:

utilizes a Boolean variable;

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to TRUE; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to FALSE.

21. The system of claim 17, wherein the algorithm for utilizing a NOT CONTAINED_BY function:

utilizes a Boolean variable;

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to FALSE; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to TRUE.

22. The system of claim 17, wherein the algorithm for utilizing an EQUALS function:

utilizes a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 0 for a single tuple not containing a specified object;

combines a plurality of bitmap variables with a UNION operator; and

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to TRUE, and a combination of bitmap variables with no zero bit; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to FALSE, or a combination of bitmap variables with a zero bit.

23. The system of claim 17, wherein the algorithm for utilizing a NOT EQUALS function:

utilizes a bitmap variable mapping a contained object to a bit, and a Boolean variable;

sets the bit mapped to the contained object to 1 for a single tuple containing a specified object;

sets the bit mapped to the contained object to 0 for a single tuple not containing a specified object;

combines a plurality of bitmap variables with a UNION operator; and

sets the Boolean variable to TRUE for a single tuple containing a specified object;

sets the Boolean variable to FALSE for a single tuple not containing a specified object;

combines a plurality of Boolean variables with an AND operator; and

sets the function predicate to TRUE for a combination of Boolean variables evaluating to FALSE, or a combination of bitmap variables with a zero bit; and

sets the function predicate to FALSE for the combination of Boolean variables evaluating to TRUE, and a combination of bitmap variables with no zero bit.

24. A computer program product for implementing set-level slice and dice operations, comprising:

computer program code utilizing early aggregation for creating the queries, generating intermediate aggregation results;

computer program code utilizing at least one of the group consisting of CONTAINS, NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, EQUALS, and NOT_EQUALS functions in creating the queries with an algorithm implementing the slice and dice operations, the algorithm including;

computer program code utilizing at least one of;

a bitmap variable mapping a contained object to a bit, and a Boolean variable;

computer program code setting the bit map to the object and the Boolean variable based on existence of a contained object;

computer program code combining two groups of bitmap variables with a UNION operator;

computer program code combining groups of Boolean variables with an AND operator; and

computer program code setting a predicate value based on at least one of;

the values of the bitmap value and the Boolean variable values;

computer program code applying the algorithm to generate a result of the queries; and

computer program code outputting a result of the algorithm on a computer user interface.