Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Limiting an Application's View of the Data, 8 of 11
You can use the LIMIT command to use a family tree to place dimension values in status. You can limit a dimension as follows:
The simplified syntax for using the LIMIT command to limit a dimension based on a relationship within a hierarchy is shown below.
LIMIT dimension TO {PARENTS|CHILDREN|ANCESTORS|DESCENDANTS|HIERARCHY} - USING parent-rel[valuelist]
The PARENTS keyword finds the parent of each value in valuelist or, when there is no valuelist, it finds the parent for each value in status. It uses the parent-rel to look up the parent.
The CHILDREN keyword finds the children of each value in valuelist or, when there is no valuelist, finds the children for each value in status. It uses the parent-rel to look up the children.
The ANCESTORS keyword finds the ancestors (that is, parents, grandparents, and so on) of each value in valuelist or, when there is no valuelist, finds the ancestors of each value in status.
The DESCENDANTS keyword finds the descendants (that is, children, grandchildren, and so on) of each value in valuelist or, when there is no valuelist, finds descendants for each value in status.
The HIERARCHY keyword is similar to DESCENDANTS and finds the descendants (that is, children, grandchildren, and so on) based on the value of the parent-rel argument.
The parent-rel argument is the name of a relation between the dimension and itself. For each dimension value, the relation holds another value of the dimension that is its parent dimension value (the one immediately above it in a given hierarchy). This parent-relation can have more than one dimension.
The valuelist argument can be any inclusive list of values.
For more information on using the HIERARCHY keyword, see "Differences between HIERARCHY and DESCENDANTS keywords". For the complete syntax of the LIMIT command, see the entry for the command in OLAP DML Reference.
Both the HIERARCHY and DESCENDANTS keywords of the LIMIT command allow you to set the status of a dimension based on its family tree; however, the different keywords give you different results.
One difference is the order of the values:
Additionally, if you use the HIERARCHY keyword, then you can include the additional arguments described in the following table that let you further manipulate the contents of the current status list.
Suppose your application issues the following command.
limit market to hierarchy depth 2 skip 1 using market.market 'TOTUS'
In processing this command, the parent relation is searched (MARKET.MARKET) to find the children and the grandchildren (DEPTH 2) of TOTUS and 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.
When you are using the HIERARCHY keyword with the LIMIT command, you can use the RUN keyword to execute a command, specified as a text expression, every time a group of children is constructed. This lets you further manipulate the values that are being placed in status.
The following command not only limits the values of the MARKET dimension to descendants using the MARKET.MARKET self-relation but also, every time a group of children is constructed, sorts the values in the MARKET dimension in increasing order based on unit sales.
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 with the LIMIT command, the SORT command has no effect.
Suppose you want to drill down on districts from the region level of the MARKET dimension. This is a two step process.
The first step in the process is to limit the MARKET dimension, which has embedded totals at the district, region, and total U.S. level, to the region-level data. This is done using the relation MLV.MARKET, which is a relation between MARKET and MARKETLEVEL.
The following command produces the report shown below it, which shows the values of MLV.MARKET.
report 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 commands limit the values of MARKET to the desired values and display the values that are currently in status for the MARKET dimension.
limit market to mlv.market 'REGION' status market The current status of MARKET is: EAST, CENTRAL, WEST
The second step in the process is to drill down on the district-level data from the region level. You can use the self-relation 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
The following commands limit MARKET to the children of the EAST, CENTRAL, and WEST regions and drill down to the district-level data by using the CHILDREN keyword with the LIMIT command.
limit market to mlv.market 'REGION' limit market to children using market.market
A report of MARKET produces the following output and shows the values that are now in status.
MARKET ------------- BOSTON ATLANTA CHICAGO DALLAS DENVER SEATTLE
For more information, see the following table.
IF you want documentation about . . . | THEN see . . . |
---|---|
limiting dimensions, |
the entry for the LIMIT command in OLAP DML Reference |
hierarchical dimensions |
"Defining Hierarchical Dimensions and Variables That Use Them" |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|