![]() Previous |
![]() Next |
A LIMIT command that uses a parent relation in its limit clause to set the status of a hierarchical dimension or its dimension surrogate, or assigns values to a valueset, based on family relationships within the hierarchy.
Syntax
LIMIT {dimension | valueset} [concat-component] limit-type-
[family-keyword ] USING parent-relation-clause [IFNONE label]
where:
family-keyword has one of the following constructs:
The syntax for parent-relation-clause varies depending on its dimensionality of the object you want to specify:
When the parent relation is multidimensional, use the following syntax:
RELATION parentrel [QUALIFY relation-dimension-name [inclusive-val-args... | CURRENT]...]
(You can also use this syntax when the parent relation is one-dimensional.)
When the parent relation is one-dimensional, you can use the following simplified syntax:
parentrel [inclusive-val-args | CURRENT]
Parameters
The name of the dimension or dimension surrogate for which you are setting status.
The name of the valueset for which you are assigning values; or a LIMIT function.
The name of the component of the concat dimension whose values are used to determine the limit. (See the main entry for LIMIT command for complete description of this argument.)
A keyword that specifies how Oracle OLAP should modify the current status list. (See the main entry for LIMIT command for a list and descriptions of these keywords.)
Finds the parent of each value in valuelist. For a dimension, when there is no valuelist, finds the parent for each value in status. For a valueset, when there is no valuelist, it finds the parent of each value in the valueset. It uses the parent-relation to look up the parent.
Finds the children of each value in valuelist. For a dimension, when there is no valuelist, finds the children for each value in status. For a valueset, when there is no valuelist, it finds the children of each value in the valueset. It uses the parent-relation to look up the children.
When you do not include the DISTANCE phrase:
Finds the ancestors (that is, parents, grandparents, and so on) of each value in valuelist. For a dimension, when there is no valuelist, it finds the ancestors of each value in status.
For a valueset, when there is no valuelist, it finds the ancestors of each value in the valueset. In other words it finds "parents" for the values and the "parents of the parents" until there are no new parents.
When you include the DISTANCE phrase, limits to the ancestors who are members of the generation specified by generation:
For generation, specify 0 for the current generation, 1 for parents, 2 for grandparents, 3 for great grandparents, and so on.
For negative values, the command returns descendant generations (that is -1 returns children, -2 returns grandchildren, and so on).
When you do not include the DISTANCE phrase:
Finds the descendants (that is, children, grandchildren, and so on) of each value in valuelist. For a dimension, when there is no valuelist, it finds descendants for each value in status.
For a valueset, when there is no valuelist, it finds the descendants of each value in the valueset. In other words, it finds the children of the values and the children of the children until there are no new children.
When you include the DISTANCE phrase, limits to the descendants who are members of the generation specified by generation:
For generation, specify 0 for the current generation, 1 for children, 2 for grandchildren, 3 for great grandchildren, and so on.
For negative, the command returns ancestor generations (that is -1 returns parents, -2 returns grandparents, and so on).
Finds all siblings of each value in valuelist, including the valuelist values, themselves. Issuing one LIMIT statement with the SIBLIGS keyword is the same as issuing two consecutive LIMIT statements: 1) LIMIT with PARENTS, 2) LIMIT with CHILDREN. For a dimension, when there is no valuelist, it finds siblings for each value in status. For a valueset, when there is no valuelist, it finds the siblings of each value in the valueset.
(Abbreviated TOPANC) Finds those members that are at the top of the hierarchy; that is, those members that do not have any ancestors which is equivalent to issuing the following two LIMIT commands.
LIMIT dimension to ANCESTORS .... LIMIT dimension REMOVE DESCENDANTS....
(Abbreviated BOTTOMDESC) Finds those members that are at the bottom of the hierarchy; that is, those members that do not have any descendants.
Finds the descendants (that is, children, grandchildren, and so on) based on a particular parent-relation. The difference is the order of the values. DESCENDANTS groups the values by level (all children, then all grandchildren, and so on); HIERARCHY places each group of children next to its parent. HIERARCHY includes the original values (that is, those in status before the LIMIT statement was executed) in status.
Indicates that children should be listed before their parents. By default, children are listed after their parents.
Excludes the original values from the status. The default is to include original values.
Skips n generations for each value in valuelist. For dimensions, when there is no valuelist, it skips n generations for each value in status. For a valueset, when there is no valuelist, it skips n generations for each value in the valueset. This keyword, in combination with DEPTH, is helpful when drilling down; see Example: Drilling Down Using SKIP and DEPT.
Includes n generations down from each value of valuelist. For dimensions, when there is no valuelist, it includes n generations for each value in status. For a valueset, when there is no valuelist, it includes n generations of each value in the valueset. The default depth value is 99. This keyword, in combination with SKIP, is helpful when drilling down on values.
Executes a statement, represented as a text expression, every time a group of children is constructed. For example, you can sort each group of children based on information stored in an Oracle OLAP variable. In the following statement, markets are sorted in increasing order based on unit sales every time a group of children is constructed.
LIMIT market TO HIERARCHY RUN 'SORT market a unit.m' USING -
market.market
Note: In this example, when you use KEEP or REMOVE instead of TO in your LIMIT statement, the SORT statement would have no effect. |
Specifies the values to use when determining parent values.
Specifies the name of the parent relation for the dimension.
To limit a dimension surrogate, use the parent relation for the dimension for which it is a surrogate.
Identifies the beginning of the parent-relation-clause. You use this keyword when parentrel is a multidimensional relation.
Specifies the name of a dimension of parentrel. The use of this clause varies depending on whether you are coding a LIMIT command or a LIMIT function as described in "Looping Behavior of LIMIT (using parent relation)".
Specifies the values to use when determining the parent values. You can specify any inclusive valuelist argument as described in the syntax of the inclusive-val-args argument for the valuelist clause for LIMIT command. See LIMIT (using values) command for detailed syntax.
Specifies that you want to limit to the values of the children of the current value of the dimension. (This is the same as specifying dimension_name
+0
.)
Specifies that program execution should branch to label when the requested status has null status or is based on a related dimension that turns out to have null status (that is, to have no values). (See the main entry for LIMIT command for complete description of this phrase.)
Usage Notes
Looping Behavior of LIMIT (using parent relation)
When you do not include a QUALIFY clause, the set of values that this LIMIT puts into status when parentrel is multi-dimensional varies depending on whether or not you are issuing a LIMIT command or a LIMIT function:
LIMIT command. All of the in-status values of the related dimensions of parentrel are included in the set of in-status values. If you only want the current values of the related dimensions of parentrel to be included in the set of in-status values, specify a QUALIFY related-dimension CURRENT clause for each of the related dimensions of parentrel.
LIMIT function. Only the current values of the related dimensions of parentrel are included in the set of in-status values. If you want all of the in-status values of the related dimensions of parentrel to be included in the set of in-status values, specify a QUALIFY related-dimension related-dimension clause for each of the related dimensions of parentrel.
Examples
A Simple Drill Down
This example drills down on districts from the region level of the market dimension. First, the market dimension, which has embedded totals at the district, region, and total U.S. level, is limited to the region level data. This LIMIT is done using the relation mlv.market
, which is a relation between marke
t and market.level
.
Issuing a REPORT mlv.market
statement produces the following output, which shows the values of mlv.market
.
MARKET MLV.MARKET -------------- ---------- Totus Totus East Region Boston District Atlanta District Central Region Chicago District Dallas District West Region Denver District Seattle District
The following LIMIT statement limits the values of MARKET
, and the STATUS statement produces the values currently in status. The output of STATUS is shown following the statements.
LIMIT market TO mlv.market 'Region' STATUS market The current status of MARKET is: EAST, CENTRAL, WEST
To drill down on the district level data from the region level, you can use LIMIT with the CHILDREN keyword. The following example uses a parent-relation called market.market
to perform the drill down. For each value of the market
dimension, this relation contains the name of its parent.
DEFINE market.market RELATION market <market> LD Self-relation for the Market Dimension
A report of market.market
produces the following output.
MARKET MARKET.MARKET -------------- ------------- Totus NA East Totus Boston Central Atlanta East Central Totus Chicago Central Dallas Central West Totus Denver West Seattle West
You can limit market
to the children of the East
, Central
, and West
regions by using the CHILDREN keyword with LIMIT.
LIMIT market TO mlv.market 'Region' Limit market TO CHILDREN USING market.market
A report of market
produces the following output.
MARKET ------------- Boston Atlanta Chicago Dallas Denver Seattle
Drilling Down Using SKIP and DEPT
Consider the following statement.
LIMIT market TO HIERARCHY DEPTH 2 SKIP 1 USING market.market 'Totus'
Oracle OLAP looks in the child-parent relation (market.market
) to find the children and the grandchildren (DEPTH 2
) of Totus
and it discards the first generation (SKIP 1
). The resulting status follows.
Totus Boston Atlanta Chicago Dallas Denver Seattle
Note that Totus
is included in status. With HIERARCHY, the original values are included in status.