Examples

Because different applications have different requirements, several different formats are commonly used for fetching data into SQL from an analytic workspace. The examples in this chapter show how to create views using a variety of different formats.

Although these examples are shown as views, the SELECT statements can be extracted from them and used directly to fetch data from an analytic workspace into an application.

Creating a View

To create a view, use a text editor to create a PL/SQL script that defines the row, the table, and the view. Example 12-3 is a template that you can use as the starting point for the SQL scripts that you will develop for views of your analytic workspace. You can then execute the script with the @ command in SQL*Plus.

Example 12-3 Template for Creating a View

SET ECHO ON
SET SERVEROUT ON

DROP TYPE table_obj;
DROP TYPE row_obj;

CREATE TYPE row_obj AS OBJECT (
   column_first     datatype,
   column_next      datatype,
   column_last      datatype);
/   
CREATE TYPE table_obj AS TABLE OF row_obj;
/
CREATE OR REPLACE VIEW view AS
SELECT column1, column2, columnn
   FROM TABLE(OLAP_TABLE(
      'connection', 
      'table_obj', 
      'olap_command',
      'limit_map'));
/
COMMIT
/ 
GRANT SELECT ON view TO PUBLIC;        

Creating Views of Embedded Total Dimensions

Example 12-4 shows the PL/SQL script used to create a view of the TIME dimension STANDARD hierarchy.

Example 12-4 Script for a Dimension View

CREATE TYPE time_std_row AS OBJECT (
   time_id                   VARCHAR2(16),
   standard_short_label      VARCHAR2(16),
   standard_end_date         DATE,
   standard_timespan         NUMBER(6));
/   
   
CREATE TYPE time_std_table AS TABLE OF time_std_row;
/

