OLAP API Overview

What is the OLAP API?

The OLAP API is a Java application programming interface (API) through which an application can access data for online analytical processing (OLAP). It is the API that is supplied with OLAP Services, an Oracle 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 recreating the entire query. Such multistep queries are easy to modify and refine dynamically.

Multidimensional concepts and the OLAP API

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 customer, product, and time. Typically, a dimension is associated with one or more hierarchies. Several distinct dimensions, combined with measures, enable end users to answer business questions. For example, a Time dimension that categorizes data by month helps to answer the question, "Did we sell more widgets in January or June?"

• Measure. Data, usually numeric and additive, that can be examined and analyzed. Typically, a given measure is categorized by one or more dimensions, and it is described as "dimensioned by" them.

• Hierarchy. A logical structure that uses ordered levels as a means of organizing dimension elements 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 hierarchy. For example, a time dimension might have a hierarchy that represents data at the day, month, quarter, and year levels.

• Attribute. A descriptive characteristic of the elements 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, which is referred to as the query's result set. The specification may require selecting, aggregating, calculating, or otherwise manipulating data. If such manipulation is required, it is an intrinsic part 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 values, and the body of a cube contains measure values. For example, sales data can be organized into a cube whose edges contain values from the time, product, and customer dimensions and whose body contains values from the sales measure.

• Edge. One side of a cube. Each edge contains values 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 Data Warehousing Guide.

What type of data can an application access through the OLAP API?

The OLAP API, as part of OLAP Services, 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 or snowflake schema, which represents a multidimensional data model. The star or snowflake 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 given data warehouse, a database administrator must first ensure that the data warehouse is configured according to a star or snowflake schema. Then the database administrator must use the OLAP management feature in Oracle Enterprise Manager to create the required metadata, which can be defined as "data about the data." Once the metadata is in place, an application can access both the data and the metadata through the OLAP API.

The collection of warehouse data for which a database administrator has created metadata using the OLAP management feature of Oracle Enterprise Manager is referred to as the data store to which the OLAP API gives access.

What can an application do with the OLAP API?

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 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.

Context for OLAP API development

The OLAP API is a Java API, so it has all 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.

As the programming interface for OLAP Services, the OLAP API is part of Oracle. The OLAP API development environment includes an Oracle database, an OLAP service, and the OLAP management feature in Oracle Enterprise Manager. The OLAP management feature in Oracle Enterprise Manager is an important companion to the OLAP API, because it generates the metadata that the OLAP API requires for accessing data that is stored in an Oracle database.

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.