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.
August 21, 2007 at 12:25 pm
[...] on August 21st, 2007. As I had pointed out in my earlier article here, Oracle OLAP 11g have two modes of operation. In the 10g mode, users would have to create sql [...]
August 30, 2007 at 4:53 pm
[...] in CUBE and ROLLUP functions are documented here. If you had gone through my previous blog entry here on using OLAP 11g, i had used a specific example to create a cube. I would be using the same one [...]