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.
September 6, 2007 at 9:38 pm
Could you please add bigger images for your screen shots. For instance, add a link to open a new page with full size images.
September 7, 2007 at 7:05 am
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.