Cio

Paper on the Strengths and Weaknesses of Oracle 9i

This is a paper evaluating the strengths and weaknesses of Oracle 9i. First I would like to discuss the evolution which has taken place within the Oracle applications in relation to OLAP. OLAP first became a feature of the Oracle database with the introduction of Oracle 8i enterprise edition. Prior to Oracle 8i OLAP was accomplished by a separate application known as OLAP server. Oracle accomplished this task when they added relational OLAP features to their database. Oracle 8i enterprise edition used a feature called dimensions, which were additional layers of metadata to put over table or set of tables. You could use this to define hierarchical relationships between columns. In this way a single dimension could contain multiple hierarchies and the database could contain multiple dimensions, which are unique within each schema. (http://www.rittman.net/archives/001113.htm)

With the introduction of Oracle 9i an option called OLAP option was integrated into the Oracle application. “The OLAP option integrated the Oracle Express Server multidimensional engine into the Oracle relational database, and also introduced a further layer of OLAP metadata, known as the OLAP catalog, together with a Java OLAP API, which provides programmatic and SQL access to OLAP data”. (http://www.rittman.net/acrchives/001113.htm)

A key feature of the Oracle 9i OLAP option is the way in which data can be stored. The OLAP data can be stored in either relational tables, or in multidimensional data types held within what is termed ‘Analytic Workspaces’. Both storage methods can be accessed by using Java OLAP API. Java OLAP API decides how to retrieve data from either the relational table or from an analytical workspace, depending on how the data has been stored. The advantage of this is that data can be easily and efficiently retrieved. This is a very strong point in using Oracle 9i.

Another very nice feature in Oracle 9i is much of the work involved in creating the OLAP is done automatically when you are using Oracle enterprise manager GUI to create your dimensions. You can also manually enter commands by using PL/SQL packages to create OLAP metadata, which enables access via the Java OLAP API. This shows how flexible that Oracle 9i is when it comes to allowing the user the ability to have complete control over OLAP metadata.

The Oracle 9i OLAP option allows you to create cube objects. “A data cube is a type of multidimensional matrix that lets users explore and analyze a collection of data from many different perspectives, usually considering three factors (dimensions) at a time.” (http://www.computerworld.com/databasetopics/data/story/0,10801,91640,00.html). Queues can then be used by the Java OLAP API, and other tools which use the API, as the basic building blocks of OLAP reports. The advantage comes after you've created dimensions in cubes using relational tables and the Oracle OLAP option; you can then go on to analyze these using OLAP API aware tools. (http://www.rittman.net/acrchives/001113.htm)

Oracle 9i also has an alternative method to store dimensions and measures besides in relational tables. It allows you to store dimensions and measures in an analytic workspace. Analytic Workspaces are multidimensional workspaces held within large object data types or LOBs in Oracle tables. This is not new technology, but rather is left over from the original