Working with Relational Tables, 4 of 7

Example: Creating an Analytic Workspace from Sales History Tables

The sample Sales History database, which is fully described in Oracle9i Sample Schemas, has six dimension tables and two fact tables:

countries, a dimension table that has a primary key of country_id.
customers, a dimension table that has a primary key of customer_id and a foreign key of country_id.
promotions, a dimension table that has a primary key of promo_id.
products, a dimension table that has a primary key of product_id.
channels, a dimension table that has a primary key of channel_id.
times, a dimension table that has a primary key of time_id.
sales, a fact table that has customer_id, promo_id, product_id, channel_id, and time_id as keys.
costs, a fact table that has which has product_id and time_id as keys.

Assume that you want to analyze all of the fact data in the sample Sales History database. In order to do this you need to design and define an analytic workspace as described in "Designing and Defining an Analytic Workspace for Sales History Data". Then you need to write OLAP DML programs to copy the necessary relational data into the analytic workspace as described in "Populating Analytic Workspace Objects with Sales History Data".

Designing and Defining an Analytic Workspace for Sales History Data

The analytic workspace for Sales History was designed and defined following the process described in "Process: Designing and Defining an Analytic Workspace to Hold Relational Data". The actual steps are outlined below:

An analytic workspace named awsh was created using the following OLAP DML command.
```
AW CREATE awsh
```
The fact data was identified. In the sales table, the quantity_sold and the amount_sold columns were identified as containing facts for analysis. While, in the costs table, the unit_cost and unit_price columns contain fact data of interest.
The primary keys to the sales and costs tables were identified. The primary keys of sales are prod_id, cust_id, time_id, channel_id, and promo_id. The primary keys of costs are prod_id and time_id.
Looking at the primary keys, the following hierarchies in the Sales History database were identified:
- Products -- This hierarchy has four levels (prod_id, prod_subcategory, prod_category, and products_all) that map to columns in the products tables. The lowest level of the hierarchy is prod_id and the highest level is products_all.
- Channels -- This hierarchy has three levels (channel_id, channel_class, and channels_all) that map to columns in the channels tables. The lowest level of the hierarchy is channel_id and the highest level is channels_all.
- Promotions -- This hierarchy has four levels (promo_id, promo_subcategory, promo_category, and promos_all) that map to columns in the promotions tables. The lowest level of the hierarchy is promo_id and the highest level is promos_all.
- Customers -- This hierarchy has seven levels that map to columns in two different relational tables. Four of these levels (country_id, region, subreagion, and world) map to columns in the countries table and three levels (cust_id, state_province, and city) map to columns in the customers table. The lowest level of the hierarchy is cust_id and the highest level is world.
- Time hierarchies-- Two time hierarchies were identified: Calendar and Fiscal.
  
  Calendar Time-- This hierarchy has five levels (time_id, cal_week_num, cal_month_num, cal_quarter_num, and cal_year) that map to columns in the times table.
  
  Fiscal Time -- This hierarchy has five levels (time_id, fis_week_num, fis_month_num, fis_quarter_num, and fis_year) that map to columns in the times table.
Also, a one-to-many relationship between prod_id and supplier_id was identified.
Our application needs to aggregate (summarize) fact data for each level of the Products, Customers, Channels, and Promotions hierarchies. For the time hierarchies, our application only needs hierarchies with two levels -- the lowest level of the hierarchy (time_id) and year (cal_year and fis_year).
The following analytic workspace objects were defined to represent the hierarchies:
- For Products, Customers, Channels, and Promotions hierarchies, a dimension was defined for each level of the hierarchy, a concat dimension was defined for each hierarchy, and a child-parent self-relation was defined for each concat dimension. These definitions are shown in examples Example 10-8 through Example 10-11 .
- For the time hierarchies, two hierarchies were defined. A dimension containing the names of the two hierarchies was created. Base dimensions were defined for time_id, fis_year, and cal_year and a concat dimension was defined that specified all of these dimensions as base dimensions. Since there are two time hierarchies the child-parent self-relation created for the Times hierarchy is dimensioned by both the concat dimension and the hierarchies (by name). These definitions are shown in Example 10-12, "Analytic Workspace Definitions for the Times Hierarchies"
- For the facts (quantity_sold, amount_sold, the unit_cost and unit_price), analytic workspace variables were defined. All of these variables would be sparsely populated if they were dimensioned by the concat dimensions, so one composite was defined for each variable. The variables are dimensioned by those composites. The definitions for the variables for the fact data is shown in Example 10-13, "Analytic Workspace Definitions for Variables for Facts" include definitions for these composites.

