Defining Relations

A relation is an object that establishes a correspondence between the values of a given dimension and the values of that same dimension or other dimensions in the analytic workspace. The structure of a relation is similar to that of a variable. However, the cells in relations do not hold actual data values; instead, each cell in a relation holds the index of the value of a dimension.

By creating a relation between two dimensions that participate in a one-to-many (parent-to-child) relationship, you can organize your data by the child dimension and view aggregates of data by the parent dimension. For example, if you define store and district dimensions and a relation between them, then you can organize data by store and view aggregates of data by district.

You can explicitly define relations between two or more dimensions, multiple relations between a set of dimensions, or a dimension with itself (a self-relation).

How Relations Are Dimensioned

Relations are dimensioned arrays. Relations can be dimensioned by the dimension with the larger number of values or the smaller number of values.

Typically, a relation is dimensioned by the dimension with the larger number of values (that is, the less aggregate or child dimension) and the related dimension is the dimension with fewer values (that is, the more aggregate or parent dimension). For example, you can create a relation called state.city to associate each city with the state that it is in. The relationship is dimensioned by city and the related dimension is state. You assign a state to each city.

Less typically, a relation is dimensioned by the dimension with fewer values (the more aggregate dimension or parent dimension). In this case, not every value of the other dimension is related. For example, you could create a relationship, named city.state, between states and their capital cities. The relation is dimensioned by state and the related dimension is city. Only the capital cities are assigned to a state.

How Relation Data Is Stored

The order in which you define the dimensions of a relation determines how its data is stored and accessed. Dimensions vary in the order you list them in the definition, with the first dimension varying fastest and the last dimension varying slowest. See "How Variable Data Is Stored" for information on faster- and slower-varying dimensions.

The data values that are stored for a relation are the indexes of the related dimension. The index is the position of the value in the dimension.

For example, the state.city relation (that is dimensioned by city and has a related dimension of state) assigns a state to each city. To implement this relationship, an index from the state dimension is stored for every value (index) in the city dimension. The following table shows the positions of the city dimension that are assigned to each position of the state dimension. It also shows the values at those positions in the dimensions.

City Position (Index)	City Value at Position	State Position (Index)	State Value at Position
1	Atlanta	1	Georgia
2	Chicago	2	Illinois
3	Springfield	2	Illinois

Example: Relation Between Two Dimensions

Most relations are a single-dimensional array that relates the values of one dimension with another. For example, you can define two simple dimensions, state and city, and a relation state.city between them to associate each city with the state that it is in.

Assume that the state.city relation was defined using the following command.

DEFINE state.city RELATION state <city>

Assume that, as shown below, the state dimension has two values and the city dimensions has three values.

STATE
--------------
GEORGIA
ILLINOIS

CITY
--------------
ATLANTA
CHICAGO
SPRINGFIELD

The state.city relation is dimensioned by city and the related dimension is state. The state.city relation assigns a state to each city as shown below.

CITY           STATE.CITY
-------------- ---------------
ATLANTA        GEORGIA
CHICAGO        ILLINOIS
SPRINGFIELD    ILLINOIS

Example: Self-relation

You can define a self-relation for a single dimension. For example, to keep track of the reporting structure of a company, you can have the emp.emp relation for the employee dimension.

Assume that the emp.emp relation was defined using the following command.

DEFINE emp.emp RELATION employee <employee>

Assume that the employee dimension contains the values shown below.

EMPLOYEE       
-------------- 
ANN LOGAN      
MICHAEL ARON   
LUCY BATES     
RALPH BURNS    

The self-relation emp.emp is dimensioned by the employee dimension and the related dimension is also the employee dimension. As shown below, the emp.emp relation assigns a manager to each employee.

EMPLOYEE        EMP.EMP
-------------- ----------
ANN LOGAN      NA
MICHAEL ARON   ANN LOGAN
LUCY BATES     ANN LOGAN
RALPH BURNS    LUCY BATES

In this example, Ann Logan, the company president, does not report to anyone; employees Lucy Bates and Michael Aron report directly to Ann Logan, the president; and employee Ralph Burns reports to employee Lucy Bates.