OFA – MindMapping

Posted February 9, 2010 by rbrooke
Categories: OFA Technical

Back in December I promised to post a utility for publishing OFA data using the popular open source mind mapping software FreeMind.

Well here it is at last. The following link is a .zip file containing all installation files, documentation. Read the readme.txt file to get information on getting started.

Due to limitations on supported filetypes on this WordPress blog, the filename has been changed. Remove the .pdf file extension and replace the ‘-zip’ with ‘.zip’. If your browser automatically opens files with a PDF extension in the Acrobat plugin, right click on the file and select the appropriate ‘save file’ option.

Freemind GPL Release 1-zip

This utility has been release under the GPL, therefore anyone is free to use it and modify as desired. The word ‘freedom’ has two commonly used meanings which are embodied in two phrases widely used in the Free Software community :

  • ‘Free as in beer’ – meaning no cost.
  • ‘Free as in speech’ – meaning freedom of expression, sometimes referred to using the French
    term ‘libre’.

The key condition of the GPL is that code released under it’s conditions is free and always remains free regardless who uses or modifies it. GPL code can never be made proprietary and hidden away.
Likewise all documentation connected with this utility is released using the ‘Creative Commons’ license. This license is the equivalent of the GPL for creative works, including written documents.

Oracle OLAP – MindMapping

Posted December 1, 2009 by rbrooke
Categories: OFA Technical, Oracle OLAP Technical

A number of years ago I wrote a simple utility for exporting OFA hierarchies into the popular open source mind mapping software FreeMind. Because the maps it creates are essentially not very different to a graphical representation of information in a hierarchical structure, they are a natural fit to document OFA hierarchies. One of it’s great advantages is that, being open source software, the FreeMind format is open and therefore anyone can see the structure and create maps. The format is straightforward XML that is well structured and intuitive.
Over the years I have used this excellent piece of software for many applications, and it has always struck me that there was more potential for using it as a means of documenting OFA, and other OLAP systems. So I finally got round to creating a generic mechanism for outputting information from OFA into a FreeMind mindmap. At the moment I have created 2 suites of Express code for use in OFA

  1. A suite of generic programs and OFA objects; dimensions and FDIs. Once the FDIs are correctly populated, with any required information, a single program call will generate a FreeMind mindmap from the FDIs.
  2. A suite of programs that populate the generic OFA objects with the necessary information to create a FreeMind mindmap from any hierarchy

Having completed this exercise, it should now be reasonable straightforward to replicate the functionality for Oracle OLAP Analytic Workspaces.
As a teaser here is a screenshot of an OFA hierarchy in FreeMind including some additional formatting and information displayed in small ‘tables’ attached to each node:

An OFA hierarchy exported to FreeMind with formatting

I am currently completing the documentation for this and will make the utility available in a few days.

Sample OLAP DML Code – “GPLCODE”

Posted June 10, 2009 by rbrooke
Categories: Oracle OLAP Technical

As promised some time ago I am putting in place the foundations for releasing OLAP DML code using the GNU Public Licence (GPL), i.e. Open Sourced OLAP DML code.

The intention is to build up a body of useful OLAP DML utilities that will help Oracle OLAP developers manipulate Analytic Workspaces and their content.

This initial release lays the foundation for this rather than releasing any particularly useful utilities. However this release is essential for distributing GPL licenced code; code that I will be releasing will be designed to work within the context of this AW.

The following links contain  files to install the AW and installation instructions. Note the first 2 links appear to be .PDF files, this is due to limitations on supported filetypes on this WordPress blog. The first is an EIF (Express Interchange File), remove the .pdf file extension and replace the ‘-eif’ with ‘.eif’. The second is an Express ‘infile’, remove the .pdf file extension and replace the ‘-inf’ with ‘.inf’



GPLCODE Installation Instructions (PDF)

GPLCODE Installation Instructions (ODT)

