LIMIT command (using parent relation) |
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 of parent-relation-clause varies depending on the dimensionality of the parent relation:
When the parent relation is multidimensional you must use the following syntax:
RELATION parent-relation-name [QUALIFY relation-dimension-name [inclusive-val-args...]...]
(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:
parent-relation-name [inclusive-val-args]
Arguments
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.
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.
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.
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.
(Abbreviated TOPANC) Finds those members that are at the top of the hierarchy; that is, those members that do not have any ancestors. This 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 will be 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 a child-parent self-relation for the dimension. For each dimension value, the relation holds another value of the dimension which is its parent dimension value (the one immediately above it in a given hierarchy). 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. This keyword must be specified if parent-relation-name is a multidimensional relation.
Specifies the name of one of the dimensions of parent-relation-name.
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 (using values).
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.)
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 is done using the relation mlv.market, which is a relation between market 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 will look in the child-parent relation (market.market) to find the children and the grandchildren (DEPTH 2) of Totus and it will discard 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.