-------------------------------------------------------------------------------------
15.4.2	OLAP Metadata Views and Validation
-------------------------------------------------------------------------------------

SELECT  CUBE_NAME,  INVALID FROM  ALL_OLAP2_CUBES;

-------------------------------------------------------------------------------------

set serveroutput on size 99999
EXECUTE cwm2_olap_manager.set_echo_on;
EXECUTE cwm2_olap_validate.validate_cube
        ('EASYDW','EASYDW_SALES','default','yes');
EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH;

-------------------------------------------------------------------------------------

set serveroutput on size 99999
EXECUTE cwm2_olap_manager.set_echo_on;
EXECUTE CWM2_OLAP_VERIFY_ACCESS.VERIFY_CUBE_ACCESS
        ('EASYDW', 'EASYDW_SALES', 'DEFAULT', 'NO');

-------------------------------------------------------------------------------------
-- 15.6.1 OLAP DML 
-------------------------------------------------------------------------------------

aw attach easyaw readwrite

-------------------------------------------------------------------------------------
-- Standard Form Entities 
-------------------------------------------------------------------------------------

FULLDSC AWTIME

FULLDSC AWEASYDW_SALES_PURCHASE_PRICE_VARIABLE

-------------------------------------------------------------------------------------
-- Reporting and Aggregating Data with OLAP DML 
-------------------------------------------------------------------------------------

report awtime

-------------------------------------------------------------------------------------

report down awtime awtime_levelrel
-------------------------------------------------------------------------------------

LIMIT awtime to awtime_levelrel EQ 'MONTH'

REPORT awtime

-------------------------------------------------------------------------------------

LIMIT awcustomer_dim TO awcustomer_dim_levelrel EQ 'STATE'
LIMIT awtime TO 'MONTH.200301' TO 'MONTH.200303'
LIMIT awproduct_dim TO 'CATEGORY.HDRW'

REPORT across awtime : aweasydw_sales_purchase_price_variable

-------------------------------------------------------------------------------------

LIMIT AWCUSTOMER_DIM TO AWCUSTOMER_DIM_LEVELREL EQ 'REGION'
LIMIT AWPRODUCT_DIM TO 'CATEGORY.HDRW'
LIMIT AWTIME TO 'MONTH.200301' TO 'MONTH.200303'

REPORT across AWTIME: 
   AGGREGATE(aweasydw_sales_purchase_price_variable USING
             aweasydw_sales_aggmap_agg1)

-------------------------------------------------------------------------------------

AGGMAP SET aweasydw_sales_aggmap_agg1 AS DEFAULT FOR
              aweasydw_sales_ship_charge_variable

REPORT ACROSS awtime: aweasydw_sales_ship_charge_variable

-------------------------------------------------------------------------------------
-- Defining Formulas and Custom Measures
-------------------------------------------------------------------------------------

DEFINE AWEASYDW_TOTAL_SALES_VARIABLE
VARIABLE DECIMAL 
   <AWEASYDW_SALES_COMPOSITE 
             <AWCUSTOMER_DIM AWPRODUCT_DIM AWTIME>>

LIMIT awtime TO awtime_levelrel EQ 'QUARTER'
LIMIT awcustomer_dim TO awcustomer_dim_levelrel EQ 'STATE'
LIMIT awproduct_dim TO awproduct_dim_levelrel EQ 'CATEGORY'

ACROSS awtime awcustomer_dim awproduct_dim 
   DO 'aweasydw_total_sales_variable =  
       aweasydw_sales_purchase_price_variable + 
       aweasydw_sales_ship_charge_variable'

-------------------------------------------------------------------------------------

LIMIT awtime TO awtime_levelrel EQ 'QUARTER'
LIMIT AWCUSTOMER_DIM TO AWCUSTOMER_DIM_PARENTREL EQ 
                          'REGION.AmerNorthEast'
LIMIT AWPRODUCT_DIM TO 'CATEGOR.HDRW'

REPORT DOWN awtime ACROSS awcustomer_dim:
                             aweasydw_total_sales_variable

-------------------------------------------------------------------------------------