I hope to post the first useful GPL code to be installed in this AW in the next few days. This is likely to cover the issue of maintaining a visible  Oracle 11g Dimension from an existing AW dimension, for example migrating an existing AW dimension into an 11g Dimension defined using AWM.

Creative Commons Licencing

Posted June 7, 2009 by rbrooke
Categories: Uncategorized

Just a quick post to note that I have added a Creative Commons (CC) licence to this blog. This covers all posts written by me. Essentially this means that you are free to re-use material in my posts provided it is correctly attributed and you include the CC licence terms if you do so. Please follow the link on the right to understand your rights and responsibilities if you do re-use material.
Please note that documents, e.g. PDFs etc. that are accessed via links from this blog are likely to be licenced on the same terms, however this will be clearly indicated where this is the case, otherwise they should be assumed to be copyright of the author (i.e. me).
Regarding code; code snippets are free to use however you wish, complete programs will be licenced as appropriate. I will be releasing some code in the next few days and will cover the subject of it’s licencing then.

Sun/Oracle – MySQL/Express

Posted April 24, 2009 by rbrooke
Categories: General Oracle & IT

OK, it’s time to chip in my 2c on the Oracle/Sun takeover. There’s been much written and said since Monday’s announcement, much of it in the nature of ‘what will they do with this’ or ‘will they kill it/spin it off/develop it’. So I thought I’d contribute my thoughts, which are of a ‘wouldn’t it be great’ nature.

And the big idea is? Open Source Express Server and integrate it into MySQL. Before you all switch off thinking this is crazy, let’s look at what I mean more closely.

It’s clear from much commentary, both from industry analysts (who are no better than glorified newspaper journalists in most cases), and also from those close to MySQL development, that MySQL has had a troubled time since it was bought by Sun. Key developers have left, and the project has forked. This is not good. Fear about the future could only increase now Oracle own this important asset.

What better way to win quick recognition from the MySQL developer community than by adding a rich MOLAP engine into the core server. Functionality that would allow MySQL to immediately leapfrog it’s Open Source competition of Postgres & Ingres. Given the long heritage and years of development behind Express Server it is clear that it would take a significant length of time for any Open Source competitor to offer similar functionality, and frankly it would probably be beyond them in the short term. Here are just some advantages :

  1. Competitive advantage over other Open Source SQL alternatives

  2. A low cost/no cost competitor to MS SQL Server + Analysis Server in some markets

  3. A relatively easy migration path from MySQL-Express to Oracle11g+OLAP Option

  4. Recognition and kudos from the MySQL developer community

I’ve already discussed points 1 & 4. How about 2 & 3. How attractive would the product stack of OpenSolaris or Oracle Enterprise Linux + MySQL + embedded Express all free be vs. the stack of MS Windows Server 2003 (with per user licensing) + SQL Server 2008 incl. Analysis Services? Admittedly this won’t appeal to everyone, but it looks like a winning combination to me.

Which brings me on to point 3. At the engine level Analytic Workspaces in Oracle 11g remain backwardly compatible with older Express databases. OK you need to do some work to expose your data through the 11g meta-data, but the initial population of data is relatively straight forward. Thus there is an implicit upgrade path from a MySQL-Express combination to Oracle 11g(12g etc….) if required.

Some commentators have pointed out the risk of MySQL cannibalising the mainstream Oracle market, however most accept that MySQL is dominant in the web sphere where Oracle dominates in the enterprise market. My suggestion does move MySQL closer to the enterprise market as that is historically where most OLAP/BI usage occurs. However I would refer back to point 2; if a business is looking at the MySQL end of the market, it is likely they cannot afford Oracle licensing, in which case would you rather they implemented MS SQL Server and Analysis Services or MySQL-Express? With the upgrade path discussed in point 3, you have additional leverage if an organisation grows to a point where it needs to upgrade to a larger enterprise database. In the film ‘The Prime of Miss Jean Brodie’ the charismatic character Jean Brodie, a teacher, makes the statement “Give me a girl at an impressionable age, and she is mine for life!”. Oracle have not been able to compete effectively at the SME end of the market despite various Partner efforts to do so. Now they have MySQL they could; add in OLAP support and you could have a killer combination in the SME enterprise market – they could be ‘yours for life’. All this without cannibalising existing markets.

