| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-02 |
|
|
View PDF |
| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-02 |
|
|
View PDF |
This chapter introduces the Oracle OLAP API to application developers who plan to use it in their Java applications.
This chapter includes the following topics:
The OLAP API is a Java application programming interface (API) through which an application can access data for online analytical processing (OLAP). The Java classes that implement the API are part of the Oracle OLAP component.
The purpose of the OLAP API is to facilitate the development of OLAP applications, which allow users 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 API are optimized for this type of application. With the OLAP API, a Java application can access, manipulate, and display data in multidimensional terms. The OLAP 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 API:
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 item, and a measure that contains data for the unit cost of product items by month, an application can formulate the query, "Did we sell more widgets in January or June?"
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.
Hierarchy. A logical structure that uses ordered levels or values as a means of organizing dimension members in parent-child relationships. Typically, end users can expand or collapse the hierarchy by drilling down or up on its levels.
Level. A position in 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 that an end user can specify to select data. For example, end users might choose products using a color attribute.
Query. A specification for a particular set of data, and for aggregations, calculations, or other operations to perform using the data. Any such operations on the data are an intrinsic part of the query. The data and the operations on it define the result set of the query.
Two additional data warehouse and OLAP concepts, cube and edge, are not intrinsic to the OLAP API, but are often incorporated into the design of applications that use the OLAP API.
Cube. A logical organization of multidimensional data. 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 units sold measure.
Edge. One side of a cube. 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 the Oracle Database Data Warehousing Guide.
The OLAP 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.
In order for the OLAP API to access the data in a data warehouse, a database administrator must first 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 must design an OLAP metadata model, map the logical metadata objects to data in the warehouse, and build a dynamic analytic workspace. Building the dynamic analytic workspace creates OLAP metadata objects. An OLAP API application can then get the OLAP metadata objects and use them to create queries that operate on the data in the warehouse.
A database administrator designs and builds a dynamic analytic workspace by using the Analytic Workspace Manager or by using the Oracle OLAP Analytic Workspace Java API. See the Oracle OLAP Application Developer's Guide for information about supported data warehouse configurations and about creating a dynamic analytic workspace. See the Oracle OLAP Analytic Workspace Java API Reference for information on creating an analytic workspace with a Java application.
The collection of warehouse data for which a database administrator has created a dynamic analytic workspace is the data store to which the OLAP API gives access. Of course, each user who accesses data through the OLAP API might have security restrictions that limit the scope of the data that he or she can access within the data store.
With classes in the oracle.olapi.AWXML package of Oracle OLAP Analytic Workspace Java API, an application developer can create transient custom metadata objects that an OLAP API program can query in the same connection to the Oracle Database instance.
Through the OLAP API, an application can do the following:
Establish a connection to a data store.
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 multidimensional format.
Modify existing queries, rather than totally redefine them, as application users refine their analyses.
The OLAP API is a Java API, so it 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 API, and because the client application is written in Java, its code can also take advantage of them.
In order to work with the OLAP API, application developers should have familiarity with Java, object-oriented programming, relational databases, data warehousing, and multidimensional OLAP concepts.
The examples of OLAP API code in this documentation are excerpts from example programs that query a dynamic analytic workspace named GLOBALAW. That analytic workspace is built from relational tables by the BuildAWExample.java example program, which uses the Oracle OLAP Analytic Workspace Java API.
You can download a zip file that contains the source code for the BuildAWExample.java program and the complete source code for the OLAP API example programs from the Documentation section of the Oracle Technology Network (OTN) Web site at
http://www.oracle.com/technology/products/bi/olap/olap.html
From the OTN Web site, you can also download the SQL scripts that create the Global Schema for Documentation. For the sample schema, see Sample Schemas for Documentation in the Documentation section.
For the example programs, see Example Programs for Documentation in the Documentation section the OTN Web site. 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 are base classes that the example program classes extend, and utility classes that they use. The base classes are BaseExample.java and ContextExample.java. The utility classes include Context10g.java and CursorPrintWriter.java. The Context10g.java class has methods that create a connection to an Oracle Database instance, that store metadata objects, and that return the stored metadata objects. The CursorPrintWriter.java class is a PrintWriter that has methods that create Cursor objects and display them.
The OLAP metadata that is generated by the GLOBALAW analytic workspace includes the following:
UNITS_AW, which has the quantity of product units sold.
SALES_AW, which has the dollar amounts for the sales of product units.
UNIT_COST_AW, which has the cost of a unit.
UNIT_PRICE_AW, which has the price of a unit.
The data in the measures is identified by detailed (leaf-level) data or aggregate (node-level) data from dimensions. The UNITS_AW measure is dimensioned by the following dimensions:
PRODUCT_AW, which has a hierarchy of product values named PRODUCT_PRIMARY_AW. The leaf level of the hierarchy has product item identification numbers and the higher levels have product family, class, and total products identifiers.
CUSTOMER_AW, which has two hierarchies of customer members, named SHIPMENTS_AW and MARKET_SEGMENT_AW. The lowest level of each hierarchy has customer identification numbers and higher levels have warehouse, region, and total customers, and account, market segment, and total market identifiers, respectively.
TIME_AW, which has a hierarchy of calendar year time period identifiers.
CHANNEL_AW, which has a hierarchy of sales channel identifiers.
The UNIT_COST_AW and UNIT_PRICE_AW measures are dimensioned by the following two dimensions:
PRODUCT_AW
TIME_AW
For an example of a program that discovers the OLAP metadata for the analytic workspace, see Chapter 4, "Discovering the Available Metadata".
Oracle OLAP metadata objects describe the data that is available to the OLAP 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 have String values that identify the product or time values and the hierarchical levels, and the dimensions have attributes that provide additional information, such as a descriptive name for each dimension member that can be used in reports.
In contrast, the fact that the price of product 13 in month 55 was 2426.07 dollars is data, not metadata.
These examples distinguish between the metadata and the data for the measure of unit prices. The OLAP API makes a similar distinction between the metadata and the data for dimensions. For example, the fact that a product dimension exists and that its members have text values is metadata. In contrast, the fact that the value of one of its members is 13 is data.
The OLAP 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 measures, dimensions, hierarchies, and attributes.
The following are some of the Java classes that are supplied by the OLAP API in its implementation of the MDM model:
MdmSchema
MdmMetadataProvider
MdmMeasure
MdmDimension
MdmHierarchy
MdmLevel
MdmAttribute
An MdmSchema is a container for MdmMeasure, MdmDimension, and other MdmSchema objects. An MdmSchema corresponds to a measure folder in the Analytic Workspace Manager. Note that an MdmSchema does not necessarily correspond to a relational schema.
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 top-level MdmSchema, which contains all of the MdmDimension objects that are accessible through this particular MdmMetadataProvider. The MdmDimension objects might be organized in a hierarchical tree, with subschemas nested under the top-level schema. Using the getMeasureDimension, getSubSchemas, and getDimensions methods of the top-level MdmSchema, and the getSubSchemas, getMeasures, and getDimensions methods of all of the nested MdmSchema objects, an application navigates through the metadata and discovers what data is available. In addition, the application can use methods to obtain the related MdmMeasure, MdmHierarchy, MdmLevel, and MdmAttribute objects.
Chapter 2, "Understanding OLAP API Metadata", provides detailed information about the OLAP API metadata.
An MdmMeasure or MdmDimension represents data in the data store. For example, an MdmMeasure object named sales might represent a set of elements whose numeric values are dollar amounts for units sold, and an MdmDimension called prodDim 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 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 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. In the OLAP API a dimension always has at least one hierarchy dimension object and that hierarchy object has at least one level object. Even a nonhierarchical dimension is represented by a hierarchy dimension object with one level object.
The OLAP 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_AW level of the CALENDAR_YEAR_AW hierarchy of the TIME_AW dimension:
CALENDAR_YEAR_AW::YEAR_AW::2
The third part of a unique value is the local value. The local value in the preceding example identifies the year 1999.
The OLAP 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 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 Application Developer's Guide.
The OLAP 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 discover metadata, specify queries, and retrieve data.
When a Java application calls methods of objects of OLAP API Java classes, it uses the OLAP API client software to communicate with Oracle OLAP, which resides within an Oracle database instance. The communication between the OLAP API client software and Oracle OLAP is provided through Java Database Connectivity (JDBC), which is a standard Java interface for connecting to relational databases. For more information about JDBC, see the Oracle Database JDBC Developer's Guide and Reference.
To use the OLAP API classes as you develop your application, import them into your Java code. When you deliver your application to users, include the OLAP API classes with the application. You must also ensure that users can access JDBC.
In order to develop an OLAP 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 API client software, see Appendix A, "Setting Up the Development Environment". For detailed information about the OLAP API classes and methods, see the Oracle OLAP Java API Reference and subsequent chapters of this guide.
An application that uses the OLAP API typically performs the following tasks:
Connects to the data store
Discovers the available metadata
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 and specifying this information in a JDBC connection method.
For more information about connecting, see Chapter 3, "Connecting to a Data Store".
Having established a connection, the application creates an MdmMetadataProvider. This object 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 MdmSchema object that represents the top-level measure folder for all of the metadata objects to which the MdmMetdataProvider provides access. The application then gets the dimensions, including the measure dimension, and the subfolders that are under the root.
Once the application has all of the dimensions, it can interrogate them to get their attributes, hierarchies, levels, and other characteristics, and the measures. 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 API Metadata". For information about how an application can discover the available metadata, see Chapter 4, "Discovering the Available Metadata".
The heart of any OLAP application lies in the construction of queries against the data store. The application user interface provides ways for the user to select data and to specify what should be done with it. 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 measure values specified by selections of dimension members.
The OLAP API object that specifies a query is a Source. Therefore, a significant portion of any OLAP API application is devoted to dealing with Source objects.
From an MdmSchema, you get MdmSource objects, such as an MdmMeasure or an MdmPrimaryDimension. You then get a Source object from the MdmSource. 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 the Source classes 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, 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 minimize your work by writing general-purpose Template classes and reusing 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 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 API. The ExpressSQLCursorManager class implements the SQLCursorManager interface.
Because the OLAP 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 structured by four MdmPrimaryDimension objects. Each MdmPrimaryDimension is represented by a 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 measure data as its values and it has the Source objects for the dimensions as its outputs.
A Cursor for the query Source has the same structure as the Source; that is, the values of the Cursor 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 the Oracle OLAP Java API Reference.