Our applications had no need of other data. However, Example 10-14, "Definitions for Variables for Promotions Dimension Attributes" show definitions of analytic workspace variables to which promotions attributes could be mapped. For an example of how to define relational views of the awsh analytic workspace see the example of using the OLAP_TABLE function in Oracle9i OLAP User's Guide.

Example 10-8 Analytic Workspace Definitions for the Products Hierarchy

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_supplier_id DIMENSION TEXT
DEFINE aw_prod_id.aw_supplier_id RELATION aw_supplier_id <aw_prod_id>

Example 10-9 Analytic Workspace Definitions for the Channels Hierarchy

DEFINE aw_channel_id DIMENSION TEXT
DEFINE aw_channel_class DIMENSION TEXT
DEFINE aw_channels_all DIMENSION TEXT
DEFINE aw_channels DIMENSION CONCAT(aw_channels_all -
                                 aw_channel_class -
                                 aw_channel_id)
DEFINE aw_channels.parents RELATION aw_channels <aw_channels>

Example 10-10 Analytic Workspace Definitions for the Promotions Hierarchy

DEFINE aw_promo_id DIMENSION NUMBER(6)
DEFINE aw_promo_subcategory DIMENSION TEXT
DEFINE aw_promo_category DIMENSION TEXT
DEFINE aw_promos_all DIMENSION TEXT
DEFINE aw_promos DIMENSION CONCAT(aw_promos_all -
                               aw_promo_category -
                               aw_promo_subcategory -
                               aw_promo_id)
DEFINE aw_promos.parents RELATION aw_promos <aw_promos>

Example 10-11 Analytic Workspace Definitions for the Customers Hierarchy

DEFINE aw_cust_id DIMENSION NUMBER (8)
DEFINE aw_city DIMENSION TEXT
DEFINE aw_state_province DIMENSION TEXT
DEFINE aw_country_id DIMENSION TEXT
DEFINE aw_subregion DIMENSION TEXT
DEFINE aw_region DIMENSION TEXT
DEFINE aw_world DIMENSION TEXT
DEFINE aw_customers DIMENSION CONCAT(aw_world -
                                  aw_region -
                                  aw_subregion -
                                  aw_country_id -
                                  aw_state_province -
                                  aw_city -
                                  aw_cust_id)
DEFINE aw_customers.parents RELATION aw_customers <aw_customers>

Example 10-12 Analytic Workspace Definitions for the Times Hierarchies

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>

Example 10-13 Analytic Workspace Definitions for Variables for Facts


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>>

DEFINE aw_salesdims COMPOSITE <aw_products aw_customers aw_times -
               aw_channels aw_promos>
DEFINE aw_quantity_sold VARIABLE NUMBER(3) <aw_salesdims -
              <aw_products aw_customers aw_times aw_channels aw_promos>>
DEFINE aw_amount_sold VARIABLE NUMBER(10,2) <aw_salesdims -
              <aw_products aw_customers aw_times aw_channels aw_promos>>

Example 10-14 Definitions for Variables for Promotions Dimension Attributes

DEFINE aw_promo_name VARIABLE TEXT <aw_promo_id>
DEFINE aw_promo_cost VARIABLE NUMBER(10,2) <aw_promo_id>
DEFINE aw_promo_begin_date VARIABLE DATE <aw_promo_id>
DEFINE aw_promo_end_date VARIABLE DATE <aw_promo_id>

Populating Analytic Workspace Objects with Sales History Data

In this example there are a number of OLAP DML programs that copy the data from the relational Sales History database into the objects in the analytic workspace named awsh:

