Limiting to the Values of a Related Dimension

Overview: Limiting to the values of related dimensions

You can use the LIMIT command to limit a dimension to the values of one or more related dimensions. The simplified syntax for using the LIMIT command in this way is shown below:

LIMIT dimension TO reldim [reldim-val]

The reldim argument is the name of a relation or a dimension that is related to the dimension being limited. Using a relation name allows you to choose which relation is used when there is more than one.

The reldim-val argument is a list of values of the related dimension, and not the dimension being limited. If this argument is present in a LIMIT command, then status is obtained by selecting the values of the dimension being limited, which are related to the related-dimension values. If valuelist is omitted, then the current status of related-dimension is used.

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

Example: Limiting with a related dimension

The following command limits DISTRICT to BOSTON and ATLANTA, which are in the EAST region.

limit district to region 'EAST'

This command limits PRODUCT to SPORTSWEAR and FOOTWEAR, which are in the division that appears last in the list of DIVISION values.

limit product to division last 1

How status is determined when you limit to a related dimension

When you limit a dimension to a related dimension, the current status list is created in a two-step process, as shown in the following table.

1. The values in the dimension's current status list are arranged in the order of the values of the related dimension.

2. If there is more than one value of the dimension for any value of the related dimension, then the values in the dimension's current status list are arranged in the order of their default status list.

Suppressing the sort when you limit to a related dimension

The LIMIT.SORTREL option controls whether or not a sort is done when you limit a dimension to a related dimension. You can suppress the sort that occurs when you limit a dimension to a related dimension by setting LIMIT.SORTREL to NO. This can significantly improve performance when the dimension you are limiting is large.

Note: When LIMIT.SORTREL is NO, printed output of a dimension may not appear in logical order.

Limiting using related time dimensions

Every time dimension (with a data type of DAY, WEEK, MONTH, QUARTER, or YEAR) is related to every other time dimension through an implicit relation. When you limit the values of a time dimension by specifying another time dimension as the related dimension, the implicit relation is used by default.

For example, you can issue the following command.

limit month to quarter year

This command will temporarily limit QUARTER to YEAR, then limit MONTH to QUARTER, and finally restore QUARTER to its original status.

However, if an explicit relation is defined between the two time dimensions, then you can override the default by specifying the name of the explicit relation as the related dimension.

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
limiting dimensions,	the rest of this chapter the entry for the LIMIT command in OLAP DML Reference
working with values in time dimensions,	"Defining Dimensions" "OLAP DML Data Types"
sorting the current status list,	the entry for the SORT command in OLAP DML Reference