Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Part Number B28126-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

About OLAP DML Data Objects

A relational database typically stores data values in tables that represent third normal form data. In this type of implementation, the values of key columns of a relational database table are unique values of a single level of data. For example, at one level in the relational database you might have a table with a key column named City that contains the names of cities and at the next highest level in the database a table with a key column named state that contains the names of states, and so on and so on.

In an analytic workspace the objects that hold the data that you want to analyze are arrays called variables. The keys into variables are stored in other objects which act as the dimensions of the variables. To support performant OLAP analysis, values from multiple levels are stored within a single dimension called a hierarchical dimension. For example, an analytic workspace might have a hierarchical dimension named geog that had as values the names of both cities and states.

The objects that store values that relate values of two or more dimensions are called relations. Thus the one-to-many relationship between values of different levels in a hierarchical dimension are stored in an analytic workspace. For example, the relationship between the city and state values in a hierarchical geog dimension would be stored in an analytic workspace relation typically called a parentrel relation. (See "Parentrel Relation" for more information.)

Additional analytic workspace objects are typically defined to keep additional information about the hierarchical dimension. Several important OLAP DML commands and functions (such as the LIMIT command) presume the existences of these objects in your analytic workspace as the name of these objects is one argument in the syntax of the statement.

Types of OLAP DML Data Objects

Introduction

Variables

The most important data object is the variable. A variable is an object that stores data. All of the data in a variable must have the same data type. Typically, you use variables to contain data values that quantify a particular aspect of your business For example, your business might have several categories of transactions (measured in dollars, units, percentages, and so on) and each category is stored in its own variable. For example, you might record sales data in dollars (a sales variable) and units (a units variable).

Since the OLAP DML is a multidimensional programming language, variables are multidimensional and correspond to what other OLAP languages sometimes call measures. Conceptually, you can think of a variable with two dimensions as a table, a variable with three dimensions as a cube, and so on. Physically, variables are stored as multidimensional arrays with the actual structure of the arrays determined by the object by which the variable is dimensioned.

The scope and permanence of a variable can vary. A permanent variable is a variable for which both the variable values and definitions are stored in an analytic workspace. Temporary variables have values only during the current session. When you update and commit the analytic workspace, only the definitions of temporary variables are saved. When you exit from the analytic workspace, the data values are discarded. You can also define variables in programs.

You can define scalar variables (and frequently do in programs), but most variables that you define using the OLAP DML are dimensioned variables. Dimensioned variables are arrays that hold more than one value. The indexes or dimensions of the variable provide the organization for the variable. The values of the dimension are similar to keys in a relational table; in that they uniquely identify a data value. For example, if you have sales variable that is dimensioned by time, geography, and product dimensions, then each combination of the values of time, geography, and product identifies a value in sales. (Note that the indexes of variable s are not actually the values of the dimension, but, instead, are the INTEGER positions of the values in the dimension.)

Variables can be dimensioned by either flat or hierarchical dimensions. A flat dimension exists when the values within a dimension are all at the same. level; no value is the child or parent of another value. A hierarchical dimension exists when the values with a single dimension are in a one-to-many (parent-to-child) relationship with each other.

A hierarchical dimension is a means of organizing and structuring this type of data within a single dimension. You can then use it to dimension a variable that contains data for all the levels. Some dimensions have multiple hierarchies. You specify the parent-to-child relationships of the dimension values by creating a self-relation.You use a hierarchical dimension to define a variable that contains data of varying levels of aggregation within a single variable. This type of storage affords a quicker response time for users who want to view the data, particularly when the variable is large.

Frequently, the cells in the variable that correspond to upper level values in the hierarchical dimension contain the sum or total of the values in the cells of the variable that correspond to the lower level dimension values. For example, in a sales variable that is defined with a hierarchical dimension representing time, the cells of the variable for each quarter might represent the total sales for the months in the quarter.

After you have defined a variable with hierarchical dimensions, you can add variable data to the lowest level of the hierarchy, and then calculate or aggregate the values for the higher levels of the hierarchy. Conversely, you can distribute or allocate data from higher levels to lower levels of the hierarchy.