The following programs copy data from the relational tables into analytic workspace dimensions and variables:
- Example 10-15, "get_products_hier Program" copies the data from the dimension tables into the base dimensions of the aw_products concat dimension using SQL FETCH commands with the APPEND keyword. As the base dimensions of aw_products are populated, Oracle OLAP automatically populates aw_products, itself. As the THEN clause of the SQL FETCH command executes, Oracle OLAP fetches data into the child-parent self-relation for aw_products. This program also populates the aw_supplier_id dimension and its relation.
- Example 10-16, "get_channels_hier Program" , Example 10-17, "get_promos_hier Program" , Example 10-18, "get_customers_hier Program" , and Example 10-19, "get_times_hiers Program" copy the data from the dimension tables into analytic workspace dimensions and relations that are used to represent hierarchical dimensions. Because these dimensions are empty before these programs execute, the SQL FETCH command uses the APPEND keyword. As the base dimensions are populated, Oracle OLAP automatically populates the concat dimension that represents the hierarchy. As the THEN clause of the SQL FETCH command executes, Oracle OLAP fetches data into the child-parent self-relation for concat dimension that represents the hierarchy.
The following programs copy the facts from the relational tables into analytic workspace variables. These examples assume that the base dimension for these variables are already populated. Consequently, the SQL FETCH commands in these programs use the MATCH keyword. Also, because the composite that the variables are dimensioned by is constructed of concat dimensions, the SQL FETCH commands uses a QDR to specify dimension values for the variable.
- Example 10-20, "get_costs Program" copies the facts from the costs table into analytic workspace variables.
- Example 10-21, "get_sales Program" copies the facts from the sales table into analytic workspace variables.
Example 10-23, "get_promos_attr Program" copies attribute data from the Promotions dimension table into analytic workspace variables. This program assumes that the base dimensions are already populated and uses a SQL IMPORT command with the MATCH keyword.

Example 10-15 get_products_hier Program

ALLSTAT
" Fetch values into the products hierarchy
SQL DECLARE grabprods CURSOR FOR SELECT prod_total, -
                                        prod_category, -
                                        prod_subcategory, -
                                        prod_id -
                                   FROM sh.products
SQL OPEN grabprods
SQL FETCH grabprods LOOP INTO :APPEND aw_products_all -
                              :APPEND aw_prod_category -
                              :APPEND aw_prod_subcategory -
                              :APPEND aw_prod_id 
                         
SQL CLOSE grabprods 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
" Fetch values into supplier_id
SQL DECLARE grabsupid CURSOR FOR SELECT supplier_id -
                                 FROM sh.products
SQL OPEN grabsupid
SQL FETCH grabsupid LOOP INTO :APPEND aw_supplier_id 
SQL CLOSE grabsupid 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

" Populate self-relation for concat dimension
" and relation between aw_prod_id and aw_supplier_id
SQL DECLARE makerels CURSOR FOR SELECT prod_total, -
                                       prod_category, -
                                       prod_subcategory, -
                                       prod_id, -
                                       supplier_id -
                                 FROM sh.products
SQL OPEN makerels
SQL FETCH makerels LOOP INTO :MATCH aw_products_all -
                             :MATCH aw_prod_category -
                             :MATCH aw_prod_subcategory -
                             :MATCH aw_prod_id -
                             :MATCH aw_supplier_id -
            THEN aw_products.parents(aw_products aw_prod_id) -
                = aw_products(aw_prod_subcategory aw_prod_subcategory) -
            aw_products.parents(aw_products aw_prod_subcategory) -
               = aw_products(aw_prod_category aw_prod_category) -
            aw_products.parents(aw_products aw_prod_category) -
               = aw_products(aw_products_all aw_products_all) -
            aw_prod_id.aw_supplier_id = aw_supplier_id         
SQL CLOSE makerels 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

Example 10-16 get_channels_hier Program

ALLSTAT
" Fetch values for the Channels hierarchy
" and populate self-relation for the hierarchy
SQL DECLARE grabchanneldata CURSOR FOR SELECT channel_total, -
                                       channel_class, -
                                       channel_id -
                                FROM sh.channels
SQL OPEN grabchanneldata
" Fetch values into analytic workspace objects for the the channels hierararchy
SQL FETCH grabchanneldata LOOP INTO :APPEND aw_channels_all -
                             :APPEND aw_channel_class -
                             :APPEND aw_channel_id -
         THEN aw_channels.parents(aw_channels aw_channel_id) -
              = aw_channels(aw_channel_class aw_channel_class) -
         aw_channels.parents(aw_channels aw_channel_class) -
              = aw_channels(aw_channels_all aw_channels_all)
SQL CLOSE grabchanneldata 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

Example 10-17 get_promos_hier Program

ALLSTAT
" Fetch values for the Promos hierarchy
" and populate self-relation for the hierarchy
SQL DECLARE grabpromodata CURSOR FOR SELECT promo_total, -
                                            promo_category, -
                                            promo_subcategory, -
                                            promo_id -
                                 FROM sh.promotions
