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.
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.
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.
TABLE(CUBE_TABLE(‘sh.product HIERARCHY producthierarchy’));
Lets try the same with a cube.
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.