Oracle® OLAP Reference 10g Release 2 (10.2.0.3) Part Number B14350-02 |
|
|
View PDF |
Because different applications have different requirements, several different formats are commonly used for fetching data into SQL from an analytic workspace. The examples in this chapter show how to create views using a variety of different formats.
See Also:
"OLAP_TABLE Syntax" for complete descriptions of the syntax used in these examples.Although these examples are shown as views, the SELECT
statements can be extracted from them and used directly to fetch data from an analytic workspace into an application.
Note:
The examples in this section use predefined ADTs. You could modify them to use automatic ADTs. See "Using OLAP_TABLE With Automatic ADTs".The examples in this section do not include a MODEL
clause. In general, you should specify a MODEL
clause for performance reasons, as described in "Using a MODEL Clause".
Example 11-5 shows the PL/SQL script used to create an embedded total view of the TIME
dimension in the GLOBAL
analytic workspace. This view is similar to the view in Example 11-2, but it specifies both a Calendar and a Fiscal hierarchy, and it includes HATTRIBUTE
subclauses for hierarchy-specific End Date attributes.
The INHIERARCHY
subclause identifies a valueset in the analytic workspace that lists all the dimension members in each hierarchy of a dimension. OLAP_TABLE
saves the status of all dimensions in the limit map that have INHIERARCHY
subclauses during the processing of the limit map. See "Order of Processing in OLAP_TABLE".
Example 11-5 Script for an Embedded Total Dimension View Using OLAP_TABLE
CREATE TYPE awtime_row AS OBJECT ( awtime_id VARCHAR2(12), awtime_short_label VARCHAR2(12), awtime_cal_end_date DATE, awtime_fis_end_date DATE); / CREATE TYPE awtime_table AS TABLE OF awtime_row; / CREATE OR REPLACE VIEW awtime_view AS SELECT awtime_id, awtime_short_label, awtime_cal_end_date, awtime_fis_end_date FROM TABLE(OLAP_TABLE( 'global DURATION SESSION', 'awtime_table', '', 'DIMENSION awtime_id FROM time WITH HIERARCHY time_parentrel (time_hierlist ''CALENDAR'') INHIERARCHY time_inhier HATTRIBUTE awtime_cal_end_date FROM time_cal_end_date HIERARCHY time_parentrel (time_hierlist ''FISCAL'') INHIERARCHY time_inhier HATTRIBUTE awtime_fis_end_date FROM time_fis_end_date ATTRIBUTE awtime_short_label FROM time_short_description')); / SQL>SELECT * FROM awtime_view; AWTIME_ID AWTIME_SHORT_LABEL AWTIME_CAL_END_DATE AWTIME_FIS_END_DATE --------- ------------------ ------------------- ------------------- 19 Jan-98 31-JAN-98 31-JAN-98 20 Feb-98 28-FEB-98 28-FEB-98 21 Mar-98 31-MAR-98 31-MAR-98 22 Apr-98 30-APR-98 30-APR-98 23 May-98 31-MAY-98 31-MAY-98 24 Jun-98 30-JUN-98 30-JUN-98 . . . . 98 Q1-03 31-MAR-03 30-SEP-03 99 Q2-03 30-JUN-03 31-DEC-03 1 1998 31-DEC-98 30-JUN-99 102 2003 31-DEC-03 30-JUN-04 119 2004 31-DEC-04 30-JUN-05 2 1999 31-DEC-99 30-JUN-00 3 2000 31-DEC-00 30-JUN-01 4 2001 31-DEC-01 30-JUN-02 85 2002 31-DEC-02 30-JUN-03
Note:
Be sure to verify that you have created the views correctly by issuingSELECT
statements against them. Only at that time will any errors in the call to OLAP_TABLE
show up.In a star schema, a separate measure view is needed with columns that can be joined to each of the dimension views. Example 11-6 shows the PL/SQL script used to create a measure view with a column populated by a ROW2CELL
clause to support custom measures.
Example 11-6 Script for a Measure View Using OLAP_TABLE
CREATE TYPE awunits_row AS OBJECT ( awtime VARCHAR2(12), awcustomer VARCHAR2(30), awproduct VARCHAR2(30), awchannel VARCHAR2(30), awunits NUMBER(16), r2c RAW(32)); / CREATE TYPE awunits_table AS TABLE OF awunits_row; / CREATE OR REPLACE VIEW awunits_view AS SELECT awunits, awtime, awcustomer, awproduct, awchannel, r2c FROM TABLE(OLAP_TABLE( 'global DURATION SESSION', 'awunits_table', '', 'MEASURE awunits FROM units_cube_units DIMENSION awtime FROM time WITH HIERARCHY time_parentrel DIMENSION awcustomer FROM customer WITH HIERARCHY customer_parentrel (customer_hierlist ''MARKET_ROLLUP'') INHIERARCHY customer_inhier DIMENSION awproduct FROM product WITH HIERARCHY product_parentrel DIMENSION channel WITH HIERARCHY channel_parentrel ATTRIBUTE awchannel FROM channel_short_description ROW2CELL r2c')) WHERE awunits IS NOT NULL; SQL>SELECT awchannel, awunits FROM awunits_view WHERE awproduct = '1' AND awcustomer = '7' AND awtime = '4'; AWCHANNEL AWUNITS --------- ------- All Channels 415392 Direct Sales 43783 Catalog 315737 Internet 55872
Rollup form uses a column for each hierarchy level to show the full parentage of each dimension member. The only difference between the syntax for rollup form and the syntax for embedded total form is the addition of a FAMILYREL
clause in the definition of each dimension in the limit map.
Example 11-7 shows the PL/SQL script used to create a rollup view of the PRODUCT
dimension. It shows a dimension view to highlight the differences in the syntax of the limit map from the one used for the embedded total form, as shown in Example 11-5, "Script for an Embedded Total Dimension View Using OLAP_TABLE". Note that the target columns for these levels are listed in the FAMILYREL
clause from most aggregate (CLASS
) to least aggregate (ITEM
), which is the order they are listed in the level list dimension. The family relation returns four columns. The most aggregate level (all products) is omitted from the view by mapping it to null.
Example 11-8 shows the alternate syntax for the FAMILYREL
clause, which uses QDRs to identify exactly which columns will be mapped from the family relation.
The limit maps in Example 11-7 and Example 11-8 generate identical views.
Example 11-7 Script for a Rollup View of Products Using OLAP_TABLE
CREATE TYPE awproduct_row AS OBJECT ( class VARCHAR2(50), family VARCHAR2(50), item VARCHAR2(50)); / CREATE TYPE awproduct_table AS TABLE OF awproduct_row; / CREATE OR REPLACE VIEW awproduct_view AS SELECT class, family, item FROM TABLE(OLAP_TABLE( 'global DURATION QUERY', 'awproduct_table', '', 'DIMENSION product WITH HIERARCHY product_parentrel FAMILYREL null, class, family, item FROM product_familyrel USING product_levellist LABEL product_short_description')); SQL> SELECT * FROM awproduct_view ORDER BY class, family, item; CLASS FAMILY ITEM -------------- ---------------- ------------------------ Hardware CD-ROM Envoy External 6X CD-ROM Hardware CD-ROM Envoy External 8X CD-ROM Hardware CD-ROM External 6X CD-ROM Hardware CD-ROM External 8X CD-ROM Hardware CD-ROM Internal 6X CD-ROM Hardware CD-ROM Internal 8X CD-ROM Hardware CD-ROM Hardware Desktop PCs Sentinel Financial Hardware Desktop PCs Sentinel Multimedia . . . Software/Other Operating Systems Unix/Windows 1-user pack Software/Other Operating Systems Unix/Windows 5-user pack Software/Other Operating Systems Software/Other
Example 11-8 Script Using QDRs in the FAMILYREL Clause of OLAP_TABLE
CREATE OR REPLACE TYPE awproduct_row AS OBJECT ( class VARCHAR2(50), family VARCHAR2(50), item VARCHAR2(50)); / CREATE TYPE awproduct_table AS TABLE OF awproduct_row; / CREATE OR REPLACE VIEW awproduct_view AS SELECT class, family, item FROM TABLE(OLAP_TABLE( 'global DURATION QUERY', 'awproduct_table', '', 'DIMENSION product WITH HIERARCHY product_parentrel FAMILYREL class, family, item FROM product_familyrel(product_levellist ''CLASS''), product_familyrel(product_levellist ''FAMILY''), product_familyrel(product_levellist ''ITEM'') LABEL product_short_description')); SQL> SELECT * FROM awproduct_view ORDER BY by class, family, item; CLASS FAMILY ITEM -------------- ---------------- ------------------------ Hardware CD-ROM Envoy External 6X CD-ROM Hardware CD-ROM Envoy External 8X CD-ROM Hardware CD-ROM External 6X CD-ROM Hardware CD-ROM External 8X CD-ROM Hardware CD-ROM Internal 6X CD-ROM Hardware CD-ROM Internal 8X CD-ROM Hardware CD-ROM Hardware Desktop PCs Sentinel Financial Hardware Desktop PCs Sentinel Multimedia . . . Software/Other Operating Systems Unix/Windows 1-user pack Software/Other Operating Systems Unix/Windows 5-user pack Software/Other Operating Systems Software/Other
Oracle Express Server applications that are being revised for use with Oracle Database can use an OLAP DML FETCH
command instead of a limit map to map workspace objects to relational columns.
The FETCH
command is supplied in the third parameter of OLAP_TABLE
, which specifies a single OLAP DML command. See "OLAP Command Parameter".
The script shown in Example 11-9 fetches data from two variables (SALES
and COST
) in the GLOBAL
analytic workspace, and calculates two custom measures (COST_PRIOR_PERIOD
and PROFIT
). This example also shows the use of OLAP_TABLE
directly by an application, without creating a view.
Important:
TheFETCH
statement in Example 11-9 is formatted with indentation for readability. In reality, the entire FETCH
statement must be entered on one line, without line breaks or continuation characters.Example 11-9 Script Using FETCH with OLAP_TABLE
CREATE TYPE measure_row AS OBJECT ( time VARCHAR2(20), geography VARCHAR2(30), product VARCHAR2(30), channel VARCHAR2(30), sales NUMBER(16), cost NUMBER(16), cost_prior_period NUMBER(16), profit NUMBER(16)); / CREATE TYPE measure_table AS TABLE OF measure_row; / SELECT time, geography, product, channel, sales, cost, cost_prior_period, profit FROM TABLE(OLAP_TABLE( 'xademo DURATION SESSION', 'measure_table', 'FETCH time, geography, product, channel, analytic_cube_f.sales, analytic_cube_f.costs, LAG(analytic_cube_f.costs, 1, time, LEVELREL time_member_levelrel), analytic_cube_f.sales - analytic_cube_f.costs', '')) WHERE channel = 'STANDARD_2.TOTALCHANNEL' AND product = 'L1.TOTALPROD' AND geography = 'L1.WORLD' ORDER BY time;
This SQL SELECT
statement returns the following result set:
TIME GEOGRAPHY PRODUCT CHANNEL SALES COST COST_PRIOR_PERIOD PROFIT --------- --------- ------------ ----------------------- --------- --------- ------------------ -------- L1.1996 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 118247112 2490243 115756869 L1.1997 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 46412113 1078031 2490243 45334082 L2.Q1.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 26084848 560379 25524469 L2.Q1.97 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 26501765 615399 560379 25886367 L2.Q2.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 30468054 649004 615399 29819049 L2.Q2.97 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 19910347 462632 649004 19447715 L2.Q3.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 27781702 582693 462632 27199009 L2.Q4.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 33912508 698166 582693 33214342 L3.APR96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 8859808 188851 8670957 . . . 27 rows selected.
The OLAP_TABLE
function returns multidimensional data in an analytic workspace as a logical table.
The order in which OLAP_TABLE
processes information specified in its input parameters is described in "Order of Processing in OLAP_TABLE".
Syntax
OLAP_TABLE( analytic_workspace IN VARCHAR2, table_object IN VARCHAR2, olap_command IN VARCHAR2, limit_map1 IN VARCHAR2, limit_map2 IN VARCHAR2, . . . limit_map8 IN VARCHAR2) RETURN TYPE;
Parameters
Table 11-2 OLAP_TABLE Function Parameters
Parameter | Description |
---|---|
analytic_workspace |
The name of the analytic workspace with the source data. This parameter also specifies how to attach the workspace to your session. See "Analytic Workspace Parameter". |
table_object |
The name of a table of objects that has been defined to structure the multidimensional data in tabular form. See "Table Object Parameter". |
olap_command |
An optional OLAP DML command. See "OLAP Command Parameter". |
limit_map1...8 |
A keyword-based map that identifies the source objects in the analytic workspace and the target columns in a table of objects. You can define up to eight limit maps in order to circumvent the 4000 byte |
Returns
A table type whose rows are objects (ADTs) that identify the selected workspace data. See "Logical Tables".
The first parameter of the OLAP_TABLE
function provides the name of the analytic workspace where the source data is stored. It also specifies how long the analytic workspace will be attached to your OLAP session, which opens on your first call to OLAP_TABLE
.
This parameter is always required by OLAP_TABLE
.
The syntax of this parameter is:
'[owner.]aw_name DURATION QUERY | SESSION'
For example:
'olapuser.xademo DURATION SESSION'
Specify owner whenever you are creating views that will be accessed by other users. Otherwise, you can omit the owner if you own the analytic workspace. It is required only when you are logged in under a different user name than the owner.
Attaches an analytic workspace for the duration of a single query. Use QUERY
only when you need to see updates to the analytic workspace made in other sessions.
SESSION
attaches an analytic workspace and keeps it attached at the end of the query. It provides better performance than QUERY
because it keeps the OLAP session open. This performance difference is significant when the function is called without either a table_object parameter or AS
clauses in the limit map; in this case, the OLAP_TABLE
function must determine the appropriate table definition. See "Using OLAP_TABLE With Automatic ADTs".
The second parameter identifies the name of a predefined table of objects, as described in "Using OLAP_TABLE With Predefined ADTs".
This parameter is optional. Omit this parameter if you are using automatic ADTs.
The syntax of this parameter is:
'table_name'
For example:
'product_dim_tbl'
When you specify the table_name parameter, the column data types for the returned data are predefined. In this case you cannot use AS
clauses in the limit map.
When you omit the table_name parameter, the column data types for the returned data are generated at runtime. You can either provide the target data types with AS
clauses in the limit map, or you can use the default data types described in Table 11-1, "Default Data Type Conversions". See "Using OLAP_TABLE With Automatic ADTs".
The third parameter of the OLAP_TABLE
function is a single OLAP DML command. If you want to execute more than one command, then you must create a program in your analytic workspace and call the program in this parameter. The power and flexibility of this parameter comes from its ability to process virtually any data manipulation commands available in the OLAP DML.
The order in which OLAP_TABLE
processes the olap_command parameter is specified in "Order of Processing in OLAP_TABLE".
The syntax of this parameter is:
'olap_command'
There are two distinct ways of using the olap_command parameter:
To make changes in the workspace session immediately before the data is fetched (after all the limits have been applied)
To specify the source data directly instead of using a limit map
Both methods are described in the following sections.
Using olap_command with a Limit Map
You may want your application to modify the analytic workspace on the fly during the execution of OLAP_TABLE
.
A common use of the olap_command parameter is to limit one or more dimensions. If you limit any of the dimensions that have INHIERARCHY
clauses in the limit map, then the status of those dimensions is changed only during execution of this call to OLAP_TABLE
; the limits do not affect the rest of your OLAP session. However, other commands (for example, commands that limit dimensions not referenced with INHIERARCHY
clauses) can affect your session.
If you want a limit on a dimension in the limit map to stay in effect for the rest of your session, and not just during the command, specify it in the PREDMLCMD
clause of the limit map or specify an OLAP_CONDITION
function in the SQL SELECT
statement.
The following is an example of a LIMIT
command in the olap_command parameter.
'LIMIT product TO product_member_levelrel ''L2'''
See Also:
Chapter 6, "OLAP_CONDITION".Using FETCH in the olap_command Parameter
If you specify an OLAP DML FETCH
command in the olap_command parameter, OLAP_TABLE
uses it, instead of the instructions in the limit map, to fetch the source data for the table object. Because of this usage, the olap_command parameter is sometimes referred to as the data map. In general, you should not specify a limit map if you specify a FETCH
command.
Note:
Normally, you should only use theFETCH
command with OLAP_TABLE
if you are upgrading an Express application that used the FETCH
command for SNAPI. If you are upgrading, note that the full syntax is the same in Oracle as in Express 6.3. You can use the same FETCH
commands in OLAP_TABLE
that you used previously in SNAPI. The syntax of the FETCH
command is documented in the Oracle OLAP DML ReferenceFETCH
specifies explicitly how analytic workspace data is mapped to a table object. The basic syntax is:
FETCH expression...
Enter one expression for each target column, listing the expressions in the same order they appear in the row definition. Separate expressions with spaces or commas.You must enter the entire statement on one line, without line breaks or continuation marks of any type.
See Also:
"Using OLAP_TABLE with the FETCH Command".The fourth (and last) parameter of the OLAP_TABLE
function maps workspace objects to relational columns and identifies the role of each one. See "Limit Maps".
The limit map can also specify special instructions to be executed by OLAP_TABLE
. For example: It can cause an OLAP DML command to execute before or after the limit map is processed; it can specify a ROW2CELL
column for the OLAP_CONDITION
and OLAP_EXPRESSION
functions. (See Chapter 6 and Chapter 7.)
The order in which OLAP_TABLE
processes information in the limit map is specified in "Order of Processing in OLAP_TABLE".
The limit map parameter is generally a required parameter. It can only be omitted when you specify a FETCH
command in the olap_command parameter. See "OLAP Command Parameter".
You can supply the entire text of the limit map as a parameter to OLAP_TABLE
, or you can store all or part of the limit map in a text variable in the analytic workspace and reference it using ampersand substitution. For example, the following OLAP_TABLE
query uses a limit map stored in a variable called limitmapvar
in the GLOBAL
analytic workspace of the GLOBAL_AW
schema.
SELECT * FROM TABLE(OLAP_TABLE( 'global_aw.global DURATION SESSION', '', '', '&(global_aw.global!limitmapvar)');
If you supply the limit map as text within the call to OLAP_TABLE
, then it has a maximum length of 4000 characters, which is imposed by PL/SQL. If you store the limit map in the analytic workspace, then the limit map has no maximum length.
The syntax of the limit map has numerous clauses, primarily for defining dimension hierarchies. Pay close attention to the presence or absence of commas, since syntax errors will prevent your limit map from being parsed. The syntax of the limit map is summarized in Example 11-10. Individual syntax components are described in the following sections.
Note:
Several objects must be predefined within the workspace to support the mapping of dimension hierarchies in the limit map. These objects are already defined in standard form workspaces. If the workspace does not conform to standard form, you may need to prepare the workspace by defining objects such as:a parent relation, which identifies the parent of each dimension member within a hierarchy.
a hierarchy dimension, which lists the hierarchies of a dimension.
an inhierarchy variable or valueset, which specifies which dimension members belong to each level of a hierarchy.
a grouping ID variable, which identifies the depth within a hierarchy of each dimension member.
a family relation, which provides the full parentage of each dimension member in a hierarchy.
a level dimension, which lists the levels of a dimension.
Instructions for creating these workspace objects are provided in the Oracle OLAP Application Developer's Guide.
Example 11-10 Syntax of an OLAP_TABLE Limit Map
'[MEASURE column [AS datatype] FROM {measure | AW_EXPR expression}] . . DIMENSION [column [AS datatype] FROM] dimension [WITH [HIERARCHY [column [AS datatype] FROM] parent_relation [(hierarchy_dimension ''hierarchy_name'')] [INHIERARCHY inhierarchy_obj] [GID column [AS datatype] FROM gid_variable] [PARENTGID column [AS datatype] FROM gid_variable] [FAMILYREL column1 [AS datatype], column2 [AS datatype], ... columnn [AS datatype] FROM {expression1, expression2, ... expressionn | family_relation USING level_dimension } [LABEL label_variable]] [HATTRIBUTE column [AS datatype] FROM hier_attribute_variable] . . ] [ATTRIBUTE column [AS datatype] FROM attribute_variable] . . ] [ROW2CELL column] [LOOP composite_dimension] [PREDMLCMD olap_command] [POSTDMLCMD olap_command]'
Where:
column is the name of a column in the target table.
datatype is the data type of column.
measure is a measure in the analytic workspace.
expression is a formula or qualified data reference for objects in the analytic workspace.
dimension is a dimension in the analytic workspace.
parent_relation is a self-relation in the analytic workspace that defines the hierarchies for dimension.
hierarchy_dimension is a dimension in the analytic workspace that contains the names of the hierarchies for dimension.
hierarchy_name is a member of hierarchy_dimension.
inhierarchy_obj is a variable or valueset in the analytic workspace that identifies which dimension members are in each level of the hierarchy.
gid_variable is a variable in the analytic workspace that contains the grouping ID of each dimension member in the hierarchy.
family_relation is a self-relation that provides the full parentage of each dimension member in the hierarchy.
level_dimension is a dimension in the analytic workspace that contains the names of the levels for the hierarchy.
label_variable is a variable in the analytic workspace that contains descriptive text values for dimension.
hier_attribute_variable is a variable in the analytic workspace that contains attribute values for hierarchy_name.
attribute_variable is a variable in the analytic workspace that contains attribute values for dimension.
composite_dimension is a composite dimension used in the definition of measure.
olap_command is an OLAP DML command.
The MEASURE
clause maps a variable, formula, or relation in the analytic workspace to a column in the target table.
MEASURE column [AS datatype] FROM {measure | AW_EXPR expression}
The AS
subclause specifies the data type of the target column. You can specify an AS
subclause when the table of objects has not been predefined. See "Using OLAP_TABLE With Automatic ADTs".
In the FROM
subclause, you can either specify the name of a workspace measure or an OLAP expression that evaluates to a measure. For example:
AW_EXPR analytic_cube_sales - analytic_cube_cost
or
AW_EXPR LOGDIF(analytic_cube_sales, 1, time, LEVELREL time.lvlrel)
You can list any number of MEASURE
clauses. This clause is optional when, for example, you wish to create a dimension view.
The DIMENSION
clause identifies a dimension or conjoint in the analytic workspace that dimensions one or more measures or attributes, or provides the dimension members for one or more hierarchies in the limit map.
DIMENSION [column [AS datatype] FROM] dimension ....
The column subclause is optional when you do not want the dimension members themselves to be represented in the table. In this case, you should include a dimension attribute that can be used for data selection.
For a description of the AS
subclause, see "Limit Map: MEASURE Clause".
Every limit map should have at least one DIMENSION
clause. If the limit map contains MEASURE
clauses, then it should also contain a single DIMENSION
clause for each dimension of the measures, unless a dimension is being limited to a single value. If the measures are dimensioned by a composite, then you must identify each dimension in the composite with a DIMENSION
clause. For the best performance when fetching a large result set, identify the composite in a LOOP
clause. See "Limit Map: LOOP Clause".
A dimension can be named in only one DIMENSION
clause. Subclauses of the DIMENSION
clause identify the dimension hierarchies and attributes.
The WITH
subclause introduces a HIERARCHY
or ATTRIBUTE
subclause. If you do not specify hierarchies or attributes, then omit the WITH
keyword. If you specify both hierarchies and attributes, then precede them with a single WITH
keyword. The syntax of the WITH
clause is included in Example 11-10, "Syntax of an OLAP_TABLE Limit Map". It is shown without the rest of the limit map syntax in Example 11-11.
Example 11-11 WITH Subclause of Limit Map DIMENSION Clause
[WITH [HIERARCHY [column [AS datatype] FROM] parent_relation [(hierarchy_dimension ''hierarchy_name'')] [INHIERARCHY inhierarchy_obj] [GID column [AS datatype] FROM gid_variable] [PARENTGID column [AS datatype] FROM gid_variable] [FAMILYREL column1 [AS datatype], column2 [AS datatype], ... columnn [AS datatype] FROM {expression1, expression2,... expressionn | family_relation USING level_dimension} [LABEL label_variable]] [HATTRIBUTE column [AS datatype] FROM hier_attribute_variable] ... ] [ATTRIBUTE column [AS datatype] FROM attribute_variable] ... ]
The HIERARCHY
subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for the dimension.
HIERARCHY [column [AS datatype] FROM] parent_relation [(hierarchy_dimension ''hierarchy_name'')]...
For a description of the column subclause, see "Limit Map: DIMENSION Clause".
If the dimension has more than one hierarchy, specify a hierarchy_dimension phrase. hierarchy_dimension identifies a dimension in the analytic workspace which holds the names of the hierarchies for this dimension. hierarchy_name is a member of hierarchy_dimension. The hierarchy dimension is limited to hierarchy_name for all workspace objects that are referenced in subsequent subclauses for this hierarchy (that is, INHIERARCHY
, GID
, PARENTGID
, FAMILYREL
, and HATTRIBUTE
).
To include multiple hierarchies for the dimension, specify a HIERARCHY
subclause for each one.
The HIERARCHY
subclause is optional when the dimension does not have a hierarchy, or when the status of the dimension has been limited to a single level of the hierarchy.
The keywords in the HIERARCHY
subclause are described as follows:
INHIERARCHY
inhierarchy_obj
The INHIERARCHY
subclause identifies a boolean variable or a valueset in the analytic workspace that identifies the dimension members in each level of the hierarchy. It is required when there are members of the dimension that are omitted from the hierarchy. It is good practice to include an INHIERARCHY
subclause, because OLAP_TABLE
saves the status of all dimensions with INHIERARCHY
subclauses during the processing of the limit map.
GID
column [AS
datatype] FROM
gid_variable
The GID
subclause maps an integer variable in the analytic workspace, which contains the grouping ID for each dimension member, to a column in the target table. The grouping ID variable is populated by the OLAP DML GROUPINGID
command.
For a description of the AS
subclause, see "Limit Map: MEASURE Clause".
The GID
subclause is required for Java applications that use the OLAP API.
PARENTGID
column [AS
datatype] FROM
gid_variable
The PARENTGID
subclause calculates the grouping IDs for the parent relation using the GID variable in the analytic workspace. The parent GIDs are not stored in a workspace object. Instead, you specify the same GID variable for the PARENTGID
clause that you used in the GID
clause.
For a description of the AS
subclause, see "Limit Map: MEASURE Clause".
The PARENTGID
clause is recommended for Java applications that use the OLAP API.
FAMILYREL
column1 [AS
datatype], column2 [AS
datatype], ... columnn [AS
datatype] FROM
{expression1, expression2, ... expressionn| family_relation USING
level_dimension } [LABEL
label_variable]
The FAMILYREL
subclause is used primarily to map a family relation in the analytic workspace to multiple columns in the target table. List the columns in the order of level_dimension (a dimension in the analytic workspace that holds the names of all the levels for the dimension). If you do not want a particular level included, then specify null for the target column. For a description of the AS
subclause, see "Limit Map: MEASURE Clause".
The tabular data resulting from a FAMILYREL
clause is in rollup form, in which each level of the hierarchy is represented in a separate column, and the full parentage of each dimension member is identified within the row. See "Example: Creating Views in Rollup Form".
The LABEL
keyword identifies a text attribute that provides more meaningful names for the dimension members.
You can use multiple FAMILYREL
clauses for each hierarchy.
HATTRIBUTE
column [AS
datatype] FROM
hier_attribute_variable
The HATTRIBUTE
subclause maps a hierarchy-specific attribute variable, dimensioned by hierarchy_dimension in the analytic workspace, to a column in the target table.
The ATTRIBUTE
subclause maps an attribute variable in the analytic workspace to a column in the target table.
ATTRIBUTE column [AS datatype] FROM attribute_variable
If attribute_variable has multiple dimensions, then values are mapped for all members of dimension, but only for the first member in the current status of additional dimensions. For example, if your attributes have a language dimension, then you must set the status of that dimension to a particular language. You can set the status of dimensions in a PREDMLCMD
clause. See "Limit Map: PREDMLCMD Clause".
The ROW2CELL
clause creates a RAW
column, between 16 and 32 characters wide, in the target table and populates it with information that is used by the OLAP_EXPRESSION
functions. The OLAP_CONDITION
function also uses the ROW2CELL
column. Specify a ROW2CELL
column when creating a view that will be used by these functions. See Chapter 6 and Chapter 7.
ROW2CELL column
The LOOP
clause identifies a single named composite that dimensions one or more measures specified in the limit map. It improves performance when fetching a large result set; however, it can slow the retrieval of a small number of values.
LOOP sparse_dimension
The PREDMLCMD
clause specifies an OLAP DML command that is executed before the data is fetched from the analytic workspace into the target table. It can be used, for example, to execute an OLAP model or forecast whose results will be fetched into the table. The results of the command are in effect during execution of the limit map, and continue into your session after execution of OLAP_TABLE
is complete. See "Order of Processing in OLAP_TABLE".
PREDMLCMD olap_command
The POSTDMLCMD
clauses specifies an OLAP DML command that is executed after the data is fetched from the analytic workspace into the target table. It can be used, for example, to delete objects or data that were created by commands in the PREDMLCMD
clause, or to restore the dimension status that was changed in a PREDMLCMD
clause. See "Order of Processing in OLAP_TABLE".
POSTDMLCMD olap_command
The following list identifies the order in which the OLAP_TABLE
function processes instructions in the limit map that can change the status of dimensions in the analytic workspace.
Execute any OLAP DML command specified in the PREDMLCMD
parameter of the limit map.
Save the current status of all dimensions in the limit map so that it can be restored later (PUSH
status).
Keep in status only those dimension members specified by INHIERARCHY
subclauses in the limit map (LIMIT KEEP
).
Within the status set during step 3, keep only those dimension members that satisfy the WHERE
clause of the SQL SELECT
statement containing the OLAP_TABLE
function (LIMIT KEEP
).
Execute any OLAP DML command specified in the olap_command parameter of the OLAP_TABLE
function. (If olap_command includes a FETCH
, fetch the data.)
Fetch the data (unless a FETCH
command was specified in the olap_command parameter).
Restore the status of all dimensions in the limit map (POP
status).
Execute any OLAP DML command specified in the POSTDMLCMD
parameter of the limit map.