Understanding the OLAP API

Object oriented

OLAP applications typically have object-oriented user interfaces where users manipulate objects that represent organized groupings of their data. Thus, there is a natural relationship between an object-oriented user interface and an object-oriented API such as the Oracle OLAP API. The OLAP API exploits this natural relationship by providing objects that match the end-user behavior that an application needs.

Object-oriented languages such as Java manipulate data by applying methods on objects. This approach enables the objects to maintain a current state and support incremental modifications to that state. This approach provides excellent support for common OLAP actions such as drill and rotate.

For example, a central activity for users of OLAP applications is refining queries. A user has a question in mind and devises a query to answer that question. In most cases, the initial results of the query prompt the user to want to dig deeper for a solution, perhaps by drilling to see more detailed data or by rotating the report to highlight correlations in the data. The OLAP API is able to use the result of one query as the input to the next query.

Intelligent caching

Analytical queries are by nature iterative. An analyst formulates a query, sees the results, and then formulates other queries based on those results. Since the likelihood is very high in business analysis of needing the same data to answer subsequent queries, the OLAP API caches the metadata so that it is available throughout the session without fetching it again. Moreover, the OLAP API defines the result set of a query geometrically. Using multidimensional cursors, the OLAP API can randomly access disparate regions of the result set. This allows an application to retrieve just the data currently of interest instead of all of the data in the result set. For example, you might scroll to the end of a page without having to fetch all of the data on the page.

To acquire data from a data warehouse, the OLAP API generates SQL statements. Data fetches use many of the newest innovations in Oracle9i, including concatenated rollup, scrollable cursors, and query rewrite.

CORBA distributed processing

Common Object Request Broker Architecture (CORBA) is a standards-based network communications architecture that provides a way for applications located on two or more servers to communicate so that they appear to run as a single unit. CORBA runs over a network protocol such as TCP/IP.

In a CORBA environment, every computer has an Object Request Broker (ORB). The application code addresses its requests to the ORB rather than to a particular server or database. The ORB responds to the request with an object registered in its namespace. From the standpoint of the application, the request is the same regardless of whether the registered object is stored on the same computer, on a network server, or somewhere on the Internet.

Objects such as OLAP API-enabled servers and databases are registered in the CORBA namespace.

Calculation capabilities

The OLAP API generates SQL commands to select and manipulate data stored in the database. These SQL commands can include the "N-pass" functions, such as RANK, PERCENTILE, TOPN, BOTTOMN, LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, and STDDEV.

The OLAP API provides expanded calculation capabilities beyond those that can be handled efficiently in other OLAP solutions, such as:

• Totals broken out by multiple attributes

• Suppression of NA and zero rows, columns, and pages

• Row and column calculations

• Union dimensions

• Measures as dimensions

• Inter-row calculations such as the following book-to-bill ratio:

  Balance(Account "BOOKED", Period "PRIOR")/ Balance(Account "BILLED", Period "LAST")

• Asymmetric queries

Additional calculations, such as modeling, forecasting, and what-if scenarios, can be performed on data in the analytic workspace.

Code example: Selecting values

This OLAP API code fragment demonstrates the selection of dimension values based on the data values of a measure. The Sales measure has four dimensions. The Geography, Channel, and Time dimensions are limited to one member each, then Product members are selected with Sales values greater than 20,000,000.

Source geographySel = geography.selectValue("BOSTON");
Source channelSel = channel.selectValue("TOTALCHANNEL");
Source timeSel = time.selectValue("1996");
Source prodSel = product.select(salesSel.gt(20000000));
Source result = sales.join(geographySel).
      join(channelSel).join(timeSel).join(prodSel);

Related information

For information about developing applications using the OLAP API, refer to the Oracle9i OLAP Services Developer's Guide to the Oracle OLAP API.

For information about programming in the DML, refer to Oracle9i OLAP Services Developer's Guide to the OLAP DML.