Objects that Can Dimension Variables

How variable and relation data is actually structured and stored is dependent on what type of object you use to dimension the variable or relation and the order in which those objects appear in the definition of the variable or relation Variables can be dimensioned by simple dimensions, concat dimensions, composites, partition templates, and alias dimensions. The object that by which you choose to dimension a variable determines how the data of the variable is stored.

Simple Dimensions

The members of a simple dimension are data values that all have the same data type. When a variable is dimensioned by a simple dimension, there is one cell in the variable for every member of the dimension. When there is a dimension member for which the variable has no data, Oracle OLAP stores NA values in the variable for that empty value. (Note that if storing these NA values would result in a full page of NA values that Oracle OLAP does not actually store the values.) Oracle OLAP does not store NA values when there is a range.)

Concat Dimensions

You define concat dimensions over previously-defined simple dimensions or conjoint dimensions. Consequently, the base dimensions of a concat dimension can be of different data types. You can represent a hierarchy with a concat dimension that is has two or more simple flat dimensions among its base dimensions. You can use concat dimensions to easily map dimensions in an analytic workspace to columns in relational tables and thereby promote more efficient loading of data from the relational structures into the analytic workspace structures.

Composites

You define composites over previously-defined dimensions or other composites. Conceptually, you can think of a composite consisting of two structures:

  • The composite object itself. The composite contains the dimension-value combinations (that is, a composite tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the composite.

  • An index between the composite values and its base dimension values.

For a variable that is dimensioned by composite, Oracle OLAP does not create a cell for every value in the base dimensions as it would if the variable was dimensioned by a simple dimension. Instead, it creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; Data for the variable is stored in order, cell by cell, for each tuple in the composite. From the perspective of data storage, each combination of base dimension values in a composite is treated like the value of a regular dimension. This means that when you define a variable with one regular dimension and one composite, the data for the variable is stored as though it was a two-dimensional variable. Using composites to reduce the number of elements created for a variable results in more efficient data storage.

Partition Template

You define a partition template over previously-defined dimensions or composites. A partition template is a specification for the partitions of a partitioned variable. A partitioned variable is stored as multiple rows in the relational table of LOBs that is the analytic workspace—each partition is a row in the table.

Alias Dimension

An alias dimension is merely an alias for a simple dimension. An alias dimension has the same type and values as its base dimension. Typically, you define an alias dimension when you want to dimension a variable by the same dimension twice.

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. Relations are dimensioned arrays. Each cell in a relation holds the index of the value of a dimension. You can define relations between two or more dimensions, multiple relations between a set of dimensions, or a dimension with itself (a self-relation).

Most frequently, a relation is a self-relation for a hierarchical dimension. By creating a relation between values in a dimension that participate in a one-to-many (parent-to-child) relationship, you can organize your data by the child values and view aggregates of data by the parent values. For example, you can create a geog.parent relation for a geography dimension to define the relationships between the city and state values in geography. In this way you can organize the data by city and view the aggregates of data by state.

See also:

DEFINE RELATION

Valueset and Surrogate Objects

The OLAP DML provides the following special data objects that you use not when you are defining your variables, but instead, when you are querying them,

Valueset Objects

A valueset is a list of dimension values for one or more previously-defined dimensions. You use a valueset to save dimension status lists across sessions.

Surrogates

A dimension surrogate is an alternative set of values for a previously-defined dimension. You cannot dimension a variable by a surrogate, but you can use a surrogate rather than a dimension in a model, in a LIMIT command, in a qualified data reference, or in data loading with statements such as FILEREAD, FILEVIEW, SQL FETCH, and SQL IMPORT.

Objects that Support the Use of Hierarchical Dimensions

Variables are typically dimensioned by hierarchical objects. For example, you might have a sales variable that is dimensioned by geog, time, and product. The geog dimension might have two hierarchies (one for political divisions and another for sales regions) and each of these hierarchies could have several levels with the top level of the political geography hierarchy being All Country and the top level of the sales geography hierarchy being All Regions. Example 1-1, "Defining and Populating a Hierarchical Dimension Named geog" illustrates defining and populating this type of hierarchical geography dimension.

Example 1-1 Defining and Populating a Hierarchical Dimension Named geog

DEFINE geog DIMENSION TEXT
LD A dimension with two hierarchies for geography
"Populate the dimension with City, State, Region, and Country values 
MAINTAIN geog ADD 'Boston' 'Springfield' 'Hartford' 'Mansfield' 'Montreal' 'Walla Walla' 'Portland' 'Oakland' 'San Diego' 'MA' 'CT' 'WA' 'CA' 'Quebec' 'East' 'West' 'All Regions' 'USA' 'Canada' 'All Country'
 
"Display the values in geog
 
REPORT geog
 
GEOG
--------------
Boston
Springfield
Hartford
Mansfield
Montreal
Walla Walla
Portland
Oakland
San Diego
MA
CT
WA
CA
Quebec
East
West
All Regions
USA
Canada
All Country
 

Typically, after you define a hierarchical dimension, you define the following objects for that dimension:

  • hierlist dimension that lists the names of the hierarchies for the dimension. See "Hierlist Dimension" for more information and an example.

  • parentrel relation that defines the hierarchies. A dimension is only a hierarchical dimension when it has a parentrel defined for it. See "Parentrel Relation" for more information and an example.

  • levellist relation that lists the names of all of the levels of all of the hierarchies. See "Levellist Dimension" for more information and an example.

  • hierlevels valueset that is the values of the levels of each hierarchy. See "Hierlevels Valueset" for more information and an example.

  • inhier valueset or variable that identifies the values of each hierarchy. See "Inhier Valueset or Variable" for more information and examples.

  • levelrel relation that relates each value of the hierarchical dimension to its level in the hierarchy. See "Levelrel Relation" for more information and an example.

  • familyrel relation that is each hierarchical dimension value and its related values. See "Familyrel Relation" for more information and an example.

  • gidrel relation that is the grouping ids of each value within each hierarchy. See "Gidrel Relation" for more information and an example.

Hierlist Dimension

A hierlist dimension is a TEXT dimension in the analytic workspace that has as values the names of the hierarchies of a hierarchical dimension. For example, if the company has a different calendar and fiscal year, the time dimension for that company would have two hierarchies: one for calendar and another for year. The hierlist dimension that supported that time hierarchy would have two values: Calendar and Fiscal.

For consistency's sake, analytic workspaces include a hierlist dimension for every hierarchical dimension -- even when that hierarchical dimension has only one hierarchy.

Example 1-2, "Defining and Populating a hierlist Dimension Named geog_hierlist" illustrates defining and populating this type of dimension.

Example 1-2 Defining and Populating a hierlist Dimension Named geog_hierlist

DEFINE geog_hierlist DIMENSION TEXT
LD List of Hierarchies for geog dimension
"Populate the geog_hierlist dimension
MAINTAIN geog_hierlist ADD 'Political_Geog' 'Sales_Geog'
"Display the values of the geog_hierlist dimension
REPORT geog_hierlist
 
GEOG_HIERLIST
--------------
Political_Geog
Sales_Geog

Parentrel Relation

A parentrel relation is a relation between the hierarchical dimension and itself (a self-relation) and the hierlist dimension. It identifies the parent of each dimension member within a hierarchy.

Example 1-3, "Defining and Populating a parentrel Relation named geog_parentrel" illustrates defining and populating this type of relation.

Example 1-3 Defining and Populating a parentrel Relation named geog_parentrel

"Define the relation 
DEFINE geog_parentrel RELATION geog <geog geog_hierlist>
LD Self-relation for geog showing parents of each value
"Populate each cell in the relation "with the parent of the geog value
"This example using assignment statement with QDRs to do that 
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Boston') = 'MA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Hartford') = 'CT'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Springfield') = 'MA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Mansfield') = 'CT'
geog_parentrel (geog_hierlist 'Sales_Geog' geog  'Montreal') = 'Quebec'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Walla Walla') = 'WA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Portland') = 'WA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Oakland') = 'CA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'San Diego') = 'CA'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'CT') = 'East'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'MA') = 'East'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'WA') = 'West'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'CA') = 'West'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'Quebec') = 'East'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'East') = 'All Regions'
geog_parentrel (geog_hierlist 'Sales_Geog' geog 'West') = 'All Regions'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Boston') = 'MA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Hartford') = 'CT'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Springfield') = 'MA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Mansfield') = 'CT'
geog_parentrel (geog_hierlist 'Political_Geog' geog  'Montreal') = 'Quebec'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Walla Walla') = 'WA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Portland') = 'WA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Oakland') = 'CA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'San Diego') = 'CA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'CT') = 'USA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'MA') = 'USA'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'WA') = 'USA'
geog_parentrel (geog_hierlist 'Political_Geog'  geog 'CA') = 'USA'
geog_parentrel (geog_hierlist 'Political_Geog'  geog 'Quebec') = 'Canada'
geog_parentrel (geog_hierlist 'Political_Geog'  geog 'USA') = 'All Country'
geog_parentrel (geog_hierlist 'Political_Geog' geog 'Canada') = 'All Country'

