How Do I Use the OLAP DML to Analyze Data?

Procedure: Using the OLAP DML

To use the OLAP DML, you:

1. Create an analytic workspace (see "Creating an analytic workspace").

2. Define data sources within the analytic workspace (see "Defining data sources within the analytic workspace").

3. Load data into the analytic workspace (see "Loading data into analytic workspaces").

4. Define and execute the OLAP DML commands and programs (see "Executing OLAP DML commands and functions").

What can I do with the data after I have analyzed it?

After you have used the OLAP DML to analyze data from a table, you can then:

• View data in an analytic workspace using the OLAP API

• Can commit data to the data warehouse

Creating an analytic workspace

Creating an analytic workspace is a very simple process and is accomplished using a command in the OLAP DML. An example of this command follows:

DATABASE CREATE \DATA\SALESFORECAST

The above command will create a new and empty analytic workspace named SALESFORECAST in the \DATA directory.

This would be similar to creating a new tablespace and data file in the relational database. That is, the physical file is created, but there are no objects stored in the file yet.

For more information about creating an analytic workspace, refer to Chapter 2.

Defining data sources within the analytic workspace

Within an analytic workspace, you create workspace objects and OLAP API data sources. Examples of workspace objects include variables, formulas, and dimensions. These are the basic building blocks within an analytic workspace.

These workspace objects can be augmented with analytic workspace metadata to expose data to the OLAP API.

For more information about creating data sources, refer to Chapter 3. To learn how to create and use analytic workspace metadata, refer to Appendix A.

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 by fetching it from the relational database or by loading it using OLAP Services' file reader. In most cases, the relational database will be the data source.

Data is loaded into the analytic workspace using commands in the OLAP DML. There are commands for fetching data from relational tables and commands for reading flat files. When loading data from the relational database, the data can be from any table (that is, it does not need to be part of a data warehouse).

For more information about loading data into an analytic workspace, refer to Chapter 10.

Executing OLAP DML commands and functions

The OLAP DML consists of various commands and functions. Commands create, delete, and modify objects, call programs, fetch and load data, and perform other needed tasks. Functions typically manipulate data (for example, return the TOTAL of SALES).

You can call individual functions and you can define and execute formulas and programs. Whether you use functions, formulas, or programs will depend on what you are trying to accomplish.

OLAP Services applications call or executes commands and functions using the SPL EXECUTE method in the OLAP API. The SPL EXECUTE method allows the OLAP API to pass OLAP DML commands to the multidimensional engine that processes the OLAP DML.

The OLAP Worksheet application allows you to work interactively with the OLAP DML (much the same way that SQL Plus allows you to interact with the relational database by typing SQL statements). Using the OLAP Worksheet, you can execute most OLAP DML commands. You can also define workspace objects, and edit programs and formulas.

You might, for example, use the OLAP Worksheet to define a OLAP DML program that loads data from the relational database and forecasts sales. Your application would then call execute the OLAP DML program through the OLAP API using the SPL EXECUTE method.

For more information about using OLAP DML commands, functions, and programs, refer to Chapter 4, Chapter 5, Chapter 6, and Chapter 7.

Viewing data in an analytic workspace

Any data in an analytic workspace that has been exposed as an OLAP API data source with analytic workspace metadata will automatically become available through the OLAP API. To the OLAP API client, there is no difference between a relational database data source and an analytic workspace data source. The OLAP API reveals all data sources to the application without requiring the application to understand the data's physical storage.

Temporary vs. persistent analytic workspaces

Analytic workspaces may 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 does 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 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.

Committing data to the data warehouse

Data created within an analytic workspace can be committed to the relational database using the OLAP DML. Data can be committed to any table, regardless of whether that table is part of a data warehouse.

The are two primary reasons why you might want to commit data in an analytic workspace to the relational database:

• You want to use the relational database for long-term persistent storage. This is advisable because the relational database has more robust disaster recovery features as compared to analytic workspaces.

• You want to make the results of calculations made within an analytic workspace available to SQL-based applications (that is, applications that use SQL rather than the OLAP API).

Two common examples of situations where you might want to commit data to the relational database include:

• Sharing the results of a final forecast with users of SQL-based applications.

• Managing the long persistence of data resulting from specialized aggregations in the relational database.