|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Basic Concepts, 4 of 5
To use the OLAP DML, you:
After you have used the OLAP DML to analyze data from a table, you can then:
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:
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.
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.
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.
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.
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.
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.
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.
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:
Two common examples of situations where you might want to commit data to the relational database include: