Business Intelligence – Oracle

Archive for September 6th, 2007

Oracle 11g – CUBE_TABLE function for Oracle OLAP

Posted by Venkatakrishnan J on September 6, 2007

If you are wondering how Oracle OLAP 11g exposes itself in the form of relational views, its all because of a new function called CUBE_TABLE. This function has been added new to 11g, to enable the OLAP cubes to be a part of the SQL Optimizer. This can be used on both Dimensions and Cubes to convert them into relational data. Lets see the syntax of this new function (taken from the docs) first.

SELECT …
FROM
TABLE(CUBE_TABLE(‘arg’));

As you see above, the CUBE_TABLE takes either a cube name or a dimension name as its parameter. One also has options of specifying multiple hierarchies for dimensions and cubes. Let us try an example here.

SELECT
*
FROM
TABLE(CUBE_TABLE(‘sh.product’));

Here, i am trying to display the dimension product in the form of a relational table. CUBE_TABLE function does that for us automatically.

      

Now lets try specifying a hierarchy clause for the same dimension. This would be helpful if you have multiple hierarchies defined in your dimension.

SELECT *
FROM
TABLE(CUBE_TABLE(‘sh.product HIERARCHY producthierarchy’));

      

Lets try the same with a cube.

SELECT *
FROM
TABLE(CUBE_TABLE(‘sh.salescube HIERARCHY product producthierarchy’));

      

The only difference between the above 2 is that in case of a cube we would have to specify the dimension name and the hierarchy name after the HIERARCHY clause. All the details about the CUBE_TABLE function is available here.

Advertisements

Posted in All Posts, OLAP, Oracle 11g | 3 Comments »