Business Intelligence – Oracle

Archive for August 17th, 2007

Oracle Olap 11g – First Impressions

Posted by Venkatakrishnan J on August 17, 2007

Update to the original post based on further work in OLAP 11g. 

I just managed to install Oracle 11g on one of our crash and burn servers here. Well, to be honest I was impressed with what I saw. No glitches in installation at all. It has been sometime since I last managed to do that. One of the main reasons why I installed 11g was to test out the OLAP option. And this article is a result of that. For users who are trying to get AWM 11g from OTN, it’s not available as a standalone install as yet(i am not sure whether it will be at all). It is bundled along with Oracle Client software. Infact one would also get ODBC drivers, SQL Developer from the client install. One of the first things that I observed was that the Analytic Workspaces open up really fast.


 As you would know, Oracle 11g support 2 options. One is the 10g mode wherein you can use the AWM similar to what you have in 10g. The other is the revolutionary 11g mode wherein you can make the cube to be part of the optimizer via MVs. I started working on the 11g mode so that I can test it out. I started with creating a sample cube from the SH schema. One of the things that I observed was, one would need more privileges to create an 11g mode cube than to create it in the 10g mode. For example, one would have to have the permission to create MVs.

 I started out with creating a Product dimension.


 As you see in the above diagram, as soon as you create dimension you would get the associated MVs Views. I believe the MV gets deployed only when one analyzes the dimension. The MVs are created only if one selects that option while creating the cube (In the Materialized View tab). One also has the option of either enabling the Query rewrite so that the MVs are available for the optimizer. Also the build logs for the dimensions have changed quite a bit for good.


One would also get a MV for each of the hierarchies. I have not explored more into the dimensional MVs. Maybe I will write about them later.




Similarly, the build log for the cube creation is also different. One can create MVs out of both dimensions and Cubes.



The following is the output of the MV cube view created.


The MV view contains the cartesian product of the prod_id and Cust_id and its corresponding quantity sold measure.


But one of the strange things that I noticed was when i fire a query that uses one of the columns in MV, the table gets locked out if the Workspace is open. I analyzed a cube so that it becomes available to the Optimizer via the MV. But when i close theworkspace i get the results of the query. Maybe someone can correct me if I am wrong here. Maybe there is some parameter that I would have to set.  Thanks to Chris Claterbos of Vlamis solutions. He has compiled a nice article showing how AWM 11g can be started from Windows and Mac here.

Posted in All Posts, OLAP | 2 Comments »