What Is the OLAP DML?

The OLAP DML is a data manipulation language. You can use DML commands and functions to perform complex analysis of data. You can also write programs that contain DML commands and functions.

The basic syntactic units of the OLAP DML are:

• Commands that initiate actions
• Functions that initiate actions and return a value
• Options to which you assign a value and that can influence the analytic workspace processing environment in various ways

OLAP DML commands, functions, and options are collectively referred to as commands. This guide introduces many of these commands. For the complete syntax for each command, usage notes, and examples, consult in the Oracle9i OLAP DML Reference help.

The purpose of the OLAP DML is to enable application developers to extend the analytical capabilities of querying languages such as SQL and the OLAP API.

To describe the purpose of the OLAP DML, it is important to discuss a few important concepts such as:

• Analytic workspaces
• The relationship of SQL to the OLAP DML
• The relationship of the OLAP API to the OLAP DML

Analytic Workspaces

An analytic workspace is a multidimensional data source. It may be temporary (that is, for the life of the session), or it may be persistent. When an analytic workspace is persisted, the data is stored as LOBs in relational tables.

The multidimensional model of the analytic workspace is designed to support rapid and advanced calculations. Analytic workspaces also provide an alternative to materialized views as a means of storing aggregate data.

An application can access data that resides in an analytic workspace in either of two ways. One way is through PL/SQL packages that are provided by Oracle for access to analytic workspace data. The other way is through the Oracle OLAP API, which is a Java application programming interface. Both the PL/SQL packages and the OLAP API provide ways to explicitly execute OLAP DML commands and programs.

SQL and the OLAP DML

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. Oracle provides PL/SQL packages that use table functions to create views of multidimensional data residing in an analytic workspace. SQL applications can access these views. Thus, the calculation engine and analytic workspace data 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).

In addition to using PL/SQL procedures for accessing analytic workspace data as SQL views, application programmers can use the Oracle OLAP packages to directly execute OLAP DML commands and return the results to their applications.

The OLAP API and the OLAP DML

Java programs using the OLAP API can access data stored either in SQL tables or in analytic workspaces. The OLAP API provides a wide variety of analytic functions that allow the application to derive calculated measures from the data.

In some cases, however, the OLAP API does not provide the means to calculate data needed by an application. Examples include forecasts, solving a model, some types of consolidations (aggregations), and allocations. In these cases, you can directly execute OLAP DML commands from within the OLAP API to calculate this data within an analytic workspace.