SUPPORTING SET-LEVEL SLICE AND DICE IN DATA WAREHOUSES
First Claim
Patent Images
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.
1 Assignment
0 Petitions
Accused Products
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.
-
Citations
24 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. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
-
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. - View Dependent Claims (9, 10, 11, 12, 13, 14)
-
-
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. - View Dependent Claims (16)
-
-
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; andapplying the algorithm to generate a result of the queries; and the data warehouse user interface outputting the results of algorithm to a computer monitor. - View Dependent Claims (18, 19, 20, 21, 22, 23)
-
-
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.
-
Specification