SQL OPEN grabpromodata
SQL FETCH grabpromodata LOOP INTO :APPEND aw_promos_all -
                                  :APPEND aw_promo_category -
                                  :APPEND aw_promo_subcategory -
                                  :APPEND aw_promo_id -
        THEN aw_promos.parents(aw_promos aw_promo_id) -
                    = aw_promos(aw_promo_subcategory aw_promo_subcategory) -
                  aw_promos.parents(aw_promos aw_promo_subcategory) -
                   = aw_promos(aw_promo_category aw_promo_category) -
            aw_promos.parents(aw_promos aw_promo_category) -
             = aw_promos(aw_promos_all aw_promos_all)
SQL CLOSE grabpromodata 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

Example 10-18 get_customers_hier Program

ALLSTAT
" Fetch values for the Customers hierarchy from the countries table
" and populate the self-relation for the hierarchy with these values
SQL DECLARE grabcountrydata CURSOR FOR SELECT country_total, -
                               country_region, -
                               country_subregion, -
                               country_id -
                           FROM sh.countries
SQL OPEN grabcountrydata
SQL FETCH grabcountrydata LOOP INTO :APPEND aw_world -
                                    :APPEND aw_region -
                                    :APPEND aw_subregion -
                                    :APPEND aw_country_id -
      THEN aw_customers.parents(aw_customers aw_country_id) = -
             aw_customers(aw_subregion aw_subregion) -
           aw_customers.parents(aw_customers aw_subregion) = -
             aw_customers(aw_region aw_region) -
           aw_customers.parents(aw_customers aw_region) = -
             aw_customers(aw_world aw_world)
SQL CLOSE grabcountrydata 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
" Fetch values for the Customers hierarchy from the customers table
" and populate the self-relation for the hierarchy with these values
SQL DECLARE grabcustdata CURSOR FOR SELECT country_id, - 
                                           cust_state_province, -
                                           cust_city, -
                                           cust_id - 
                                 FROM sh.customers
SQL OPEN grabcustdata
SQL FETCH grabcustdata LOOP INTO :MATCH aw_country_id -
                                 :APPEND aw_state_province -
                                 :APPEND aw_city -
                                 :APPEND aw_cust_id -
                THEN aw_customers.parents(aw_customers aw_cust_id) = -
                   aw_customers(aw_city aw_city) -
               aw_customers.parents(aw_customers aw_city) = -
                   aw_customers(aw_state_province aw_state_province) -
               aw_customers.parents(aw_customers aw_state_province) = -
                   aw_customers(aw_country_id aw_country_id)
SQL CLOSE grabcustdata 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

Example 10-19 get_times_hiers Program

NLS_DATE_FORMAT = '<YYYY><MM><DD>'
DATEFORMAT = '<YYYY>-<MM>-<DD>' 
" Populate the hierachy name dimension with names of hierarchies
MAINTAIN aw_times_hiernames ADD 'Calendar' 'Fiscal' 
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

" Fetch values for the CalTimes and FisTimes hierarchies
" and populate self-relation time
SQL DECLARE grabcalyear CURSOR FOR SELECT calendar_year -
                                FROM sh.times
SQL OPEN grabcalyear
SQL FETCH grabcalyear LOOP INTO :APPEND aw_cal_year 
SQL CLOSE grabcalyear 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
SQL DECLARE grabfisyear CURSOR FOR SELECT fiscal_year -
                                    FROM sh.times
SQL OPEN grabfisyear
SQL FETCH grabfisyear LOOP INTO :APPEND aw_fis_year 
SQL CLOSE grabfisyear 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
SQL DECLARE grabtimeid CURSOR FOR SELECT time_id -
                                    FROM sh.times
SQL OPEN grabtimeid
SQL FETCH grabtimeid LOOP INTO :APPEND aw_time_id 
SQL CLOSE grabtimeid 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
ALLSTAT
LIMIT aw_times_hiernames TO 'Calendar'
SQL DECLARE makecalhier CURSOR FOR SELECT calendar_year, -
                                            time_id -
                                FROM sh.times
SQL OPEN makecalhier
SQL FETCH makecalhier LOOP INTO :MATCH aw_cal_year -
                                :MATCH aw_time_id -
       THEN aw_times.parents(aw_times aw_time_id) -
              = aw_times(aw_cal_year aw_cal_year) 
