Analytic Workspace Manager Locking

So, following on from my previous blog about plugins for the AWM, I thought I would provide a little information about something I learned whilst writing my own plugin.

Writing a plugin for the AWM and testing it can be cumbersome if you have to constantly start the AWM, login and then navigate to a node in the tree that allows you to activate your plugin. No, arguably a better way is to create your own testbed to simulate the AWM environment. That way you can run your code with less effort and interaction and are also afforded the ability to step through and debug your code if, like me, you need to.

In my case I merely created a Java class that effectively created an Oracle database connection and then called my plugin code, passing in the connection and anything else that it required. This was all fine and dandy whilst developing the code and afforded me the luxury of being able to test my code with the minimum of effort. However, when I eventually placed my code into the plugin framework (which to be fair I was doing simultaneously anyway) my code, well, didn't really stop working but rather, well... just seemed to stall.

This was masked by the fact that I was using multiple threads and thus parts of the plugin, though still functioning, arguably produced incorrect results. After a little investigation (okay, okay, so more than a little and certainly more than I would have liked) I discovered the reason...

There appears to be no fine-grained locking in Oracle OLAP, certainly as far as the AWM is concerned.

So what do I mean by that? Well, just that it is geared towards a single developer updating a given analytic workspace. Multiple updates to different parts of the workspace simultaneously are not supported nor, as far as I can tell, even possible.

So what does that mean to us? Well, when the AWM connects to the analytic workspace it attaches it in "read/write" mode. This means that any plugin code can only affect the contents of that analytic workspace by using the connection provided to it when the plugin is invoked. Retrieving/creating a new connection using the data source made available in the Map object presented to the plugin means that any code executed over that connection is in fact in a different session. As such, any code intended to affect the same analytic workspace that was attached by the AWM session created when the analytic workspace was attached will be blocked and will not be able to make changes or even process any objects in that analytic workspace.

At first I thought that that was it, game over but there is a "trick". This "trick", albeit a bit of hack and one not to be taken lightly, involves the use of the DBMS_AW package . More specifically, the use of the DETACH, UPDATE and ATTACH sub-programs allowing us to detach the analytic workspace over the main connection provided by the AWM to the plugin code. We can then "re-attach" it on the new connection in read-only mode (or read/write if you want) and perform whatever actions you would like on the given analytic workspace. The UPDATE sub-program is required in order to commit any changes that have already been made to the analytic workspace whilst it was in read/write mode prior to detaching it.

Note that there is a potential issue here. Detaching the analytic workspace and re-attaching it introduces the risk that in the meantime another session locks the workspace before the plugin secures it for its own use. Also, failure to ensure that the analytic workspace is attached in read/write mode before the plugin exits breaks the environment in which the AWM expects to function. (i.e. The AWM still "thinks" that the workspace is attached in read/write mode and will allow a user to try to make changes even though this may not be the case.)

Clearly the functionality that this provides needs to be weighed in conjunction with the expected working environment and the associated risks. If the environment is inherently single-user then no harm is done. However, if there is a multi-user environment where multiple people are likely to request read/write locks on the same analytic workspace, well, then your mileage may vary. Clearly you may be able to use transactions to mitigate some, if not all, of these risks but that remains to be seen.

As mentioned previously, oh how nice it would have been to be able to lock a specific dimension or cube etc. which, based on the metadata stored in the database, appears quite possible but just not implemented. This could have opened up opportunities for multi-user development environments to allow teamed development, maintenance and testing etc. What a novel concept, though sorely lacking here...

Analytic Workspace Manager Plugin Best Practices

The Analytic Workspace Manager (or AWM for short) is the tool to use when wanting to quickly create OLAP structures in the Oracle OLAP engine embedded within the Oracle relational database. Clearly you can use OWB etc. but AWM provides a lightweight easy to use interface for creating quick demos, restructuring content or processing etc. However, what the AWM provides may well not be everything to everyone and thus it is possible to create your own plugins, using Java, to get it to do what you want.

