GROUPINGID

The GROUPINGID command populates a previously-defined object with the grouping ids for the values of a hierarchical dimension. A grouping id is a numeric value that corresponds to a level of a hierarchical dimension. The grouping id for the lowest-level of the hierarchy is 0 (zero).

Grouping ids are especially useful for identifying values of different levels of a hierarchical dimension. Dimension values in the same level of the hierarchy have the same value for their grouping id. Selecting dimension values for a specific level is easier with grouping ids because the desired values can be identified with a single condition of groupingid = n.

Typically, you use a GROUPINGID statement when you are planning on accessing analytic workspace data in SQL using the OLAP_TABLE function. For more information on the OLAP_TABLE function see the Oracle OLAP Reference.

See also:

The GROUPING_ID function in Oracle Database SQL Reference for more information on grouping ids.

Syntax

GROUPINGID [family-relation] INTO destination-object -

{USING level-relation} [INHIERARCHY {inh-variable | inh-valueset}] [LEVELORDER lo-valueset]

where destination-object is one of the following:

grouping-relation

grouping-variable

grouping-surrogate

Arguments

family-relation: A self-relation for a hierarchical dimension. This self-relation is dimensioned by a hierarchical dimension. The values of the self-relation are the parents of each value in the hierarchical dimension. The family-relation argument is optional only when you use the GROUPINGID statement to populate a surrogate and the GROUPINGID statement includes a LEVELORDER clause.
grouping-relation: The name of a previously-defined relation. One of the dimensions of grouping-relation must be the hierarchical dimension. The values of grouping-relation are calculated and populated when the GROUPINGID statement executes. See DEFINE RELATION for information on defining relations.
grouping-variable: The name of a previously-defined numeric variable. One of the dimensions of grouping-variable must be the hierarchical dimension. The data type of grouping-variable can be any numeric type including NUMBER. The values of grouping-variable are calculated and populated when the GROUPINGID statement executes.See DEFINE VARIABLE for information on defining variables.
grouping-surrogate: The name of a previously-defined surrogate for the hierarchical dimension. The values of grouping-surrogate are calculated and populated when the GROUPINGID statement executes. See DEFINE SURROGATE for information on defining surrogates.
USING: Specifies that the level of the values of the hierarchical dimension are to be considered when creating grouping ids.
level-relation: A relation that is dimensioned by the hierarchical dimension. For each value of the hierarchical dimension, the relation has its value the name of the level for the dimension's value.
INHIERARCHY: Specifies that only some of the values of the hierarchical dimension are to be considered when creating grouping ids.
inh-variable: A BOOLEAN variable that is dimensioned by the hierarchical dimension and, when the hierarchical dimension is a multi-hierarchical dimension, by a dimension that is the names of the hierarchies. The values of the variable are TRUE when the dimension value is in a hierarchy and FALSE when it is not.
inh-valueset: The name of a valueset object whose values identify the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored.
LEVELORDER: Specifies the top-down order of the levels when creating grouping ids.
lo-valueset: The name of a valueset object whose values are the names of the levels to be used when creating grouping ids. The order of the values in the valueset object determine the grouping id assigned.

Examples

Example 16-5 Using GROUPINGID to Populate a Variable with Grouping Ids

Assume that you have the following objects in your analytic workspace.

DEFINE geography DIMENSION TEXT WIDTH 12
LD Geography Dimension Values
DEFINE geography.parent RELATION geography <geography>
LD Child-parent relation for geography
DEFINE geography.hierarchyid DIMENSION INTEGER
LD Dimension whose values are ids for hierarchies in geography

To create a grouping id variable for the Standard hierarchy of geography, define a child-parent relation of only those values that are in the hierarchy whose grouping ids you want to generate, and define a variable to hold the grouping ids. Examples of these definitions follow.

DEFINE geog.gid INTEGER VARIABLE <geography>
DEFINE geography.newparent RELATION geography <geography>

Then populate these variables using statements similar to these.

AW DETACH myaw
AW ATTACH myaw ro
PUSH OKNULLSTATUS
OKNULLSTATUS = TRUE
" Populate the child-parent relation for hierarchy 1
geography.newparent = geography.parent(geography.hierarchyid 1)
" Populate the grouping id variables
GROUPINGID geography.newparent INTO geog.gid
" Save changes to analytic workspace
POP OKNULLSTATUS
ALLSTAT
UPDATE
COMMIT

Reports for the new objects created by this code (geography.newparent and GEOG.GID) follow.

REPORT geography.newparent

GEOGRAPHY          GEOGRAPHY.NEWPARENT
---------------- ----------------
World            NA
Americas         World
Canada           Americas
Toronto          Canada
Montreal         Canada
Ottawa           Canada
Vancouver        Canada
Edmonton         Canada
Calgary          Canada
Usa              Americas
Boston           Usa
Losangeles       Usa
Dallas           Usa
Denver           Usa
Newyork          Usa
Chicago          Usa
Seattle          Usa
Mexico           Americas
...              ...
Japan            Asia
Tokyo            Japan
Osaka            Japan
Kyoto            Japan
China            Asia
Beijing          China
Shanghai         China
...              ...
India            Asia
Ireland          Europe
Taiwan           Asia
Thailand         Asia

REPORT geog.gid
GEOGRAPHY            GEOG.GID
---------------- ----------------
World                           7
Americas                        3
Canada                          1
Toronto                         0
Montreal                        0
Ottawa                          0
Vancouver                       0
Edmonton                        0
Calgary                         0
Usa                             1
Boston                          0
Losangeles                      0
Dallas                          0
Denver                          0
Newyork                         0
Chicago                         0
Seattle                         0
Mexico                          1
...                             ...
Japan                           1
Tokyo                           0
Osaka                           0
Kyoto                           0
China                           1
Beijing                         0
Shanghai                        0
...                             ...
India                           1
Ireland                         1
Taiwan                          1
Thailand                        1