OLAP Execution Model Using Relational Operations

Abstract

In one embodiment the present invention includes an OLAP execution model using relational operations. In one embodiment, the present invention includes, a method comprising receiving a first query in an online analytic processor (OLAP) executing on one or more computers, the OLAP generating and comprising a model specifying a graph defining a plurality of nodes and a plurality of tiers, each node corresponding to a different operation on data. A second query is generated by the OLAP. The second query includes a plurality of layered subqueries each corresponding to one of the nodes in the graph for specifying the different operations on data. The second query is received in a relational engine coupled to the datastore. The relational engine executes the second query, and in accordance therewith, retrieves data.

What is claimed is: 
     
         1 . A method comprising:
 receiving a first query in an online analytic processor (OLAP) executing on one or more computers;   generating a model, the model specifying a graph defining a plurality of nodes and a plurality of tiers, each node corresponding to a different operation on data;   generating a second query based on the model, the second query including a plurality of layered subqueries each corresponding to one of the nodes in the graph for specifying the different operations on the data: and   receiving the second query in a relational engine coupled to a datastore, wherein the relational engine executes the second query, and in accordance therewith, retrieves data.   
     
     
         2 . The method of  claim 1  wherein a first node on a highest tier in the model corresponds to a subquery comprising an exception aggregation, the exception aggregation associating a first aggregation type with a first measure and a first dimension of a dimension table, the first aggregation type being different than a default aggregation type used on the first measure when aggregating said first measure across other dimensions, wherein the second query comprises at least one SQL statement corresponding to a second node on a tier below the highest tier nested in a SQL statement corresponding to the first query. 
     
     
         3 . The method of  claim 1  wherein the OLAP generates and comprises said model, and wherein the second query is generated by said OLAP. 
     
     
         4 . The method of  claim 1  wherein the plurality of nodes correspond to successive operations on the data in the database performed by the relational engine. 
     
     
         5 . The method of  claim 1  wherein the plurality of nodes of said graph comprise:
 a first node on a first tier corresponding to first data, wherein the first data is all the data required to answer the first query, the first node having a corresponding first subquery for retrieving the first data; and 
 a plurality second nodes on a plurality of tiers above the first tier, each second node corresponding to a different operation on the first data. 
 
     
     
         6 . The method of  claim 1  wherein the plurality of nodes includes a root node, a plurality of second tier nodes, and one or more higher tier nodes, wherein the root node has a corresponding first subquery to operate on raw data in the database to produce first data required to answer the first query, wherein the plurality of second tier nodes are coupled to the root node in the graph and each have corresponding subqueries that operate on the first data, and wherein at least one of the second tier nodes is coupled to the one or more higher tier nodes, wherein each higher tier node has a corresponding subquery for operating on data generated by the subquery of the at least one second tier node. 
     
     
         7 . The method of  claim 1  wherein the datastore is a database, and wherein the data is stored in the database in at least one fact table and a plurality of dimension tables. 
     
     
         8 . The method of  claim 1  wherein the second query is a nested SQL statement. 
     
     
         9 . The method of  claim 1  wherein each subquery is a database view containing a SQL statement. 
     
     
         10 . The method of  claim 1  wherein a portion of the nodes of the graph are represented by subqueries executed in the relational engine and one or more of the nodes in the graph correspond to data operations executed by said OLAP, the method further comprising:
 returning said retrieved data from the relational engine to the OLAP; 
 processing the retrieved data in the OLAP based on said one or more nodes in the graph corresponding to data operations executed by said OLAP; and 
 returning a result to said first query. 
 
     
     
         11 . The method of  claim 1  wherein the graph specifies an execution plan where operations corresponding to lower tier nodes are performed on data before operations corresponding to higher tier nodes and wherein data resulting from an operation corresponding to a first node on a lower tier is operated on by a second higher tier node directly connected to the first node in the graph. 
     
     
         12 . A system comprising:
 one or more computers;   a database;   an online analytic processor (OLAP), executing on the one or more computers, to receive a first query;   a model, the model specifying a graph defining a plurality of nodes on a plurality of tiers, each node corresponding to a different operation on data, wherein the OLAP generates a second query based on the model, the second query including a plurality of layered subqueries each corresponding to one of the nodes in the graph for specifying the different operations on data; and   a relational engine coupled to a datastore to receive the second query, wherein the relational engine executes the second query, and in accordance therewith, retrieves data.   
     
     
         13 . The system of  claim 12  wherein the plurality of nodes correspond to successive operations on the data in the database performed by the relational engine. 
     
     
         14 . The system of  claim 12  wherein the plurality of nodes of said graph comprise:
 a first node on a first tier corresponding to first data, wherein the first data is all the data required to answer the first query, the first node having a corresponding first subquery for retrieving the first data; and 
 a plurality second nodes on a plurality of tiers above the first tier, each second node corresponding to a different operation on the first data. 
 
     
     
         15 . The system of  claim 12  wherein the plurality of nodes includes a root node, a plurality of second tier nodes, and one or more higher tier nodes, wherein the root node has a corresponding first subquery to operate on raw data in the database to produce first data required to answer the first query, wherein the plurality of second tier nodes are coupled to the root node in the graph and each have corresponding subqueries that operate on the first data, and wherein at least one of the second tier nodes is coupled to the one or more higher tier nodes, wherein each higher tier node has a corresponding subquery for operating on data generated by the subquery of the at least one second tier node. 
     
     
         16 . The system of  claim 12  wherein the datastore is a database, and wherein the data is stored in the database in at least one fact table and a plurality of dimension tables. 
     
     
         17 . The system of  claim 12  wherein the second query is a nested SQL statement. 
     
     
         18 . The system of  claim 12  wherein each subquery is a database view containing a SQL statement. 
     
     
         19 . The system of  claim 12  wherein a portion of the nodes of the graph are represented by subqueries executed in the relational engine and one or more of the nodes in the graph correspond to data operations executed by said OLAP, the method further comprising:
 returning said retrieved data from the relational engine to the OLAP; and 
 processing the retrieved data in the OLAP based on said one or more nodes in the graph corresponding to data operations executed by said OLAP; and 
 returning a result to said first query. 
 
     
     
         20 . A non-transitory computer readable storage medium embodying a computer program for performing a method, said method comprising:
 receiving a first query in an online analytic processor (OLAP) executing on one or more computers;   generating a model, the model specifying a graph defining a plurality of nodes and a plurality of tiers, each node corresponding to a different operation on data;   generating a second query based on the model, the second query including a plurality of layered subqueries each corresponding to one of the nodes in the graph for specifying the different operations on the data; and   receiving the second query in a relational engine coupled to a datastore, wherein the relational engine executes the second query, and in accordance therewith, retrieves data.