Defining Relations

Definition: Relation

A relation is an OLAP DML 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 two relations, or a self-relation. Additionally, relations between dimensions in your analytic workspace that have time data types (DAY, WEEK, MONTH, QUARTER, or YEAR) are automatically defined.

How relations are dimensioned

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

Dimensioning with the larger 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.

Dimensioning with the fewer number of values

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. Dimension values vary in the order you list them in the definition, with the first value varying fastest and the last value varying slowest.

The data values that are stored for a relation are the indexes of the related 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.

STATE.CITY Relation
CITYPosition (Value)	C1 (Atlanta)	C2 (Chicago)	C3 (Springfield)
STATEPosition (Value)	S1 (Georgia)	S2 (Illinois)	S2 (Illinois)

Example: Relation between two dimensions

Most relations are a single-dimensional array that relates the values of one dimension with another. For example, as the figure below illustrates, 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's 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.

For information about using self-relations with hierarchical dimensions, see "Defining Hierarchical Dimensions and Variables That Use Them".

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
using self-relations with hierarchical dimensions,	"Defining Hierarchical Dimensions and Variables That Use Them".
adding values to relations,	"Assigning Values to Data Objects".
using relations in expressions,	"Using OLAP DML Objects in Expressions".