Is Oracle AWM 11.1.0.7.0A Better?

So, I'm about to present at a workshop tomorrow on Oracle 11g's Interval Partitioning and on the OLAP option and in preparation for the event I have had to create a demo. A while back I patched our 11.1.0.6.0 relational database to patch 8 but although both patch 10 and 11.1.0.7.0 have since been released, I decided not to risk upsetting things too much by installing those and have thus gone with the status quo. (Note that we have lots of Oracle software on that machine for demos and breaking things at any time is just not on!)

Incidentally, I just have to ask, does anyone actually know what each number in Oracle's version numbers stand for? I searched a while back but to no avail. But I digress...

Searching around on 11g versions and patches I came across an article that said that in order to patch the OLAP option you need to install either Patch 'A' or Patch 'B' but in order to patch the relational side of the engine you use the Patches 6, 8, 10 etc. Huh? When I installed patch 8 it had to back out patch 'A' (which is what the person who posted on the forum also found) so does that mean that the OLAP patches are mutually exclusive from the relational patches? That's a question I have yet to answer 'cos maybe you are meant to re-apply Patch 'A' after patch 8 etc. but that's a subject for a later post. (Especially since 11.1.0.7.0 has been released which appears to be for both relational and OLAP with patch #6890831. See the OLAP Certification page.)

Anyhoo, my big grumble with the Oracle Analytic Workspace Manager is that it seems to contain more bugs than an anthill. Seriously, does anyone actually test this thing? Within 2 minutes of using it I can get it to break. Lol, now I appreciate that my ineptitude is clearly a culprit in this issue in that if I did everything correctly it wouldn't break. However, exceptions are just that, exceptions and they need to be catered for.

Documentation seems to indicate that the preferred method of creating analytic workspaces etc. is to use Oracle Warehouse Builder and that the Analytic Workspace Manager is for "end users" who don't have access to the ETL/ELT process. It can also be used where OWB is not the ETL/ELT tool being employed.

Really? Whenever an exception is thrown I seem to get some Java exception ramblings about not being able to create a cursor or transaction or something equally inane. I'm sorry. It's coding like this that gives Java a bad name! Issues such as not being able to switch between a "star-schema" dimension and "snowflake" dimension after it has been built. Sure, you click on the drop down box and select it but sometimes the relevant member is selected but you don't get the correct representation in the mapping pane (i.e. visible/invisible "parent" member) or it just won't select.

Other irritations include:

• After defining a cube you can click on the cube name in the schema tree and even though the dimensions are greyed out, you can double click an available dimension and it will appear to be added to the cube's dimension list. It hasn't really, click off the cube and come back and the model is back as expected.
• Try creating a dimension over the "Sales Territory" dimension as available in the Microsoft AdventureWorks database and it will build. You can even browse it. However, join that dimension to a cube built off of the Internet Sales fact table and whoa... nasty exception.
• Why does the above nasty exception terminate my connection and cause random stuff to happen?
• How come building cubes off a database using the WE8MSWIN1252 characterset causes ORA-12704 characterset mismatch errors seemingly at random? Sometimes when I merely process something but always when trying to create cube organized materialized views. Sure I should be using UTF8 but the default for the "warehouse" database created by dbca is to use WE8MSWIN1252. What gives with that?
  
• ...

It is probably worth pointing out that the version of AWM that I was using was 11.1.0.7.0A and apparently that is expected to work against an 11.1.0.6.0 database. Maybe I should log these as bugs for Oracle to fix, I get that. My complaint though, is how did some of the issues I experience (maybe not listed here) even get through the door?

Does anyone on the development team actually use the product in a way that is not based on contrived samples? We all know that clinical test cases will work but what about the real world?

After all that, is the new version of AWM better? Yes. The fact that you can now create dimension levels at the time when you create a dimension (which I always thought was odd that you couldn't) is accepted with open arms. Despite all the ramblings above, it does seem a little more stable though that really isn't saying much. The default way of mapping columns to OLAP structures has changed from the graphical mapping view to the table mapping view which I think is for the better.

Bottom line, if you haven't switched yet, switch now. It's better for sure but don't expect an "Analysis Services development environment (i.e. BIDS)" type experience or you'll be sorely disappointed!