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.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:
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.
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.
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.
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.
Specifies that the level of the values of the hierarchical dimension are to be considered when creating grouping ids.
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.
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.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.
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.
Specifies the top-down order of the levels when creating grouping ids.
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.
Specifies that Oracle OLAP creates the grouping ids in the same manner as SQL does when you specify ROLLUP in a SQL SELECT statement.
The ROLLUP keyword is valid only when the destination object is a relation. When you specify this keyword, $GID_TYPE and $GID_LIST properties.
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.
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.
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