![]() Previous |
![]() Next |
The DEFINE command with the WORKSHEET keyword adds a new worksheet object to an analytic workspace. A worksheet, like a spreadsheet, is a two-dimensional object that is dimensioned by a worksheet row and a worksheet column. It can temporarily store data that you want to transfer between spreadsheet packages and workspace dimensions and variables.
When you first define a worksheet, it does not contain any values. You can populate a worksheet with values from an existing spreadsheet by using an IMPORT (spreadsheet) statement or add or delete values from worksheet row and a worksheet column dimensions with a MAINTAIN statement.
Syntax
DEFINE name WORKSHEET [<column-dim row-dim>] [TEMP] [AW workspace] [SESSION]
Parameters
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a worksheet.
The names of the dimensions of the worksheet. When you supply this argument, you must give the names of two INTEGER
dimensions for column-dim and row-dim. When you omit this argument, the worksheet is dimensioned automatically by WKSCOL
and WKSROW
. See "Worksheet Dimensions" for more information
Indicates that the worksheet is only temporary. The worksheet is defined in the specified workspace and can contain values during the current session. However, when you update and commit, only the definition of the worksheet is saved. When you end your session or switch to another workspace, the data values are discarded.
The name of an attached workspace in which you want to define the worksheet. The worksheet must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. The behavior specified by SESSION is different than the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.
Usage Notes
Worksheet Dimensions
A worksheet must always dimensioned by two dimensions that represent a worksheet row and a worksheet column. The worksheet row and a worksheet column dimensions can either be automatically created by Oracle OLAP or explicitly created by you:
When you have not created worksheet row and a worksheet column dimensions and specified their names in the column-dim and row-dimt arguments of DEFINE WORKSHEET, Oracle OLAP automatically creates the following dimensions:
For the worksheet row, an INTEGER dimension named WKSROW
with values from 1 to 63.
For the worksheet column, an INTEGER dimension named WKSROW
with values from 1 to 63.
Note: WhenWKSCOL and WKSROW already exist in any attached workspace, Oracle OLAP cannot create them in the current worksheet. In this case, the DEFINE WORKSHEET command fails to create a worksheet with these default dimensions. (Note, also, that WKSCOL and WKSROW do not appear in a worksheet description generated using DESCRIBE.) |
You create worksheet row and a worksheet column dimensions the same way you create any other simple dimension by issuing the following statements:
Create two simple INTEGER dimensions using a DEFINE DIMENSION (simple) statement. One dimension is for row numbers and the other for column numbers.
Using MAINTAIN statements, populate one dimension with integers that represent row numbers and populate with integers that represent column numbers.
Examples
Defining a Worksheet
These statements define a temporary worksheet named travelexp
, which is dimensioned by columns
and rows
.
DEFINE itemsheet WORKSHEET DEFINE columns INT DIMENSION MAINTAIN columns ADD 5 DEFINE rows INT DIMENSION MAINTAIN rows ADD 10 DEFINE travelexp WORKSHEET <columns rows> TEMPORARY
Importing Spreadsheet Data
You can import data from a spreadsheet to a worksheet. When all the cells contain the same type of data, you can use UNRAVEL to transfer the data to a variable with one statement. You can also limit the worksheet dimensions to a smaller group of cells and use UNRAVEL to transfer each group to a separate variable. To transfer imported data from a worksheet named itemsheet
to a variable named items
, you might use the following statements.
DEFINE itemsheet WORKSHEET IMPORT itemsheet FROM dif FILE 'file name' LIMIT WKSCOL TO FIRST 3 LIMIT WKSROW TO FIRST 10 items = UNRAVEL(itemsheet)