OLAP_TABLE Function, 6 of 6

Example: Using the OLAP_TABLE Function

In the discussion on working with relational tables in Oracle9i OLAP Developer's Guide to the OLAP DML there is an example of creating an analytic workspace named awsh from the sample Sales History database. Assume that you want to create relational views of the cost data in the awsh analytic workspace

For cost data, the Sales History database (which is fully described in Oracle9i Sample Schemas) has a fact table named costs that contains columns for its keys (product_id and time_id) and columns for facts (unit_cost and unit_price). The keys of costs are primary keys of the products dimension table and the times dimension table. The products table represents one hierarchy with four levels (prod_id, prod_subcategory, prod_category, and products_all). The times table represents two hierarchies: Calendar Time and Fiscal. The Calendar Time hierarchy has five levels: date, calendar week, calendar month, calendar quarter, and calendar year. The Fiscal Time hierarchy has five levels: date, fiscal week, fiscal month, fiscal quarter, and fiscal year.

In the awsh analytic workspace, the Products and Times hierarchies are defined as a number of analytic workspace objects as illustrated in Example 11-1, "Definitions for Cost Data in the awsh Analytic Workspace". For the Products hierarchy, there is a dimension for each level of a hierarchy, a concat dimension named aw_products for all of the levels, and a child-parent self-relation for the concat dimension. When designing awsh, it was determined that applications only need to summarize or aggregate data from at the year level. Consequently, there are only three levels in the Time hierarchies in the analytic workspace: time id, fiscal year, and calendar year. To define for the Time hierarchies, there is an analytic workspace dimension containing the names of the two hierarchies (Calendar and Fiscal), a dimensions for each of the three levels (time, fiscal year, and calendar year), a concat dimension named aw_times for all of the levels, and a child-parent self-relation for the concat dimension. Since there are two time hierarchies the child-parent self-relation created for aw_times is dimensioned by both the concat dimension and the hierarchies (by name). The facts in the costs table are defined as analytic workspace variables dimensioned by a composite (named aw_costsdims) of aw_products and aw_times.

To prepare awsh for access by OLAP_TABLE, you need to add the analytic workspace object definitions in Example 11-2, "Definitions for Additional Analytic Workspace Objects". Example 11-3, "Preparing the awsh Analytic Workspace for OLAP_TABLE" illustrates an OLAP DML program that populates these analytic workspace objects using the HEIRHEIGHT command, the GROUPINGID command, and other OLAP DML commands.

After all of the necessary analytic workspace objects are defined and populated you can use of the OLAP_TABLE function to create a relational view of the cost data in awsh. Example 11-4, "Creating Views Using the OLAP_TABLE Function" is a script that creates these views.

The aw_products_view is a view of the Products hierarchy. It includes a column for all of the values in the hierarchy, a column for the parent of each of the values in the hierarchy, columns for the values of each level in the hierarchy and, a column for the grouping id of each value in the hierarchy. The columns that contain the values of the levels are mapped to analytic workspace data using the LEVELREL clause of the limit-map parameter of the OLAP_TABLE function.
The aw_times_view is a view of the Times hierarchies. It includes a column for all of the values in the hierarchy, a column for the parent of each values in the hierarchy, and columns for the values of each level in the hierarchy. The columns that contain the values of the levels are mapped to analytic workspace data using the ATTRIBUTE phrases in the limit-map parameter of the OLAP_TABLE function. Each ATTRIBUTE phrase maps a column in the view to a analytic workspace formula that retrieves the values of one level of the aw_times dimension.
The aw_costs_view is a view of the actual cost facts (unit_price and unit_cost) and the analytic workspace dimensions that act as the keys to these facts.

Example 11-1 Definitions for Cost Data in the awsh Analytic Workspace

DEFINE aw_prod_id DIMENSION NUMBER (6)
DEFINE aw_prod_subcategory DIMENSION TEXT
DEFINE aw_prod_category DIMENSION TEXT
DEFINE aw_products_all DIMENSION TEXT
DEFINE aw_products DIMENSION CONCAT (aw_products_all -
                                  aw_prod_category -
                                  aw_prod_subcategory -
                                  aw_prod_id)
DEFINE aw_products.parents RELATION aw_products <aw_products>

DEFINE aw_time_id DIMENSION TEXT
DEFINE aw_cal_year DIMENSION NUMBER(4)
DEFINE aw_fis_year DIMENSION NUMBER(4)
DEFINE aw_times DIMENSION CONCAT (aw_cal_year -
                                  aw_fis_year -
                                  aw_time_id)
DEFINE aw_times_hiernames DIMENSION TEXT
DEFINE aw_times.parents RELATION aw_times <aw_times aw_times_hiernames>

