Supporting set-level slice and dice in data warehouses

• US 9,646,071 B2
• Filed: 09/07/2012
• Issued: 05/09/2017
• 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): Reyes, Mariela
• Assistant Examiner(s): CONYERS, DAWAUNE A
• Application Number:
  US13/607,320
• Publication Number:
  US 20130006969A1
• Time in Patent Office: 1,705 Days
• Field of Search: 707602
• 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 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, wherein the queries include set level predicates,

wherein the early aggregation utilizes the intermediate aggregation results for creating the queries without storing tuples used to create the intermediate aggregation results;

wherein the early aggregation aggregates functions with a GETSUM function by;

selecting a first sum key from a first GETSUM group;

grouping members of the first GETSUM group by the first sum key;

selecting a second sum key; and

grouping members of the first GETSUM group by the second sum key;

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

wherein in response to CONTAINS being selected for use in the queries, the algorithm;

utilizes a bitmap variable, mapping a 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 0 for a single tuple not containing the specified object;

combines two groups of bitmap variables with the UNION operator;

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, and

wherein in response to either NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, or EQUALS, or NOT_EQUALS being selected for use in the queries, the algorithm;

utilizes at least one of;

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

sets the bit mapped to the contained object and the Boolean variable based on existence of the 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,

creating a group key and a non-group key, wherein the group key identifies a group of objects and the non-group key identifies a single object;

determining those of the queries that have only a single set-level operator;

determining those of the queries that have more than one set-level operator;

rewriting all of the queries as ANDED set level predicates with a remaining expression, wherein the queries with only a single set-level operator are first rewritten as the ANDED set level predicates, and then those of the queries with more than one set-level operator are rewritten as the ANDED set level predicates;

inserting the group key in a first temporary table; and

for the remaining expression;

inserting the group key into a second temporary table;

joining the queries with the group key and the non-group key to reference the group of objects and the single object, wherein the queries include matching the group key and the non-group key with specified values in a single expression;

creating a set of conditions for joining the ANDED set-level predicates and the remaining expression with dimension tables;

joining the set-level predicates from the queries to dimension tables corresponding to fact tables according to the created set of conditions;

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.

2. The method of claim 1, 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 the specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing the 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.

3. The method of claim 1, 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 the specified object;

sets the bit mapped to the contained object to 1 for a single tuple not containing the 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.

4. The method of claim 1, wherein the algorithm for utilizing a CONTAINED_BY function:

utilizes a Boolean variable;

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

sets the Boolean variable to FALSE for a single tuple not containing the 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.

5. The method of claim 1, 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 the specified object;

sets the Boolean variable to FALSE for a single tuple not containing the 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.

6. The method of claim 1, wherein the algorithm for utilizing an EQUALS function:

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

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

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

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

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

sets the Boolean variable to FALSE for a single tuple not containing the 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.

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

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

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

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

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

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

sets the Boolean variable to FALSE for a single tuple not containing the 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.

8. 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 configured to input 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 is configured to utilize early aggregation for creating the queries, generating intermediate aggregation results;

wherein the early aggregation for creating the queries utilizes the intermediate aggregation results for creating the queries without storing tuples used to create the intermediate aggregation results;

wherein the data warehouse query engine aggregates the aggregation functions utilizing a GETSUM function in creating the queries by;

selecting a first sum key from a first GETSUM group;

grouping members of the first GETSUM group by the first sum key;

selecting a second sum key;

grouping members of the first GETSUM group by the second sum key; and

filtering the GETSUM function with a CONTAINS function;

the data warehouse query engine further creates the queries with an algorithm by utilizing at least one function selected from the group consisting of CONTAINS, NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, EQUALS, and NOT_EQUALS functions, 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 mapped to the contained object and the Boolean variable based on existence of the contained object;

combines two groups of bitmap variables with a UNION operator;

combines groups of Boolean variables, that have been found to intersect using an INTERSECT operator, with an AND operator,

wherein the data warehouse query engine is configured to;

create a group key;

rewrite the queries as ANDED set level predicates with a remaining expression,

wherein at least one of the set level predicates is based on values of the combined groups of bitmap variables and the combined groups of Boolean variables, and

wherein the at least one of the set level predicates is implemented as an IN function joined to at least one attribute of the at least one of the set level predicates; and

insert the group key in a first temporary table; and

for the remaining expression the data warehouse query engine is configured to;

insert the group key into a second temporary table;

implement one of the set-level predicates in the remaining expression as an attribute of the group key in the second temporary table;

join the queries with the group key; and

join the set-level predicates from the queries to dimension tables corresponding to fact tables,

wherein the data warehouse query engine is configured to

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

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

9. The system 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 0 for a single tuple not containing the 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 system 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 the 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 system 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 the 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 system 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 the 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 system 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 the specified object;

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

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

sets the Boolean variable to FALSE for a single tuple not containing the 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.

14. The system 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 the specified object;

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

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

sets the Boolean variable to FALSE for a single tuple not containing the 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.

15. A computer program product stored on a non-transitory computer storage medium for implementing set-level slice and dice operations, comprising:

computer code configured to generate queries for implementing the set-level slice and dice operations with;

computer program code configured to create a group key and a non-group key for use in creating the queries, wherein the group key identifies a group of objects and the non-group key identifies a single object;

computer program code configured to store the group key in a first temporary table;

computer program code configured to generate the queries with an algorithm by utilizing at least one function selected from the group consisting of CONTAINS, NOT_CONTAINS, CONTAINED_BY, NOT_CONTAINED_BY, EQUALS, and NOT_EQUALS functions implementing the slice and dice operations, the algorithm being implemented by;

computer program code configured to utilize at least one of;

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

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

computer program code configured to combine two groups of bitmap variables with a UNION operator;

computer program code configured to match both the group key and the non-group key in a single expression, thereby selecting the group of objects and the single object in the single expression;

computer program code configured to combine groups of Boolean variables, that have been found to intersect using an INTERSECT operator, with an AND operator,

wherein the EQUALS function includes syntax that utilizes both the CONTAINS and CONTAINED BY operators;

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

computer program code configured to rewrite the queries as ANDED set level predicates with a remaining expression,

wherein at least one of the set level predicates is based on values of the bitmap variable and the Boolean variable, and

wherein the at least one of the set level predicates is implemented as an IN function joined to at least one attribute of the at least one of the set level predicates; and

for the remaining expression;

computer program code configured to join the queries using the group key after inserting the group key in a second temporary table; and

computer program code configured to implement one of the set-level predicates in the remaining expression as an attribute of the group key in the second temporary table;

computer program code configured to create a set of conditions for joining the ANDED set-level predicates and the remaining expression with dimension tables;

computer program code configured to join the set-level predicates from the queries to the dimension tables corresponding to fact tables according to the created conditions, wherein the dimension tables have a one-to-one relationship with the fact tables;

computer program code configured to output a result of the algorithm on a computer user interface.