"Display the values of geog_parentrel
REPORT DOWN geog W 20 geog_parentrel
               -------------GEOG_PARENTREL--------------
               --------------GEOG_HIERLIST--------------
GEOG              Political_Geog         Sales_Geog
-------------- -------------------- --------------------
Boston         MA                   MA
Springfield    MA                   MA
Hartford       CT                   CT
Mansfield      CT                   CT
Montreal       Quebec               Quebec
Walla Walla    WA                   WA
Portland       WA                   WA
Oakland        CA                   CA
San Diego      CA                   CA
MA             USA                  East
CT             USA                  East
WA             USA                  West
CA             USA                  West
Quebec         Canada               East
East           NA                   All Regions
West           NA                   All Regions
All Regions    NA                   NA
USA            All Country          NA
Canada         All Country          NA
All Country    NA                   NA
 

Levellist Dimension

A levellist dimension is a TEXT dimension that has as values the names all of the levels of the hierarchies of a hierarchical dimension.

Example 1-4, "Defining and Populating a levellist Dimension Named geog_levellist" illustrates defining and populating this type of dimension.

Example 1-4 Defining and Populating a levellist Dimension Named geog_levellist

DEFINE geog_levellist DIMENSION TEXT
LD List of levels used by hierarchies of the geog dimension
"Populate the geog_levellist dimension with the names of the levels of both the 
"Political_Geog and Sales_Geog hierarchies
MAINTAIN geog_levellist ADD 'All Country' 'Country' 'All Regions' 'Region' MAINTAIN geog_levellist ADD 'State-Prov' 'City'
"Display the values of the geog_levellist dimension
 
