Using the OLAP DML to Work with Analytic Workspaces

To make the data and the object definitions of an analytic workspace available to your session, the analytic workspace must be attached. Analytic workspaces that are currently attached are known as active analytic workspaces. Attaching analytic workspaces is described in "How to Attach an Analytic Workspace".

You can view a list of the active analytic workspaces by using the AW command with the LIST keyword.

AW LIST

This command displays a list of the active analytic workspaces. The express analytic workspace, which is a system analytic workspace that contains objects used internally, always appears in the analytic workspace list.

Current Analytic Workspace

The current analytic workspace is the first analytic workspace in the list of the active analytic workspaces that you view with the AW command with the LIST keyword. By default, when you define new workspace objects, they reside in the current analytic workspace, unless you specify the name of another active analytic workspace. Additionally, programs such as LISTNAMES list only the objects in the current analytic workspace. However, even when an active analytic workspace is not current, you can still change and update its data, edit and run its programs, and modify its object definitions.

Your session does not have to have a current analytic workspace. If you start Oracle OLAP without specifying an analytic workspace name, then the express analytic workspace is first on the list. However, the express analytic workspace is not current; there is no current analytic workspace until you specify one with the AW command.

You can retrieve the name of the current analytic workspace by using the AW function with the NAME keyword.

Suppose that you have two analytic workspaces attached, one named marketing and another named personnel. The following commands use the AW function with the NAME keyword to retrieve the name of the current analytic workspace into a variable named MYTEXT, and then display the value of MYTEXT. This value is shown after the commands.

mytext = AW(NAME)
SHOW mytext

PERSONNEL

How to Create An Analytic Workspace

The AW command is used to create a new analytic workspace. The following example creates an analytic workspace named finance.

AW CREATE finance

When you create an analytic workspace, Oracle OLAP automatically executes a COMMIT command.

You are the only user who has access to a workspace that you have just created. If you want others to use the workspace, you must give them access to the relational table in which the workspace is stored. The name of the table is AW$ followed by the workspace name that you specified in your AW CREATE command.

To give read access to another user, execute a command like the following one in SQL. In this example, the workspace name is demo and the user's name is scott.

GRANT SELECT ON aw$demo TO scott

To give write access to another user, execute a SQL command like the following one.

GRANT UPDATE ON aw$demo TO scott

As in any SQL GRANT command, you can specify a group or role instead of a user.

How to Attach an Analytic Workspace

You can use the AW command to attach and detach analytic workspaces during a session. In addition, as you work in your session, you can use the AW command to switch freely among active analytic workspaces.

You attach an analytic workspace by using the AW command with the ATTACH keyword. The analytic workspace that you specify is automatically attached and made to be the current analytic workspace. The following example attaches an existing analytic workspace named finance and makes it the current analytic workspace. Previously attached workspaces move down the list of attached workspaces to make room for the new one at the top of the list.

AW ATTACH finance

When you attach an analytic workspace, the default access to it is read-only. If you want a different attachment mode, then you must explicitly specify it in the AW command as described in "Specifying the Analytic Workspace Attachment Mode".

Specifying the Analytic Workspace Attachment Mode

You can specify whether you want the analytic workspace attached in read-only mode, read/write nonexclusive mode, or read/write exclusive mode by using the RO, RW, and RX keywords of the AW command.

An analytic workspace that is attached in read/write nonexclusive mode or read-only mode can be accessed simultaneously by several sessions. However, only one session can have the analytic workspace open with read/write access. If another user has already attached an analytic workspace in read/write mode, then you cannot attach the same analytic workspace in read/write mode until that other user detaches it.

An analytic workspace that is attached in read/write exclusive mode cannot be accessed by any other session. If other users have already attached an analytic workspace, then you cannot attach the same analytic workspace in read/write exclusive mode until all of the other users detach it.

Sharing Analytic Workspaces

An analytic workspace can be accessed simultaneously by several sessions, assuming that the session users have been granted access by the creator of the workspace. Many sessions can access a workspace, but only one session can have it open with read/write access at any given time.

When you attach an analytic workspace, your default access to it is read only. Oracle OLAP supports simultaneous access for one writer and many readers of an analytic workspace. Provided your user ID has the appropriate access rights, you can always get read-only access to an analytic workspace, no matter how many other users are using it. If another user has read/write access and commits changes to the analytic workspace, then your view of the analytic workspace does not change; you must detach and reattach the analytic workspace to see the changes.

If you want read/write access, then you must explicitly specify it in the AW command. If the analytic workspace is attached in read/write mode by another session, the response to your request for access depends on the keywords used in AW command.

You can specify whether or not you want to wait until an analytic workspace is available for the type of access you are you are requesting by using the WAIT and NOWAIT keywords of the AW command.

• If you specify the NOWAIT keyword (the default) and if the analytic workspace is not available for the type of access you are requesting, then an error message is produced that indicates that the analytic workspace is unavailable.
• If you specify the WAIT keyword and the analytic workspace is not available for the type of access you are requesting, then Oracle OLAP places you on the wait list for the analytic workspace.

How to Detach an Analytic Workspace

To detach an analytic workspace, you use the AW command with the DETACH keyword. The following command detaches the finance analytic workspace.

AW DETACH finance

A detached analytic workspace remains in the database. However, it is no longer accessible in your session. To access it again, use the AW command with the ATTACH keyword.

How to Delete an Analytic Workspace

To delete an analytic workspace from the database, you use the AW command with the DELETE keyword. Before deleting, you must detach the analytic workspace. The following commands delete the finance analytic workspace.

AW DETACH finance
AW DELETE finance

A deleted analytic workspace is no longer in the database; you can never access it again. When you delete an analytic workspace from the database, Oracle OLAP automatically executes a COMMIT command.

Workspace Localization Settings

Oracle supports locales that vary in their character sets, date formats, currency symbols, and other language-specific characteristics. Oracle globalization support is based on the value of parameters that begin with "NLS." For information about NLS parameters, see the Oracle9i SQL Reference and the Oracle9i Database Globalization Support Guide.

Within a session you can dynamically modify the value of some NLS parameters by setting them using the OLAP DML options that begin with "NLS." For example, you can set the value of NLS_LANG or NLS_TERRITORY in the OLAP DML. When you set the value of an OLAP DML NLS option, the setting affects your entire database session. It is not limited to your work in an analytic workspace.

Alternatively, you can use the following SQL command to change an NLS parameter for your entire session, including Oracle OLAP.

ALTER SESSION SET parameter = value

For more information about the OLAP DML NLS options, see the Oracle9i OLAP DML Reference help.