Accessing 9i OLAP Cubes Using ASP or VB Code

"Hi Mark,

I am facing a problem in accessing Oracle Cubes with ASP. How to access cubes using ASP or VB code? Presently i am using MS Analysis services with Data Source pointing to Oracle Cubes, and in asp code i am using MSOLAP provider and ADOMD Which i think is not a suitable solution because it needs both SQL Server and Oracle. I am not very much aware of Oracle or Analytical workspace manager; Please Assist. This is really very urgent."

Working on the assumption that we're talking about 9i OLAP cubes rather than Express Server cubes (which already have an active-x access solution available for free download) there are a couple of options that are worth looking at.

To recap, what we're looking to do is access 9i OLAP datasources using some form of ODBC/OLEDB/ADOMD solution, so that they can be used in an Microsoft ASP or Visual Basic application. Having spoken to Julian and Jon at Plus, there are a couple of possible solutions.

1. First up, there's no way at present that you can use the Microsoft ADOMD multidimensional API to access 9i OLAP cubes. The ADOMD API is a multidimensional API that uses MDX to drive a multidimensional cube, and the equivalent in the Oracle world is the Java OLAP API. ADOMD is COM-based (as far as I know) whilst the Java API is java-based; if you want to work with 9i OLAP data sources using a multidimensional API you'll need to recode your application in Java and use this API instead.

2. If you're prepared to access the 9i OLAP datasource using SQL, you could use the Analytic Workspace Manager (or the PL/SQL DBMS_AW / DBMS_AW packages) to create SQL Views over the 9i OLAP multidimensional data, and then SELECT against these views to provide data for your application. These views are fairly straightforward to set up these days (using the Analytic Workspace Manager GUI tool) but this isn't the same as a proper multidimensional API - queries aren't persistent across the session and you haven't got the same rich set of commands you'd get in a MOLAP API. You also wouldn't get access to the OLAP Catalog which only really comes into effect when you use the OLAP API directly.

3. If the data in the Oracle cubes is actually relational data, you can of course directly access the underlying tables using OLEDB or ODBC, and Oracle would still use any dimensions, materialized views and so on in the background. Oracle Enterprise Manager, or Oracle Warehouse Builder, could be used instead of the Analysis Services GUI to build and populate the ROLAP data structures.

4. Lastly, one other option pointed out by Julian is the data access middleware offering from Simba Technologies, which provides a bridge between ODBC and JDBC technologies, allowing COM applications to access data that requires a JDBC connection. Whether this then works with ADOMD is probably unlikely, although you'll need to contact Simba directly for more details. I know of a company that are evaluating this technology as a way of building ASP and VB applications that talk to 9i OLAP in this way, although it's early days yet. Whatever happens, 9i OLAP is not MDX-compliant and you'd need to recode your application to talk to the bridged Java API, which may or may not be a significant amount of work.

Summing up then, if you're looking to swap out the Analysis Services MOLAP database and replace it with a 9i OLAP analytic workspace, whilst there may be options with the Simba ODBC-JDBC bridging techology, the best option would probably be to recode the client application in Java of some sort, and then recode the data access elements using JDBC and the Java OLAP API. Hope this helps.