CWM2_OLAP_AW_ACCESS , 7 of 10

Example: Creating Views

This example creates fact views and dimension views for two variables, sales and costs. These variables were not created by the AW_CREATE process.

The following are the object definitions for sales and costs. Note that they are dimensioned identically.

DEFINE SALES VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
DEFINE COSTS VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>

In a star schema for use with OLAP Catalog metadata, you would create dimension views for each hierarchy and fact views for each combination of dimension hierarchies.

If the hierarchies shown in Table 15-4 have been defined for these dimensions, then the following views must be generated:

Six dimension views(2+1+1+2)

Four fact views (2*1*1*2)

Table 15-4 Sample Dimension Hierarchies

Dimensions	Hierarchies	Required Number of Views
`geography`	`standard` `consolidated`	2
`product`	`standard`	1
`channel`	`standard`	1
`time`	`standard` `ytd`	2

Example: Input Files for Mapping Variables to Views

This example creates views in a star schema for use by the OLAP API.

Geography Dimension Standard Hierarchy View

These statements define the geography dimension view for the STANDARD hierarchy. A separate file is required to generate another view to support the CONSOLIDATED hierarchy, but it is not included in this example.

DIMENSION::geography
HIERARCHY::geography.parentrel
INHIERARCHY: geography.inhierarchy
HIERARCHY DIMENSION::geography.hierarchies
HIERARCHY DIMENSION VALUE::STANDARD
GID::geography.gid
PARENT GID::geography.gid
ATTRIBUTES::geography.longlabel::geography.shortlabel
COLUMN LEVEL DIMENSION::geography.lvldim
COLUMN LEVEL RELATION::geography.hierheight

DIMENSION COLUMN::geography
PARENT COLUMN::geog_parent
GID COLUMN::geog_gid
PARENT GID COLUMN::geogp_gid
DIMENSION DATATYPES::varchar2(16)::varchar2(16)::number(10)::number(10)
LEVEL COLUMNS::city::country::continent::world
LEVEL DATATYPES::varchar2(16)::varchar2(16)::varchar2(16)::varchar2(16)
ATTRIBUTE COLUMNS::geog_long::geog_short
ATTRIBUTE DATATYPES::varchar(32)::varchar(16)

Product Dimension View

The following statements define the product dimension view.

DIMENSION::product
HIERARCHY::product.parentrel
GID::product.gid
PARENT GID::product.gid
ATTRIBUTES::product.longlabel::product.shortlabel
COLUMN LEVEL DIMENSION::product.lvldim
COLUMN LEVEL RELATION::product.hierheight

DIMENSION COLUMN::product
PARENT COLUMN::prod_parent
GID COLUMN::prod_gid
PARENT GID COLUMN::prod_gid
DIMENSION DATATYPES::varchar2(16)::varchar2(16)::number(10)::number(10)
LEVEL COLUMNS::equipment::component::division::totalprod
LEVEL DATATYPES::varchar2(16)::varchar2(16)::varchar2(16)::varchar2(16)
ATTRIBUTE COLUMNS::prod_long::prod_short
ATTRIBUTE DATATYPES::varchar(32)::varchar(16)

Channel Dimension View

These statements define the channel dimension view.

DIMENSION::channel
HIERARCHY::channel.parentrel
GID::channel.gid
PARENT GID::channel.gid
ATTRIBUTES::channel.longlabel::channel.shortlabel
COLUMN LEVEL DIMENSION::channel.lvldim
COLUMN LEVEL RELATION::channel.hierheight

DIMENSION COLUMN::channel
PARENT COLUMN::chan_parent
GID COLUMN::chan_gid
PARENT GID COLUMN::chanp_gid
DIMENSION DATATYPES::varchar2(16)::varchar2(16)::number(10)::number(10)
LEVEL COLUMNS::outlet::totalchan
LEVEL DATATYPES::varchar2(16)::varchar2(16)
ATTRIBUTE COLUMNS::chan_long::chan_short
ATTRIBUTE DATATYPES::varchar(32)::varchar(16)

