Using relational structures to create and support a cube within a relational database system
A method and mechanism for performing an operation based on multidimensional data in a relational database. A first query is submitted to a relational database engine of the relational database management system, thereby causing a relational cube to be generated. The first query includes instructions to select a portion of multidimensional data for inclusion in the relational cube. The first query includes instructions to group the selected portion of multidimensional data using at least two or more levels of granularity of at least one dimension. The first query also includes instructions to generate a materialized view. Based on a received request for an operation, a second query is submitted to the relational database engine for execution against the relational cube, thereby causing the operation to be performed.
1. A computer-implemented method comprising:
storing multidimensional data in a relational database, wherein the multidimensional data that is stored in the relational database includes a plurality of dimensions, and wherein at least one dimension of the plurality of dimensions includes a hierarchy having two or more levels of granularity;
receiving a particular query that requests to retrieve multi-dimensional data from the relational database via a certain view referred to by a reference within said particular query, said particular query conforming to a database language supported by a relational database system;
wherein a partial materialized view includes a portion of said multidimensional data aggregated at one or more particular levels of granularity of said hierarchy;
wherein said particular query requests data aggregated at a number of levels of granularity of:
said hierarchy, and
another dimension different than the dimension of said hierarchy; and
rewriting said particular query to translate said certain view into a view defined by an inner subquery that returns data aggregated at said number of levels of granularity, and
rewriting said inner subquery; and
wherein (1) said rewriting said particular query to translate said certain view into a view defined by an inner subquery that returns and (2) said rewriting said inner subquery both do not involve rewriting said particular query to access said partial materialized view;
after rewriting said inner subquery, rewriting said particular query to access said partial materialized view; and
wherein the method is performed by one or more computers.
2. The method of claim 1 , wherein:
the inner subquery references a union of branch subqueries, at least one branch subquery of said branch subqueries referencing said partial materialized view;
and wherein rewriting said inner subquery includes rewriting a plurality of said branch subqueries.
3. The method of claim 2 , wherein the method further includes rewriting said at least one branch subquery of said branch subqueries by pushing a predicate into said at least at one branch subquery.
4. The method of claim 2 , wherein rewriting said inner subquery includes pruning one or more groupings declared by said inner subquery.
5. The method of claim 1 , wherein the partial materialized view includes lineage for at least one level of said two or more levels.
6. The method of claim 1 , wherein the partial materialized view does not include lineage for any of said two or more levels.
7. The method of claim 1 , wherein the inner subquery includes a union of branch subqueries, wherein at least one of branch subquery of said branch subqueries referencing said partial materialized view, and at least one of said branch subqueries referencing another materialized view.
8. A non-transitory computer-readable storage medium storing one or more sequences of instructions, wherein execution of the one or more sequences of instructions by one or more processors causes:
storing multidimensional data in a relational database, wherein the multidimensional data that is stored in the relational database includes a plurality of dimensions, and wherein at least one dimension of the plurality of dimensions includes a hierarchy having two or more levels of granularity;
receiving a particular query that requests to retrieve multi-dimensional data from the relational database via a certain view referred to by a reference within said particular query, said particular query conforming to a database language supported by a relational database system;
wherein a partial materialized view includes a portion of said multidimensional data aggregated at one or more particular levels of granularity of said hierarchy;
wherein said particular query requests data aggregated at a number of levels of granularity of:
said hierarchy, and
another dimension different than the dimension of said hierarchy; and
before rewriting said particular query to access said partial materialized view:
rewriting said particular query to translate said certain view into a view defined by an inner subquery that returns data aggregated at said number of levels of granularity, and
rewriting said inner subquery; and
rewriting said particular query to access said partial materialized view.
9. The non-transitory computer-readable storage medium of claim 8 , wherein:
the inner subquery references a union of branch subqueries, at least one branch subquery of said branch subqueries referencing said partial materialized view; and wherein rewriting said inner subquery includes rewriting a plurality of said branch subqueries.
10. The non-transitory computer-readable storage medium of claim 9 , wherein the one or more sequences of instructions further include instructions for rewriting said at least one branch subquery of said branch subqueries by pushing a predicate into said at least at one branch subquery.
11. The non-transitory computer-readable storage medium of claim 8 , wherein rewriting said inner subquery includes pruning one or more groupings declared by said inner subquery.
12. The non-transitory computer-readable storage medium of claim 8 , wherein the partial materialized view includes lineage for at least one level of said two or more levels.
13. The non-transitory computer-readable storage medium of claim 8 , wherein the partial materialized view does not include lineage for any of said two or more levels.
14. A computer-implemented method comprising:
storing multidimensional data in a relational database, wherein the multidimensional data that is stored in the relational database includes a plurality of dimensions, and
wherein at least one dimension of the plurality of dimensions includes a hierarchy having two or more levels of granularity;
receiving a particular query that requests to retrieve multi-dimensional data from the relational database via a certain view referred to by a reference within said particular query, said particular query conforming to a database language supported by a relational database system;
wherein a partial materialized view includes a portion of said multidimensional data aggregated at one or more particular levels of granularity of said hierarchy;
wherein said particular query requests data aggregated at a number of levels of granularity of:
said hierarchy, and
another dimension different than the dimension of said hierarchy; and
rewriting said particular query to translate said certain view into a view defined by an inner subquery that returns data aggregated at said number of levels of granularity, and
rewriting said inner subquery, wherein rewriting said inner subquery comprises performing at least one of the following transformations:
group pruning,
predicate pushdown,
expansion into a union of branch queries, and elimination of one or more branches in a union of branch queries;
wherein (1) said rewriting said particular query to translate said certain view into a view defined by an inner subquery that returns and (2) said rewriting said inner subquery both do not involve rewriting said particular query to access said partial materialized view;
after rewriting said inner subquery, rewriting said particular query to access said partial materialized view; and
wherein the method is performed by one or more computers.
15. A non-transitory computer-readable storage medium storing one or more sequences of instructions, wherein execution of the one or more sequences of instructions by one or more processors causes:
storing multidimensional data in a relational database, wherein the multidimensional data that is stored in the relational database includes a plurality of dimensions, and wherein at least one dimension of the plurality of dimensions includes a hierarchy having two or more levels of granularity;
receiving a particular query that requests to retrieve multi-dimensional data from the relational database via a certain view referred to by a reference within said particular query, said particular query conforming to a database language supported by a relational database system;
wherein a partial materialized view includes a portion of said multidimensional data aggregated at one or more particular levels of granularity of said hierarchy;
wherein said particular query requests data aggregated at a number of levels of granularity of:
said hierarchy, and
another dimension different than the dimension of said hierarchy; and rewriting said particular query to translate said certain view into a view defmed by an inner
subquery that returns data aggregated at said number of levels of granularity, and
rewriting said inner subquery, wherein rewriting said inner subquery comprises performing at least one of the following transformations:
group pruning,
predicate pushdown,
expansion into a union of branch queries, and
elimination of one or more branches in a union of branch queries;
wherein (1) said rewriting said particular query to translate said certain view into a view defmed by an inner subquery that returns and (2) said rewriting said inner subquery both do not involve rewriting said particular query to access said partial materialized view; and
after rewriting said inner subquery, rewriting said particular query to access said partial materialized view.