DEFINE aw_costsdims COMPOSITE <aw_products aw_times>
DEFINE aw_unit_cost VARIABLE  NUMBER (10,2) <aw_costsdims -
        <aw_products aw_times>>
DEFINE aw_unit_price VARIABLE NUMBER (10,2) <aw_costsdims -
        <aw_products aw_times>>

Example 11-2 Definitions for Additional Analytic Workspace Objects

DEFINE temp_levelnames DIMENSION TEXT 
LD A dimension used to sort names of levels
DEFINE aw_products_levelnames DIMENSION TEXT 
LD Names of levels of the Products hierarchy 
DEFINE aw_products_levelnums DIMENSION INTEGER
LD Levels of the Products hierarchy identified by number
DEFINE aw_times_levelnames DIMENSION TEXT 
LD Names of levels of the Times hierarchy 
DEFINE aw_times_levelnums DIMENSION INTEGER
LD Levels of the Times hierarchy identified by number
DEFINE aw_products_gid VARIABLE INTEGER <aw_products>
LD Levels of the Products hierarchy identified by GID
DEFINE aw_products.aw_products_levelnums RELATION aw_products <aw_products 
aw_products_levelnums>
LD Concat dimension values for the Products hierarchy by level number
DEFINE aw_times.aw_times_levelnums RELATION aw_times <aw_times 
aw_times_hiernames aw_times_levelnums>
LD Concat dimension values for the Times hierarchy by level number and hierarchy
DEFINE aw_products_basevalues VARIABLE aw_products <aw_products 
aw_products_levelnums>

DEFINE AW_FISCAL_YEAR_FORM FORMULA -
     BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 2 -
       aw_times_hiernames 'Fiscal'))
LD Formula that returns the base values of fiscal year
DEFINE AW_CALENDAR_YEAR_FORM FORMULA -
      BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 1 -
         aw_times_hiernames 'Calendar'))
LD Formula that returns the base values of calendar year

DEFINE AW_TIMEID_FORM FORMULA -
       BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 3 -
              aw_times_hiernames 'Calendar'))
LD Formula that returns the values of time_id

Example 11-3 Preparing the awsh Analytic Workspace for OLAP_TABLE

" Initialize the levelnames dimension for aw_products
"   Initialize the temp_levelnames dimension
MAINTAIN temp_levelnames DELETE ALL
"   Populate temp_levelnames with the lowercase names of 
"     the base dimensions of aw_products in the order they were defined
"    which is top level (world) has position 1
MAINTAIN temp_levelnames ADD LOWCASE(OBJ(DATA 'aw_products'))
"    Sort levelnames so that bottom level (prod_id)
"    has position 1
SORT temp_levelnames A temp_levelnames
"    Populate aw_products_levelnames with sorted levelnames
MAINTAIN aw_products_levelnames ADD VALUES (temp_levelnames)
"       Populate aw_products_levelnums with integers reprsenting levels
" value of 1 represents bottom level
MAINTAIN aw_products_levelnums ADD STATLEN (aw_products_levelnames)

" Initialize the levelnames dimension for aw_times
"    Initialize the temp_levelnames dimension
MAINTAIN temp_levelnames DELETE ALL
"    Populate temp_levelnames with the lowercase names of 
"    the base dimensions of aw_times in the order they were defined
MAINTAIN temp_levelnames ADD LOWCASE(OBJ(DATA 'aw_times'))
"    Sort levelnames so that bottom level has position 1
SORT temp_levelnames A temp_levelnames
"    Populate aw_times_levelnames with sorted levelnames
MAINTAIN aw_times_levelnames ADD VALUES (temp_levelnames)
"    Populate aw_products_levelnums with integers reprsenting levels
"    value of 1 represents bottom level
MAINTAIN aw_times_levelnums ADD STATLEN (aw_times_levelnames)
" Populate aw_products_gid with grouping ids
" for levels of products
GROUPINGID aw_products.parents INTO aw_products_gid 

" Populate relation with concat dimension values for the Products hierarchy
HIERHEIGHT aw_products.parents INTO aw_products.aw_products_levelnums 
" Populate relation with concat dimension values for the Times hierarchy
HIERHEIGHT aw_times.parents INTO aw_times.aw_times_levelnums  
" Populate variable with base dimension values for the Products hierarchy
aw_products_basevals  = BASEVAL(aw_products.aw_products_levelnums)
" Populate variable with base dimension values for the Times hierarchy
aw_times_basevals  = BASEVAL(aw_times.aw_times_levelnums)

"Update the analytic workspace and make changes permanent
UPDATE
COMMIT

Example 11-4 Creating Views Using the OLAP_TABLE Function

AW CONNECT / as sysdba
SET ECHO ON
SET SERVEROUT ON

