OLAP_TABLE is a SQL function that extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table.
See Also:
"Creating Relational Views Using OLAP_TABLE" and "Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements".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".
OLAP_TABLE is the fundamental mechanism in the database for querying an analytic workspace. Within a SQL statement, you can specify an OLAP_TABLE function call wherever you would provide the name of a table or view.
OLAP_TABLE returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE.
Note:
You cannot execute this function from within the OLAP Worksheet. You must execute it in a SQL tool such as SQL Worksheet.A table type whose rows are objects (ADTs) that identify the selected workspace data. See "Creating Relational Views Using OLAP_TABLE".
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;
Provides the name of the analytic workspace where the source data is stored. It also specifies how long the analytic workspace is 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'
owner
Specify owner whenever you are creating views to 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.
QUERY
Attaches an analytic workspace for the duration of a single query. Use QUERY only when you must see updates to the analytic workspace made in other sessions.
SESSION
Attaches an analytic workspace and keeps it attached after 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 name of a predefined table of objects, as described in "Using OLAP_TABLE With Predefined ADTs".
This parameter is optional. Omit this parameter when 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 run time. 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 A-1, "Default Data Type Conversions". See "Using OLAP_TABLE With Automatic ADTs".
A single OLAP DML command. To execute multiple commands, 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) as described in "Using FETCH in the olap_command Parameter".
To specify the source data directly instead of using a limit map as described in "Using olap_command with a Limit Map".
Maps workspace objects to relational columns and identifies the role of each one. See "Creating Relational Views Using OLAP_TABLE".
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 "OLAP_CONDITION" and "OLAP_EXPRESSION".)
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 the discussion of olap_command.
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 MYAW analytic workspace of the MYAW_AW schema.
SELECT * FROM TABLE(OLAP_TABLE(
'myaw_aw.myaw DURATION SESSION',
'',
'',
'&(myaw_aw.myaw!limitmapvar)');
If you supply the limit map as text within the call to OLAP_TABLE, then it has a maximum length of 4,000 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.
Note:
Several analytic workspace objects must be predefined within the workspace to support the mapping of dimension hierarchies in the limit map. For more information, see "Required OLAP DML Objects".The syntax of the limit map has numerous clauses, primarily for defining dimension hierarchies. Pay close attention to the presence or absence of commas, because syntax errors prevents your limit map from being parsed.
'[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_relation] [PARENTGID column [AS datatype] FROM gid_relation] [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 loop-clause] [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 variable, formula, or relation 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. See "Parentrel Relation" for more information.
hierarchy_dimension is a dimension in the analytic workspace that contains the names of the hierarchies for dimension. See "Hierlist Dimension" for more information
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. See "Inhier Valueset or Variable" for more information
gid_relation is a relation in the analytic workspace that contains the grouping ID of each dimension member in the hierarchy. See "Gidrel Relation" for more information
family_relation is a self-relation that provides the full parentage of each dimension member in the hierarchy. See "Familyrel Relation" for more information
level_dimension is a dimension in the analytic workspace that contains the names of the levels for the hierarchy. See "Levellist Dimension" for more information
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.
loop_clause specifies how Oracle OLAP determines how it loops through data values and what rows to create in the relational table. When you exclude a LOOP clause, Oracle OLAP loops through all of the data values that are identified by the dimensions in the DIMENSION clauses in the limit map.
For the complete syntax of the LOOP clause see "LOOP Clause".
olap_command is an OLAP DML command.
Detailed syntax for each of the clauses of the limit-map parameter follows.
MEASURE Clause
The MEASURE clause maps a variable, formula, or relation in the analytic workspace to a column in the target table. You can list any number of MEASURE clauses. This clause is optional when, for example, you want to create a dimension view.
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 variable, formula, or relation or an OLAP expression that evaluates to one of these objects. For example:
AW_EXPR analytic_cube_sales - analytic_cube_cost
or
AW_EXPR LOGDIF(analytic_cube_sales, 1, time, LEVELREL time.lvlrel)
DIMENSION Clause
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.
The column subclause is optional when you do not want the dimension members themselves to be represented in the table. In this case, include a dimension attribute that can be used for data selection.
For a description of the AS subclause, see "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 "LOOP Clause".
A dimension can be named in only one DIMENSION clause. Subclauses of the DIMENSION clause identify the dimension hierarchies and attributes.
WITH Subclause for 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.
WITH HIERARCHY Subclause
The HIERARCHY subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for the dimension. See "Parentrel Relation" for more information,
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. When a dimension has multiple hierarchies, specify a HIERARCHY subclause for each one and specify a hierarchy_dimension phrase. The hierarchy_dimension identifies a dimension in the analytic workspace which holds the names of the hierarchies for this dimension. See "Hierlist Dimension" for more information.
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).
For a description of the column subclause, see "DIMENSION Clause ".
The keywords in the HIERARCHY subclause are described as follows:
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. See "Inhier Valueset or Variable" for more information.
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. It is required when there are members of the dimension that are omitted from the hierarchy.
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 command. See "Gidrel Relation" for more information.
The GID subclause is required for Java applications that use the OLAP API.
For a description of the AS subclause, see "MEASURE Clause".
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 an analytic workspace object. Instead, you specify the same GID variable for the PARENTGID clause that you used in the GID clause. The PARENTGID clause is recommended for Java applications that use the OLAP API.
For a description of the AS subclause, see "MEASURE Clause".
The FAMILYREL subclause is used primarily to map a family relation in the analytic workspace to multiple columns in the target table. See "Familyrel Relation" for more information. You can use multiple FAMILYREL clauses for each hierarchy.
List the columns in the order of level_dimension which is a dimension in the analytic workspace that holds the names of all the levels for the dimension. See "Levellist Dimension" for more information.
If you do not want a particular level included, then specify null for the target column. For a description of the AS subclause, see "MEASURE Clause".
The LABEL keyword identifies a text attribute that provides more meaningful names for the dimension members.
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 A-7, "Script for a Rollup View of Products Using OLAP_TABLE".
The HATTRIBUTE subclause maps a hierarchy-specific attribute variable, dimensioned by hierarchy_dimension in the analytic workspace, to a column in the target table.
WITH ATTRIBUTE Subclause
The ATTRIBUTE subclause maps an attribute variable in the analytic workspace to a column in the target table.
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.
ROW2CELL 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 to be used by these functions. See "Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements" and "Adding Calculated Columns to the Relational View".
LOOP Clause
The LOOP clause specifies how Oracle OLAP loops through the data when retrieving values. When you omit a LOOP clause in a limit map, Oracle OLAP loops uses the DIMENSION clauses in that limit map to determine what values that Oracle OLAP loops over. Oracle OLAP loops over only those tuples that identify measure cells that do not contain NA or null.When you include a LOOP clause, you specify one of the following types of subclauses: the optimized_subclause or the union_subclause.
The optimized_subclause specifies that Oracle OLAP automatically create the union_subclause by which it loops through the data. The union_subclause created never includes any DENSE phrases. Consequently, when you specify the optimized_subclause, Oracle OLAP loops over only those tuples that identify measure cells that do not contain NA or null. The optimized_subclause has the following syntax.
OPTIMIZED [MEASURES]
where the optional MEASURES keyword specifies that after identifying the tuples to loop through, Oracle OLAP remove any values that are dimension values that are not dimensions of the objects identified in the MEASURES clauses of the limit map.
Tip:
You can use the$LOOP_AGGMAP, $LOOP_DENSE, and $LOOP_VAR properties, to specify more information as to how OLAP_TABLE loops over a formula.The union_subclause specifies exactly how Oracle OLAP determines which base dimensions it uses to identify what data values to loop through. For a successful union to occur, the objects that are referenced in this subclause must have the same base dimensions. The union_subclause has the syntax show below.
[ignore_phrase] [dense_phrase] UNION ({aggmap_phrase | list_phrase }...)]
where:
aggmap_phrase has the following syntax which specifies how Oracle OLAP loops through the values of an aggregated variable.
AGGMAP (ignore_phrase] [dense_phrase] aggmap {variable | dimension_list})
list_phrase has the following syntax which specifies how Oracle OLAP loops through the values of a composite, partition template, or dimension. It has the following syntax.
LIST ([ignore_phrase] [dense_phrase] dimension_list)
ignore_phrase — Specifies the dimension values that you do not want Oracle OLAP to loop over. It has the following syntax.
IGNORE (ignore_list )
For ignore_list you can specify one or more of the following separated by commas.
dimension_name valueset_name COMPLEMENT (valueset_name)
Within a single ignore_list you cannot specify a dimension in multiple ways. In other words, you cannot specify both a dimension and a valueset for that same dimension; you cannot specify two valuesets for the same dimension.
Note:
The result of specifying IGNOR valueset-name is similar to specifying LIMIT REMOVE valueset-name (see LIMIT command).dense_phrase — Specifies values that Oracle OLAP loops over even when the measure cells identified by those values contain NA or null. In other words, specifying a dimension in a DENSE phrase is similar to requesting a relational outer join. Typically, you include dimension values in a DENSE phrase to perform time-series processing (for example, to lag over time). A DENSE phrase has the following syntax.
DENSE (dense_list )
For dense_list you can specify one or more of the following separated by commas.
dimension_name valueset_name COMPLEMENT (valueset_name)
Within a single dense_list you cannot specify a dimension in multiple ways. In other words, you cannot specify both a dimension and a valueset for that same dimension; you cannot specify two valuesets for the same dimension.
However, you can have one valueset for a dimension in the outer DENSE phrase and another valueset for the same dimension in an inner DENSE phrase. If a dimension or a valueset of a dimension is specified in both an inner and outer DENSE phrase, Oracle OLAP loops densely over the union of the dense regions. If the dimension, itself, appears in either place, Oracle OLAP loops densely over the whole dimension.
aggmap — The name of an aggmap object. When you specify only the name of an aggmap object, Oracle OLAP uses the values in the PRECOMPUTE phrases of the aggmap to identify the values to loop
variable — The name of the variable aggregated by aggmap_name.
dimension_list — A list of one or more composites, partition templates, or dimensions.
PREDMLCMD Clause
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".
POSTDMLCMD Clause
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" .
OLAP_TABLE uses a limit map to map dimensions and measures defined in an analytic workspace to columns in a logical table. The limit map combines with the WHERE clause of a SQL SELECT statement to generate a series of OLAP DML LIMIT commands that are executed in the analytic workspace.
OLAP_TABLE can use a limit map with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at run time.
See Also:
The discussion of the limit_map parameter.The logical table populated by OLAP_TABLE is actually a table type whose rows are user-defined object types, also known as Abstract Data Types or ADTs.
A user-defined object type is composed of attributes, which are equivalent to the columns of a table. The basic syntax for defining a row is as follows.
CREATE TYPE object_name AS OBJECT ( attribute1 datatype, attribute2 datatype, attributen datatype);
A table type is a collection of object types; this collection is equivalent to the rows of a table. The basic syntax for creating a table type is as follows.
CREATE TYPE table_name AS TABLE OF object_name;
See Also:
Oracle Database Object-Relational Developer's Guide for information about object types
CREATE TYPE in Oracle Database SQL Language Reference
Using OLAP_TABLE With Predefined ADTs
You can predefine the table of objects or generate it dynamically. When you create the table type in advance, it is available in the database for use by any invocation of OLAP_TABLE. Queries that use predefined objects typically perform better than queries that dynamically generate the objects. See "Using OLAP_TABLE With Predefined ADTs".
Using OLAP_TABLE With Automatic ADTs
If you do not supply the name of a table type as an argument, OLAP_TABLE uses information in the limit map to generate the logical table automatically. In this case, the table type is only available at run time within the context of the calling SQL SELECT statement. See "Using OLAP_TABLE With Automatic ADTs".
When automatically generating ADTs, OLAP_TABLE uses default relational data types for the target columns unless you override them with AS clauses in the limit map. The default data type conversions used by OLAP_TABLE are described in Table A-1.
Table A-1 Default Data Type Conversions
| Analytic Workspace Data Type | SQL Data Type |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other |
|
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:
"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, do not specify a limit map if you specify a FETCH command.
Note:
Normally, use theFETCH command with OLAP_TABLE only 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. For the syntax of the FETCH command, see "FETCH".FETCH 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 FETCH in the olap_command Parameter".Order of Processing in OLAP_TABLE
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 an OLAP DML 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.
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 section show how to create views using a variety of different formats.
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".Example A-5 Script for an Embedded Total Dimension View Using OLAP_TABLE
This example shows the PL/SQL script used to create an embedded total view of the TIME dimension in an analytic workspace named MYAW. This view is similar to the view in Example A-2, "Sample View of the TIME Dimension Using Predefined ADTs", 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".
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(
'myaw 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 that you must be sure to verify that you have created the views correctly by issuing SELECT statements against them. Only at that time do any errors in the call to OLAP_TABLE appear.
Example A-6 Creating a View of an Embedded Total Measure Using OLAP_TABLE
In a star schema, a separate measure view is needed with columns that can be joined to each of the dimension views. This example shows the PL/SQL script used to create a measure view with a column populated by a ROW2CELL clause to support custom measures. For information on ROW2CELL, "ROW2CELL Clause".
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(
'myaw 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
Example A-7 Script for a Rollup View of Products Using OLAP_TABLE
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. For information on FAMILYREL, see "WITH HIERARCHY Subclause".
This example 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 A-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 A-8, "Script Using QDRs in the FAMILYREL Clause of OLAP_TABLE" shows the alternate syntax for the FAMILYREL clause, which uses QDRs to identify exactly which columns are mapped from the family relation.
The limit maps in Example A-7 and Example A-8 generate identical views.
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(
'myaw 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 A-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(
'myaw 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
Example A-9 Script Using FETCH with OLAP_TABLE
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.
The script that follows fetches data from two variables (SALES and COST) in an analytic workspace named MYAW, 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.
Note that the FETCH statement in the following example is formatted with indentation for readability. In reality, the entire FETCH statement must be entered on one line, without line breaks or continuation characters
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.