Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)
E17122-08
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
PDF · Mobi · ePub

GROUPINGID command

The GROUPINGID command populates a previously-defined object with the grouping ids for the values of a hierarchical dimension, and creates and populates the $GID_DEPTH system property.

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.

See also:

See "Gidrel Relation" for more information and the GROUPING_ID function in Oracle Database SQL Language Reference for more information on grouping ids.

Syntax

GROUPINGID [parent-relation] INTO destination-object -

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

[ROLLUP | GROUPSET]

where destination-object is one of the following:

grouping-relation
grouping-variable
grouping-surrogate

Parameters

parent-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 parent-relation argument is optional only when you use the GROUPINGID command to populate a surrogate and the GROUPINGID command includes a LEVELORDER clause.

grouping-relation

The name of a previously-defined relation. One dimension of grouping-relation must be the hierarchical dimension. The values of grouping-relation are calculated and populated when the GROUPINGID command executes. When you specify a relation as the destination object, Oracle OLAP automatically creates and sets the $GID_DEPTH property on the relation when it populates it.

grouping-variable

The name of a previously-defined numeric variable. One dimension 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 command executes.See the DEFINE VARIABLE command 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 command executes. See the DEFINE SURROGATE command 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 values of the hierarchical dimension are to be considered when creating grouping ids.

Note:

You cannot specify an INHIERARCHY clause when you specify ROLLUP or GROUPSET.
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.

ROLLUP

Specifies that Oracle OLAP creates the grouping ids in the same manner as SQL does when you specify ROLLUP in a SQL SELECT statement.

See also:

Rollup cube clause in Oracle Database SQL Language Reference.

The ROLLUP keyword is valid only when the destination object is a relation. When you specify this keyword, $GID_TYPE and $GID_LIST properties.

GROUPSET

Specifies that Oracle OLAP creates the grouping ids in the same manner as SQL does when you specify GROUPING SET in a SQL SELECT statement.

See also:

Grouping sets clause in Oracle Database SQL Language Reference for more information.

The GROUPSET keyword is valid only when the destination object is a relation. When you specify this keyword, Oracle OLAP also creates and populates two properties on the grouping id relation: the $GID_TYPE and $GID_LIST properties.

Examples

Example 9-145 Using GROUPINGID Command to Populate a Relation with Grouping Ids

Assume your analytic workspace contains the following objects.

DEFINE GEOG DIMENSION TEXT
LD A dimension with two hierarchies for geography
 
DEFINE geog_hierlist DIMENSION TEXT
LD List of Hierarchies for geog dimension
 
DEFINE GEOG_INHIER VALUESET GEOG <GEOG_HIERLIST>
LD A valueset of geog that are just the values in each hierarchy
 
DEFINE GEOG_PARENTREL RELATION GEOG <GEOG GEOG_HIERLIST>
LD Self-relation for geog showing parents of each value
 
DEFINE GEOG_INHIER VALUESET GEOG <GEOG_HIERLIST>
LD A valueset of geog that are just the values in each hierarchy
 
DEFINE GEOG_LEVELREL RELATION GEOG_LEVELLIST <GEOG GEOG_HIERLIST>
LD Level of each dimension member for geog

Assume that those objects have the values shown in the following reports.

REPORT geog_hierlist
 
GEOG_HIERLIST
--------------
Political_Geog
Sales_Geog
 
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 Countries        NA
Canada         All Countries        NA
All Countries  NA                   NA
 
->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 Countries
Sales_Geog     Boston
               Springfield
               Hartford
               Mansfield
               Montreal
               Walla Walla
               Portland
               Oakland
               San Diego
               MA
               CT
               WA
               CA
               Quebec
               East
               West
               All Regions
 
->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 Countries  All Countries        NA

To create grouping ids for the values of geog, you first define a GID dimension with the following definition and you populate it with more values than you expect to have for grouping ids.

DEFINE GID_DIMENSION DIMENSION NUMBER (16,0)

Next you define a relation to hold the grouping ids.

DEFINE GEOG_GIDREL RELATION GID_DIMENSION <GEOG GEOG_HIERLIST>

Now you execute the GROUPINGID command to populate the geog_gidrel relation.

GROUPINGID geog_parentrel INTO geog_gidrel USING geog_levelrel -
       INHIERARCHY geog_inhier

A report of geog_gidrel shows that the relation is now populated.

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 Countries  7                    NA

When you execute a FULLDSC of geog_gidrel, you can see that the $GID_DEPTH property has been created and populated for geog_gidrel.

DEFINE GEOG_GIDREL RELATION GID_DIMENSION <GEOG GEOG_HIERLIST>
PROPERTY '$GID_DEPTH' 4

Example 9-146 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.

" 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

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
Go to previous page
Previous		 Go to next page
Next
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