Supporting set-level slice and dice in data warehouses

Patent metadata

Field	Value
Publication number	US-9646071-B2
Application number	US-201213607320-A
Country	US
Kind code	B2
Filing date	Sep 7, 2012
Priority date	Nov 11, 2009
Publication date	May 9, 2017
Grant date	May 9, 2017

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.

First claim

We claim:

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 Bo

Assignees

• He Bin
• Chen Ying
• IBM

Inventors

• He Bin
• Chen Ying

Classifications

• G06F16/24528

  Standardisation; Simplification · CPC title

• G06F16/283 Primary

  Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP · CPC title

• G06F17/30439

  Physics · mapped topic

• G06F17/30592 Primary

  Physics · mapped topic