| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
| 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.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|