Time Standard Hierarchy Input File

These statements define the time dimension view for the STANDARD hierarchy. A separate file is required to generate another view to support the YTD hierarchy, but it is not included in this example.

DIMENSION::time
HIERARCHY::time.parentrel
INHIERARCHY: time.inhierarchy
HIERARCHY DIMENSION::time.hierarchies
HIERARCHY DIMENSION VALUE::STANDARD
GID::time.gid
PARENT GID::time.gid
ATTRIBUTES::time.longlabel::time.shortlabel
COLUMN LEVEL DIMENSION::time.lvldim
COLUMN LEVEL RELATION::time.hierheight

DIMENSION COLUMN::time
PARENT COLUMN::time_parent
GID COLUMN::time_gid
PARENT GID COLUMN::timep_gid
DIMENSION DATATYPES::varchar2(8)::varchar2(8)::number(10)::number(10)
LEVEL COLUMNS::month::quarter::year
LEVEL DATATYPES::varchar2(16)::varchar2(16)::varchar2(16)
ATTRIBUTE COLUMNS::time_long::time_short
ATTRIBUTE DATATYPES::varchar(32)::varchar(16)

Sales and Costs Fact Views

For the OLAP API, you need to create a fact view for each combination of dimension hierarchies. In addition to the fact columns, the OLAP API also needs columns for dimension members and grouping IDs.

The following statements identify two workspace measures, sales and costs, as the source objects for a fact view. The fact view will have columns for the data from sales and costs. Both of these columns will have a NUMBER data type with 12 significant digits and 2 decimal places. The data from sales will be fetched into the sales column, and the data from costs will be fetched into the costs column.

The following is an example of just one of the four input files needed by the sales and costs measures. The statements defining the product and channel columns are also omitted, as indicated by the ellipsis.

MEASURE::sales::costs
MEASURE COLUMNS::sales::costs
MEASURE DATATYPES::number(12,2)::number(12,2)

DIMENSION::geography
HIERARCHY::geography.parentrel
INHIERARCHY: geography.inhierarchy
HIERARCHY DIMENSION::geography.hierarchies
HIERARCHY DIMENSION VALUE::STANDARD
GID::geography.gid

DIMENSION COLUMN::geography
GID COLUMN::geog_gid
DIMENSION DATATYPES::varchar2(16)::number(10)
     .
     .
     .
DIMENSION::time
HIERARCHY::time.parentrel
INHIERARCHY: time.inhierarchy
HIERARCHY DIMENSION::time.hierarchies
HIERARCHY DIMENSION VALUE::STANDARD
GID::time.gid

DIMENSION COLUMN::time
GID COLUMN::time_gid
DIMENSION DATATYPES::varchar2(8)::number(10)

Example: Script for the Product View

This PL/SQL command uses the /users/oracle/mapfiles/product.txt input file shown in "Product Dimension View" to generate a script named /users/oracle/scripts/product.sql. The resulting view will be named electro_product_view.

CALL CWM2_OLAP_AW_ACCESS.CREATEAWACCESSSTRUCTURES_FR(
   '/users/oracle/scripts/', 'product.sql', 'electro_product_',
   'scott.electronics', '/users/oracle/mapfiles/', 'product.txt');

Before executing the script, you may edit it.

--product.sql
--Generated on: 15-FEB-2002 09:16:42am

SET ECHO ON
SET LINESIZE 200
SET PAGESIZE 50
SET SERVEROUT ON

DROP TYPE electro_product_TBL;
DROP TYPE electro_product_OBJ;

CREATE TYPE electro_product_OBJ AS OBJECT (
    PRODUCT VARCHAR2(16),
    PROD_PARENT VARCHAR2(16),
    PROD_GID NUMBER(10),
    PRODP_GID NUMBER(10),
    EQUIPMENT VARCHAR2(16),
    COMPONENT VARCHAR2(16),
    DIVISION VARCHAR2(16),
    TOTALPROD VARCHAR2(16),
    PROD_LONG VARCHAR(32),
    PROD_SHORT VARCHAR(16));