CREATE OR REPLACE VIEW time_std_view AS
SELECT time_id, standard_short_label, standard_end_date, standard_timespan
FROM TABLE(OLAP_TABLE('xademo DURATION SESSION', 'time_std_table', 
      'LIMIT time_hierlist TO ''STANDARD''',
      'DIMENSION time_id FROM time WITH 
       HIERARCHY time_member_parentrel
           INHIERARCHY time_member_inhier
       ATTRIBUTE standard_short_label FROM time_short.description
       ATTRIBUTE standard_end_date FROM time_end_date
       ATTRIBUTE standard_timespan FROM time_time_span'));
/
          
          
SQL> SELECT * FROM time_std_view;

TIME_ID  STANDARD STANDARD_ STANDARD_TIMESPAN
-------- -------- --------- -----------------
L1.1996  1996     31-DEC-96               366
L1.1997  1997     31-MAY-97               151
L2.Q1.96 Q1.96    31-MAR-96                91
L2.Q2.96 Q2.96    30-JUN-96                91
L2.Q3.96 Q3.96    30-SEP-96                92
L2.Q4.96 Q4.96    31-DEC-96                92
L2.Q1.97 Q1.97    31-MAR-97                90
L2.Q2.97 Q2.97    31-MAY-97                61
L3.JAN96 Jan96    31-JAN-96                31
L3.FEB96 Feb96    29-FEB-96                29
L3.MAR96 Mar96    31-MAR-96                31

                                  .
                                  .
                                  .

Creating Views of Embedded Total Measures

In a star schema, a separate measure view is needed with columns that can be joined to each of the dimension views. Example 12-5 shows the PL/SQL script used to create a measure view with a column populated by ROW2CELL to support custom measures. For an example of creating a custom measure, refer to "OLAP_EXPRESSION Function".

Example 12-5 Script for a Measure View

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(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'))
   WHERE sales IS NOT NULL;
/

SQL> SELECT channel, sales, cost, promotions, quota, units FROM measure_view 
     WHERE    product = 'L1.TOTALPROD'
     AND    geography = 'L1.WORLD'
     AND         time = 'L1.1996';
         

CHANNEL                             SALES       COST PROMOTIONS      QUOTA     UNITS
------------------------------ ---------- ---------- ---------- ---------- ---------
STANDARD_1.CATALOG               76843552     125398     110249      16525     25209
STANDARD_1.DIRECT                41403560    2364845     518649    5458917    118851
STANDARD_2.TOTALCHANNEL         118247112    2490243     628898    5475442    144060

Creating Views in Rollup Form

Rollup form uses a column for each hierarchy level to show the full parentage of each dimension member. The only difference between the syntax for rollup form and the syntax for embedded total form is the addition of a FAMILYREL clause in the definition of each dimension in the limit map.

Example 12-6 shows the PL/SQL script used to create a rollup view of the PRODUCT dimension. It shows a dimension view to highlight the differences in the syntax of the limit map from the one used for the embedded total form, as shown in Example 12-4, "Script for a Dimension View". Note that the target columns for these levels are listed in the FAMILYREL clause from base level to most aggregate, which is the order they are listed in the level list dimension. The family relation returns four columns. The most aggregate level (all products) is omitted from the view by mapping it to null.

Example 12-7 shows the alternative syntax for the FAMILYREL clause, which uses QDRs to identify exactly which columns will be mapped from the family relation.

These two limit maps generate identical views.

Example 12-6 Script for a Rollup View of Products

CREATE TYPE product_row AS OBJECT (
   equipment      VARCHAR2(20),
   components     VARCHAR2(20),
   divisions      VARCHAR2(20));
/   
   
CREATE TYPE product_table AS TABLE OF product_row;
/

CREATE OR REPLACE VIEW product_view AS
SELECT equipment, components, divisions
   FROM TABLE(OLAP_TABLE('xademo DURATION QUERY', 'product_table', 
   '',
   'DIMENSION product WITH 
       HIERARCHY product_member_parentrel
       FAMILYREL equipment, components, divisions, null
          FROM product_member_familyrel USING product_levellist
          LABEL product_short.description 
     '));


SQL>  SELECT * FROM product_view
      ORDER BY divisions, components, equipment;

EQUIPMENT            COMPONENTS           DIVISIONS
-------------------- -------------------- --------------------
Chrm Cas             Audio Tape           Accessory Div
Mtl Cassette         Audio Tape           Accessory Div
Std Cassette         Audio Tape           Accessory Div
                     Audio Tape           Accessory Div
                     .
                     .
                     .
Standard VCR         VCR                  Video Div
Stereo VCR           VCR                  Video Div
                     VCR                  Video Div
                                          Video Div

Example 12-7 Script Using QDRs in the FAMILYREL Clause

CREATE TYPE product_row AS OBJECT (
   equipment      VARCHAR2(15),
   components     VARCHAR2(15),
   divisions      VARCHAR2(15));
/   
   
CREATE TYPE product_table AS TABLE OF product_row;
/

CREATE OR REPLACE VIEW product_view AS
SELECT equipment, components, divisions
   FROM TABLE(OLAP_TABLE('xademo DURATION QUERY', 'product_table', 
   '',
   'DIMENSION product WITH 
       HIERARCHY product_member_parentrel
          FAMILYREL equipment, components, divisions FROM 
             product_member_familyrel(product_levellist ''L4''), 
             product_member_familyrel(product_levellist ''L3''),
             product_member_familyrel(product_levellist ''L2'')
             LABEL product_short.description   
     '));
/
          
SQL> SELECT * FROM product_view
     ORDER BY divisions, components, equipment;

EQUIPMENT       COMPONENTS      DIVISIONS
--------------- --------------- ---------------
Chrm Cas        Audio Tape      Accessory Div
Mtl Cassette    Audio Tape      Accessory Div
Std Cassette    Audio Tape      Accessory Div
                Audio Tape      Accessory Div
                .
                .
                .
Standard VCR    VCR             Video Div
Stereo VCR      VCR             Video Div
                VCR             Video Div
                                Video Div