The documentation around doing this, I found, is a little light on the ground so to speak. So I have tried to document my findings here to help those that may wish to follow suit. However, before I start I should probably point out that in no way is the following post endorsed by Oracle. These are merely my observations after coding my own plugin for the Analytic Workspace Manager.

So here goes...
Step one is to find the Developing_AWM_Plugins_11g.pdf document. When I started coding my plugin for the 11g version of AWM, I could only find the 10g document which is not quite accurate for 11g. The OLAP API has changed somewhat in 11g and this is reflected in the OLAP API interface between the AWM and user plugin code. Looking at the document, I guess the pertinent points are that the 11g version of AWM requires the plugin to be compiled with Java 5 SE. (Or version 1.5 in "old school", just why did Sun confuse matters?) We'll come back to this but suffice to say that you'll need to remember that Java 5 brings with it support for generics.

Also in the document, if you have coded for 10g plugins, you may notice that the interface method signatures have changed a little. The AW object passed into the handle method is now an oracle.AWXML.AW object. The params parameter to the handle method provides a map, where the DATAPROVIDER key provides the ability to get a connection to the underlying Oracle database without requiring username/password information.

Step two, and arguably the most important step, is the understanding that on startup the Analytic Workspace Manager loads all Java code in the plugin directory and looks for any class files that implement the AWMPlugin interface. This is also documented in the above mentioned document, however, what is not made clear is that there will naturally be a performance impact on the startup time of AWM, the more classes and JAR files that you add to the plugin directory. Also, if you use 3rd-party JAR files you are at the mercy of those maintainers.

For example, placing the "jfreechart" JAR files in the plugin directory causes exceptions to be thrown in the AWM command window regarding missing Eclipse files. (I'm guessing that "jfreechart" is coded using Eclipse and some code dependencies are left behind.) Allowing this to happen with a user-defined plugin is pretty inelegant at best but most likely just down-right rude. We should avoid that, and we can.

The best practice I have come up with is as follows:

• The interface between the AWM and your plugin should reside in its own JAR file and only contain the minimum number of class files etc. in order to start the actual plugin. This keeps the startup time for the AWM to the minimum.
  
• The actual plugin code should be packaged into its own JAR file and placed elsewhere in the file system. (I use a subdirectory of the plugin directory so that it can be found using relative paths from the plugin interface.)
• Associated plugin dependencies should also be placed into a subdirectory (I use the same one as my own code as mentioned above) of the plugin directory.
  

Now, Oracle kindly provide a directory from which plugins can be found and executed but this clearly isn't scalable from a startup point of view if you require the use of large dependencies or lots of plugins. Oracle unfortunately don't provide the equivalent of an "Ext" directory into which dependencies could be placed. So here's the dilemma... Do we customise the Windows batch file (or *nix shell script) to include our dependencies in the classpath, or do we place our dependencies in one of the directories that is already referenced by the Oracle prescribed classpath?

My answer... neither! Customising the startup script is prone to failure if Oracle ever decide to update it. It also means deploying the plugin is a pain, in that everyone's installation may be different so deploying the script needs to take that into consideration. Hijacking the Oracle directories is also pretty "rude" and prone to cause issues down the line, not to mention taking into consideration once again the uniqueness of each installation. No, best practice would be to customise a URLClassLoader in order to dynamically load the dependencies at runtime and start the plugin. This unfortunately requires the use of reflection and is a bit more long winded but it will be far more robust and stand you in good stead during further developments.

My plugin used both "log4j" and "jfreechart", both of which had to be loaded onto the classpath with "log4j" needing to be instantiated in order to log progress. I may blog more on this in detail in the future, as doing this can be tricky and I couldn't find a definitive guide on this on the web.

Finally, coming back to generics, note that the AWMPlugin interface needs to be backwards compatible and as such does not make use of generics. This tends to generates compiler warnings. The way I would recommend handling this is to annotate the interface methods with "@SuppressWarnings("unchecked")" to suppress those warnings but only on the methods provided by Oracle. If you need to assign one of the variables found in the interface parameter list to to your own variable, you can use the generic wildcard, such as declaring "Map<?>", indicating the supertype.

And that's all there is to it.

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!