![]() Previous |
![]() Next |
The DEFINE command with the RELATION keyword adds a new relation object to an analytic workspace. A relation describes a correspondence between the values of two or more dimensions. It can have dimensions, just like a variable, but the values of the relation must be values from the related dimension.
Note: Defining a relation merely adds the definition of the relation to the analytic workspace; it does not populate the relation. To populate relations using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements. |
Syntax
DEFINE name RELATION related-dim [<dimensions...>] [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 relation.
Specifies the dimension to which one or more dimensions are related. A relation is normally used to store information about the relationship between two dimensions; for example, the cities that belong in each region.
In the definition, the dimension having fewer values is normally specified as the related dimension (for example, regions). The dimension having more values is normally specified as a dimension of the relation (for example, cities).
The names of the dimensions of the relation. You must enclose the names of the dimensions in a single set of angle brackets (< >
). You must define a dimension before including it in the definition of a relation. Do not include composites in the dimension list.
Restriction: Oracle OLAP does not support the use of composites as dimensions for relations. Do not attempt to define them. |
Tip: When defining two relations between the same dimensions, use the RELATION command to identify which relation is the default relation. |
Indicates that the values of the relation are only temporary. The relation is defined in the current workspace and can contain values during the current session. However, when you update and commit the workspace, only the definition of the relation is saved. When you end the session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary relation are NA
.
The name of an attached workspace in which you want to define the relation. The relation 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 the SESSION keyword differs from 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.
Examples
Creating, Populating, and Totaling by a Relation
The following example defines a relation between division
and product
, stores the values of the relation, and then totals units
by division
, even though units
is dimensioned by product
. The following statement defines the div.prod
relation.
DEFINE div.prod RELATION division <product>
The following statements store values of division
in div.prod
.
LIMIT product TO 'Tents' 'Canoes' div.prod = 'Camping' LIMIT product TO 'Racquets' div.prod = 'Sporting' LIMIT product TO 'Sportswear' 'Footwear' div.prod = 'Clothing'
You can use a REPORT statement to see the values stored in div.prod
.
report div.prod
This statement produces the following output.
PRODUCT DIV.PROD ------------- ---------- Tents Camping Canoes Camping Racquets Sporting Sportswear Clothing Footwear Clothing
The div.prod
relation lets you look at division totals in a report, even though the data is dimensioned by product
.
REPORT TOTAL(units division)