×

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
First Claim
Patent Images

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

View all claims
  • 0 Assignments
Timeline View
Assignment View
    ×
    ×