REPORT geog_levellist
 
GEOG_LEVELLIST
--------------
All Country
Country
All Regions
Region
State-Prov
City

Hierlevels Valueset

A hierlevels valueset is those values of the hierlevels dimension (typically ordered from bottom to top) that are included in each hierarchy of the hierarchical dimension.

Example 1-5, "Defining and Populating a hierlevels Valueset named geog_hierlevels" illustrates defining and populating this type of valueset.

Example 1-5 Defining and Populating a hierlevels Valueset named geog_hierlevels

DEFINE geog_hierlevels VALUESET geog_levellist <geog_hierlist>
"Using LIMIT populate the valueset with the appropriate values for each hierarchy
LIMIT geog_hierlevels TO ALL
LIMIT geog_hierlevels (geog_hierlist 'Political_Geog') TO 'City' 'State-Prov' 'Country' 'All Country'
LIMIT geog_hierlevels (geog_hierlist 'Sales_Geog') TO 'City' 'State-Prov' 'Region' 'All Regions'

"Display the values in the valueset 
REPORT W 22 geog_hierlevels
 
GEOG_HIERLIST     GEOG_HIERLEVELS
-------------- ----------------------
Political_Geog City
               State-Prov
               Country
               All Country
Sales_Geog     City
               State-Prov
               Region
               All Regions
 

