Business Intelligence – Oracle

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

3 Responses to “Oracle 11g – CUBE_TABLE function for Oracle OLAP”

  1. Could you please add bigger images for your screen shots. For instance, add a link to open a new page with full size images.

  2. Venkatakrishnan J said

    Done. I have added links to the images now. For the future posts i would increase the size of the images. Thanks for your feedback Arnold.

  3. Joachim said

    Those Cube_Tables really make some sense… But I wonder how to make a formula / calculated expression defined via OLAP-DML (OWA Worksheet) show up in a Cube-Table?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: