|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Developing OLAP Applications, 5 of 5
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.
The OLAP API accesses the data through the OLAP catalog, that is, the relational tables that contain OLAP metadata. The application does not need to be aware of whether the data is located in relational tables or in an analytic workspace, nor does it need to know the mechanism for accessing that data.
Oracle OLAP translates all queries from the OLAP API into SQL; when a query is issued through the OLAP API, the SQL generator in Oracle OLAP issues a SELECT statement against a relational table or view. This has several advantages for application developers:
Figure 3-4 shows how a query in the OLAP API that uses data from both a multidimensional workspace object and a relational table is resolved.
As an alternative access method, the OLAP API provides a way for a Java application to directly manipulate workspace data, without the need for any metadata and without the use of the OLAP API data manipulation classes. The Java application uses the
SPLExecutor class in the OLAP API to send DML commands directly to Oracle OLAP for execution in the workspace.
Whichever access method is used, the application establishes a connection, opens the workspace, accesses the data (either through MDM metadata or through
SPLExecutor), closes the workspace, and closes the connection.
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.
The OLAP API generates SQL commands to select and manipulate data stored in the relational tables. These SQL commands can include the "N-pass" functions, such as
The OLAP API provides expanded calculation capabilities beyond those that can be handled efficiently in other OLAP solutions, such as:
Balance(Account "BOOKED", Period "PRIOR")/ Balance(Account "BILLED", Period "LAST")
The OLAP engine performs additional calculations, such as:
These types of analysis can be performed on data in the analytic workspace.
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);