Inhier Valueset or Variable

An inhier valueset is those values of the inhier dimension that are in each hierarchy. Example 1-6, "Defining and Populating an inhier Valueset Named geog_inhier" illustrates defining and populating this type of valueset.

An inhier variable is a BOOLEAN variable that is dimensioned by the hierarchical dimension and the hierlist dimension. For each hierarchy, it has a TRUE value for each dimension value that is in that hierarchy. Example 1-7, "Defining and Populating an inhier Variable Named geog_inhiervar" illustrates defining and populating this type of valueset

Example 1-6 Defining and Populating an inhier Valueset Named geog_inhier

"Define the valueset
DEFINE geog_inhier VALUESET geog <geog_hierlist>
"Using LIMIT commands, populate the valueset
LIMIT geog_inhier (geog_hierlist 'Political_Geog') REMOVE 'East' 'West' 'All Regions'
LIMIT geog_inhier (geog_hierlist 'Sales_Geog') REMOVE 'Canada' 'USA' 'All Country'
"Display the values in the valueset
 
REPORT W 20 geog_inhier
 
GEOG_HIERLIST      GEOG_INHIER
-------------- --------------------
Political_Geog Boston
               Springfield
               Hartford
               Mansfield
               Montreal
               Walla Walla
               Portland
               Oakland
               San Diego
               MA
               CT
               WA
               CA
               Quebec
               USA
               Canada
               All Country
Sales_Geog     Boston
               Springfield
               Hartford
               Mansfield
               Montreal
               Walla Walla
               Portland
               Oakland
               San Diego
               MA
               CT
               WA
               CA
               Quebec
               East
               West
               All Regions
 

Example 1-7 Defining and Populating an inhier Variable Named geog_inhiervar

DEFINE geog_inhiervar VARIABLE BOOLEAN <geog geog_hierlist>

"Using LIMIT commands and assignment statements, populate
" the variable 
LIMIT geog_hierlist TO ALL
LIMIT geog_hierlist TO 'Political_Geog'
LIMIT geog TO 'East' 'West' 'All Regions'
geog_inhiervar = FALSE
LIMIT geog COMPLEMENT
geog_inhiervar = TRUE
LIMIT geog_hierlist TO ALL
LIMIT geog_hierlist TO 'Sales_Geog'
LIMIT geog TO ALL
LIMIT geog TO 'Canada' 'USA' 'All Country'
geog_inhiervar = FALSE
LIMIT geog COMPLEMENT
geog_inhiervar = TRUE
LIMIT geog TO ALL
LIMIT geog_hierlist TO ALL
 
"Display the values of the variable 
REPORT DOWN geog geog_inhiervar
 
               ---GEOG_INHIERVAR----
               ----GEOG_HIERLIST----
               Political_
GEOG              Geog    Sales_Geog
-------------- ---------- ----------
Boston                yes        yes
Springfield           yes        yes
Hartford              yes        yes
Mansfield             yes        yes
Montreal              yes        yes
Walla Walla           yes        yes
Portland              yes        yes
Oakland               yes        yes
San Diego             yes        yes
MA                    yes        yes
CT                    yes        yes
WA                    yes        yes
CA                    yes        yes
Quebec                yes        yes
East                   no        yes
West                   no        yes
All Regions            no        yes
USA                   yes         no
Canada                yes         no
All Country           yes         no

Levelrel Relation

A levelrel relation is a relation between the levellist and hierlist dimensions that records the level for each member of the hierarchical dimension

Example 1-8, "Defining and Populating a levelrel Relation named geog_levelrel" illustrates defining and populating this type of relation.

Example 1-8 Defining and Populating a levelrel Relation named geog_levelrel

"Define the relation 
DEFINE geog_levelrel RELATION geog_levellist <geog geog_hierlist>
LD Level of each dimension member for geog

