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.