Alternatives for Express, OFA, OSA

Posted December 18, 2014 by rbrooke
Categories: Uncategorized

After a number of years break I am posting information that may be helpful to anyone still following this blog…..

For the last 4 years I have been working for a Swiss BI/CPM software vendor called BOARD. Their product is architecturally remarkably similar to Express and the Analyzer/Objects toolkits. However it is thoroughly modern in appearance and in its technology. It is based on the Microsoft .Net architecture and has a full multi-read and write in-memory architecture.

I have written a paper aimed at former Express/OFA/OSA professionals and clients who may be interested in alternatives to those products that are part of the mainstream conventional Essbase/TM1/SAP BW/etc. options.

BOARD Basics for Express Developers

If you have any questions please contact me using this form:



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 = “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 = “/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