Saving Analytic Workspace Changes

Typically, you want to save an analytic workspace at the end of your session to save changes that were made during the session. You can also save an analytic workspace periodically during a session to save changes as you go along.

If you have read/write access to the analytic workspace, then you can save the changes you have made. If you have read-only access to the analytic workspace, then you can make changes to the analytic workspace, but you cannot save these changes.

Two commands are used together to save changes to an analytic workspace: UPDATE and COMMIT.

UPDATE Command

The UPDATE command moves analytic workspace changes from a temporary area to the database table in which the workspace is stored. Your changes are not saved until you execute a COMMIT command, either from the OLAP DML or from SQL.

If you want changes that you have made in a workspace to be committed when you execute a COMMIT command, then you must first update the workspace using the UPDATE command. Changes that have not been moved to the table are not committed.

The simplified syntax for the UPDATE command is show below.

UPDATE [awname1 [awname2 . . .]]

An awname argument specifies the name of a read/write analytic workspace that is attached to your session. If you do not specify any analytic workspace names, then all the attached read/write analytic workspaces are updated.

For example, you can issue the following command to move changes to all attached analytic workspace from a temporary area to the database tables in which the workspaces are stored.

UPDATE

COMMIT Command

The COMMIT command executes a SQL COMMIT command. All changes made during your session are committed, whether they were made through Oracle OLAP or through another form of access (such as SQL) to the database.

For example, you can issue the following two commands to save all analytic workspace changes in the database.

UPDATE
COMMIT

Many users execute DML commands using SQL*Plus or OLAP Worksheet. Both of these tools automatically execute a COMMIT command when you end your session. However, you must first execute an UPDATE command in order to save your analytic workspace changes.

If you have attached a shared analytic workspace and another user has read/write access, then that user's COMMIT command does not affect your view of the analytic workspace. Your view of the data remains the same as when you attached the analytic workspace. If you want access to the changes, then you must detach the analytic workspace and reattach it.

Effect of the ROLLBACK Command

The OLAP DML does not provide a way to issue a ROLLBACK command; however, you could execute one in your session from outside Oracle OLAP (for example, through PL/SQL). When a ROLLBACK command is executed in your session, Oracle OLAP checks to see whether there are uncommitted updates in an attached workspace.

• If there are uncommitted updates (that is, you have made changes and executed an UPDATE command, but you have not subsequently executed a COMMIT command), then Oracle OLAP discards your changes and detaches the workspace.
• If you have no uncommitted updates, then Oracle OLAP takes no action in response to the ROLLBACK command. This means that, if you have not issued an UPDATE command since your last COMMIT command, Oracle OLAP takes no action and all your changes remain in the workspace during your session.

If you rollback to a savepoint and there are uncommitted updates that occurred subsequent to the savepoint, Oracle OLAP discards those updates and detaches the workspace. Uncommitted updates that occurred before the savepoint remain in the workspace, and you can see them if you reattach the workspace in the same session.

Minimizing Analytic Workspace Growth

You can minimize analytic workspace growth by frequently updating the analytic workspace when you are attached exclusively. You can reorganize your analytic workspace files by exporting all of the objects in your analytic workspace and then importing them into a new analytic workspace. The new workspace may be substantially smaller.

To reorganize your analytic workspace by exporting and importing workspace objects, follow the procedure outlined below.

1. Issue an ALLSTAT command against the original analytic workspace.
2. Use the EXPORT command with the ALL keyword to put all of the data in the original analytic workspace into an EIF file.
3. Create a new analytic workspace with a different name than the original analytic workspace.
4. Use the IMPORT command to import the EIF file into the new analytic workspace.
5. Use the UPDATE and COMMIT commands to save the new analytic workspace.
6. After checking that the objects were successfully moved into the new analytic workspace, delete the original analytic workspace.

If you have programs that reference a given workspace, they can refer to the workspace by an alias. This way, it does not matter how many times you import to a workspace with a different name. The alias can be assigned to the appropriate workspace each time.