Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
OLAP_TABLE Function, 5 of 6
The syntax for the OLAP_TABLE
function is shown below.
OLAP_TABLE ( aw-attach IN VARCHAR2, table-name IN VARCHAR2, olap-command IN VARCHAR2, limit-map IN VARCHAR2);
The OLAP_TABLE
function returns a table of objects.
An optional text expression that specifies the name of the analytic workspace that contains the data you want to query and whether the analytic workspace is detached after this function executes. The format of aw-attach
is shown below:
[aw-name DURATION QUERY|SESSION]
where:
aw-name
is the name of the analytic workspace that you want attached as the active analytic workspace for Oracle OLAP. When there is one or more analytic workspaces attached, you do not have to specify a value for the aw_attach
parameter. In this case, Oracle OLAP searches these analytic workspaces for the analytic workspace objects referenced in the olap-command
and limit-map
parameters. When you do not specify a value for the aw_attach
parameter and an analytic workspace is not attached, an error is returned.
The DURATION
phrase specifies when the analytic workspace specified by aw-name
is detached. When the analytic workspace specified by aw-name
is already attached, the DURATION
phrase is ignored. You can specify either of the following keywords:
QUERY
specifies that the analytic workspace is detached after this function executes.SESSION
specifies that the analytic workspace is detached when the connection to the database ends.The name of the table of objects that this function returns.
A text expression that specifies one or more OLAP DML commands, including an OLAP DML program. When you specify a value for this parameter, Oracle OLAP executes these OLAP commands before it selects the data using the mapping provided by the limit-map
parameter. When using quotation marks in an OLAP command, follow these guidelines:
'
) in an OLAP command, use two single quotes ('').
The SQL processor strips one of the single quotes before it sends the OLAP command to Oracle OLAP."
) to indicate the beginning of a comment.The main reasons why you specify a value for the olap-command
parameter is if you do not want to support the use of WHERE
clauses. In this case, you specify a FETCH
command (or an OLAP DML program that includes a FETCH
command) for the value of the olap-command
parameter instead of specifying a value for the limit-map
parameter. For the syntax of the OLAP FETCH
command, see the topic for that command in Oracle9i OLAP DML Reference help.
A text expression that specifies how Oracle OLAP accesses analytic workspace data. The format of the limit-map
parameter is shown below:
[MEASURE object-attribute FROM aw-measure] ... DIMENSION [object-attribute FROM] aw-dim [WITH [HIERARCHY [object-attribute FROM] aw-parent-rel [(aw-hierdim aw-hierdim-value [[, aw-hierdim aw-hierdim-value] ...])] [INHIERARCHY aw-inhier-object] [GID object-attribute FROM aw-gid-object] [PARENTGID object-attribute FROM aw-gid-object] [LEVELREL object-attribute-list FROM aw-level-rel USING aw-level-dim]] [[ATTRIBUTE object-attribute FROM aw-attribute] ...] [LOOP sparse-dimension]... [PREDMLCMD olap-command] [POSTDMLCMD olap-command]
MEASURE
block -- Each MEASURE
block maps the values of one analytic workspace variable (or a function that returns an analytic workspace variable) specified by aw-measure
to an object attribute (table column) specified by object-attribute
. All of the analytic workspace variables mapped using a single MEASURE
block must have exactly the same analytic workspace dimensions.
DIMENSION
block -- Each DIMENSION
block maps the values of an analytic workspace dimension to one or more object attributes (table columns). When yo are mapping an analytic dimension, you specify that dimension using a single DIMENSION
block. When you are mapping one or more analytic workspace variables using a MEASURE
block, you also include one DIMENSION
block for each dimension of the analytic workspace variables.
The syntax varies depending on whether or not the dimension is a hierarchical dimension.
When mapping a nonhierarchical dimension, use the object-attribute FROM
clause to map the dimension values to a single object attribute (table column) specified by object-attribute
.
When mapping a hierarchical dimension, use the WITH HIERARCHY
clause to map the dimension values to several object attributes (table columns).
The [object-attribute FROM] aw-parent-rel [(aw-hierdim aw-hierdim-value [[, aw-hierdim aw-hierdim-value]...])
subclause maps the values of an hierarchical analytic workspace dimension to columns in the relational object.
object-attribute
is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.
aw-parent-rel
is a relation that is dimensioned by aw-dim
and all of the aw-hierdim
dimensions. The values of aw-parent-rel
are the values of the aw-dim
dimension. For each unique combination of dimension values, aw-parent-rel
has the value of aw-dim
that is the parent. When aw-parent-rel
represents more than one hierarchy, you use the aw-hierdim
parameter to qualify it to a single hierarchy.
aw-hierdim
is the name of a dimension whose values are the names of hierarchies and aw-hierdim-value
is the name of a particular hierarchy.
aw-inhier-object
is the name of an analytic workspace variable or relation whose non-NA values indicate membership in the hierarchy being mapped. aw-inhier-object
must have the same dimensions as aw-parent-rel
and aw-gid-object
(that is, it must be dimensioned by aw-dim
and all of the aw-hierdim
dimensions).
The GID object-attribute FROM aw-gid-object
subclause maps the grouping ids of the children of an hierarchical analytic workspace dimension to a column in the relational object.
object-attribute
is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.
aw-gid-object
is the name of an analytic workspace variable whose values are the grouping ids for the hierarchy or the name of an analytic workspace relation that returns a Number
dimension whose values are the grouping ids for the hierarchy. aw-gid-object
must have the same dimensions as aw-parent-rel
and aw-inhier-object
(that is, it must be dimensioned by aw-dim
and all of the aw-hierdim
dimensions). When the aw-gid-object
does not exist in the analytic workspace, you can create it using the OLAP GROUPINGID
command. (For the syntax of GROUPINGID
, see the topic for the command in the Oracle9i OLAP DML Reference help.)
PARENTGID object-attribute FROM aw-gid-object
sub
clause maps the grouping ids of the parent values of an hierarchical analytic workspace dimension to a column in the relational object.
object-attribute
is the name of the object attribute (relational table column) to which you want the analytic workspace value mapped.
aw-gid-object
is the name of an analytic workspace variable whose values are the grouping ids for the hierarchy or the name of an analytic workspace relation that returns a Number
dimension whose values are the grouping ids for the hierarchy. aw-gid-object
must have the same dimensions as aw-parent-rel
and aw-inhier-object
(that is, it must be dimensioned by aw-dim
and all of the aw-hierdim
dimensions). When the aw-gid-object
does not exist in the analytic workspace, you can create it using the OLAP GROUPINGID
command. (For the syntax of GROUPINGID
, see the topic for the command in the Oracle9i OLAP DML Reference help.)
LEVELREL object-attribute-list FROM aw-level-rel USING aw-level-dim
subclause specifies how to map the values of a single hierarchical dimension into several columns of the relational table. There is one column in the table for each level of the analytic workspace hierarchy.
object-attribute-list
is a list of the names of object attributes that represent columns of the relational table to which you want the values mapped. Specify one attribute (column) name for each level in the hierarchy. Separate the names of the attributes (columns) using commas. The order must be the same as the order specified in aw-level-relation
.
aw-level-rel
is the name of an analytic workspace relation that is dimensioned by aw-dim
, aw-level-dim
, and all aw-hierdim
dimensions. The values of aw-level-rel
are the numerical values that represent the levels of the dimension. These are the values that will be in the table columns specified by object-attribute-list
. When aw-level-rel
does not exist in the analytic workspace, you can create it using the OLAP HEIRHEIGHT
command. In this case, you can specify if you want the attributes in descending (the default) or ascending order. (For the syntax of HIERHEIGHT
, see the topic for the command in the Oracle9i OLAP DML Reference help.)
aw-level-dim
is the name of an integer dimension whose values are 1 through the highest-level of hierarchy in the analytic workspace.
ATTRIBUTE object-attribute FROM aw-attribute
subclause specifies how dimension table attributes are mapped.
LOOP sparse-dimension
subclause specifies how the OLAP_TABLE
function loops over the values of aw-measure
to retrieve its values. You can specify this subclause for only one dimension of a measure. Using this subclause causes the function to loop sparsely over aw-measure
using the analytic workspace composite, conjoint or multidimensional dimension specified by sparse-dimension
rather than looping over aw-measure
densely using the object specified by aw-dim
. Typically, the composite specified for sparse-dimension
is one by which measure is dimensioned.PREDMLCMD
and POSTMLCMD
blocks --PREDMLCMD
is an optional block that specifies an OLAP command to be executed before the data is fetched. POSTDMLCMD
is an optional block that specifies an OLAP command to be executed after the data is fetched. In both blocks, the olap-command
parameter specifies a text expression that specifies one or more OLAP commands, including an OLAP DML program. When using quotation marks in an OLAP command, follow these guidelines:
'
) in an OLAP command, use two single quotes ('').
The SQL processor strips one of the single quotes before it sends the OLAP command to Oracle OLAP."
) to indicate the beginning of a comment.Keep the following points in mind when creating a limit map:
SELECT
statements and WHERE
clauses. When an object attribute (column) is referenced in a SELECT
statement or a WHERE
clause but not in the limit map, that portion of the WHERE
clause is ignored when retrieving the data.
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|