Working with Valuesets

Definition: Valueset

A valueset is an OLAP DML object that contains a list of dimension values for a particular dimension. You use a valueset to save a dimension status list for later use. The values in a valueset can be saved across OLAP Services sessions. When you attach an analytic workspace, each dimension has all of the values in the default status list. You can then limit a dimension to the values stored in the valueset for that dimension. When you first define a valueset, its value is null. After defining a valueset, you use the LIMIT command to assign values from the dimension to the valueset. You can use the LIMIT command with valuesets in many of the ways that you use it with dimensions. For example, you can use the LIMIT command to expand, reduce, and replace values in the list of values of a valueset.

Creating a valueset

To create a valueset, take the following steps.

1. Define a valueset for the dimension values. Use the DEFINE command with the VALUESET keyword.

2. Limit the dimension for which you want to create a valueset to the values you want to save.

3. Limit the valueset you created in Step 1 to the dimension you limited in Step 2.

Example: Creating a valueset

This example adds the valueset LINESET to the demonstration analytic workspace. It is dimensioned by LINE and, therefore, it can be limited by the current values of the LINE dimension. The LD command attaches a description to the object.

The following OLAP DML commands produce the output shown below them.

limit line to first 2
status line
The current status of LINE is:
REVENUE, COGS

The following OLAP DML commands produce the output shown below them.

define lineset valueset line
ld Valueset for LINE dimension values
limit lineset to line
show values(lineset)
REVENUE
COGS

Limiting using a valueset

Once you have defined a valueset, you can use it to limit a dimension with a single LIMIT command.

For example, the following command limits the LINE dimension to the values stored in the LINESET valueset and displays the new status of LINE.

limit line to lineset
status line
The current status of LINE is:
REVENUE, COGS

Example: Limiting using a valueset

The following commands limit DISTRICT to the districts in which sportswear sales exceeded $1,000,000 in 1996. The current status list for the DISTRICT dimension is saved in the valueset SPORTS.DISTRICT. Once you have created the valueset, you can limit the DISTRICT dimension to the same values with one LIMIT command.

define sports.district valueset district
limit product to 'SPORTSWEAR'
limit month to year 'YR96'
limit sports.district to total(sales district) gt 1000000
limit district to sports.district

The following OLAP DML command produce the output shown below it.

status district
The current status of DISTRICT is:
ATLANTA TO DENVER

Changing the values of a valueset

You can use the LIMIT command to change the values in a valueset. The simplified syntax for using the LIMIT command in this way is shown below:

LIMIT valueset keyword selection

The valueset argument specifies the name of the valueset you want to change.

The keyword that you specify determines how the command affects the values that are currently in the valueset. The following table outlines the use of the keywords.

IF you want to . . .	THEN use the LIMIT command with . . .
replace the values that are currently in the valueset with new values,	either the TO or COMPLEMENT keyword.
remove values from the current valueset,	either the REMOVE or KEEP keyword.
expand the valueset,	either the ADD or INSERT keyword.
sort the values in the valueset,	the SORT keyword.

The selection argument specifies the selection criteria that you want to be used to determine what values to assign to the valueset. In general, you can use the same arguments when you are using the LIMIT command to select values for a valueset that you can use when you use the LIMIT command to limit a dimension.

For the complete syntax of the LIMIT command, see the entry for the command in OLAP DML Reference.

Identifying and retrieving the values in a valueset

You can use the following commands and functions to identify and retrieve dimension values that are in a valueset.

Command or function	Description
INSTAT function	Checks whether a dimension value is in a valueset.
STATFIRST function	Retrieves the first value in a valueset.
STATLAST function	Retrieves the last value in a valueset.
STATUS command	Sends to the current outfile the status of one or more values in a valueset.
VALUES function	Retrieves the values in a valueset. Depending on whether you specify the INTEGER keyword, the function either returns a multiline text value that contains one dimension value per line or returns, as integers, the position numbers of the values in the existing dimension, not in the valueset.

For more information on these commands and functions, see the entry for the command or function in OLAP DML Reference.

Retrieving the values in a valueset

Suppose an analytic workspace contains a valueset called MONTHSET that has the values JAN95, MAY95, and DEC95. You can use the VALUES function to list the values in that valueset.

The following OLAP DML command produces the output shown below it.

show values(monthset)
JAN95
MAY95
DEC95

Retrieving the dimension positions of values in a valueset

Suppose that you want to retrieve the position of the values in the MONTHSET valueset, rather than retrieve the actual values themselves. To retrieve the position of values, you use the VALUES function with the INTEGER keyword. When you use this keyword, the position numbers are returned instead of the actual dimension values that are included in a valueset. The position numbers that are returned do not represent positions in the valueset; they represent positions in the dimension on which the valueset is based.

The following OLAP DML command produces the output shown below it.

show values(monthset integer)
61
65
72

The value JAN95 is shown as the sixty-first value in the MONTH dimension, MAY95 as the sixty-fifth value, and DEC95 as the seventy-second value, although they are the first, second, and third values in MONTHSET.