Business Intelligence – Oracle

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.

 olap1.jpg

 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.

 olap2.jpg

 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.

olap3.jpg

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.

 

olap4.jpg

 

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

olap5.jpg

olap6.jpg

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

olap7.jpg

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

olap8.jpg

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.

Advertisements

2 Responses to “Oracle Olap 11g – First Impressions”

  1. […] 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 […]

  2. […] 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 […]

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: