The Oracle OLAP Java API is an application programming interface that provides access to the online analytic processing (OLAP) technology in Oracle Database with the OLAP option. This topic lists operations that an OLAP Java API client application can perform, describes the classes in the OLAP Java API, describes the objects in a dimensional data model, and discusses organizing data for online analytical processing.

For a description of the advantages of OLAP technology, see Oracle OLAP User's Guide. That document describes the capabilities that Oracle OLAP provides for the analysis of multidimensional data by business intelligence and advanced analytical applications. It describes in depth the dimensional data model, and it discusses the database administration and management tasks related to Oracle OLAP.

Oracle OLAP metadata objects organize and describe the data that is available to a client application. The metadata objects contain other information, as well, such as the data type of the data. However, you cannot retrieve data directly from a metadata object. To specify the data that you want, you must create a query. In specifying the data, you usually must specify one or more dimension member values. To retrieve the specified data, you create a Cursor. This topic briefly describes those actions.

Another way that you can query the data contained in OLAP metadata objects is through SQL queries of the views that Oracle OLAP creates for the metadata objects. For information about querying these views, see "Using OLAP Views" in Understanding OLAP Java API Metadata.

The examples of OLAP Java API code in this documentation are excerpts from a set of example programs that are available on the Oracle Technology Network (OTN) website. One example, CreateAndBuildAW.java, has methods that create and build an analytic workspace. Another example, SpecifyAWValues, calls the methods of CreateAndBuildAW.java and specifies values, such as names for the metadata objects and names of columns of relational tables for mapping the metadata objects to data sources. The analytic workspace produced by these examples is named GLOBAL_AWJ. Other examples query that analytic workspace. The metadata objects in the analytic workspace are mapped to columns in relational tables that are in the Global schema.

From the OTN website, you can download a file that contains SQL scripts that create the Global schema and a file that contains the example programs. The OTN website is at http://www.oracle.com/technetwork/database/options/olap/index.html.

To get either file, select Sample Code and Schemas in the Download section of the web page. To get the sample schema, select Global Schema 11g. To get the example programs, select Example Programs for Documentation and then select Download the Example Programs for 11g Release 2 (11.2) to download the compressed file that contains the examples.

The example programs are in a package structure that you can easily add to your development environment. The classes include a base class that the example program classes extend, and utility classes that they use. The base class is BaseExample11g.java. The utility classes include Context11g.java and CursorPrintWriter.java. The Context11g.java class has methods that create a connection to an Oracle Database instance, that store metadata objects, that return the stored metadata objects, and that create Cursor objects. The CursorPrintWriter.java class is a PrintWriter that has methods that display the contents of Cursor objects.

The OLAP metadata objects are created and built by the CreateAndBuildAW.java and the SpecifyAWValues programs. Those metadata objects include the following:

• GLOBAL_AWJ, which is the analytic workspace that contains the other objects.

• PRODUCT_AWJ, which is a dimension for products. It has one hierarchy named PRODUCT_PRIMARY. The lowest level of the hierarchy has product item identifiers and the higher levels have product family, class, and total products identifiers.

• CUSTOMER_AWJ, which is a dimension for customers. It has two hierarchies named SHIPMENTS and MARKETS. The lowest level of each hierarchy has customer identifiers and higher levels have warehouse, region, and total customers, and account, market segment, and total market identifiers, respectively.

• TIME_AWJ, which is a dimension for time values. It has a hierarchy named CALENDAR_YEAR. The lowest level has month identifiers, and the other levels have quarter and year identifiers.

• CHANNEL_AWJ, which is a dimension for sales channels. It has one hierarchy named CHANNEL_PRIMARY. The lowest level has sales channel identifiers and the higher level has the total channel identifier.

• UNITS_CUBE_AWJ, which is a cube that contains the measures COST, SALES, and UNITS. COST has values for the costs of product units. SALES has the dollar amounts for the sales of product units. UNITS has values for the quantities of product units sold. The cube is dimensioned by all four dimensions. The aggregation method for the cube is SUM, in which each the value for each parent is the sum of the values of the children of the parent.

• PRICE_CUBE_AWJ, which is a cube that contains the measures UNIT_COST and UNIT_PRICE. UNIT_COST has the costs of the units. UNIT_PRICE has the prices of the units. The cube is dimensioned by the PRODUCT_AWJ and TIME_AWJ dimensions. The aggregation method for the cube is AVG, in which the value for each parent is the average of the values of the children of the parent.

For an example of a program that discovers the OLAP metadata for the analytic workspace, see Discovering Metadata.

A client application that uses the OLAP Java API typically performs the following tasks:

1. Connects to the data store and creates a DataProvider and a UserSession.

2. Creates or discovers metadata objects.

3. Deploys, maps, and builds metadata objects, as needed.

4. Specifies queries that select and manipulate data.

5. Retrieves query results.

The rest of this topic briefly describes these tasks, and the rest of this guide provides detailed information about how to accomplish them.

Task 1: Connect to the Data Store and Create a DataProvider and UserSession

You connect to the data store by identifying some information about the target Oracle Database instance and specifying this information in a JDBC connection method. Having established a connection, you create a DataProvider and use it and the connection to create a UserSession. For more information about connecting and creating a DataProvider and UserSession, see "Connecting to Oracle OLAP" in Discovering Metadata.

