Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Defining Data Objects, 4 of 9
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.
All relations are dimensioned arrays. Relations can be dimensioned by the dimension with the larger number of values or the fewer 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. 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 |
|||
---|---|---|---|
(Value) |
( |
( |
( |
(Value) |
( |
( |
( |
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
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".
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, |
|
using relations in expressions, |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|