The downsides? The main one that occurs to me is the cannibalising of existing markets, however I think I have dealt with that above. If anyone can think of other please comment and we’ll see if there are answers.

And so the big question is, what do I mean by integrate Express Server into MySQL? Really I am not thinking anything too complicated – just do the minimum software engineering to get the Express engine embedded and working. If you release the code under the GPL, you can let the MySQL community do the rest, it’s just a Darwinistic ‘let the market decide’ approach. If it’s a good idea and the basic engine works, the larger community will work out problems such as

  • the best ways to tightly integrate it to the relational engine
  • access method to MOLAP data, APIs etc.
  • database development tools

The beauty of the Open Source approach is the rich seam of ideas that can be tapped into. Who knows, we could see Web2 interfaces to MOLAP data, e.g. AJAX applications.

And finally Oracle, while you’re at it, why not Open Source and release the code for OFA & OSA – then you’ve got two custom made application ready to go with your newly released free OLAP engine combined with MySQL. What’s the harm? It’s not likely to cannibalise your existing market; long time and former users are hardly likely to flood back. But you could bring a whole new generation of SMEs to the Oracle family with robust, proven, powerful and relatively easy to implement BI applications. If a potential customer is looking at the low cost end of the market they are hardly likely to be looking at Essbase/Hyperion. Microsoft have tried to enter the financial planning market with a product based on SQL Server, but have abandoned it due to development complexity. OFA is proven to work, why waste it? Release it as a free product with MySQL-Express and you have a product that MS can never compete with, you could have the market sewn up – one in the eye for MS. Similarly for OSA – there’s nothing else out there quite like it whether your’e a medium sized web retailer or a multi-national FMCG company. If you’re a SME retail/manufacturing company needing sales & marketing analysis this could be an easy way to get top class analytics without paying through the nose. Where else will they go? MS? Is that what you want?

So Larry – are you up for the challenge? what’s stopping you? Your President Charles Phillips and chief corporate architect Edward Screven declared the other day that they won’t be killing MySQL – why not go further, be really bold – restore it to it’s position as the premier Open Source relational database by giving it OLAP functionality and releasing ready-to-go BI apps with it. You could grasp the opportunity to finally make OLAP technology affordable to every business, large or small. It’s a big prize – go for it.

More on Virtualisation and Express – running the guest as a server

Posted March 30, 2009 by rbrooke
Categories: OFA Technical

One of the objectives of my virtualisation experiments, was to use the ‘Express’ guest as a ‘server’, i.e. to enable apps running on the host to access ‘Express’ applications running in the guest. For example, to make OFA running in the guest accessible to a browser, for example Firefox, running on the host. Once this is achieved it should be possible to make this available to a wider network.

Reading the VirtualBox documentation, this should be easily achievable, and indeed it proves to be pretty straightforward. The default networking mode for VirtualBox is NAT, or Network Address Translation. In a normal configuration this simply means that any network connection on the host, for example an internet connection, is transparently available to the guest. Thus the guest machine can connect directly to the internet without having to set anything extra up.

A feature of the NAT implementation in VirtualBox, is ‘Port Forwarding’. To quote from the help system :

….As the virtual machine is connected to a private network internal to VirtualBox and invisible to the host, network services on the guest are not accessible to the host machine or to other computers on the same network. However, VirtualBox can make given services available outside of the guest by using port forwarding…..

This sounds to be exactly what I want. The help system then goes on to give an example of the required syntax. My configuration is a Linux host, Ubuntu, and a Windows XP Pro guest. So the examples I am going to give as based on that configuration, it may be slightly different for a Windows host.

Firstly the basic syntax, there are 3 commands that need to be issued :

VBoxManage setextradata “name-of-VM

“VBoxInternal/Devices/type-network-card/network-interface-number/LUN#0/Config/name-of-service/Protocol” protocol