Task 2: Create or Discover Metadata Objects

You use the DataProvider to get an MdmMetadataProvider. The MdmMetadataProvider gives access to all of the metadata objects in the data store. You next obtain the MdmRootSchema object by calling the getRootSchema method of the MdmMetdataProvider. The MdmRootSchema object contains all of the OLAP metadata objects in the database. From the MdmRootSchema, you get the MdmDatabaseSchema objects for the schemas that the current user has permission to access. An MdmDatabaseSchema represents a named Oracle Database user as returned by the SQL statement SELECT username FROM all_users.

From an MdmDatabaseSchema, you can discover the existing metadata objects that are owned by the schema or you can create new ones. Methods such as getMeasures and getDimensions get all of the measures or dimensions owned by the MdmDatabaseSchema. Methods such as findOrCreateAW and findOrCreateCube get an analytic workspace or cube, if it exists, or create one if it does not already exist.

From a top-level metadata object contained by the MdmDatabaseSchema, such as an analytic workspace, cube, or dimension, you can get the objects that it contains. For example, from an MdmPrimaryDimension, you can get the hierarchies, levels, and attributes that are associated with it. Having determined the metadata objects that are available to the user, you can present relevant lists of objects to the user for data selection and manipulation.

For a description of the metadata objects, see Understanding OLAP Java API Metadata. For information about how you can discover the available metadata, see Discovering Metadata.

Task 3: Deploy, Map, and Build Objects

If you create a new MdmCube or MdmPrimaryDimension, you must deploy it as an analytic workspace object or as a relational OLAP (Rolap) object. To deploy a cube, you call an MdmCube method such as findOrCreateAWCubeOrganization. To deploy a dimension, you call an MdmPrimaryDimension method such as findOrCreateAWPrimaryDimensionOrganization.

If you create a new metadata object that represents data, you must specify an Expression that maps the metadata object to a relational source table or view, or that Oracle OLAP uses to generate the data. For objects that are contained by an analytic workspace, you can build the metadata objects after mapping them. For information on creating metadata, deploying, mapping, and building metadata objects, see Creating Metadata and Analytic Workspaces.

Task 4: Select and Calculate Data Through Queries

An OLAP Java API application can construct queries against the data store. A typical application user interface provides ways for the user to select data and to specify the operations to perform using the data. Then, the data manipulation code translates these instructions into queries against the data store. The queries can be as simple as a selection of dimension members, or they can be complex, including several aggregations and calculations involving the measure values that are specified by selections of dimension members.

The OLAP Java API object that represents a query is a Source. Metadata objects that represent data are extensions of the MdmSource class. From an MdmSource, such as an MdmMeasure or an MdmPrimaryDimension, you can get a Source object. With the methods of a Source object, you can produce other Source objects that specify a selection of the elements of the Source, or that specify calculations or other operations to perform on the values of a Source.

If you are implementing a simple user interface, then you might use only the methods of a Source object to select and manipulate the data that users specify in the interface. However, if you want to offer your users multistep selection procedures and the ability to modify queries or undo individual steps in their selections, then you should design and implement Template classes. Within the code for each Template, you use the methods of the Source classes, but the Template classes themselves allow you to dynamically modify and refine even the most complex query. In addition, you can write general-purpose Template classes and reuse them in various parts of your application.

For information about working with Source objects, see Understanding Source Objects. For information about working with Template objects, see Creating Dynamic Queries.

Task 5: Retrieve Query Results

When users of an OLAP Java API application are selecting, calculating, combining, and generally manipulating data, they also want to see the results of their work. This means that the application must retrieve the result sets of queries from the data store and display the data in multidimensional form. To retrieve a result set for a query through the OLAP Java API, you create a Cursor for the Source that specifies the query.

You can also get the SQL that Oracle OLAP generates for a query. To do so, you create a SQLCursorManager for the Source instead of creating a Cursor. The generateSQL method of the SQLCursorManager returns the SQL specified by the Source. You can then retrieve the data by means outside of the OLAP Java API.

Because the OLAP Java API was designed to deal with a multidimensional view of data, a Source can have a multidimensional result set. For example, a Source can represent an MdmMeasure that is dimensioned by four MdmPrimaryDimension objects. Each MdmPrimaryDimension has an associated Source. You can create a query by joining the Source objects for the dimensions to the Source for the measure. The resulting query has the Source for the measure as the base and it has the Source objects for the dimensions as outputs.

A Cursor for a query Source has the same structure as the Source. For example, the Cursor for the Source just mentioned has base values that are the measure data. The Cursor also has four outputs. The values of the outputs are those of the Source objects for the dimensions.

To retrieve all of the items of data through a Cursor, you can loop through the multidimensional Cursor structure. This design is well adapted to the requirements of standard user interface objects for painting the computer screen. It is especially well adapted to the display of data in multidimensional format.

For more information about using Source objects to specify a query, see Understanding Source Objects. For more information about using Cursor objects to retrieve data, see Understanding Cursor Classes and Concepts. For more information about the SQLCursorManager class, see Oracle OLAP Java API Reference.