| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
This chapter introduces the Oracle OLAP Java API to application developers who plan to use it in their Java applications.
This chapter includes the following topics:
The OLAP Java API is a Java application programming interface (API) through which an application can implement a metadata model, define and build analytic workspaces, and access data for online analytical processing (OLAP). That data can be in relational database structures or it can be in analytic workspaces. The Java classes that implement the API are part of the Oracle OLAP component of Oracle Database.
The purpose of the OLAP Java API is to facilitate the development of OLAP applications, which allow users to create analytic workspaces and to dynamically select, aggregate, calculate, and perform other analytical tasks on data through a graphical user interface. Typically, the user interface of an OLAP application displays data in multidimensional formats, such as graphs and crosstabs.
In general, OLAP applications are developed within the context of business intelligence and data warehousing systems, and the features of the OLAP Java API are optimized for this type of application. With the OLAP Java API, a Java application can create and maintain analytic workspaces, and access, manipulate, and display relational or analytic workspace data in multidimensional terms. The OLAP Java API also makes it possible to define a query in a step-by-step process that allows for undoing individual query steps without reproducing the entire query. Such multistep queries are easy to modify and refine dynamically.
Data warehousing and OLAP applications are based on a multidimensional view of data, and they work with queries that represent selections of data. The following definitions introduce concepts that reflect the multidimensional view and are basic to data warehousing, OLAP, and the OLAP Java API:
Cube. A logical organization of multidimensional data that associates one or more measures with a set of dimensions. All of the measures are dimensioned by the same set of dimensions. An OLAP cube has edges and a body. Typically, the edges of a cube contain dimension member values, and the body of a cube contains measure values. For example, data on the quantity of product units sold can be organized into a cube whose edges contain values for members from the time, product, customer, and channel dimensions and whose body contains values from the measure of units sold.
Measure. Data, usually numeric and additive, that can be examined and analyzed. Typically, a measure is categorized by one or more dimensions, and it is described as "dimensioned by" them.
Dimension. A structure that categorizes data. Commonly-used dimensions are customers, products, and times. Typically, the members of a dimension are organized one or more hierarchies that have one or more levels. Sets of members of different dimensions identify measure values. By specifying dimension members, measures, and calculations to perform on the data, end users formulate business questions and get answers to their queries. For example, using a time dimension that categorizes data by month, a product dimension that categorizes data by unit item, and a measure that contains data for the quantities of product units sold by month, an application can formulate the query, "Did we sell more widgets in January or June?"
Hierarchy. A logical structure that uses ordered levels or values as a means of organizing dimension members in parent-child relationships. Typically, in the user interface end users can expand or collapse the hierarchy by drilling down or up on the levels.
Level. A component of a level-based hierarchy. For example, a time dimension might have a hierarchy that has members that represents data at the day, month, quarter, and year levels.
Attribute. A descriptive characteristic of the members of a dimension. An end user can use an attribute to select data. For example, an end user might select a set of products using a color attribute.
Query. A specification for a particular set of data. The term query in the OLAP Java API refers to a Source object that specifies a set of data and can include aggregations, calculations, or other operations to perform using the data. The data and the operations on it define the result set of the query. The 11g release introduces a Query class in the oracle.olapi.syntax package. A Query represents a multi-row, multi-column result set that is similar to a relational table, a SQL SELECT statement, or an OLAP function. In this documentation, the general term query continues to refer to a Source object.
An edge is one side of a cube. The OLAP concept of an edge is not represented by a metadata object in the OLAP Java API, but is often incorporated into the design of applications that use the OLAP Java API. Each edge contains values of members from one or more dimensions. Although there is no limit to the number of edges on a cube, data is often organized for display purposes along three edges, which are referred to as the row edge, column edge, and page edge.
For more information about all of these concepts, see Oracle OLAP User's Guide and Oracle Database Data Warehousing Guide.
The OLAP Java API, as part of Oracle OLAP, makes it possible for Java applications (including applets) to access data that resides in an Oracle data warehouse. A data warehouse is a relational database that is designed for query and analysis, rather than transaction processing. Warehouse data often conforms to a star schema, which represents a multidimensional data model. The star schema consists of one or more fact tables and one or more dimension tables that are related through foreign keys. Typically, a data warehouse is created from a transaction processing database by an extraction transformation transport (ETT) tool, such as Oracle Warehouse Builder.
For the data in a data warehouse to be accessible to an OLAP Java API application, a database administrator must ensure that the data warehouse is configured according to an organization that is supported by Oracle OLAP. The star schema is one such organization, but not the only one. Once the data is organized in the warehouse, the database administrator can design an OLAP metadata model, map the logical metadata objects to data in the warehouse, and build an analytic workspace using Analytic Workspace Manager (AWM). Building the analytic workspace populates the storage structures with the data that the OLAP metadata objects represent. See Oracle OLAP User's Guide for information about supported data warehouse configurations and about creating an analytic workspace with AWM.
With the OLAP Java API, an application can also design the metadata model, map the logical objects to data in the warehouse, and build an analytic workspace. An application can then get the OLAP metadata objects created either by AWM or through the OLAP Java API. It can use the metadata objects to create queries that operate on the data in the warehouse.
The collection of warehouse data for which a database administrator has created an analytic workspace is the data store to which the OLAP Java API gives access. Of course, each user who accesses data through the OLAP Java API might have security restrictions that limit the scope of the data that he or she can access within the data store.
Through the OLAP Java API, an application can do the following:
Establish a connection to an Oracle Database instance.
Provide for multiple user sessions that share the same connection and the same cache of metadata objects.
Create logical metadata objects and map them to relational sources.
Deploy the metadata objects as an analytic workspace or as relational tables and views and commit the objects to the database.
Explore the metadata to discover what data is available for viewing or analysis.
Create queries that specify and manipulate the data according to the needs of application users (for example, selecting, aggregating, and calculating data).
Retrieve query results that are structured for display in a multidimensional format.
Modify existing queries, rather than totally redefine them, as application users refine their analyses.
The OLAP Java API has all of the advantages of the Java environment. It is platform independent and it provides the benefits of an object-oriented API, such as abstraction, encapsulation, polymorphism, and inheritance. These strengths are built into the OLAP Java API and because the client application is written in Java, it can take advantage of them.
To work with the OLAP Java API, application developers should have familiarity with Java, object-oriented programming, relational databases, data warehousing, and multidimensional OLAP concepts.
The examples of OLAP Java API code in this documentation are excerpts from example programs that query an analytic workspace named GLOBAL_AWJ. That analytic workspace is built from relational tables by the BuildAW11g.java example program. The relational tables are in the Global schema.
From the Oracle Technology Network (OTN) Web site, you can download a zip file that contains the SQL scripts that create the Global schema. To get that file, go to Sample Schemas for Documentation in the Documentation section. The Oracle Technology Network (OTN) Web site is at
http://www.oracle.com/technology/products/bi/olap/olap.html
On the OTN Web site, the complete code for the examples is available in the examples.zip file. That file is also in an Oracle Database installation. It is located in the same directory as the OLAP Java API class libraries. See Appendix A, "Setting Up the Development Environment" for that location.
The example programs are in a package structure that you can easily add to your development environment. At the top level of the package hierarchy is 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 persistent OLAP metadata objects that the BuildAW11g.java program creates 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 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 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 UNITS and SALES. UNITS has values for the quantities of product units sold. SALES, which has the dollar amounts for the sales of product units. The cube is dimensioned by all four dimensions.
PRICE_CUBE_AWJ, which is a cube that contains the measures UNIT_COST and UNIT_PRICE. UNIT_COST has the costs of a unit. UNIT_PRICE has the prices of a unit. The cube is dimensioned by the PRODUCT_AWJ and TIME_AWJ dimensions.
For an example of a program that discovers the OLAP metadata for the analytic workspace, see Chapter 3, "Discovering Metadata".
Oracle OLAP metadata objects describe the data that is available to the OLAP Java API through a connection to the database. The metadata objects record three things:
The existence of sets of data. For example, a measure of unit price figures, dimensions of product and time member values, and attributes that contain information about the members of the dimensions all exist as named entities in the data store.
The structure of the sets of data. For example, the Unit Price measure is dimensioned by products and times, an attribute is dimensioned by the dimension for which it records information, and the members of the dimensions are organized into hierarchical levels.
The characteristics of the data. For example, the Unit Price measure contains numeric values that are specified by the dimension member values, the dimension members are unique String values, and the dimensions have attributes that provide additional information, such as a descriptive name for each dimension member.
In contrast, the fact that the price of a specific product in a specific month was 2,426.07 dollars is data, not metadata.
These examples distinguish between the metadata and the data for the measure of unit prices. The OLAP Java API makes a similar distinction between the metadata and the data for dimensions. For example, the fact that a product dimension exists and that the dimension members have text values is metadata. In contrast, the fact that the unique value of one of the dimension members is PRODUCT_PRIMARY::ITEM::ENV STD is data.
The OLAP Java API multidimensional metadata (MDM) model describes data in multidimensional terms, which are familiar to OLAP and data warehousing audiences. For example, it includes objects for cubes, measures, dimensions, hierarchies, and attributes.
The following are some of the Java classes that are supplied by the OLAP Java API in the implementation of the MDM model:
MdmMetadataProvider
MdmRootSchema
MdmDatabaseSchema
MdmCube
MdmMeasure
MdmDimension
MdmDimensionLevel
MdmHierarchy
MdmHierarchyLevel
MdmAttribute
An MdmMetadataProvider gives an application access to the MDM metadata objects that represent the OLAP metadata objects. To obtain the MDM metadata objects, an application uses the getRootSchema method of an MdmMetadataProvider. This method returns the MdmRootSchema, which is a container for the other accessible OLAP metadata objects. From the MdmRootSchema an application can get the MdmDatabaseSchema objects that are available. An MdmDatabaseSchema represents a schema in the relational database. As each database user owns a single relational schema, each user has a single MdmDatabaseSchema.
An MdmDatabaseSchema contains the other accessible metadata objects, such as cubes, measures, and dimensions. An MdmDatabaseSchema can have one or more subschemas. These subschemas are instances of the MdmOrganizationalSchema class, which corresponds to a measure folder in Analytic Workspace Manager.
From the MdmRootSchema, an application can also get all of the MdmCube, MdmMeasure, and MdmDimension objects that are available. From an MdmDatabaseSchema, an application can get the MdmCube, MdmMeasure, and MdmDimension objects that are owned by that schema.
The MdmDimension objects and MdmMeasure objects might be organized in a hierarchical tree, with MdmOrganizationalSchema subschemas nested under an MdmDatabaseSchema. An application can navigate through the objects owned by an MdmDatabaseSchema to discover the metadata objects that are available.
Chapter 2, "Understanding OLAP Java API Metadata", provides detailed information about the OLAP Java API metadata.
An MdmMeasure or MdmDimension represents data in the data store. For example, an MdmMeasure object named mdmSales might represent a set of elements whose numeric values are dollar amounts for units sold, and an MdmDimension called mdmProdDim might represent a set of members whose text values are product identifiers. However, an application cannot create a query on the data using an MdmMeasure or MdmDimension. As metadata, MdmMeasure and MdmDimension objects provide descriptive information about data, but they do not provide the ability to construct a query that specifies the data. To select, calculate, and otherwise manipulate data for analysis, an application must create a query.
To create a query on the data for an MdmMeasure or MdmDimension, an application must first get the Source object for the MdmMeasure or MdmDimension by calling the getSource method of the metadata object. This method returns a Source object that the application can use to specify a query. The query defines a result set, and, in this case, the result set is the data for the MdmMeasure or MdmDimension.
In addition to representing the data for metadata objects, Source objects can represent the data for any query that an application creates. For example, a Source might specify a query for a selection of MdmDimension values (such as January, February, and March of the year 2002) or a calculation of the values of one MdmMeasure minus those of another (such as unitPrice minus unitCost). An application can use the powerful methods of the Source class and its subclasses to combine data in any way that the user requires.
One of the useful characteristic of Source objects is that they make no distinction between attributes, dimensions, and measures. The Source objects for all of them behave in the same way.
To retrieve the data specified by a Source, an application creates a Cursor for that Source. The application then uses this Cursor to request and retrieve the data from the data store. When an application makes a request for data, it can specify the typical amount of data that it requires at a given time (for example, enough to fill a 40-cell table on the screen). Oracle OLAP then handles the issues related to efficient retrieval. The application does not need to manage the timing, sizing, and caching of the data blocks that it retrieves through the OLAP Java API.
The members of an Oracle OLAP dimension are usually organized into one or more hierarchies. Some hierarchies have parent-child relationships based on levels and some have those relationships based on values.
The OLAP Java API uses a three-part format to specify the hierarchy, the level, and the value of a dimension member, and thus identify a unique value in the hierarchy. The first part of a unique value is the name of the hierarchy object, the second part is the name of the level object, and the third part is the value of the member in the level. The parts of the unique value are separated by a value separation string, which by default is double colons (::). The following is an example of a unique member value in the YEAR level of the CALENDAR_YEAR hierarchy of the TIME_AWJ dimension:
CALENDAR_YEAR::YEAR::CY2001
The third part of a unique value is the local value. The local value in the preceding example identifies the calendar year 2001.
The OLAP Java API has classes and methods that you can use to get the local values of dimension members. The MdmPrimaryDimension class has a method for getting an MdmAttribute that records the local values for the members of the hierarchies that are components of the MdmPrimaryDimension, and the MdmDimensionMemberInfo class has methods for getting the local or unique values for a member of a hierarchy or a level.
In addition to ensuring that data and metadata have been prepared appropriately, an application developer must ensure that application users can make a connection to the data store through the OLAP Java API and that users have database privileges that give them access to the data. For information about setting up for such connections, see the Oracle OLAP User's Guide.
The OLAP Java API client software is a set of Java packages containing classes that implement the programming interface to Oracle OLAP. An application creates objects of these classes and calls their methods to create or discover metadata, specify queries, and retrieve data.
When a Java application calls methods of objects of OLAP Java API classes, it uses the OLAP Java API client software to communicate with Oracle OLAP, which resides within an Oracle Database instance. The communication between the OLAP Java API client software and Oracle OLAP is provided through the Java Database Connectivity (JDBC) API, which is a standard Java interface for connecting to relational databases.
To use the OLAP Java API classes as you develop your application, import them into your Java code. When you deliver your application to users, include the OLAP Java API classes with the application. You must also ensure that users can access JDBC.
In order to develop an OLAP Java API application, you must have the Java Development Kit (JDK), such as one in Oracle JDeveloper or one from Sun Microsystems. Users must have a Java Runtime Environment (JRE) whose version number is compatible with the JDK that you used for development.
For information about Java version requirements and about setting up the OLAP Java API client software, see Appendix A, "Setting Up the Development Environment". For detailed information about the OLAP Java API classes and methods, see Oracle OLAP Java API Reference and the subsequent chapters of this guide.
An application that uses the OLAP Java API typically performs the following tasks:
Connects to the data store
Creates or discovers metadata objects
Defines and builds an analytic workspace, as needed
Specifies queries that select and manipulate data
Retrieves query results
The rest of this topic briefly describes these tasks, and the rest of this guide provides detailed information.
An application connects to the data store by identifying some information about the target Oracle Database instance and specifying this information in a JDBC connection method.
For more information about connecting, see Chapter 3, "Discovering Metadata".
Having established a connection, the application creates a DataProvider and uses it to get an MdmMetadataProvider. The MdmMetadataProvider gives access to all of the metadata objects in the data store.
To discover the available metadata, an application uses the getRootSchema method of the MdmMetdataProvider to obtain the MdmRootSchema object, which contains all of the metadata objects. The application then gets the MdmDatabaseSchema object or objects that the current user has permission to access.
From an MdmDatabaseSchema, the application can discover the existing metadata objects that are owned by schema or 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.
If an application creates a new metadata object that represents data, it must specify an Expression that maps the metadata object to a relational source table or that Oracle OLAP uses to generate the data. For information on creating metadata, see Chapter 4, "Creating Metadata and Analytic Workspaces".
From a top-level metadata objects, such as an analytic workspace, cube, or dimension, an application can get the objects that belong to it. For example, from an MdmPrimaryDimension, an application can get the hierarchies, levels, and attributes that are associated with it. Having determined the metadata objects that it has to work with, the application can present relevant lists of objects to the user for data selection and manipulation.
For a description of the metadata objects, see Chapter 2, "Understanding OLAP Java API Metadata". For information about how an application can discover the available metadata, see Chapter 3, "Discovering Metadata".
A typical OLAP application constructs queries against the data store. The 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 on 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 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 Chapter 5, "Understanding Source Objects". For information about working with Template objects, see Chapter 10, "Creating Dynamic Queries".
When users of an OLAP 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, the application creates a Cursor for the Source that specifies the query.
An application can also get the SQL that Oracle OLAP generates for a query. To do so, the application creates a SQLCursorManager for the Source instead of creating a Cursor. The generateSQL method of the SQLCursorManager returns the SQL specified by the Source. The application can then retrieve the data by methods 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. An application can create a query by joining the Source objects for the dimensions to the Source for the measure. The query has the Source for the measure as the base and it has the Source objects for the dimensions as outputs.
A Cursor for the query Source has the same structure as the Source; that is, the Cursor has base values that are the measure data and the Cursor 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, the application 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 Chapter 5, "Understanding Source Objects". For more information about using Cursor objects to retrieve data, see Chapter 8, "Understanding Cursor Classes and Concepts". For more information about the SQLCursorManager class, see Oracle OLAP Java API Reference.
