Using the OLAP DML to Work with Analytic Workspaces

Definition: Active analytic workspace

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".

Listing the active analytic workspaces

You can view a list of the active analytic workspaces by using the DATABASE command with the LIST keyword. For the complete syntax for the DATABASE command, see the OLAP DML Reference. The simplified syntax for this command is shown below.

database list

This command displays a list of the active analytic workspaces, along with their update status and full path name. The express.db analytic workspace, which is a system analytic workspace that contains objects used internally, always appears in the analytic workspace list.

The meaning of the update status, CHANGED or UNCHANGED, depends on whether the analytic workspace is attached with read/write or read-only access and whether the analytic workspace is being shared with other users.

Definition: 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 DATABASE command with the LIST keyword. By default, when you define new OLAP DML objects, they reside in the current analytic workspace, unless you specify the name of another active analytic workspace. Additionally, programs such as DBDESCRIBE list only the objects in the current analytic workspace.

Your session does not have to have a current analytic workspace. If you start OLAP Services without specifying an analytic workspace name, then the express.db analytic workspace is first on the list. However, the express.db analytic workspace is not current; there is no current analytic workspace until you specify one with the DATABASE command. Even though an active analytic workspace is not current, you can still change and update its data, edit and run its programs, and modify its analytic workspace definitions.

Retrieving the name of the current analytic workspace

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

Example: Retrieving the name of the current analytic workspace

Suppose that you have two analytic workspaces attached, one named programs.db and another named demo.db. The following commands use the DATABASE 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 = database(name)
show mytext
PROGRAMS

How to attach an analytic workspace

The system administrator can change OLAP Services configuration settings so that OLAP Services starts up with one or more application analytic workspaces already attached. To reconfigure OLAP Services, use the OLAP Services Instance Manager.

You can also use the DATABASE command to attach and detach analytic workspaces during a session. During your OLAP Services session, you can use the DATABASE command to switch freely between active analytic workspaces.

You can attach an analytic workspace by using the DATABASE command with or without the ATTACH keyword. As shown below, the action that is taken varies depending on whether or not you use the ATTACH keyword:

• If you attach an analytic workspace by using the DATABASE command with the ATTACH keyword, then the analytic workspace that you specify is automatically attached and made to be the current analytic workspace.

• If you attach an analytic workspace by using the DATABASE command with the ATTACH keyword, then different actions are taken, depending on whether or not there is a current analytic workspace:
  • If there is no current analytic workspace, then the new analytic workspace is attached and made current.

  • If there is a current analytic workspace, then it is first detached. The new analytic workspace is attached and made current.

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 DATABASE command as described in "Specifying the analytic workspace attachment mode".

Note: You can create programs that are automatically executed when you attach an analytic workspace. For more information, see "Programs that run when a user attaches to an analytic workspace".

Examples: Attaching an analytic workspace

The following example attaches an existing analytic workspace named finance.db and makes it the current analytic workspace. If another analytic workspace was current before this command executes, then that analytic workspace remains attached but is no longer current.

database attach finance

The following example attaches the finance.db analytic workspace and makes it current. The analytic workspace that was current is detached before this command was executed.

database finance

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 RW EXCLUSIVE keywords of the DATABASE 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.

For more information on sharing analytic workspaces across sessions and specifying the analytic workspace attachment mode, see "Sharing Analytic Workspaces".

Programs that run when a user attaches to an analytic workspace

When a user attaches to an analytic workspace, permission programs and AUTOGO programs will be run automatically if they exist:

1. Any permission programs that are associated with the analytic workspace will be automatically executed.

2. Any AUTOGO program associated with the analytic workspace or any program specified in the DATABASE ATTACH command will be executed.

Permission programs are, as the name suggests, programs that check the permission of the user. AUTOGO programs and programs specified in the DATABASE ATTACH command can contain any type of functionality. For more information on permission programs, see "Adding Security to an Analytic Workspace". For more information on other programs that run when a user attaches to an analytic workspace, see "Working with AUTOGO Programs".

Attaching multiple analytic workspaces

You can attach more than one analytic workspace at a time. However, when working with multiple analytic workspaces, keep the following points in mind:

• Oracle Corporation does not recommend that you give the same name to more than one analytic workspace. However, if there is more than one analytic workspace with the same name, then specify the full path name of the analytic workspace you want.

• If you are going to attach more than one analytic workspace, then you must take more care when you name objects. When you request an object by name, either with the DESCRIBE command or by referring to it in a command or program, all the active analytic workspaces are searched in order until the named object is found. When you intend to use several analytic workspaces together, do not give the same name to objects in different analytic workspaces. If you do, then you can create unanticipated interactions between the objects.

• If you have analytic workspace permission programs defined in analytic workspaces that are currently attached, then the one in the analytic workspace that you are attaching is executed. However, if you have analytic workspace permission programs in more than one currently attached analytic workspace, then you must take special care when you edit them, or use them in any other way, to ensure that you access the appropriate version.

Detaching analytic workspaces

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

database detach finance