SQL CLOSE makecalhier 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
ALLSTAT
UPDATE
COMMIT
LIMIT aw_times_hiernames TO 'Fiscal'
SQL DECLARE makefishier CURSOR FOR SELECT fiscal_year, -
                                        time_id -
                                FROM sh.times
SQL OPEN makefishier
SQL FETCH makefishier LOOP INTO :MATCH aw_fis_year -
                                :MATCH aw_time_id -
       THEN aw_times.parents(aw_times aw_time_id) -
              = aw_times(aw_fis_year aw_fis_year)  
SQL CLOSE makefishier 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
ALLSTAT
UPDATE
COMMIT

Example 10-20 get_costs Program

ALLSTAT
NLS_DATE_FORMAT = '<YYYY><MM><DD>'
DATEFORMAT = '<YYYY>-<MM>-<DD>'
" Declare a cursor named grabcosts
SQL DECLARE grabcosts CURSOR FOR SELECT prod_id, -
                                        time_id, -
                                        unit_cost, -
                                        unit_price -
                            FROM sh.costs 
" Open the cursor
SQL OPEN grabcosts
" Import the data
SQL FETCH grabcosts LOOP INTO :MATCH aw_prod_id -
                         :MATCH aw_time_id -
                         :aw_unit_cost (aw_products aw_prod_id -
                    aw_times aw_time_id) -
                         :aw_unit_price (aw_products aw_prod_id -
                    aw_times aw_time_id)
" Close the cursor
SQL CLOSE grabcosts 
" Cleanup from SQL query
SQL CLEANUP
" Update and make changes permanent
UPDATE
COMMIT

Example 10-21 get_sales Program

ALLSTAT
NLS_DATE_FORMAT = '<YYYY><MM><DD>'
DATEFORMAT = '<YYYY>-<MM>-<DD>'
" Declare a cursor named grabsales
SQL DECLARE grabsales CURSOR FOR SELECT prod_id, -
                                        cust_id, -
                                        time_id, -
                                        channel_id, -
                                        promo_id, -
                                        quantity_sold, -
                                        amount_sold -
                      FROM sh.sales 
" Open the cursor
SQL OPEN grabsales
" Import values into analytic workspace objects
SQL FETCH grabsales LOOP INTO :MATCH aw_prod_id -
                              :MATCH aw_cust_id -
                              :MATCH aw_time_id -
                              :MATCH aw_channel_id -
                              :MATCH aw_promo_id -
                              :aw_quantity_sold (aw_products aw_prod_id -
                                             aw_customers aw_cust_id -
                                              aw_times aw_time_id -
                                            aw_channels aw_channel_id -
                                              aw_promos aw_promo_id) -
                              :aw_amount_sold (aw_products aw_prod_id -
                                          aw_customers aw_cust_id -
                                          aw_times aw_time_id -
                                          aw_channels aw_channel_id -
                                           aw_promos aw_promo_id)
" Close the cursor
SQL CLOSE grabsales 
" Cleanup from SQL query
SQL CLEANUP
" Update and make changes permanent
UPDATE
COMMIT

Example 10-22 Definitions for Variables for Promotions Dimension Attributes

DEFINE aw_promo_name VARIABLE TEXT <aw_promo_id>
DEFINE aw_promo_cost VARIABLE NUMBER(10,2) <aw_promo_id>
DEFINE aw_promo_begin_date VARIABLE DATE <aw_promo_id>
DEFINE paw_romo_end_date VARIABLE DATE <aw_promo_id>

Example 10-23 get_promos_attr Program

ALLSTAT
" Declare a cursor named grabpromoattr
SQL DECLARE grabpromoattr CURSOR FOR SELECT promo_id, -
                                            promo_name, -
                                            promo_cost, -
                                            promo_begin_date, -
                                            promo_end_date -
                        FROM sh.promotions
" Open the cursor
SQL OPEN grabpromoattr
" Import new values into the analytic workspace objects
SQL IMPORT grabpromoattr INTO :MATCH aw_promo_id -
                                    :aw_promo_name -
                                    :aw_promo_cost -
                                    :aw_promo_begin_date -
                                    :aw_promo_end_date 
" Close the cursor
SQL CLOSE grabpromoattr 
" Cleanup from SQL query
SQL CLEANUP
" Update and make changes permanent
UPDATE
COMMIT