DROP TYPE aw_products_tbl;
DROP TYPE aw_products_obj;
DROP TYPE aw_times_tbl;
DROP TYPE aw_times_obj;
DROP TYPE aw_costs_tbl;
DROP TYPE aw_costs_obj;

-- Create objects and tables

-- Define an object that identifies the columns for product data

CREATE TYPE aw_products_obj AS OBJECT (
     prod_hier_value       VARCHAR2(35),
     prod_hier_parent      VARCHAR2(35),
     prod_id               VARCHAR2(10),
     prod_subcategory      VARCHAR2(20),
     prod_category         VARCHAR2(5),
     prod_all              VARCHAR2(15),
     prod_hier_gid         NUMBER(10));
     
     
     
-- Define an object that identifies the columns for times data
     

CREATE TYPE aw_times_obj AS OBJECT (
     time_hier_value       VARCHAR2(20),
     time_hier_parent      VARCHAR2(20),
     calendar_year         NUMBER(4,0),
     fiscal_year           NUMBER(4,0),
     date_id               VARCHAR2(10));
    
     
-- Define an object that identifies the columns for cost data
      
CREATE TYPE aw_costs_obj AS OBJECT (
     prod_hier_value       VARCHAR2(35),
     prod_hier_parent      VARCHAR2(35),
     time_hier_value       VARCHAR2(20),
     time_hier_parent      VARCHAR2(20),
     unit_cost             NUMBER(10,2),
     unit_price            NUMBER(10,2));
      
      
 
-- Define a table of objects for products data
CREATE TYPE aw_products_tbl AS TABLE OF aw_products_obj;
-- Define a table of objects for times data
CREATE TYPE aw_times_tbl AS TABLE OF aw_times_obj;
-- Define a table of objects for cost data
CREATE TYPE aw_costs_tbl AS TABLE OF aw_costs_obj;


-- Define a view of products data
CREATE OR REPLACE VIEW aw_products_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE 
(
        'awsh duration session', 'aw_products_tbl', '', 
        'DIMENSION prod_hier_value FROM aw_products 
         WITH HIERARCHY prod_hier_parent FROM aw_products.parents 
         INHIERARCHY aw_products_inhier 
         GID prod_hier_gid FROM aw_products_gid 
         LEVELREL prod_id, prod_subcategory, prod_category, prod_all 
         FROM aw_products_basevalues USING aw_products_levelnums') 
        AS aw_products_tbl));
        
        
    
    
-- Define a view of times data
       
CREATE OR REPLACE VIEW aw_times_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE (
        'awsh duration session', 'aw_times_tbl', '', 
        'DIMENSION time_hier_value FROM aw_times 
         WITH HIERARCHY time_hier_parent FROM aw_times.parents 
         ATTRIBUTE calendar_year FROM aw_calendar_year_form
         ATTRIBUTE fiscal_year FROM aw_fiscal_year_form
         ATTRIBUTE date_id FROM aw_timeid_form ') 
        AS aw_times_tbl));    

-- Define a view of cost data

CREATE OR REPLACE VIEW aw_costs_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE (
        'awsh duration session', 'aw_costs_tbl', '', 
        'MEASURE unit_cost FROM aw_unit_cost
         MEASURE unit_price FROM aw_unit_price
         DIMENSION prod_hier_value FROM aw_products 
         WITH HIERARCHY prod_hier_parent FROM aw_products.parents
         DIMENSION time_hier_value FROM aw_times 
         WITH HIERARCHY time_hier_parent FROM aw_times.parents') 
        AS aw_costs_tbl));    
        
 
-- Grant selection rights to the views
GRANT SELECT ON aw_products_view TO PUBLIC;   
GRANT SELECT ON aw_times_view TO PUBLIC; 
GRANT SELECT ON aw_costs_view TO PUBLIC; 
-- Define a view of sales data
CREATE OR REPLACE VIEW olap_sales_view AS
  SELECT * 
  FROM TABLE(OLAP_TABLE('XADEMO DURATION SESSION', 'XASALES_T', '', 
                    'MEASURE sales FROM aw_f.sales
                     DIMENSION et_chan FROM aw_channel WITH
                       HIERARCHY aw_channel.parent
                         GID gid_chan FROM aw_channel.gid
                     DIMENSION et_prod FROM aw_product WITH
                       HIERARCHY aw_product.parent
                         GID gid_prod FROM aw_prod.gid
                     DIMENSION et_geog FROM aw_geography WITH
                       HIERARCHY aw_geography.parent
                         GID gid_geog FROM aw_geog.gid
                     DIMENSION et_time FROM aw_time WITH
                       HIERARCHY time.parent
                         GID gid_time FROM aw_time.gid'));