Oracle9i OLAP Developer's Guide to the OLAP DML Release 2 (9.2) Part Number A95298-01 |
|
Defining Data Objects, 6 of 11
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).
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.
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 |
See Also:
|
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
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.
See Also:
|
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|