"Populate the relation
"This example uses assignment statements with QDRs to populate
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Boston') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Hartford') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Springfield') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Mansfield') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog  'Montreal') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Walla Walla') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Portland') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Oakland') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'San Diego') = 'City'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'CT') = 'State-Prov'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'MA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'WA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'CA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'Quebec') = 'State-Prov'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'East') = 'Region'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'West') = 'Region'
geog_levelrel (geog_hierlist 'Sales_Geog' geog 'All Regions') = 'All Regions'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Boston') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Hartford') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Springfield') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Mansfield') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog  'Montreal') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Walla Walla') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Portland') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Oakland') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'San Diego') = 'City'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'CT') = 'State-Prov'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'MA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'WA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Political_Geog'   geog 'CA') = 'State-Prov'
geog_levelrel (geog_hierlist 'Political_Geog'  geog 'Quebec') = 'State-Prov'
geog_levelrel (geog_hierlist 'Political_Geog'  geog 'USA') = 'Country'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'Canada') = 'Country'
geog_levelrel (geog_hierlist 'Political_Geog' geog 'All Country') = 'All Country'

"Display the values
REPORT DOWN geog W 20 geog_levelrel
 
               --------------GEOG_LEVELREL--------------
               --------------GEOG_HIERLIST--------------
GEOG              Political_Geog         Sales_Geog
-------------- -------------------- --------------------
Boston         City                 City
Springfield    City                 City
Hartford       City                 City
Mansfield      City                 City
Montreal       City                 City
Walla Walla    City                 City
Portland       City                 City
Oakland        City                 City
San Diego      City                 City
MA             State-Prov           State-Prov
CT             State-Prov           State-Prov
WA             State-Prov           State-Prov
CA             State-Prov           State-Prov
Quebec         State-Prov           State-Prov
East           NA                   Region
West           NA                   Region
All Regions    NA                   All Regions
USA            Country              NA
Canada         Country              NA
All Country    All Country          NA

Familyrel Relation

The familyrel relation is a relation between the hierarchical dimension and the levellist and hierlist dimensions that provides the full parentage of each dimension member in the hierarchy.

Example 1-9, "Defining and Populating a familyrel Relation named geog_familyrel" illustrates defining and populating this type of relation.

Example 1-9 Defining and Populating a familyrel Relation named geog_familyrel

"Define the relation 
DEFINE geog_familyrel RELATION geog <geog geog_levellist geog_hierlist>
LD FEATURES Family/Ancestry structure for the geog dimension
 
"Populate the relation using the HIERHEIGHT command 
HIERHEIGHT geog_parentrel INTO geog_familyrel USING geog_levelrel
 
"Display the values of the familyrel relation
"First the values for the Political_Geog hierarchy are displayed
"Then the values for the Sales_Geog hierarchy
REPORT DOWN geog W 12 geog_familyrel
 
GEOG_HIERLIST: Political_Geog
               -------------------------------GEOG_FAMILYREL--------------------------------
               -------------------------------GEOG_LEVELLIST--------------------------------
GEOG            All Country  Country    All Regions     Region     State-Prov      City
-------------- ------------ ------------ ------------ ------------ ------------ ------------
Boston         All Country  USA          NA           NA           MA           Boston
Springfield    All Country  USA          NA           NA           MA           Springfield
Hartford       All Country  USA          NA           NA           CT           Hartford
Mansfield      All Country  USA          NA           NA           CT           Mansfield
Montreal       All Country  Canada       NA           NA           Quebec       Montreal
Walla Walla    All Country  USA          NA           NA           WA           Walla Walla
Portland       All Country  USA          NA           NA           WA           Portland
Oakland        All Country  USA          NA           NA           CA           Oakland
San Diego      All Country  USA          NA           NA           CA           San Diego
MA             All Country  USA          NA           NA           MA           NA
CT             All Country  USA          NA           NA           CT           NA
WA             All Country  USA          NA           NA           WA           NA
CA             All Country  USA          NA           NA           CA           NA
Quebec         All          Canada       NA           NA           Quebec       NA
               Countries
