Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The DBMS_AW
package provides procedures and functions for performing operations within analytic workspaces. With DBMS_AW
, you can:
Embed OLAP DML commands in SQL statements
Write queries that return the data resulting from calculations within the workspace
Obtain information to help you manage aggregate data within the workspace
See Also:
|
This chapter includes the following topics:
With the DBMS_AW
package you can perform the full range of OLAP processing within analytic workspaces. You can import data from legacy workspaces, relational tables, or flat files. You can define OLAP objects and perform complex calculations.
Note: If you use theDBMS_AW package to create analytic workspaces from scratch, you may not be able to use OLAP utilities that require standard form. You will have to develop your own relational views of the workspaces using the OLAP_TABLE function. To make the workspaces accessible to the OLAP API, you will have to create your own metadata for the views using the CWM2 packages. |
The DBMS_AW
package provides several procedures for executing ad hoc OLAP DML commands. Using the EXECUTE
or INTERP_SILENT
procedures or the INTERP
or INTERCLOB
functions, you can execute a single OLAP DML command or a series of commands separated by semicolons.
Which procedures you use will depend on how you want to direct output and on the size of the input and output buffers. For example, the EXECUTE
procedure directs output to a printer buffer, the INTERP_SILENT
procedure suppresses output, and the INTERP
function returns the session log.
The SQL processor evaluates the embedded OLAP DML commands, either in whole or in part, before sending them to Oracle OLAP for processing. Follow these guidelines when formatting the OLAP DML commands in the olap-commands
parameter of DBMS_AW
procedures:
Wherever you would normally use single quote ('
) in an OLAP DML command, use two single quotes (''
). The SQL processor strips one of the single quotes before it sends the OLAP DML command to Oracle OLAP.
In the OLAP DML, a double quote ("
) indicates the beginning of a comment.
The OLAP_EXPRESSION
function in the DBMS_AW
package dynamically executes a single-row numeric function in an analytic workspace and returns the results. You can embed OLAP_EXPRESSION
functions in the WHERE
and ORDER BY
clauses of SELECT
statements.
You can use variants of OLAP_EXPRESSION
to calculate text, date, or boolean expressions.
The following script was used to create a view named MEASURE_VIEW
, which is used in Example 21-1 and Example 21-2 to illustrate the use of OLAP_EXPRESSION
.
CREATE TYPE measure_row AS OBJECT ( time VARCHAR2(12), geography VARCHAR2(30), product VARCHAR2(30), channel VARCHAR2(30), sales NUMBER(16), cost NUMBER(16), promotions NUMBER(16), quota NUMBER(16), units NUMBER(16), r2c RAW(32)); / CREATE TYPE measure_table AS TABLE OF measure_row; / CREATE OR REPLACE VIEW measure_view AS SELECT sales, cost, promotions, quota, units, time, geography, product, channel, r2c FROM TABLE(CAST(OLAP_TABLE( 'xademo DURATION SESSION', 'measure_table', '', 'MEASURE sales FROM analytic_cube_f.sales MEASURE cost FROM analytic_cube_f.costs MEASURE promotions FROM analytic_cube_f.promo MEASURE quota FROM analytic_cube_f.quota MEASURE units FROM analytic_cube_f.units DIMENSION time FROM time WITH HIERARCHY time_member_parentrel INHIERARCHY time_member_inhier DIMENSION geography FROM geography WITH HIERARCHY geography_member_parentrel INHIERARCHY geography_member_inhier DIMENSION product FROM product WITH HIERARCHY product_member_parentrel INHIERARCHY product_member_inhier DIMENSION channel FROM channel WITH HIERARCHY channel_member_parentrel INHIERARCHY channel_member_inhier ROW2CELL r2c') AS measure_table)) WHERE sales IS NOT NULL; / COMMIT / GRANT SELECT ON measure_view TO PUBLIC;
Example 21-1 OLAP_EXPRESSION: Time Series Function with a WHERE Clause
This example uses the view described in "Sample View: MEASURE_VIEW".
The following SELECT
statement calculates an expression with an alias of PERIODAGO
, and limits the result set to calculated values greater than 200,000. The calculation uses the LAG
function to return the value of the previous time period.
SELECT time, cost, OLAP_EXPRESSION(r2c, 'LAG(analytic_cube_f.costs, 1, time, LEVELREL time_member_levelrel)') periodago FROM measure_view WHERE geography = 'L1.WORLD' AND CHANNEL = 'STANDARD_2.TOTALCHANNEL' AND PRODUCT = 'L1.TOTALPROD' and OLAP_EXPRESSION(r2c, 'LAG(analytic_cube_f.costs, 1, time, LEVELREL time_member_levelrel)') > 200000;
This SELECT
statement produces these results.
TIME COST PERIODAGO ------------ ---------- ---------- L1.1997 1078031 2490243.07 L2.Q1.97 615399 560379.445 L2.Q2.96 649004 615398.858 L2.Q2.97 462632 649004.473 L2.Q3.96 582693 462632.064 L2.Q4.96 698166 582693.091 L3.AUG96 194498 209476.344 L3.FEB96 186762 252738.981 L3.JAN96 185755 205214.946 . . .
Example 21-2 OLAP_EXPRESSION: Numeric Calculation with an ORDER BY CLause
This example uses the view described in "Sample View: MEASURE_VIEW".
This example subtracts costs from sales to calculate profit, and gives this expression an alias of PROFIT
. The rows are ordered by geographic areas from most to least profitable.
SELECT geography, sales, cost, OLAP_EXPRESSION(r2c, 'analytic_cube_f.sales - analytic_cube_f.costs') profit FROM measure_view WHERE channel = 'STANDARD_2.TOTALCHANNEL' AND product = 'L1.TOTALPROD' AND time = 'L3.APR97' ORDER BY OLAP_EXPRESSION(r2c, 'analytic_cube_f.sales - analytic_cube_f.costs') DESC;
This SELECT
statement produces these results.
GEOGRAPHY SALES COST PROFIT ------------------------------ ---------- ---------- ---------- L1.WORLD 9010260 209476 8800783.17 L2.EUROPE 3884776 95204 3789571.85 L2.AMERICAS 2734436 55322 2679114.66 L2.ASIA 1625379 37259 1588120.61 L3.USA 1603043 27547 1575496.86 L2.AUSTRALIA 765668 21692 743976.058 L3.UK 733090 19144 713945.952 L3.CANADA 731734 19666 712067.455 L4.NEWYORK 684008 8020 675987.377 L3.GERMANY 659428 12440 646988.197 L3.FRANCE 596767 19307 577460.113 . . .
The management of aggregate data within analytic workspaces can have significant performance implications. To determine an optimal set of dimension member combinations to preaggregate, you can use the ADVISE_REL
and ADVISE_CUBE
procedures in the DBMS_AW
package. These procedures are known together as the Aggregate Advisor.
Based on a percentage that you specify, ADVISE_REL
suggests a set of dimension members to preaggregate. The ADVISE_CUBE
procedure suggests a set of members for each dimension of a cube. The Aggregate Advisor procedures require database standard form.
See Also: Oracle OLAP Application Developer's Guide for information on standard form analytic workspaces. |
Instructions for storing aggregate data are specified in a workspace object called an aggmap. The OLAP DML AGGREGATE
command uses the aggmap to preaggregate the data. Any data that is not preaggregated is aggregated dynamically by the AGGREGATE
function when the data is queried.
Choosing a balance between static and dynamic aggregation depends on many factors including disk space, available memory, and the nature and frequency of the queries that will run against the data. After weighing these factors, you may arrive at a percentage of the data to preaggregate.
Once you have determined the percentage of the data to preaggregate, you can use the Aggregate Advisor. These procedures analyze the distribution of dimension members within hierarchies and identify an optimal set of dimension members to preaggregate.
Based on a precompute percentage that you specify, the ADVISE_REL
procedure analyzes a family relation, which represents a dimension with all its hierarchical relationships, and returns a list of dimension members.
ADVISE_CUBE
applies similar heuristics to each dimension in an aggmap for a cube.
Example 21-3 uses a sample Customer dimension to illustrate the ADVISE_REL
procedure.
The Customer dimension in GLOBAL_AW.GLOBAL
has two hierarchies: SHIPMENTS_ROLLUP
with four levels, and MARKET_ROLLUP
with three levels. The dimension has 106 members. This number includes all members at each level and all level names.
The members of the Customer dimension are integer keys whose text values are defined in long and short descriptions.
The following OLAP DML commands illustrate some aspects of the standard form representation of the Customer dimension.
" ---- Number of members of Customer dimension >show statlen(customer) 106 " ---- Hierarchies in Customer dimension; >rpr w 40 customer_hierlist CUSTOMER_HIERLIST ---------------------------------------- MARKET_ROLLUP SHIPMENTS_ROLLUP " ---- Levels in Customer dimension >rpr w 40 customer_levellist CUSTOMER_LEVELLIST ---------------------------------------- ALL_CUSTOMERS REGION WAREHOUSE TOTAL_MARKET MARKET_SEGMENT ACCOUNT SHIP_TO " ---- In the MARKET_ROLLUP hierarchy, ACCOUNT is the leaf level. " ---- In the SHIPMENTS_HIER hierarchy, SHIP_TO is the leaf level. " ---- MARKET_HIER SHIPMENTS_HIER " ------------------------------------------------------------ " ---- TOTAL_MARKET ALL_CUSTOMERS " ---- MARKET_SEGMENT REGIONS " ---- ACCOUNT WAREHOUSE " ---- SHIP_TO " ---- " ---- Parent relation showing parent-child relationships in the Customer dimension >limit customer to last 20 "Only show the last 20 members >rpr w 10 down customer w 20 customer_parentrel -----------CUSTOMER_PARENTREL------------ ------------CUSTOMER_HIERLIST------------ CUSTOMER MARKET_ROLLUP SHIPMENTS_ROLLUP ---------- -------------------- -------------------- 103 44 21 104 45 21 105 45 21 106 45 21 7 NA NA 1 NA NA 8 NA 1 9 NA 1 10 NA 1 11 NA 8 12 NA 10 13 NA 9 14 NA 9 15 NA 8 16 NA 9 17 NA 8 18 NA 8 19 NA 9 20 NA 9 21 NA 10 " ---- Show text descriptions for the same twenty dimension members >report w 15 down customer w 35 across customer_hierlist: <customer_short_description> ALL_LANGUAGES: AMERICAN_AMERICA ---------------------------CUSTOMER_HIERLIST--------------------------- -----------MARKET_ROLLUP----------- ---------SHIPMENTS_ROLLUP---------- CUSTOMER CUSTOMER_SHORT_DESCRIPTION CUSTOMER_SHORT_DESCRIPTION --------------- ----------------------------------- ----------------------------------- 103 US Marine Svcs Washington US Marine Svcs Washington 104 Warren Systems New York Warren Systems New York 105 Warren Systems Philladelphia Warren Systems Philladelphia 106 Warren Systems Boston Warren Systems Boston 7 Total Market NA 1 NA All Customers 8 NA Asia Pacific 9 NA Europe 10 NA North America 11 NA Australia 12 NA Canada 13 NA France 14 NA Germany 15 NA Hong Kong 16 NA Italy 17 NA Japan 18 NA Singapore 19 NA Spain 20 NA United Kingdom 21 NA United States
Example 21-3 ADVISE_REL: Suggested Preaggregation of the Customer Dimension
This example uses the GLOBAL
Customer dimension described in Sample Dimension: Customer in the Global Analytic Workspace.
The following PL/SQL statements assume that you want to preaggregate 25% of the Customer dimension. ADVISE_REL
returns the suggested set of members in a valueset.
SQL>SET SERVEROUTPUT ON SQL>EXECECUTE dbms_aw.execute('aw attach global_aw.global'); SQL>EXECECUTE dbms_aw.execute('define customer_preagg valueset customer'); SQL>EXECECUTE dbms_aw.advise_rel('customer_parentrel', 'customer_preagg', 25); SQL>EXECECUTE dbms_aw.execute('show values(customer_preagg)'); 31 2 4 5 6 7 1 8 9 20 21
The Customer members returned are shown below with their text descriptions, related levels, and related hierarchies.
Customer Member | Description | Hierarchy | Level |
---|---|---|---|
31 | Kosh Enterprises | MARKET_ROLLUP | ACCOUNT |
2 | Consulting | MARKET_ROLLUP | MARKET_SEGMENT |
4 | Government | MARKET_ROLLUP | MARKET_SEGMENT |
5 | Manufacturing | MARKET_ROLLUP | MARKET_SEGMENT |
6 | Reseller | MARKET_ROLLUP | MARKET_SEGMENT |
7 | TOTAL_MARKET | MARKET_ROLLUP | TOTAL_MARKET |
1 | ALL_CUSTOMERS | SHIPMENTS_ROLLUP | ALL_CUSTOMERS |
8 | Asia Pacific | SHIPMENTS_ROLLUP | REGION |
9 | Europe | SHIPMENTS_ROLLUP | REGION |
20 | United Kingdom | SHIPMENTS_ROLLUP | WAREHOUSE |
21 | United States | SHIPMENTS_ROLLUP | WAREHOUSE |
The following table describes the subprograms provided in DBMS_AW
.
Table 21-1 DBMS_AW Subprograms
Subprogram | Description |
---|---|
ADVISE_CUBE Procedure |
Suggests how to preaggregate a standard form cube, based on a specified percentage of the cube's data. |
ADVISE_REL Procedure |
Suggests how to preaggregate a standard form dimension, based on a specified percentage of the dimension's members. |
AW_ATTACH Procedure |
Attaches an analytic workspace to a session. |
AW_COPY Procedure |
Creates a new analytic workspace and populates it with the object definitions and data from another analytic workspace. |
AW_CREATE Procedure |
Creates a new, empty analytic workspace. |
AW_DELETE |
Deletes an analytic workspace |
AW_DETACH Procedure |
Detaches an analytic workspace from a session. |
AW_RENAME Procedure |
Changes the name of an analytic workspace. |
AW_UPDATE Procedure |
Saves changes made to an analytic workspace. |
"EXECUTE Procedure" |
Executes one or more OLAP DML commands. Input and output is limited to 4K. Typically used in an interactive session using an analytic workspace. |
"GETLOG Function" |
Returns the session log from the last execution of the INTERP or INTERPCLOB functions. |
"INTERP Function" |
Executes one or more OLAP DML commands. Input is limited to 4K and output to 4G. Typically used in applications when the 4K limit on output for the EXECUTE procedure is too restrictive. |
"INTERPCLOB Function" |
Executes one or more OLAP DML commands. Input and output are limited to 4G. Typically used in applications when the 4K input limit of the INTERP function is too restrictive. |
"INTERP_SILENT Procedure" |
Executes one or more OLAP DML commands and suppresses the output. Input is limited to 4K and output to 4G. |
"OLAP_EXPRESSION Function" |
Returns the result set of a single-row numeric function calculated in an analytic workspace. |
"OLAP_EXPRESSION_BOOL Function" |
Returns the result set of a single-row boolean function calculated in an analytic workspace. |
"OLAP_EXPRESSION_DATE Function" |
Returns the result set of a single-row date function calculated in an analytic workspace. |
"OLAP_EXPRESSION_TEXT Function" |
Returns the result set of a single-row text function calculated in an analytic workspace. |
"PRINTLOG Procedure" |
Prints a session log returned by the INTERP , INTERCLOB , or GETLOG functions. |
The ADVISE_CUBE
procedure helps you determine how to preaggregate a standard form cube in an analytic workspace. When you specify a percentage of the cube's data to preaggregate, ADVISE_CUBE
recommends a set of members to preaggregate from each of the cube's dimensions.
The ADVISE_CUBE
procedure takes an aggmap and a precompute percentage as input. The aggmap must have a precompute clause in each of its RELATION
statements. The precompute clause must consist of a valueset. Based on the precompute percentage that you specify, ADVISE_CUBE
returns a set of dimension members in each valueset.
Syntax
ADVISE_CUBE ( aggmap_name IN VARCHAR2 precompute_percentage IN INTEGER DEFAULT 20);
Parameters
Table 21-2 ADVISE_CUBE Procedure Parameters
Parameter | Description |
---|---|
aggmap_name |
The name of an aggmap associated with the cube.
Each |
precompute_percentage | A percentage of the cube's data to preaggregate. The default is 20%. |
Example
This example illustrates the ADVISE_CUBE
procedure with a cube called UNITS
dimensioned by PRODUCT
and TIME
. ADVISE_CUBE
returns the dimension combinations to include if you want to preaggregate 40% of the cube's data.
SET SERVEROUTPUT ON --- View valuesets SQL>EXECUTE dbms_aw.execute('describe prodvals'); DEFINE PRODVALS VALUESET PRODUCT SQL>EXECUTE dbms_aw.execute('describe timevals'); DEFINE TIMEVALS VALUESET TIME --- View aggmap SQL>EXECUTE dbms_aw.execute ('describe units_agg'); DEFINE UNITS_AGG AGGMAP RELATION product_parentrel PRECOMPUTE (prodvals) RELATION time_parentrel PRECOMPUTE (timevals) SQL>EXECUTE dbms_aw.advise_cube ('units_agg', 40); ---- ---- The results are returned in the prodvals and timevals valuesets
See Also
The ADVISE_REL
procedure helps you determine how to preaggregate a standard form dimension in an analytic workspace. When you specify a percentage of the dimension to preaggregate, ADVISE_REL
recommends a set of dimension members.
The ADVISE_REL
procedure takes a family relation, a valueset, and a precompute percentage as input. The family relation is a standard form object that specifies the hierarchical relationships between the members of a dimension. The valueset must be defined from the dimension to be analyzed. Based on the precompute percentage that you specify, ADVISE_REL
returns a set of dimension members in the valueset.
Syntax
ADVISE_REL ( family_relation_name IN VARCHAR2, valueset_name IN VARCHAR2, precompute_percentage IN INTEGER DEFAULT 20);
Parameters
Table 21-3 ADVISE_REL Procedure Parameters
Parameter | Description |
---|---|
family_relation_name |
The name of a family relation, which specifies a dimension and the hierarchical relationships between the dimension members. |
valueset_name |
The name of a valueset to contain the results of the procedure. The valueset must be defined from the dimension in the family relation. If the valueset is not empty, ADVISE_REL deletes its contents before adding new values. |
precompute_percentage |
A percentage of the dimension to preaggregate. The default is 20%. |
See Also
The AW_ATTACH
procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session.
AW_ATTACH
can also be used to create a new analytic workspace, but the AW_CREATE
procedure is provided specifically for that purpose.
Syntax
DBMS_AW.AW_ATTACH ( awname IN VARCHAR2, forwrite IN BOOLEAN DEFAULT FALSE, createaw IN BOOLEAN DEFAULT FALSE, attargs IN VARCHAR2 DEFAULT NULL, tablespace IN VARCHAR2 DEFAULT NULL);
Parameters
Table 21-4 AW_ATTACH Procedure Parameters
Parameter | Description |
---|---|
awname |
The name of an existing analytic workspace, unless createaw is specified as TRUE . See the description of createaw. |
forwrite |
TRUE attaches the analytic workspace in read/write mode, giving you exclusive access and full administrative rights to the analytic workspace. FALSE attaches the analytic workspace in read-only mode. |
createaw |
TRUE creates an analytic workspace named awname. If awname already exists, then an error is generated. FALSE attaches an existing analytic workspace named awname. |
attargs |
Keywords for attaching an analytic workspace, such as FIRST or LAST , as described in the Oracle OLAP DML Reference under the AW command. |
Example
The following SQL call attaches an analytic workspace named GLOBAL
in read/write mode.
EXECUTE DBMS_AW.AW_ATTACH('global', TRUE);
The next SQL call attaches GLOBAL_PROGRAMS
in read-only mode as the last user-owned analytic workspace. If GLOBAL_PROGRAMS
is already attached, this call just changes its position in the list of analytic workspaces.
EXECUTE DBMS_AW.AW_ATTACH('global_programs', false, false, 'last');
The AW_COPY
procedure creates a new analytic workspace and copies into it both the object definitions and the data from another analytic workspace.
Syntax
DBMS_AW.AW_COPY ( oldname IN VARCHAR2, newname IN VARCHAR2, tablespace IN VARCHAR2 DEFAULT NULL, partnum IN NUMBER DEFAULT 8);
Parameters
Table 21-5 AW_COPY Procedure Parameters
Parameter | Description |
---|---|
oldname |
The name of an existing analytic workspace. |
newname |
A name for the new analytic workspace. |
tablespace |
The name of a tablespace in which newname will be stored. If this parameter is omitted, then the analytic workspace is created in the user's default tablespace. |
partnum |
The number of partitions that will be created for the AW$ newname table. |
Example
The following command creates a new analytic workspace named DEMO
and copies the contents of GLOBAL
into it. The workspace is stored in a table named AW$DEMO
, which has three partitions and is stored in the user's default tablespace.
EXECUTE DBMS_AW.AW_COPY('global', 'demo', null, 3);
The AW_CREATE
procedure creates a new, empty analytic workspace.
Syntax
DBMS_AW.AW_CREATE ( awname IN VARCHAR2 , tablespace IN VARCHAR2 DEFAULT NULL , partnum IN NUMBER DEFAULT 8 );
Parameters
Table 21-6 AW_CREATE Procedure Parameters
Parameter | Description |
---|---|
awname |
The name of a new analytic workspace. The name must comply with the naming requirements for a table in an Oracle database. This procedure creates a table named AW$awname, in which the analytic workspace is stored. |
tablespace |
The tablespace in which the analytic workspace will be created. If you omit this parameter, the analytic workspace is created in your default tablespace. |
partnum |
The number of partitions that will be created for the AW$ awname table. |
Example
The following command creates a new, empty analytic workspace named GLOBAL
. The new analytic workspace is stored in a table named AW$GLOBAL
with eight partitions in the user's default tablespace.
EXECUTE DBMS_AW.AW_CREATE('global');
The next command creates an analytic workspace named DEMO
in the GLOBAL_AW
schema. AW$DEMO
will have two partitions and will be stored in the GLOBAL
tablespace.
EXECUTE DBMS_AW.AW_CREATE('global_aw.demo', 'global', 2);
The AW_DELETE
procedure deletes an existing analytic workspace.
Syntax
DBMS_AW.AW_DELETE ( awname IN VARCHAR2 );
Parameters
Table 21-7 AW_DELETE Procedure Parameters
Parameter | Description |
---|---|
awname |
The name of an existing analytic workspace that you want to delete along with all of its contents. You must be the owner of awname or have DBA rights to delete it, and it cannot currently be attached to your session. The AW$awname file is deleted from the database. |
Example
The following SQL call deletes the GLOBAL
analytic workspace in the user's default schema.
EXECUTE DBMS_AW.AW_DELETE('global');
The AW_DETACH
procedure detaches an analytic workspace from your session so that its contents are no longer accessible. All changes that you have made since the last update are discarded. Refer to "AW_UPDATE Procedure" for information about saving changes to an analytic workspace.
Syntax
DBMS_AW.AW_DETACH ( awname IN VARCHAR2);
Parameters
Table 21-8 AW_DETACH Procedure Parameters
Parameter | Description |
---|---|
awname |
The name of an attached analytic workspace that you want to detach from your session. |
Example
The following command detaches the GLOBAL
analytic workspace.
EXECUTE DBMS_AW.AW_DETACH('global');
The AW_RENAME
procedure changes the name of an analytic workspace.
Syntax
DBMS_AW.AW_RENAME ( oldname IN VARCHAR2 DEFAULT NULL, newname IN VARCHAR2 );
Parameters
Table 21-9 AW_RENAME Procedure Parameters
Parameter | Description |
---|---|
oldname |
The current name of the analytic workspace. The analytic workspace cannot be attached to any session. |
newname |
The new name of the analytic workspace. |
Example
The following command changes the name of the GLOBAL
analytic workspace to DEMO
.
EXECUTE DBMS_AW.AW_RENAME('global', 'demo');
The AW_UPDATE
procedure saves the changes made to an analytic workspace in its permanent database table. For the updated version of this table to be saved in the database, you must issue a SQL COMMIT
statement before ending your session.
Syntax
DBMS_AW.AW_UPDATE ( awname IN VARCHAR2 DEFAULT NULL);
Parameters
Table 21-10 AW_UPDATE Procedure Parameters
Parameter | Description |
---|---|
awname |
Saves changes to awname by copying them to a table named AW$ awname. If this parameter is omitted, then changes are saved for all analytic workspaces attached in read/write mode. |
Example
The following command saves changes to the GLOBAL
analytic workspace to a table named AW$GLOBAL
.
EXECUTE DBMS_AW.AW_UPDATE('global');
The EXECUTE
procedure executes one or more OLAP DML commands and directs the output to a printer buffer. It is typically used to manipulate analytic workspace data within an interactive SQL session.
When you are using SQL*Plus, you can direct the printer buffer to the screen by issuing the following command:
SET SERVEROUT ON
If you are using a different program, refer to its documentation for the equivalent setting.
Input and output is limited to 4K. For larger values, refer to the INTERP
and INTERPCLOB
functions in this package.
This procedure does not print the output of the DML commands when you have redirected the output by using the OLAP DML OUTFILE
command.
Syntax
EXECUTE ( olap_commands IN VARCHAR2 text OUT VARCHAR2);
Parameters
Table 21-11 EXECUTE Procedure Parameters
Parameter | Description |
---|---|
olap-commands |
One or more OLAP DML commands separated by semicolons. See "Guidelines for Using Quotation Marks in OLAP DML Commands". |
text | Output from the OLAP engine in response to the OLAP commands. |
Example
The following sample SQL*Plus session attaches an analytic workspace named XADEMO
, creates a formula named COST_PP
in XADEMO
, and displays the new formula definition.
SQL> SET SERVEROUT ON SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH xademo RW; DEFINE cost_pp FORMULA LAG(analytic_cube_f.costs, 1, time, LEVELREL time_levelrel)'); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_AW.EXECUTE('DESCRIBE cost_pp'); DEFINE COST_PP FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME> EQ lag(analytic_cube_f.costs, 1, time, levelrel time.levelrel) PL/SQL procedure successfully completed.
This function returns the session log from the last execution of the INTERP
or INTERPCLOB
functions in this package.
To print the session log returned by this function, use the DBMS_AW.PRINTLOG
procedure.
Syntax
GETLOG() RETURN CLOB;
Returns
The session log from the latest call to INTERP
or INTERPCLOB
.
Example
The following example shows the session log returned by a call to INTERP
, then shows the identical session log returned by GETLOG
.
SQL> SET SERVEROUT ON SIZE 1000000 SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('AW ATTACH xademo; LISTNAMES AGGMAP')); 2 AGGMAPs ------------------------------------------ ANALYTIC_CUBE.AGGMAP.1 SALES_MULTIKEY_CUBE.AGGMAP.1 PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.GETLOG()); 2 AGGMAPs ------------------------------------------ ANALYTIC_CUBE.AGGMAP.1 SALES_MULTIKEY_CUBE.AGGMAP.1 PL/SQL procedure successfully completed.
The INTERP
function executes one or more OLAP DML commands and returns the session log in which the commands are executed. It is typically used in applications when the 4K limit on output for the EXECUTE
procedure may be too restrictive.
Input to the INTERP
function is limited to 4K. For larger input values, refer to the INTERPCLOB
function of this package.
This function does not return the output of the DML commands when you have redirected the output by using the OLAP DML OUTFILE
command.
You can use the INTERP
function as an argument to the PRINTLOG
procedure in this package to view the session log. See the example.
Syntax
INTERP ( olap-commands IN VARCHAR2) RETURN CLOB;
Parameters
Table 21-12 INTERP Function Parameters
Parameter | Description |
---|---|
olap-commands |
One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands". |
Returns
The log file for the Oracle OLAP session in which the OLAP DML commands were executed.
Example
The following sample SQL*Plus session attaches an analytic workspace named XADEMO
and lists the members of the PRODUCT
dimension.
SQL> SET SERVEROUT ON SIZE 1000000 SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('AW ATTACH cloned; REPORT product')); PRODUCT -------------- L1.TOTALPROD L2.ACCDIV L2.AUDIODIV L2.VIDEODIV L3.AUDIOCOMP L3.AUDIOTAPE . . . PL/SQL procedure successfully completed.
The INTERPCLOB
function executes one or more OLAP DML commands and returns the session log in which the commands are executed. It is typically used in applications when the 4K limit on input for the INTERP
function may be too restrictive.
This function does not return the output of the OLAP DML commands when you have redirected the output by using the OLAP DML OUTFILE
command.
You can use the INTERPCLOB
function as an argument to the PRINTLOG
procedure in this package to view the session log. See the example.
Syntax
INTERPCLOB ( olap-commands IN CLOB) RETURN CLOB;
Parameters
Table 21-13 INTERPCLOB Function Parameters
Parameter | Description |
---|---|
olap-commands |
One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands". |
Returns
The log for Oracle OLAP session in which the OLAP DML commands were executed.
Example
The following sample SQL*Plus session creates an analytic workspace named ELECTRONICS
, imports its contents from an EIF file stored in the dbs
directory alias, and displays the contents of the analytic workspace.
SQL> SET SERVEROUT ON SIZE 1000000 SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERPCLOB('AW CREATE electronics; IMPORT ALL FROM EIF FILE ''dbs/electronics.eif'' DATA DFNS; DESCRIBE')); DEFINE GEOGRAPHY DIMENSION TEXT WIDTH 12 LD Geography Dimension Values DEFINE PRODUCT DIMENSION TEXT WIDTH 12 LD Product Dimension Values DEFINE TIME DIMENSION TEXT WIDTH 12 LD Time Dimension Values DEFINE CHANNEL DIMENSION TEXT WIDTH 12 LD Channel Dimension Values . . . PL/SQL procedure successfully completed.
The INTERP_SILENT
procedure executes one or more OLAP DML commands and suppresses all output from them. It does not suppress error messages from the OLAP command interpreter.
Input to the INTERP_SILENT
function is limited to 4K. If you want to display the output of the OLAP DML commands, use the EXECUTE
procedure, or the INTERP
or INTERPCLOB
functions.
Syntax
INTERP_SILENT ( olap-commands IN VARCHAR2);
Parameters
Table 21-14 INTERP_SILENT Function Parameters
Parameter | Description |
---|---|
olap-commands |
One or more OLAP DML commands separated by semi-colons. See "Guidelines for Using Quotation Marks in OLAP DML Commands". |
Example
The following commands show the difference in message handling between EXECUTE
and INTERP_SILENT
. Both commands attach the XADEMO analytic workspace in read-only mode. However, EXECUTE
displays a warning message, while INTERP_SILENT
does not.
SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH xademo'); IMPORTANT: Analytic workspace XADEMO is read-only. Therefore, you will not be able to use the UPDATE command to save changes to it. PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_AW.INTERP_SILENT('AW ATTACH xademo'); PL/SQL procedure successfully completed.
The OLAP_EXPRESSION
function enables you to execute single-row numeric functions in an analytic workspace and thus generate custom measures in SELECT
statements. In addition to calculating an expression, OLAP_EXPRESSION
can be used in the WHERE
and ORDER BY
clauses to modify the result set of a SELECT
.
Syntax
OLAP_EXPRESSION( r2c IN RAW(32), expression IN VARCHAR2 ) RETURN NUMBER;
Parameters
Table 21-15 OLAP_EXPRESSION Function Parameters
Parameter | Description |
---|---|
r2c |
The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE .
|
expression |
A numeric calculation that will be performed in the analytic workspace. |
Returns
An evaluation of expression for each row of the table object returned by the OLAP_TABLE
function.
To return text, boolean, or date data, use the OLAP_EXPRESSION_TEXT
, OLAP_EXPRESSION_BOOL
, or OLAP_EXPRESSION_DATE
functions in this package.
Note
You can use OLAP_EXPRESSION
only with a table object returned by the OLAP_TABLE
function. The returned table object must have a column populated by a ROW2CELL
. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.
Example
See "Embedding Custom Measures in SELECT Statements".
The OLAP_EXPRESSION_BOOL
function enables you to execute single-row boolean functions in an analytic workspace and thus generate custom measures in SELECT
statements. In addition to calculating an expression, OLAP_EXPRESSION_BOOL
can be used in the WHERE
and ORDER BY
clauses to modify the result set of a SELECT
.
Syntax
OLAP_EXPRESSION_BOOL( r2c IN RAW(32), expression IN VARCHAR2 ) RETURN NUMBER;
Parameters
Table 21-16 OLAP_EXPRESSION_BOOL Function Parameters
Parameter | Description |
---|---|
r2c |
The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE .
|
expression |
A boolean calculation that will be performed in the analytic workspace. |
Returns
An evaluation of expression for each row of the table object returned by the OLAP_TABLE
function.
Return values are numbers 1
(true) or 0
(false).
To return text, numeric, or date data, use the OLAP_EXPRESSION_TEXT
, OLAP_EXPRESSION
, or OLAP_EXPRESSION_DATE
functions in this package.
Note
You can use OLAP_EXPRESSION_BOOL
only with a table object returned by the OLAP_TABLE
function. The returned table object must have a column populated by a ROW2CELL
. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.
Example
See "Embedding Custom Measures in SELECT Statements".
The OLAP_EXPRESSION_DATE
function enables you to execute single-row date functions in an analytic workspace and thus generate custom measures in SELECT
statements. In addition to calculating an expression, OLAP_EXPRESSION_DATE
can be used in the WHERE
and ORDER BY
clauses to modify the result set of a SELECT
.
Syntax
OLAP_EXPRESSION_DATE( r2c IN RAW(32), expression IN VARCHAR2 ) RETURN DATE;
Parameters
Table 21-17 OLAP_EXPRESSION_DATE Function Parameters
Parameter | Description |
---|---|
r2c |
The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE .
|
expression |
A date calculation that will be performed in the analytic workspace. |
Returns
An evaluation of expression for each row of the table object returned by the OLAP_TABLE
function.
To return text, boolean, or numeric data, use the OLAP_EXPRESSION_TEXT
, OLAP_EXPRESSION_BOOL
, or OLAP_EXPRESSION
functions in this package.
Note
You can use OLAP_EXPRESSION_DATE
only with a table object returned by the OLAP_TABLE
function. The returned table object must have a column populated by a ROW2CELL
. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.
Example
See "Embedding Custom Measures in SELECT Statements".
The OLAP_EXPRESSION_TEXT
function enables you to execute single-row text functions in an analytic workspace and thus generate custom measures in SELECT
statements. In addition to calculating an expression, OLAP_EXPRESSION_TEXT
can be used in the WHERE
and ORDER BY
clauses to modify the result set of a SELECT
.
Syntax
OLAP_EXPRESSION_TEXT( r2c IN RAW(32), expression IN VARCHAR2 ) RETURN VARCHAR2;
Parameters
Table 21-18 OLAP_EXPRESSION_TEXT Function Parameters
Parameter | Description |
---|---|
r2c |
The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE .
|
expression |
A text calculation that will be performed in the analytic workspace. |
Returns
An evaluation of expression for each row of the table object returned by the OLAP_TABLE
function.
To return numeric, boolean, or date data, use the OLAP_EXPRESSION
, OLAP_EXPRESSION_BOOL
, or OLAP_EXPRESSION_DATE
functions in this package.
Note
You can use OLAP_EXPRESSION_TEXT
only with a table object returned by the OLAP_TABLE
function. The returned table object must have a column populated by a ROW2CELL
. Refer to Chapter 26, "OLAP_TABLE" for more information about using this function.
Example
See "Embedding Custom Measures in SELECT Statements".
This procedure sends a session log returned by the INTERP
, INTERPCLOB
, or GETLOG
functions of this package to the print buffer, using the DBMS_OUTPUT
package in PL/SQL.
When you are using SQL*Plus, you can direct the printer buffer to the screen by issuing the following command:
SET SERVEROUT ON SIZE 1000000
The SIZE
clause increases the buffer from its default size of 4K.
If you are using a different program, refer to its documentation for the equivalent setting.
Syntax
DBMS_AW.PRINTLOG ( session-log IN CLOB);
Parameters
Example
The following example shows the session log returned by the INTERP
function.
SQL> SET SERVEROUT ON SIZE 1000000 SQL> EXECUTE DBMS_AW.PRINTLOG(DBMS_AW.INTERP('DESCRIBE analytic_cube_f.profit')); DEFINE ANALYTIC_CUBE.F.PROFIT FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME> EQ analytic_cube.f.sales - analytic_cube.f.costs PL/SQL procedure successfully completed.