DEFINE AWEASYDW_SALES_PREV_MONTH 
   FORMULA 
   DECIMAL <AWCUSTOMER_DIM AWPRODUCT_DIM AWTIME>
   EQ LAGDIF (AWEASYDW_SALES_PURCHASE_PRICE_VARIABLE,1,
              AWTIME, AWTIME_LEVELREL EQ 'MONTH')

LIMIT awproduct_dim TO 'CATEGORY.HDRW'
LIMIT awcustomer_dim TO 'REGION.AmerNorthEast'
LIMIT awtime TO awtime_levelrel EQ 'MONTH'

REPORT down awtime across awproduct_dim:   
   <AWEASYDW_SALES_PURCHASE_PRICE_VARIABLE, 
    AWEASYDW_SALES_PREV_MONTH>

-------------------------------------------------------------------------------------
-- Forecasting using OLAP DML
-------------------------------------------------------------------------------------

DEFINE AWEASYDW_SALES_FORECAST_VARIABLE VARIABLE DECIMAL
   <AWEASYDW_SALES_COMPOSITE 
             <AWCUSTOMER_DIM AWPRODUCT_DIM AWTIME>>

LIMIT AWTIME TO AWTIME_LEVELREL EQ 'MONTH'
LIMIT AWCUSTOMER_DIM TO AWCUSTOMER_DIM_LEVELREL EQ 'CUSTOMER'
LIMIT AWPRODUCT_DIM TO 'CATEGORY.HDRW'

DEFINE sf_handle VARIABLE INTEGER;

sf_handle = FCOPEN('EasyDWSalesForecast')

FCSET sf_handle method 'automatic' histperiods 3 periodicity 6 

FCEXEC sf_handle TIME AWTIME 
   INTO AWEASYDW_SALES_FORECAST_VARIABLE
   AWEASYDW_SALES_PURCHASE_PRICE_VARIABLE

FCCLOSE sf_handle

-------------------------------------------------------------------------------------

LIMIT awtime to 'MONTH.200501'
LIMIT awcustomer_dim to awcustomer_dim_parentrel eq 'STATE.MA'
LIMIT AWPRODUCT_DIM TO 'CATEGORY.HDRW'

REPORT down awcustomer_dim across awtime:
               AWEASYDW_SALES_FORECAST_VARIABLE

-------------------------------------------------------------------------------------
-- 15.6.2 DBMS_AW package
-------------------------------------------------------------------------------------

SET SERVEROUTPUT ON;
BEGIN
  DBMS_AW.EXECUTE(q'[
  AW ATTACH easyaw
  LIMIT awtime to awtime_levelrel EQ 'MONTH'
  REPORT awtime
  AW DETACH easyaw
]');
END;
/

-------------------------------------------------------------------------------------
-- 15.6.3 SQL Access to Analytic Workspaces
-------------------------------------------------------------------------------------

CREATE TYPE purchases_type AS OBJECT
(cust VARCHAR2(80),
 cust_gid NUMBER,
 time VARCHAR2(30),
 time_gid NUMBER,
 prod VARCHAR2(30),
 prod_gid NUMBER,
 purchase_price NUMBER);
/

CREATE TYPE purchases_table AS TABLE OF purchases_type;
/

CREATE VIEW purchase_price_view
as
SELECT * FROM TABLE(OLAP_TABLE(
'easyaw duration session', 'purchases_table', '',
'dimension cust from awcustomer_dim
  with hierarchy awcustomer_dim_parentrel
  gid cust_gid from awcustomer_dim_gid
 dimension time from awtime
  with hierarchy awtime_parentrel
  gid time_gid from awtime_gid
 dimension prod from awproduct_dim
  with hierarchy awproduct_dim_parentrel
  gid prod_gid from awproduct_dim_gid
 measure purchase_price 
  from aweasydw_sales_purchase_price_variable
'));

SELECT time, sum(purchase_price)
FROM purchase_price_view
WHERE prod = 'CATEGORY.HDRW' and cust = 'STATE.MA'
GROUP BY time

-------------------------------------------------------------------------------------