East           NA           NA           NA           NA           NA           NA
West           NA           NA           NA           NA           NA           NA
All Regions    NA           NA           NA           NA           NA           NA
USA            All Country  USA          NA           NA           NA           NA
Canada         All Country  Canada       NA           NA           NA           NA
All Country    All Country  NA           NA           NA           NA           NA
 
GEOG_HIERLIST: Sales_Geog
               -------------------------------GEOG_FAMILYREL--------------------------------
               -------------------------------GEOG_LEVELLIST--------------------------------
GEOG            All Country Country    All Regions     Region     State-Prov      City
-------------- ------------ ------------ ------------ ------------ ------------ ------------
Boston         NA           NA           All Regions  East         MA           Boston
Springfield    NA           NA           All Regions  East         MA           Springfield
Hartford       NA           NA           All Regions  East         CT           Hartford
Mansfield      NA           NA           All Regions  East         CT           Mansfield
Montreal       NA           NA           All Regions  East         Quebec       Montreal
Walla Walla    NA           NA           All Regions  West         WA           Walla Walla
Portland       NA           NA           All Regions  West         WA           Portland
Oakland        NA           NA           All Regions  West         CA           Oakland
San Diego      NA           NA           All Regions  West         CA           San Diego
MA             NA           NA           All Regions  East         MA           NA
CT             NA           NA           All Regions  East         CT           NA
WA             NA           NA           All Regions  West         WA           NA
CA             NA           NA           All Regions  West         CA           NA
Quebec         NA           NA           All Regions  East         Quebec       NA
East           NA           NA           All Regions  East         NA           NA
West           NA           NA           All Regions  West         NA           NA
All Regions    NA           NA           All Regions  NA           NA           NA
USA            NA           NA           NA           NA           NA           NA
Canada         NA           NA           NA           NA           NA           NA
All Country    NA           NA           NA           NA           NA           NA
 

Gidrel Relation

A gidrel relation is a relation between a NUMBER dimension, the hierarchical dimension, and the hierlist dimension that contains the grouping ID of each dimension member in each hierarchy of the hierarchical dimension. It also has a $GID_DEPTH property that identifies the depth within a hierarchy of each dimension member.

Example 1-10, "Defining and Populating a gidrel Relation named geog_gidrel" illustrates defining and populating this type of relation.

Example 1-10 Defining and Populating a gidrel Relation named geog_gidrel

"Create a dimension that has values that are numbers
DEFINE gid_dimension DIMENSION NUMBER (38,0)"Add values to that dimension 
"This example uses MAINTAIN ADD to add a few numbers
MAINTAIN gid_dimension ADD 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 
 
"Define the gidrel relation
DEFINE geog_gidrel RELATION gid_dimension <geog geog_hierlist>
"Display the complete definition of the geog_gidrel relation
"Note that it has no properties
DEFINE GEOG_GIDREL RELATION GID_DIMENSION <GEOG GEOG_HIERLIST>
"Populate the gidrel relation using the GROUPINGID command 
GROUPINGID geog_parentrel INTO geog_gidrel USING geog_levelrel INHIERARCHY geog_inhier
"Display the values of the geog_gidrel relation
 
REPORT down geog w 20 geog_gidrel
 
               ---------------GEOG_GIDREL---------------
               --------------GEOG_HIERLIST--------------
GEOG              Political_Geog         Sales_Geog
-------------- -------------------- --------------------
Boston         0                    0
Springfield    0                    0
Hartford       0                    0
Mansfield      0                    0
Montreal       0                    0
Walla Walla    0                    0
Portland       0                    0
Oakland        0                    0
San Diego      0                    0
MA             1                    1
CT             1                    1
WA             1                    1
CA             1                    1
Quebec         1                    1
East           NA                   3
West           NA                   3
All Regions    NA                   7
USA            3                    NA
Canada         3                    NA
All Country    7                    NA
 
"Display the complete definition of the geog_gidrel relation
"Note that it now has a $GID_DEPTH property
DEFINE GEOG_GIDREL RELATION GID_DIMENSION <GEOG GEOG_HIERLIST>
PROPERTY '$GID_DEPTH' 4