| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
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.
See also:
"Types of OLAP DML Data Objects" and"Objects that Support the Use of Hierarchical Dimensions".Introduction
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.
See:
DEFINE VARIABLEHow 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.
See:
DEFINE COMPOSITEPartition 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.
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 RELATIONThe 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.
See:
DEFINE VALUESETSurrogates
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.
See:
DEFINE SURROGATEVariables 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.
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
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
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
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
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
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
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
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
