Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
This chapter introduces methods of accessing the data in an analytic workspace using SQL. Most of these methods can be used at runtime as part of an application.
This chapter contains the following topics:
Using SQL, you can manipulate analytic workspace data and extract that data into your application. There are various methods that you can use, and the best one depends on the type of analytic workspace you have, the particular task you want to accomplish, and your personal preferences.
To manipulate analytic workspace data using SQL, you must use PL/SQL procedures that execute OLAP DML commands. The OLAP DML is the language for working in an analytic workspace. Using it, you can create, modify, delete, and populate workspace objects. Any method that you use for performing these tasks uses the OLAP DML.
Several PL/SQL packages are available that execute OLAP DML commands. A call to a single procedure can execute a single OLAP DML command, or dozens of commands to perform a specific task. Among these packages are:
DBMS_AW
contains procedures for executing individual OLAP DML commands.
DBMS_AW_UTILITIES
contains procedures for managing custom measures in standard form analytic workspaces that have been enabled for the BI Beans.
DBMS_AWM
contains procedures for creating standard form analytic workspaces.
You can use any of these packages directly in a SQL interface such as SQL*PLus. Analytic Workspace Manager and OLAP Worksheet are applications that use these SQL packages. Figure 7-1 shows the relationships among them.
Figure 7-1 Analytic Workspace Manager's Use of PL/SQL Packages
The OLAP_TABLE
function provides the basic technology for querying an analytic workspace, as described in "Using OLAP_TABLE for Direct Access to Workspace Data". It operates outside of the conventions of standard form, and can access data from any analytic workspace. However, tools that use OLAP_TABLE
, such as the enablers, require standard form to construct the appropriate syntax.
Oracle OLAP provides catalogs of information about standard form analytic workspaces. These active catalogs are generated and maintained automatically without requiring any action by the DBA.
The active catalogs are implemented as public views with names that begin ALL_OLAP2_AW
. For example, ALL_OLAP2_AW_CUBES
lists cubes in all analytic workspaces, and ALL_OLAP2_AW_DIMENSIONS
lists all of the dimensions. You can query the active catalogs directly from SQL.
For descriptions of the active catalogs, refer to the Oracle OLAP Reference.
A custom measure is calculated from one or more measures stored in the analytic workspace. Often, it is created by an analyst just for the duration of a session. However, a custom measure can also be saved as a permanent part of the analytic workspace.
These saved custom measures can either be solved at run-time or stored in variables. Run-time calculations do not require disk storage space and do not extend the processing time required for data maintenance. However, they may slow performance. You need to decide which measures to calculate on demand and which, if any, to store. The custom measures described in this chapter are calculated for a query. For instructions on creating stored custom measures, refer to Chapter 9.
Two PL/SQL packages support custom measures in an analytic workspace:
DBMS_AW_UTILITIES
contains procedures for creating, updating, and deleting custom measures. This package operates only on the views created by the enabler for the BI Beans. The custom measures are stored in the predefined columns provided in these views for custom measures. You can define a custom measure to persist either for the duration of the session or permanently.
DBMS_AW
contains various procedures to execute OLAP DML commands. Several of them can be used in SELECT
statements to execute a calculation or data manipulation in the analytic workspace. The calculations are returned along with the rest of the result set. This type of custom measure exists only for the duration of the SELECT
statement.
In addition, you can use the OLAP_TABLE
function to define and access custom measures outside of the framework of standard form, as described in "Using OLAP_TABLE for Direct Access to Workspace Data".
Regardless of the method that you use to define a custom measure, you will express the calculation itself using the OLAP DML. Following are descriptions of the many functions and commands available for manipulating your data. In addition, you can perform inter-row calculations using operators for multiplication (*), division (/), addition (+), subtraction (-), and so forth.
The OLAP DML offers the most sophisticated and up-to-date forecasting and regression tools, including simple linear regressions, non-linear regression methods, single exponential smoothing, double exponential smoothing, and the Holt-Winters method.
The time series functions perform operations such as lead, lag, and moving average. Table 7-1 describes the time series functions, which can easily be incorporated into custom measures.
Table 7-1 OLAP DML Time Series Functions
Function | Returns |
---|---|
CUMSUM |
Cumulative totals |
LAG |
Value for a previous time period at a specified offset |
LAGABSPCT |
Percentage difference between a value and the absolute value for a previous time period at a specified offset |
LAGDIF |
Difference between a value and the value for a previous time period at a specified offset |
LAGPCT |
Percentage difference between a value and the value for a previous time period at a specified offset |
LEAD |
Value for a subsequent time period at a specified offset |
MOVINGAVERAGE |
A series of averages over a specified range |
MOVINGMAX |
A series of maximum values over a specified range |
MOVINGMIN |
A series of minimum values over a specified range |
MOVINGTOTAL |
A series of totals over a specified range |
The financial functions include interest rate calculations, depreciation, and payment schedules, similar to those provided in spreadsheets.
For example, the FPMTSCHED
function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. The following call to FPMTSCHED
calculates 36 payments based on the amounts listed in the LOANS
variable, at the interest rates listed in the RATES
variable, for the MONTH
dimension of these variables.
FPMTSCHED(loans, rates, 36, month)
Statistical operations include standard deviation, rank, and correlation. For example, the STDDEV
function calculates the standard deviation. The function call
STDDEV(units month)
returns the standard deviation of values in the UNITS
measure for all months that are currently selected.
Functions are available to perform a wide variety of computations (such as sine, cosine, square root, minimum, and maximum) and data type conversions.
For example, the MAX
function compares two expressions and returns the larger value. This function call
MAX(actual, forecast)
compares the ACTUAL
and FORECAST
measures and returns the larger values for all dimension members currently selected.
The OLAP DML provides support for manipulating both single- and multibyte character sets, with functions for concatenating strings, locating a string within a larger body of text, inserting a string, and so forth.
For example, the EXTCHARS
function extracts a portion of text. The function call
EXTCHARS('lastname,firstname', 1,8)
extracts the first 8 characters, which contains the characters
lastname
Allocations are a critical part of planning applications. Given a target for the organization, whether for sales quota, product growth, salary, or equipment, managers must allocate that target among its contributors. The supported allocation methods include:
Copy methods (hierarchical copy, minimum, maximum, first, last)
Even distribution (even, hierarchical even)
Proportional distribution (including weighted distributions and user-defined multidimensional functions)
Aggregation is a basic feature of analytic workspaces. When you create a standard form analytic workspace, it contains a default aggregation plan for each cube. Wizards in Analytic Workspace Manager enable you to identify stored aggregate levels quickly and easily.
The OLAP DML offers a broader range of aggregation methods than are currently available through Analytic Workspace Manager or PL/SQL procedures. You can choose whatever method seems appropriate: by level, individual member, member attribute, time range, data value, or other criteria.
A model is a set of interrelated equations. These are some of the modeling features supported by the OLAP DML:
You can perform calculations for individual dimension members following unique calculation rules.
Oracle OLAP determines the order of the calculations, so you can list them in any order without concern for dependencies.
Oracle OLAP solves simultaneous equations.
You can assign results either to a variable or to a dimension member. Dimension-based equations provide flexibility; since you do not need to specify the modeling variable until you solve a model, you can run the same model with any other measure with the same dimension. For example, you could run the same model on Budget and Actual, which both have a Line dimension.
The enabler for the BI Beans creates fact views with columns specifically for custom measures defined by the DBMS_AW_UTILITIES
package. There are 100 columns for numeric data named CUST_MEAS_NUM1
to CUST_MEAS_NUM100
, and 100 columns for text data named CUST_MEAS_TEXT1
to CUST_MEAS_TEXT100
.
This is the basic syntax for creating a custom measure:
CALL DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE( schema.aw_name, aw_formula_name, aw_formula_expression, 'PERMANENT'|'TEMPORARY', schema.view_name;
The BI Beans enabler creates CWM2
metadata for the views of analytic workspaces, and DBMS_AW_UTILITIES
creates CWM2
metadata for the custom measures added to these views. This metadata is stored in tables that identify the mapping between the custom measures and the generic column names of the view:
CWM2$_AW_TEMP_CUST_MEAS_MAP
lists temporary custom measures for the current user.
CWM2$_AW_PERM_CUST_MEAS_MAP
lists permanent custom measures for users with the DBA
role.
"Identifying Required Business Facts" identifies the data requirements of the Global Corporation. Only three facts are stored in the star schema; the others must be calculated in the analytic workspace. Because GLOBAL
is a standard form analytic workspace that has been enabled for the BI Beans, the DBMS_AW_UTILITIES
package is available for the DBA to define these measures.
Before you can define custom measures, you must know the names of measures that are already defined in the analytic workspace. You can query the ALL_OLAP2_AW_CUBE_MEASURES
view in the Active Catalog for the names of measures defined in the GLOBAL
analytic workspace. Example 7-1 shows how to obtain the names of the measures.
Example 7-1 Querying the Active Catalog for Measure Names
SELECT aw_cube_name, aw_measure_name FROM all_olap2_aw_cube_measures WHERE aw_owner = 'GLOBAL_AW' AND aw_name = 'GLOBAL'; AW_CUBE_NAME AW_MEASURE_NAME ------------- ----------------- PRICE_CUBE UNIT_COST PRICE_CUBE UNIT_PRICE UNITS_CUBE UNITS
The ALL_AW_CUBE_ENABLED_VIEWS
view identifies the cubes that are enabled for the BI Beans, the names of the views created by the enabler to access those cubes, and the dimensions and dimension hierarchies for each view.
Example 7-2 shows that the Price cube is dimensioned by PRODUCT
and TIME
, and can be queried through a view named GLOB_GLOBA_PRICE_CU4VIEW
. The Units cube is dimensioned by CHANNEL
, CUSTOMER
, PRODUCT
, and TIME
. The CUSTOMER
dimension has two hierarchies: MARKET_SEGMENT
is shown in GLOB_GLOBA_UNITS_CU9VIEW
and SHIPMENTS
is shown in GLOB_GLOBA_UNITS_CU10VIEW
.
Example 7-2 SELECT Statement for Querying the Active Catalog
SELECT cube_name, system_viewname, hiercombo_str FROM all_aw_cube_enabled_views WHERE aw_name = 'GLOBAL' AND cube_name = 'PRICE_CUBE' OR cube_name = 'UNITS_CUBE'; CUBE_NAME SYSTEM_VIEWNAME HIERCOMBO_STR ------------ ------------------------- -------------------------------------------------------- PRICE_CUBE GLOB_GLOBA_PRICE_CU4VIEW DIM:PRODUCT/HIER:PRODUCT_ROLLUP;DIM:TIME/HIER:Calendar UNITS_CUBE GLOB_GLOBA_UNITS_CU9VIEW DIM:CHANNEL/HIER:CHANNEL_ROLLUP; DIM:CUSTOMER/HIER:MARKET_SEGMENT; DIM:PRODUCT/HIER:PRODUCT_ROLLUP; DIM:TIME/HIER:Calendar UNITS_CUBE GLOB_GLOBA_UNITS_CU10VIEW DIM:CHANNEL/HIER:CHANNEL_ROLLUP; DIM:CUSTOMER/HIER:SHIPMENTS; DIM:PRODUCT/HIER:PRODUCT_ROLLUP; DIM:TIME/HIER:Calendar
After getting the information you need to define a custom measure, you can define your custom measures using DBMS_AW_UTILITIES
. This example defines SALES
, which calculates the product of two other measures, UNITS
and UNIT_PRICE
, for each combination of dimension members.
UNITS
is a measure in the Units cube, and UNIT_PRICE
is a measure in the Price cube. The Units cube has four dimensions: TIME
, PRODUCT
, CUSTOMER
, and CHANNEL
. The Price cube has only two dimensions, TIME
and PRODUCT
. The product of these two measures will have four dimensions, so SALES
must be added to a view of the Units cube.
Example 7-3 adds the SALES
measure to both views for the Units cube. Notice that only the first call specifies the equation for the SALES
formula. The second call just identifies the existing SALES
formula.
Note: Whenever you useDBMS_AW_UTILITIES in a SQL environment such as SQL*Plus, be sure to begin with these settings:
SET SERVEROUT ON EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON Otherwise, you will not see any diagnostic messages. |
Example 7-3 Defining SALES Using DBMS_AW_UTILITIES
SET SERVEROUT ON EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON EXECUTE DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE( 'global_aw.global', 'sales', 'units * unit_price', 'PERMANENT', 'global_aw.glob_globa_units_cu9view'); EXECUTE DBMS_AW_UTILITIES.CREATE_CUSTOM_MEASURE( 'global_aw.global', 'sales', '', 'PERMANENT', 'global_aw.glob_globa_units_cu10view');
Use this command to see the formula created in the analytic workspace:
EXECUTE DBMS_AW.EXECUTE('DESCRIBE sales'); DEFINE SALES FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> EQ units * unit_price
You can also view the property sheet for SALES
in Analytic Workspace Manager.
OLAPSYS.CWM2$_AW_PERM_CUST_MEAS_MAP
identifies the mapping between the SALES
custom measure and a column in the views.
SELECT aw_access_view_name, cust_adt_column, aw_measure_name FROM olapsys.cwm2$_aw_perm_cust_meas_map WHERE workspace_name = 'global_aw.global'; AW_ACCESS_VIEW_NAME CUST_ADT_COLUMN AW_MEASURE_NAME ------------------------------------ ---------------- --------------- global_aw.glob_globa_units_cu9view CUST_MEAS_NUM1 sales global_aw.glob_globa_units_cu10view CUST_MEAS_NUM1 sales
Queries for the SALES
measure must select values from the CUST_MEAS_NUM1
columns of the two tables.
The DBMS_AW
package contains several procedures for specifying run-time calculations.
OLAP_EXPRESSION
performs numeric calculations
OLAP_EXPRESSION_BOOL
performs Boolean calculations
OLAP_EXPRESSION_DATE
performs date calculations
OLAP_EXPRESSION_TEXT
performs text manipulations
You can use these procedures to specify inter-row calculations using SELECT
statements on a view of analytic workspace data. The calculations are performed by the OLAP engine. The only requirement for using these functions is that the SELECT
statement for the view must contain a call to the OLAP_TABLE
function with a ROW2CELL
clause. The enabler for the BI Beans generates views of this type, and you can also generate custom views with ROW2CELL
columns as described in "Using OLAP_TABLE for Direct Access to Workspace Data".
The syntax of the four functions is identical. The difference between them is only in data type. This is the basic syntax for OLAP_EXPRESSION
:
OLAP_EXPRESSION(r2c, expression)
For example: OLAP_EXPRESSION('R2C', 'units * unit_price')
Enablement for the BI Beans created two views of the Units cube, one for each of the two hierarchies for the CUSTOMER
dimension. The following SELECT
statement queries one of the views and generates a new column for Sales. The SALES
column is calculated in the analytic workspace.
SELECT time_et, units, OLAP_EXPRESSION(r2c, 'units * unit_price') sales FROM global_aw.glob_globa_units_cu9view WHERE channel_et ='1' AND product_et = '4' AND customer_et = '24' AND time_et > '66' AND units IS NOT NULL ORDER BY OLAP_EXPRESSION(r2c, 'units * unit_price') DESC;
The result set of this SELECT
statement is sorted so that the sales figures are listed in descending order.
TIME_ET UNITS SALES --------- -------- ---------- 8 6 170017.38 68 5 123300.25 9 3 93293.85 7 3 64931.7 67 2 50932.26
The OLAP_TABLE
function provides the basic technology for extracting data from an analytic workspace. All of the views of analytic workspaces that are generated by the enablers use the OLAP_TABLE
function. By using OLAP_TABLE
directly, you have full control over data access. You can develop your own views to support applications for which there are no enablers, and you can extract workspace data directly into your application. This capability can provide your application with tremendous flexibility, since user queries can be formulated into calls to OLAP_TABLE
at runtime.
While the OLAP tools that use the OLAP_TABLE
function require a standard form analytic workspace, the OLAP_TABLE
function itself does not use standard form metadata.
The number of views that you create, and the number and characteristics of the columns in these views, depends largely on the requirements of the applications that these views are designed to support.
Because analytic workspaces contain aggregate data, the views must include the aggregates. There are several formats for presenting aggregate data:
Create a star schema with dimension views and measure views. The dimension views list dimension members at all levels in a single column.
Create a view that includes columns for all of the dimensions, attributes, and measures.
Create a view in rollup form that shows the full parentage of each dimension member in multiple columns.
Create a separate table for each aggregation level.
Choose a format that is appropriate for your application and its metadata.
These are the basic steps you must follow to generate views of data stored in an analytic workspace.
Explore the analytic workspace and identify the variables, formulas, relations, and dimensions that you want to expose to your application.
Decide how you want to present these objects in relational tables or views, based on the requirements of the application that will use them.
For each table or view that you plan to create, issue a SELECT
statement using the OLAP_TABLE
function. The SELECT
statement can be an argument to a CREATE VIEW
statement.
Commit these changes to the database if you are creating views for general use.
Create whatever metadata is required by your application to query the views.
You use the OLAP_TABLE
function in a SQL SELECT
statement to query the multidimensional data stored in an analytic workspace. OLAP_TABLE
can be used wherever you would use the name of a table or view. You can use SELECT
statements to create views, or to fetch data directly from an analytic workspace into an application.
OLAP_TABLE
returns a table of objects that can be joined to relational tables and views, or to other tables of objects populated by OLAP_TABLE
. It can also return stored workspace data, or it can perform calculations on stored data and return the results of the calculations.
Example 7-4 is a template that you can use as the starting point for the SQL scripts that you will develop for extracting data from your analytic workspace. You can then execute the script with the @
command in SQL*Plus.
Note: Be sure to verify that you have created the views correctly by issuingSELECT statements against them. Only at that time will any errors in the call to OLAP_TABLE appear. |
Example 7-4 Template for Using OLAP_TABLE
SET ECHO ON SET SERVEROUT ON --CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, columnn FROM TABLE(OLAP_TABLE( 'connection', 'table_obj', 'datamap', 'limit_map')) MODEL ( DIMENSION BY(et_dims, gids) MEASURES(measures, attributes, row2cell) RULES UPDATE SEQUENTIAL ORDER(); / COMMIT / GRANT SELECT ON view_name TO PUBLIC;
See Also: Oracle OLAP Reference for a description ofOLAP_TABLE syntax. |
When used in a SELECT
statement that queries OLAP_TABLE
, the MODEL
clause is an optimization that results in significantly faster response time. It can be used only when creating a table type with embedded total dimensions, such as the views used by the BI Beans and the OLAP API.
Note that while the MODEL
clause is used in relational queries for inter-row calculations, you should not use it for this purpose with OLAP_TABLE
. For OLAP_TABLE
, the MODEL
clause is used only to optimize the query.
See Also: TheSELECT entry in the Oracle Database SQL Reference for general information about the MODEL clause. |
When used in a SELECT
statement that queries an analytic workspace, MODEL
has the following arguments.
The names of the embedded total dimension columns, as defined in the limit map. For BI Beans applications, include the GID
columns in this list.
Any other columns in the DIMENSION BY
list disables this optimization. A properly constructed SELECT
statement still executes, but more slowly.
The measures, attributes, R2Cs, and any other columns excluded from the DIMENSION BY
list.
The RULES
clause is required, but it should not include complex or inter-row calculations since they will slow the query. Any calculations specified in the RULES
clause are performed by SQL. If you want to perform inter-row calculations, you can create a custom measure in the analytic workspace using any of the alternative methods discussed in this chapter, including the limit map of OLAP_TABLE
.
UPDATE
indicates that you are not adding any custom members in the DIMENSION BY
clause. Be sure to include this keyword, because otherwise the SQL WHERE
clauses for measures are discarded, which can significantly degrade performance.
SEQUENTIAL ORDER
prevents Oracle from evaluating the rules to ascertain their dependencies.
The Global Corporation requires numerous custom measures in addition to the three stored measures fetched from a star schema into the GLOBAL
analytic workspace. The OLAP_TABLE
function offers a method of creating these derived measures, although other methods (described previously in this chapter) are also available to GLOBAL
.
UNITS
is one of the stored measures, and the units for the prior period is a required derived measures. Although they are not required, other derived measures such as the difference from the prior period or the percent change may also be desirable.
Derived measures can be defined permanently in the analytic workspace or specified in the syntax of the OLAP_TABLE
function. This example adds these two measures:
UNITS_PP
calculates the units sold in the prior period.
UNITS_PCTCHG_PP
is the percent change from the prior period.
This example creates a new OLAP Catalog cube for these measures.
If it does not already exist, add UNITS_PP
, which returns the value of the prior time period, to the GLOBAL
analytic workspace with these commands:
DEFINE units_pp FORMULA LAG(units, 1, time, LEVELREL time_levelrel) UPDATE;COMMIT
This syntax for defining a formula gives it the same data type and dimensionality as the source object. The new formula has this definition:
DEFINE UNITS_PP FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> EQ lag(units, 1, time, levelrel time_levelrel)
Alternatively, you can define UNITS_PP
using the property sheets in Analytic Workspace Manager.
Example 7-7 defines UNITS_PCTCHG_PP
in the OLAP_TABLE
function, using the OLAP DML LAGPCT
function. UNITS_PCTCHG_PP
calculates the percent change from the prior period.
Neither UNITS_PP
nor UNITS_PCTCHG_PP
are defined as standard form measures. To comply with standard form, they need several OLAP DML properties, and they must be registered as measures in the standard form catalogs. However, OLAP_TABLE
and the OLAP Catalog do not require standard form; only the tools that simplify their use require standard form.
Example 7-5 shows a script that fetches data directly into a SQL application using a SELECT
statement with the OLAP_TABLE
function. This selection is separate from any application enablement process.
To query the Units measures in the GLOBAL
analytic workspace, take these steps:
Open a file with any text editor, and enter the body of the SQL script shown in Example 7-5. Save it with a name such as units_query.sql
.
Open a SQL*Plus session with a user name that has access rights to the GLOBAL
analytic workspace.
Execute the SQL script with a command like this one:
@units_query
There is neither standard form metadata nor application metadata for UNITS_PP
. An explanation of the example follows the code.
Example 7-5 UNITS_QUERY Script for Querying with OLAP_TABLE
SELECT time_name, units, units_pp FROM TABLE(OLAP_TABLE( 'global DURATION SESSION', '', 'LIMIT customer_hierlist TO 2', 'MEASURE units AS NUMBER(16) FROM units MEASURE units_pp AS NUMBER(16) FROM units_pp DIMENSION channel_dim FROM channel WITH HIERARCHY channel_parentrel DIMENSION product_dim FROM product WITH HIERARCHY product_parentrel DIMENSION customer_dim FROM customer WITH HIERARCHY customer_parentrel DIMENSION time_dim FROM time WITH HIERARCHY time_parentrel ATTRIBUTE time_name AS VARCHAR2(8) FROM time_long_description')) WHERE units IS NOT NULL and channel_dim = '1' and product_dim = '1' and customer_dim = '21'; MODEL DIMENSION BY(channel_dim, product_dim, customer_dim, time_dim) MEASURES(units, units_pp, time_name) RULES UPDATE SEQUENTIAL ORDER (); /
Example 7-6 shows the results of running the script in Example 7-5.
Example 7-6 Results of Running the UNITS_QUERY Script
@units_query TIME_NAM UNITS UNITS_PP -------- ---------- ---------- Jan-98 11357 Feb-98 11336 11357 Mar-98 11184 11336 . . . 2001 230913 202580 2002 201590 230913 2003 109711 201590 93 rows selected.
In Example 7-5, the arguments to OLAP_TABLE
provide the most basic information: the measures you want to see, their dimensions, and the descriptive names for time periods that make this data meaningful. In addition, the OLAP_TABLE
function needs the names of the parent relations, which define the hierarchical structure of the dimensions. Since these dimensions were created by the Create Analytic Workspace wizard in Analytic Workspace Manager, the parent relations are named dimension_PARENTREL
.
The CUSTOMER
dimension has two hierarchies, and a LIMIT
command selects the second hierarchy, MARKET_SEGMENTS
; SHIPMENTS
is the first hierarchy in the CUSTOMER_HIERLIST
hierarchy dimension, and so it is the default. The other dimensions have only one hierarchy, so there is no need to limit their hierlist dimensions.
The limit map identifies two measures (UNITS and UNITS_PP), both of which are formulas in the analytic workspace. UNITS calculates aggregates from a stored measure, and UNITS_PP
returns the value of the prior period, as defined in "Defining Formulas in the Analytic Workspace". Data types are specified only for the selected columns: TIME_NAME
, UNITS
, and UNITS_PP
.
In Example 7-5, the SELECT
statement identifies the columns and rows of interest, just as it does for physical tables in the database. In this particular selection, the WHERE
clause limits all dimensions except TIME
to a single value, then labels the result set only with the long descriptions for TIME
.
Example 7-7 shows how you can make the data in an analytic workspace available to the BI Beans using OLAP_TABLE
. The process involves these steps:
Create views that conform with the requirements of the BI Beans.
Define OLAP Catalog metadata so that the views can be queried by the BI Beans.
This example creates a measure view of UNITS
, UNITS_PP
, and UNITS_PCTCHG_PP
for the CUSTOMER
MARKET_ROLLUP
hierarchy. A second view is required for the SHIPMENTS_ROLLUP
hierarchy. The example does not show the dimension views either, although the OLAP Catalog and the BI Beans require views of each dimension.
UNITS_PCTCHG_PP
is a custom measure defined in the limit map using the AW_EXPR
keyword. It uses the OLAP DML LAGPCT
function to calculate the percent difference from the prior period.
To create the views for the OLAP API, take these steps:
Open a file with any text editor, and enter the body of the SQL script shown in Example 7-7. Save it with a name such as ts_views.sql
.
Open a SQL*Plus session with a user name that has access rights to the GLOBAL
analytic workspace.
Execute the SQL script with a command like this one:
@ts_view
Commit these changes to the database.
Issue SELECT
commands against the views to verify that they were defined correctly; if not, an error will be generated.
Example 7-7 Creating Views for the OLAP API
CREATE OR REPLACE VIEW ts_view_1 AS SELECT * FROM TABLE(OLAP_TABLE( 'global DURATION SESSION', '', 'LIMIT customer_hierlist to 1', 'MEASURE units AS NUMBER(16) FROM units MEASURE units_pp AS NUMBER(16) FROM units_pp MEASURE units_pctchg_pp AS NUMBER(8,2) FROM AW_EXPR LAGPCT(units, 1, time LEVELREL time_levelrel ROW2CELL r2c DIMENSION channel_et AS VARCHAR2(4) FROM channel WITH HIERARCHY channel_parentrel INHIERARCHY channel_inhier GID channel_gid AS NUMBER(2) FROM channel_gid DIMENSION product_et AS VARCHAR2(4) FROM product WITH HIERARCHY product_parentrel INHIERARCHY product_inhier GID product_gid AS NUMBER(2) FROM product_gid DIMENSION customer_et AS VARCHAR2(4) FROM customer WITH HIERARCHY customer_parentrel INHIERARCHY customer_inhier GID customer_gid AS NUMBER(2) from customer_gid DIMENSION time_et AS VARCHAR2(8) FROM time WITH HIERARCHY time_parentrel INHIERARCHY time_inhier GID time_gid AS NUMBER(2) FROM time_gid')) WHERE units IS NOT NULL MODEL DIMENSION BY(channel_et, channel_gid, product_et, product_gid, customer_et, customer_gid, time_et, time_gid) MEASURES(units, units_pp, units_pctchg_pp,r2c) RULES UPDATE SEQUENTIAL ORDER ();
Example 7-7 defines a view that conforms to the requirements of the OLAP API for a fact table:
Each dimension has one embedded total column for its members at all hierarchical levels. The columns are named dimension_ET
to match the views generated by the OLAP API enabler.
Each dimension has a column for its grouping IDs. The columns are named dimension_GID
to match the views generated by the OLAP API enabler.
A ROW2CELL
column is defined for use by the OLAP_EXPRESSION
function.
For each dimension, the view identifies these analytic workspace objects:
The HIERARCHY
relation, which defines the hierarchical relationship among dimension members by identifying the parent of each member.
The INHIERARCHY
variable, which identifies whether a dimension member is in the selected hierarchy.
The GID
variable, as described previously.
These objects were created by the Create Analytic Workspace wizard. Notice that the GID variables are the only ones that are mapped to columns in the view.
To define OLAP Catalog metadata for views of an analytic workspace, you must use the CWM2
write APIs. You can then view CWM2
metadata in the OLAP Catalog view of Analytic Workspace Manager, or by querying the OLAP Catalog views directly in SQL. You can neither define nor view CWM2
metadata using Oracle Enterprise Manager.
The new measures (UNITS_PP
and UNITS_PCTCHG_PP
) could be added to the existing Units cube. However, Example 7-8 shows how you can create a new cube for them using predefined dimensions. The example also creates a new measure folder.
To create the OLAP Catalog metadata for the new measures, follow these steps:
Open a file with any text editor, and enter the body of the SQL script shown in Example 7-8. Save it with a name such as ts_cwm.sql
.
Refer to the Oracle OLAP Reference for the complete syntax and usage notes for the CWM2
APIs.
Open a SQL*Plus session with a user name that has access rights to the GLOBAL
analytic workspace and issue these commands:
SET ECHO ON SET LINESIZE 135 SET PAGESIZE 50 SET SERVEROUTPUT ON FORMAT WRAPPED SIZE 1000000 EXECUTE CWM2_OLAP_MANAGER.SET_ECHO_ON;
These settings enable you to see any error messages and view the full report from the validation programs that are run by the script. It is important to validate the metadata before committing it to your database.
Execute the SQL script with a command like this one:
@ts_cwm
Note: If the validation messages exceed the maximum buffer size for SQL*Plus, you can redirect them to a log file by using CWM2_OLAP_MANAGER.BEGIN_LOG
.
If there are errors, then take these steps:
Issue a ROLLBACK
command,
Fix the errors in the script.
Rerun the script.
Copy the metadata to special views for the BI Beans:
EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH();
This procedure issues a COMMIT
.
Once these measures are defined in the OLAP Catalog, they are available to BI Beans applications the same as the standard form measures. Figure 7-2 shows the result set of a query issued through a BI Beans application.
Figure 7-2 New Measures Queried Using a BI Beans Sample Application
Example 7-8 Script for Creating OLAP Catalog Metadata for GLOBAL Measures
BEGIN -- Define TS_CUBE cube with predefined dimensions CWM2_OLAP_CUBE.CREATE_CUBE('GLOBAL_AW', 'TS_CUBE', 'TS Cube', 'TS Cube', 'Units Time Series Cube'); CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'CHANNEL'); CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'PRODUCT'); CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'CUSTOMER'); CWM2_OLAP_CUBE.ADD_DIMENSION_TO_CUBE('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'Time'); CWM2_OLAP_MEASURE.CREATE_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'Units PP', 'Units Prior Period', 'Units Sold in Prior Period'); CWM2_OLAP_MEASURE.CREATE_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'Units PctChgPP', 'Units Pct Chg PP', 'Percent Difference in Units Sold From Prior Period'); -- Map TS_VIEW_1 view to metadata cube TS_CUBE CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_LEVELKEY('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'TS_VIEW_1', 'ET', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.Time/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET'); CWM2_OLAP_TABLE_MAP.ADD_AWVIEW('GLOBAL_AW', 'TS_VIEW_1', 'r2c'); CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'GLOBAL_AW', 'TS_VIEW_1', 'UNITS_PP', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;'); CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'GLOBAL_AW', 'TS_VIEW_1', 'UNITS_PCTCHG_PP', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:SHIPMENTS/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;'); CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_LEVELKEY('GLOBAL_AW', 'TS_CUBE', 'GLOBAL_AW', 'TS_VIEW_2', 'ET', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;'); CWM2_OLAP_TABLE_MAP.ADD_AWVIEW('GLOBAL_AW', 'TS_VIEW_2', 'r2c'); -- Map TS_VIEW_2 view to metadata cube TS_CUBE CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PP', 'GLOBAL_AW', 'TS_VIEW_2', 'UNITS_PP', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;'); CWM2_OLAP_TABLE_MAP.MAP_FACTTBL_MEASURE('GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP', 'GLOBAL_AW', 'TS_VIEW_2', 'UNITS_PCTCHG_PP', 'DIM:GLOBAL_AW.CHANNEL/HIER:CHANNEL_ROLLUP/GID:CHANNEL_GID/LVL:CHANNEL/COL:CHANNEL_ET; DIM:GLOBAL_AW.CUSTOMER/HIER:MARKET_SEGMENT/GID:CUSTOMER_GID/LVL:SHIP_TO/COL:CUSTOMER_ET; DIM:GLOBAL_AW.PRODUCT/HIER:PRODUCT_ROLLUP/GID:PRODUCT_GID/LVL:ITEM/COL:PRODUCT_ET; DIM:GLOBAL_AW.TIME/HIER:Calendar/GID:TIME_GID/LVL:Month/COL:TIME_ET;'); -- Validate the cube metadata CWM2_OLAP_VALIDATE.VALIDATE_CUBE('GLOBAL_AW', 'TS_CUBE', 'OLAP API'); -- Create a measure folder CWM2_OLAP_CATALOG.CREATE_CATALOG('GLOBAL_ANALYTIC_CAT', 'Global Analytic Measures'); CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS'); CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS_PP'); CWM2_OLAP_CATALOG.ADD_CATALOG_ENTITY('GLOBAL_ANALYTIC_CAT', 'GLOBAL_AW', 'TS_CUBE', 'UNITS_PCTCHG_PP'); --COMMIT; end; /