VBoxManage setextradata “name-of-VM

“VBoxInternal/Devices/type-network-card/network-interface-number/LUN#0/Config/name-of-service/GuestPort” guest-port-number

VBoxManage setextradata “name-of-VM

“VboxInternal/Devices/type-network-card/network-interface-number/LUN#0/Config/name-of-service/HostPort” host-port-number

Where :

name-of-VM is the name of the VM as displayed in VirtualBox setting in the ‘General’ section.

type-network-card is the type of network card as shown in the VirtualBox settings ‘Network’ section. This is usually ‘pcnet’, the default used by VirtualBox, unless you have changed this.

network-interface-number is the network card number (counting from 0). Usually this is 0, unless you have configured multiple virtual network cards on your VM.

name-of-service is any name you wish to give to the service you are setting up for port forwarding.

protocol is either ‘TCP’ or ‘UDP’.

guest-port-number is the port number on the guest that you wish to have traffic forwarded to from the host.

host-port-number is the port number on the host that you wish to forward to the guest.

This looks a bit intimidating, but I’ll work through it using my VM as an example. Firstly I need to gather the information required to complete the options described above:

name-of-VM My VM is called ‘WinXPOESOFAOSA_baseline’.

type-network-card is the default ‘pcnet’.

network-interface-number I only have one configured virtual network card so this is the default, 0

name-of-service I called this ‘guestexpress’.

protocol is TCP.

guest-port-number My guest is running Apache to enable the web services for Express Web Agent, OFA web and OSA web. It is configured to listen on the default port 80, so that is the guest-port-number.

host-port-number According to the VirtualBox help, Unix and Unix like OSs (Linux & MacOS), cannot forward traffic for IP addresses lower than 1024 from applications that are not running as root. Therefore to avoid problems I set this to 8080.

So my commands looked like this :

VBoxManage setextradata “WinXPOESOFAOSA_baseline” “VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestexpress/Protocol” TCP

VBoxManage setextradata “WinXPOESOFAOSA_baseline” “VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestexpress/GuestPort” 80

VBoxManage setextradata “WinXPOESOFAOSA_baseline” “VBoxInternal/Devices/pcnet/0/LUN#0/Config/guestexpress/HostPort” 8080

These were entered in a Linux console window. For Windows enter the commands in a cmd.exe window. Before entering the command ensure that the guest OS is completely shutdown (not paused), and close VirtualBox. The settings will take effect next time VirtualBox and the VM are started.

After re-starting my VM I opened Firefox in the host and entered the address ‘http://localhost:8080′, this should be forwarded to the guest port 80, and return the default Apache page, which on my guest simply throws out the message ‘It Works!’, and here it is, in both the guest and host :

Default Apache page in guest VM and host

Default Apache page in guest VM and host

Now to get the Express web apps working. The Express Web Agent (EWA) samples worked without issue. However OFA web did not. Comparing the EWA sample ‘index.html’ page with the standard OFA ‘index.html’ page soon showed the answer. Here are relevant snippets from each :

From EWA :

TD WIDTH=296><a href=”/oowa-bin/oowa.exe/ExpSrv634/dbxwdevkit/xwd_init?samples/xs_start/required”>

Locally install Express Web Agent files and run Samples</a>:&nbsp;

Download the Express Web Agent Java classes to your local machine once.

From OFA :

var language = navigator.browserLanguage;

