Accessing a Workspace from OLAP Worksheet

OLAP Worksheet is an interactive interface to Oracle OLAP that you can use to perform the following tasks:

• Connect to an analytic workspace
• Execute most OLAP DML commands
• Create and populate data objects
• Create, modify, compile, and execute DML programs
• Execute SQL statements

OLAP Worksheet has a Command Input window and a program Edit window.

You can enter commands in the query (input) pane at the bottom of the command input window and see results in the response (output) pane at the top.

Once you have opened OLAP Worksheet, you can use it to establish a connection to Oracle OLAP, open a workspace, execute OLAP DML commands or write and debug programs, save any changes, close the workspace, and close the connection.

Procedures: How to Open OLAP Worksheet

You can open OLAP Worksheet either from Oracle Enterprise Manager or from the operating system command line.

To open OLAP Worksheet from Oracle Enterprise Manager, take these steps:

1. Open Oracle Enterprise Manager and open a connection to your database.
2. Expand the database folder.
3. Right click on OLAP to see a menu, then choose OLAP Worksheet.

   
   

   Tip: If you are unable to start OLAP Worksheet, then check system variables HOMEDRIVE and HOMEPATH. They do not need to be defined, but if they are, then they must be set to valid values.

On Unix, to open OLAP Worksheet from the command line, take these steps:

1. Using a command-line interface, go to the bin subdirectory of the OLAP Worksheet installation directory.
2. Run the runapp.sh script.

On Windows, click the OLAP Worksheet icon that was created during installation.

Establishing a Connection

Take these steps to establish a connection to Oracle OLAP:

1. From the OLAP Worksheet menu bar, choose Server.
2. Choose Connect.

   You see the Login to Database box.

3. Enter valid database user credentials and connection information in the Login dialog box that appears.

   In the Service box, type the identification of the Oracle database, in the following format:

   host:port:SID
   
   

   For example, mycomputer:1521:rel9i.

Oracle controls your access to data on the basis of your database user ID. Your user ID must have access rights to the analytic workspaces and relational tables that you want to use in OLAP Worksheet, or you will get an error when you try to access them.

Executing Commands

You can execute OLAP DML commands and SQL statements in the Command Input window of OLAP Worksheet.

By selecting different options in the Options menu, you can specify whether you want to execute OLAP DML commands or SQL statements. You can also specify whether you want commands executed individually or saved in a buffer and executed together.

• To execute OLAP DML commands, select SQL Off from the Options menu. To execute SQL statements, select SQL On.
• To execute commands as soon as you press Enter, select Execute on Enter from the Options menu.

  Or, to save the commands in a buffer, clear Execute on Enter. Then, to execute all of the commands that you have entered in the query pane, choose Execute from the View menu.

Be sure to locate your cursor in the query pane before you start to type. If you want to break a long command into several lines, you can continue the command on the next line by typing a continuation character (-) at the end of the current line.

When the SQL option is ON, just type in the SQL statements and press Enter. Do not terminate SQL statements with a semicolon. If you do, you will get an error.

Editing an OLAP DML Program

You can open a DML program in an Edit window so that you can add or modify program content. You can have multiple Edit windows open simultaneously, but an object definition can appear in only one Edit window at a time.

In addition to using the Edit window to work on a program, you can use it to edit a model or an aggregation map.

To edit a program, follow these steps:

1. In the input pane of the Command Input window, type

   edit object_name 
   
   

   Where object_name is the name of a DML program object that already exists. Use the DEFINE command to create a new program object. If you want to edit a model or an aggregation map, type MODEL or AGGMAP before the object name.

2. In the Edit window, you can add, modify, or delete program content.
3. To save your changes, choose Save from the File menu. Note that this choice executes and UPDATE command, which updates all the changes that have been made in the analytic workspace up to this point.
4. To close the Edit window, choose Quit from the File menu.

   
   

   See Also: Chapter 7, "Developing Programs" for more information about DML programs.

Closing the Connection

Use the following procedure to close a connection to Oracle OLAP:

1. In the OLAP Worksheet menu bar, choose Server.
2. Choose Disconnect.
3. When prompted to disconnect, choose Yes.

When you disconnect, OLAP Worksheet executes a COMMIT command before ending your session. If you have executed the UPDATE command or chosen Save from the File menu of an Edit window before disconnecting, then the changes that you made before the update are made permanent. Otherwise, they are discarded. Any changes that you have made after the update are discarded when you disconnect.