We have been using a combination of ASP.NET and Oracle OLAP for some years to build sophisticated planning and reporting tools and found them to work very well together. Surprisingly, though, there is very little user discussion on the Internet about this particular combination suggesting that it is not widely used.

For us, the Oracle OLAP engine remains the most powerful and cost-effective OLAP tool for building modelling applications (i.e. where it is doing more than just reporting on pre-existing data). However, when it comes to putting together a quick front end that requires minimal coding, yet is very flexible, we find ASP.NET provides a fine solution (although we do also use Python for simpler applications). The core of our business is helping companies, not writing software – all we are interested in is the business benefit that our systems can deliver. We therefore want to put the majority of our effort into writing the ‘clever stuff’ in the back end – if we can slap on a front end with a single line of code, that would be ideal, so the library of powerful components provided by ASP.NET is ideal.

Having said that, Oracle have not made it particularly easy to get data out of OLAP, particularly if you are not using Java (there is a separate Java API available if you are). The OLAP_TABLE function that is used to retrieve OLAP data using SQL is hugely complicated, particularly if the nature of your requirement is that the shape and type of data that you need to retrieve is very diverse (as it is with our tools). In a later post, I will talk in more detail about our approach, but for now, suffice to say that we have managed to define just two API functions which we can use to retrieve any data we need. For each call, the data is processed and assembled by the relevant programme in the OLAP system, and the results placed in two standard variables – one holding text data and one holding numerical data. The interface function then retrieves these two variables and returns them to the front end, which knows how to interpret them in the context of the particular page being displayed. One function returns a fixed number of text and numerical fields which meets the needs of the vast majority of web pages. The other function is for multi-dimensional reporting and is ‘tall’ rather than ‘wide’ and so not restricted by the number of fields available. We also use a third approach for transferring bulk spreadsheet-type data, for which we use the OLAP log, but that uses the first API function and so is not really different. By channelling all interaction through these two APIs, we have been able to concentrate on developing the underlying programmes, and not worry about the interface between Oracle OLAP and ASP.NET.