Using the OLAP DML

The following are some situations in which you might use the OLAP DML:

• When you need to calculate data that cannot be calculated as part of your data warehouse extraction, transformation, and load (ETL) process or by using the Java OLAP API.
• When your application needs to perform and persist various calculations, but you do not want to immediately commit this calculation in SQL tables.
• When you want to manipulate data that is stored in an analytic workspace.

The most common types of calculations that the OLAP DML is used for include:

• Forecasts
• Models (a group of calculations in which the results of one calculation are used as input to another calculation)
• Allocations (a "reverse aggregation" in which you distribute data to lower levels based on a particular distribution scheme)
• Some types of non-additive aggregations (consolidations), such as hierarchical weighted averages

In addition, the OLAP DML can be used when you want to perform calculations that are not easily accomplished in the ETL process or by using the OLAP API.

You can commit data to the analytic workspace without committing it to SQL tables. This is very useful for work in process. For example, you might have a forecasting application where you want to allow users to save personal forecasts and reuse them during a later session, but you do not want users to commit the forecast to the SQL tables.

How to Use the OLAP DML to Analyze Data

To use the OLAP DML, you:

1. Create an analytic workspace.
2. Define data objects within the analytic workspace.
3. Load data into these objects.
4. Define and execute OLAP DML commands and programs.

After you use the OLAP DML to analyze data, you can then:

• View data in an analytic workspace using the OLAP API or SQL.
• Write data to SQL tables.

Creating an Analytic Workspace

You can create an analytic workspace with a command such as the following:

AW CREATE salesforecast

This command creates a new and empty analytic workspace named salesforecast.

For more information about creating an analytic workspace, refer to Chapter 2, "Defining and Working with Analytic Workspaces".

Loading Data Into Analytic Workspaces

To use the OLAP DML, data must exist in the analytic workspace. Data can be loaded into an analytic workspace from SQL tables or from flat files. In most cases, tables within the database will be the data source. To load data into the analytic workspace, you use commands in the OLAP DML.

For more information about loading data into an analytic workspace, refer to Chapter 10, "Working with Relational Tables" and Chapter 11, "Reading Data from Files".

Temporary vs. Persistent Analytic Workspaces

Analytic workspaces can be either temporary or persistent, depending on your needs. If the analytic workspace is needed only to perform a specific calculation and the results of the calculation do not need to be persisted in the workspace, the workspace can be discarded at the end of the session. This might occur if, for example, your application needs to forecast a small amount of sales data. Since the forecast can be rerun at any time, there might not be any point in persisting the results.

Analytic workspaces can also be persisted across sessions. You might want to persist data in the analytic workspace if you have calculated a significant amount of data (for example, a large forecast or the results of solving a model), or if you have aggregated data using non-additive aggregation methods.

Sharing Data In Analytic Workspaces

Data in analytic workspaces may be shared by many different users. To share data in an analytic workspace, the workspace needs to be persisted during the period of time it is to be shared.

For example, if you want to allow a user to share the results of a forecast, you can allow the user to persist the analytic workspace. If another user attaches that workspace during their application session, they can be allowed to see the other user's forecast.