/

CREATE TYPE electro_product_TBL AS TABLE OF electro_product_OBJ;
/

CREATE OR REPLACE FUNCTION electro_product_LMAP RETURN VARCHAR2 IS
--This function will return the following Limit Map:
--DIMENSION PRODUCT FROM PRODUCT
--    WITH HIERARCHY PROD_PARENT FROM PRODUCT.PARENTREL
--        GID PROD_GID FROM PRODUCT.GID
--        PARENTGID PRODP_GID FROM PRODUCT.GID
--        LEVELREL EQUIPMENT, COMPONENT, DIVISION, TOTALPROD FROM 
PRODUCT.HIERHEIGHT USING PRODUCT.LVLDIM
--    ATTRIBUTE PROD_LONG FROM PRODUCT.LONGLABEL
--    ATTRIBUTE PROD_SHORT FROM PRODUCT.SHORTLABEL
vRetVal VARCHAR2(443) := '';

BEGIN
    vRetVal := vRetVal || 'DIMENSION PRODUCT FROM PRODUCT  ';
    vRetVal := vRetVal || 'WITH HIERARCHY PROD_PARENT FROM PRODUCT.PARENTREL  ';
    vRetVal := vRetVal || 'GID PROD_GID FROM PRODUCT.GID  ';
    vRetVal := vRetVal || 'PARENTGID PRODP_GID FROM PRODUCT.GID  ';
    vRetVal := vRetVal || 'LEVELREL EQUIPMENT, COMPONENT, DIVISION, TOTALPROD 
FROM PRODUCT.HIERHEIGHT USING PRODUCT.LVLDIM  ';
    vRetVal := vRetVal || 'ATTRIBUTE PROD_LONG FROM PRODUCT.LONGLABEL  ';
    vRetVal := vRetVal || 'ATTRIBUTE PROD_SHORT FROM PRODUCT.SHORTLABEL';
    RETURN vRetVal;
END electro_product_LMAP;
/

SHOW ERRORS;

CREATE OR REPLACE VIEW electro_product_VIEW AS SELECT * FROM 
TABLE(CAST(OLAP_TABLE('scott.electronics DURATION QUERY', 'electro_product_TBL', 
'', electro_product_LMAP())AS electro_product_TBL));

--The command below should be modified to provide appropriate security to 
Analytic Workspace data.
--GRANT SELECT ON electro_product_VIEW TO PUBLIC;

--End of file: product.sql

Example: Product View

The script shown in "Example: Script for the Product View" creates a view named ELECTRO_PRODUCT_VIEW, which has the following definition:

SELECT "PRODUCT", "PROD_PARENT", "PROD_GID", "PRODP_GID" "EQUIPMENT",
     "COMPONENT","DIVISION, "TOTALPROD", "PROD_LONG", "PROD_SHORT" 
     FROM TABLE(CAST (OLAP_TABLE('scott.electronics DURATION QUERY', 
     'electro_product_TBL', '', electro_product_LMAP()) AS electro_product_TBL))

Use a command like the following to access data about products from the electronics analytic workspace:

select product, prod_long, prod_short from electro_product_view 
     where prod_gid=0;

PRODUCT          PROD_LONG                        PROD_SHORT
---------------- -------------------------------- ----------------
PORTCD           Portable CD Player               Port CD
PORTST           Portable Stereo                  Port Stereo
PORTCAS          Portable Cassette                Port Cassette
TUNER            Tuner                            Tuner
                     .
                     .
                     .
METALCAS         Metal Cassette                   Mtl Cassette
STNDCAS          Standard Cassette                Std Cassette
STNDVHSVIDEO     Standard VHS Video               VHS Video
8MMVIDEO         8MM Video                        8MM Video
HI8VIDEO         Hi 8 Video                       Hi8 Video

22 rows selected.