Components of Oracle OLAP

Analytical queries and predictive analyses require a multidimensional OLAP solution. Oracle OLAP consists of the following components:

• Calculation engine
• Analytic workspaces
• OLAP DML
• PL/SQL table functions
• OLAP API
• OLAP Catalog metadata

This guide explains the relationships among these components from the perspectives of both database administrators and application developers.

Calculation Engine

The OLAP calculation engine supports the selection and rapid calculation of multidimensional data. The status of an individual session persists to support a series of queries, which is typical of analytical applications; the output from one query is easily used as input to the next query. The OLAP engine runs within the Oracle kernel.

Analytic Workspace

An analytic workspace stores multidimensional data objects and procedures written in the OLAP DML. Within a single database, many analytic workspaces can be created and shared among users. Like relational tables, an analytic workspace is owned by a particular user ID, and other users can be granted access to it. Because individual users can save a personal copy of their alterations to a workspace, the workspace environment is particularly conducive to planning applications.

An analytic workspace can be temporary (that is, for the life of the session) or it can be persistent, that is, saved from one session to the next. When an analytic workspace is persistent, the data is stored as LOBs in database tables. Analytic workspaces also provide an alternative to materialized views as a means of storing aggregate data.

OLAP DML

The OLAP DML is a data manipulation language that is understood by the Oracle OLAP calculation engine. The OLAP DML extends the analytical capabilities of querying languages such as SQL and the OLAP API to include forecasting, modeling, and what-if scenarios. Application developers can create stored procedures that use conditional logic and the extensive library of DML commands and functions to perform complex analyses of data. Moreover, the OLAP DML is a very accessible calculation language, similar to that of a spreadsheet, which is easy for power users and DBAs to learn and use.

OLAP DML commands and functions include the following categories:

Aggregation

Allocation

Data Selection

Date and Time Operations

File Reading and Writing

Financial Operations

Forecasts and Regressions

Numeric Manipulation

Models

Statistical Operations

Text Manipulation

Time Series Manipulation

Both the OLAP API and PL/SQL can embed OLAP DML commands in their syntax.

Using the OLAP DML, database administrators and application developers can create multidimensional data objects that are stored in an analytic workspace. The OLAP DML operates on data that is stored (permanently or temporarily) in these multidimensional objects.

SQL Table Functions

SQL table functions can take a set of rows as input and produce a set of rows as output that can be queried like a physical database table. Application developers who use SQL can access SQL packages that use table functions to create views of multidimensional data. SQL applications can then access these views. Thus, the calculation engine and multidimensional data sources are accessible to SQL, making analytic and predictive functions available to SQL-based applications. SQL applications can connect to the database using either the Oracle Call Interface (OCI) or Java Database Connectivity (JDBC).

OLAP API

The Oracle OLAP API is an application programming interface to Oracle OLAP. It is a querying language that selects and manipulates data for display in a Java client. Because the OLAP API is all Java, it supports deployment of analytical applications to large, geographically distributed user communities on the Internet. It is object oriented, so that application developers define the results they want, not the process by which the results are obtained. The OLAP API connects to the database using JDBC.

The OLAP API is the technology underlying the Oracle BI Beans for access to relational and multidimensional data. JavaBeans are the building blocks of application development. They are reusable pieces of Java code that can be assembled quickly into an application. The Oracle BI Beans provide pre-built OLAP-aware application building blocks: Connecting to a database; authenticating user credentials; selecting and fetching data; and displaying the data in a variety of tabular and graphical formats. Using the BI Beans, developers can create applications with a common "look and feel," enabling users to gain expertise quickly in the new product.

The BI Beans can be used within Oracle JDeveloper or other Java development environments to build analytical applications, which can be deployed as either Java or HTML clients.

OLAP Catalog

Metadata is typically defined as "data about data." OLAP Catalog metadata is created and stored in relational tables in the database. OLAP applications can query this metadata repository to find out what data is available for analyses and display. The metadata contains information about the physical location of the data, that is, whether it is stored in a relational table or in an analytic workspace. The application does not need to be aware of the location of the data or alter its processing to accommodate the storage location. Since the data is queried using SQL, data from relational data and multidimensional data can be joined in a single SQL query.

Whether the data is stored in a relational schema or in an analytic workspace, the metadata identifies the data in terms of the multidimensional objects: measures, dimensions, levels, and attributes. The metadata provides information critical to the selection, manipulation, and display of that data.