var win = window.open( “http://localhost:80/oowa-bin/oowa.exe/ExpSrv634/dbxwdevkit/xwd_init?%27ofaserve.db%27/ofa.ext.init.web/C:%2fofasystems%2fbaseline%2fshared%2f/YES/YES/”+language, ofaName, “toolbar=no,menubar=no,status=yes,height=screen.height,resizable=yes,width=screen.width”);

The OFA index.html file contains a hard coded HTTP address to the server and port number. So I simply removed this :

var language = navigator.browserLanguage;

var win = window.open( “/oowa-bin/oowa.exe/ExpSrv634/dbxwdevkit/xwd_init?%27ofaserve.db%27/ofa.ext.init.web/C:%2fofasystems%2fbaseline%2fshared%2f/YES/YES/”+language, ofaName, “toolbar=no,menubar=no,status=yes,height=screen.height,resizable=yes,width=screen.width”);

This now works fine in both the guest and host, as shown :

OFA Web main page in guest VM and host browser

OFA Web main page in guest VM and host browser

Virtualisation and the Express product stack

Posted March 16, 2009 by rbrooke
Categories: OFA Technical

One of the ‘hot’ technologies of the moment, is virtualisation. Approximately a year ago I started messing around with some of the technologies available with a view to using one to support rapid development of OFA/OSA/Express etc. solutions. The idea is neither new nor particularly novel, however it has been an interesting exercise and I now do virtually all my development using one or more virtual machines.

What’s more it has enabled me to finally make a break with the Windows treadmill for my day to day work, prefering the stability, speed and security of Ubuntu. Using an appropriate virtualisation technology enables me to have the best of both worlds.

So far I have explored two common virtualisation products, VMWare and Sun xVM in the form of Virtualbox. Of the two I much prefer Virtualbox for it’s ease of use and configuration. I’ll give a brief summary of my experiences in this post.


Primarily I played around with the free VMWare products, VMWare Converter and VMWare Player. At the time I was experimenting the free VMWare Server product was not available. The VMWare converter was a very useful tool, though it wasn’t particularly robust. My objective in using it was to make a copy of my existing, and 7 year old, Dell laptop that had all my old OFA development apps, copies of customer OFA systems and generally 7 years of apps that I didn’t want to lose; those for which I’d lost the install disks, licence numbers etc. etc. I wanted to virtualise the PC so that I could run it on my shiny new Ubuntu Dell laptop. An interesting feature of the VMWare converter is it’s ability to make a ‘hot’ copy of a running PC, in my case this was to be the Dell laptop running W2K.

The exercise took a number of attempts, as the process ran for approx 18 hrs and kept falling over close to completion. One of the problems seemed to be with the fact that I had to create the VM disk images on an externally attached hard drive – one of the problems of virtualising an existing machine is that almost by definition there will not be sufficient space on the PC to create it locally – and the USB connection to this kept freezing. I was at the point of giving up when I decided to break up the process. What I haven’t mentioned is that the source PC hard drive was partitioned into 3 logical drives, seen by W2K as the C: E: and F: drives. By virtualising these one at a time I was able to successfully virtualise the full PC. Obviously only one of these contains a full bootable operating system, the c:, however the VMWare Converter also enables you to virtualise a non-bootable partition as a virtual hard drive that can be attached to any running virtual machine as though it were a physical drive.

Once this exercise was complete, I then started the virtual ‘laptop’ up using the free VMWare player on another Windows XP PC. This was not a happy experience. In fact I was never able to get it working properly, for a start the player kept crashing, it was grindingly slow and I couldn’t get the video drivers working to get high quality graphics. There were other problems too and it seemed as though the whole project was a complete waste of time. Another thing that struck me was the lack of configurability. The VMWare player really felt like a free product, and was not a good advert for the VMWare product range. Then I discovered VirtualBox….

Sun VirtualBox

Quite by accident I discovered this product when surfing various websites and blogs for help with the VMWare player. A recurring theme was that VirtualBox was a good alternative for VMWare that was both easy to use and configure. In addition VirtualBox offers extensive support for different operating systems both as host and guest and it works with VMWare VMDK machine images.

At the time VirtualBox was produced by a small independent German software house called Innotek. The initial version I used was 1.6. Shortly afterwards Innotek were bought by Sun, since then development has accelerated and the current version is 2.14.

Downloading and installing the software was a straightforward operation. Similarly setting up a new virtual machine and associating the VMWare image was a quick operation and my old W2K PC was up and running in a matter of minutes. Installing the VirtualBox Guest Additions enables a number of additional features, e.g. resizing of the guest window to any resolution and seamless use of the virtual machine whereby applications running in the guest look as though they are running on the host – OFA appearing to run on Ubuntu.

I have installed all the updates since V1.6 up to the present 2.14. Each version has brought further stability and new features that make this a very attractive alternative to the ubiquitous VMWare, not least the existence of the free ‘Open Source Edition’, not just a free proprietary application such as VMWare Player.

Features I have found to invaluable are :

  • Snapshots – these are useful when installing patches, updates, or new software. If problems occur after the installation it is simple to roll back to the previous snapshot. If the update or installation works correctly then the snapshot can be merged into the new machine state.

  • Cloning – Cloning allows a machine to be cloned, this is not the same as just copying a disk image. Cloning creates a new machine with a different id. This is useful for creating a clone of a ‘master’ image, e.g. a machine with certain software installed, for use in development for a new project.

  • Portability – Cloning is necessary where the master and clone(s) will be run on the same host. However one of the advantages of virtualisation is portability. If you need to run a virtual machine on another physical host, then the image can just be copied onto the host machine. In this case because the virtual machine is being run on an independent host there is no need to clone. This is useful for distributing a common ‘master’ image among a team of developers. You can be sure that all developers are working from a common clean master.

  • Backup – as a result of the portability, creating backups is easy, simply copy the disk image(s) to an external drive.

So back to my W2K PC. As I mentioned above, once I had this running in VirtualBox, everything worked fine. Networking to the internet is seamless, the guest simply connecting straight through the host connection. All my old Windows apps work perfectly. All my old data is instantly available. All my old OFA client developments are available. Performance wise it flies along at a much faster rate than the old physical PC and it boots in about a quarter of the time. So far (10 months) I’ve not had a single problem.

My ‘Express’ machine

Having satisfied myself that the technology was stable I created a master ‘Express’ virtual machine. As at 14/03 this is configured as follows :

  • Windows XP Pro SP3

  • Non-Oracle software :

    • MS Excel 2003

    • Adobe Acrobat 8

    • Firefox 3

    • Apache 2.2

    • Java 6 Standard Edition (V6 Update 12)

  • Oracle software :

    • Express Server 6.3.4 + all current patches

      • Express Web Agent

      • Express Administrator

      • Express Relational Access Administrator

      • Express Batch Manager

    • Express Client software 6.3.2 + all current patches

      • Express Analyzer

        • Express Objects

        • Express Web Publisher

    • Oracle Financial Analyzer (11i) 6.4 + all current patches

    • Oracle Sales Analyzer (11i) 6.4 + all patches

    • Other Express software :

      • Express Excel Add-in (6.3.4_2)

      • Personal Express 5.0.3

      • OLAP Worksheet 6.3.4

Web components for all products are configured and shortcuts stored in Firefox.

This machine is already proving useful and has been cloned several times as the basis for client developments and demonstrations. As the machine is running ‘virtually’ nothing other than the Express products, performance is excellent.

The success of this machine has led me to create another master for 11g – OLAP option. But perhaps I’ll keep that for another post.


Virtualisation is an excellent and rapidly maturing technology that promises to revolutionise development and deployment of software projects.

The Express technology stack works well in a virtualised environment and the availability of low cost or free virtualisation technologies enables new projects to be initiated quickly. The ability to clone environments has the potential to enable new delivery mechanisms for products, with the ability to be sure that the underlying configuration is a known and stable platform.

Finally a screenshot showing a number of Express Apps running simultaneously in my VirtualBox standard Express virtual machine. The screenshot shows the apps running in ‘seamless’ mode, if you look carefully the host PC is running Linux (Ubuntu), but the Express apps appear to be running on the host desktop. Just for good measure, PCX503 is actually running on Ubuntu courtesy of Wine (a Windows Compatability Layer that allows many Windows apps to run directly on Linux.)

Express Apps running in Virtual Box

Express Apps running in Virtual Box


Get every new post delivered to your Inbox.