--------------------------------------------------------------------------------------
-- 8.2 Creating a Dimension
--------------------------------------------------------------------------------------
GRANT create any dimension to easydw; 
GRANT alter any dimension to easydw; 
GRANT drop any dimension to easydw; 

--------------------------------------------------------------------------------------
-- 8.2.1 Defining a Dimension with a Single Hierarchy
--------------------------------------------------------------------------------------

CREATE DIMENSION geography_dim 
LEVEL postal_code IS customer.postal_code
LEVEL city        IS customer.city
LEVEL state       IS customer.state
LEVEL country     IS customer.country
HIERARCHY loc_rollup (
          postal_code      CHILD OF
          city             CHILD OF
          state            CHILD OF
          country
         );

--------------------------------------------------------------------------------------
-- 8.2.2 Defining a Dimension with Multiple Hierarchies 
--------------------------------------------------------------------------------------

CREATE DIMENSION time_dim
 LEVEL time_key    IS time.time_key
 LEVEL month       IS time.month
 LEVEL quarter     IS time.quarter
 LEVEL year        IS time.year
 LEVEL week_number IS time.week_number
 HIERARCHY calendar_rollup (
                time_key  CHILD OF 
                month     CHILD OF	
                year
           )
 HIERARCHY fiscal_rollup (
                time_key    CHILD OF
                week_number CHILD OF
                quarter  
           );

--------------------------------------------------------------------------------------
-- 8.2.3 Defining a Dimension with Attributes
--------------------------------------------------------------------------------------

CREATE DIMENSION product_dim 
 LEVEL product_id   IS product.product_id
 LEVEL category     IS product.category 
 HIERARCHY merchandise_rollup ( 
                   product_id   CHILD OF 
                   category 
                  )
 ATTRIBUTE product_id DETERMINES (product_name)
 ATTRIBUTE prod_manufacturer 
           LEVEL product_id DETERMINES (manufacturer);

--------------------------------------------------------------------------------------
-- 8.2.4 Defining a Dimension with Normalized Tables
--------------------------------------------------------------------------------------

CREATE DIMENSION time_dim
 LEVEL time_key IS time.time_key
 LEVEL month    IS month.month
 LEVEL quarter  IS quarter.quarter
 LEVEL year     IS year.year
 LEVEL week     IS week.week_number
 HIERARCHY calendar_rollup (
                    time_key CHILD OF
                    month    CHILD OF
                    year
                    JOIN KEY time.month REFERENCES month
                    JOIN KEY month.year REFERENCES year
           )
 HIERARCHY fiscal_rollup  (
                    time_key CHILD OF
                    week     CHILD OF
                    quarter 
                    JOIN KEY time.week_number  REFERENCES week
                    JOIN KEY week.quarter REFERENCES quarter
           )
 ATTRIBUTE time_key DETERMINES time.day_of_the_week
 ATTRIBUTE time_key DETERMINES month.month_name; 
 
--------------------------------------------------------------------------------------
-- 8.3 Describing a dimension
--------------------------------------------------------------------------------------

set serveroutput on;
execute dbms_dimension.describe_dimension('EASYDW.product_dim');

DIMENSION EASYDW.PRODUCT_DIM
LEVEL CATEGORY IS EASYDW.PRODUCT.CATEGORY
LEVEL PRODUCT_ID IS EASYDW.PRODUCT.PRODUCT_ID
HIERARCHY MERCHANDISE_ROLLUP (
PRODUCT_ID CHILD OF
CATEGORY
)
ATTRIBUTE PROD_MANUFACTURER LEVEL PRODUCT_ID 
          DETERMINES EASYDW.PRODUCT.MANUFACTURER
ATTRIBUTE PRODUCT_ID LEVEL PRODUCT_ID 
          DETERMINES EASYDW.PRODUCT.PRODUCT_NAME

--------------------------------------------------------------------------------------
-- 8.4 Validating a Dimension
--------------------------------------------------------------------------------------

CREATE DIMENSION customer_dim
  LEVEL customer IS customer.customer_id
  LEVEL city     IS customer.city
  LEVEL state    IS customer.state
  HIERARCHY  customer_zone ( 
                     customer CHILD OF
                     city     CHILD OF
                     state 
              ) 
  ATTRIBUTE city DETERMINES postal_code 
  ATTRIBUTE customer DETERMINES (gender, occupation);

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

INSERT INTO customer (customer_id, city, state, 
                      postal_code, gender, region, 
                      country, tax_rate, occupation) 
VALUES ('AB130000', 'Boston', 'MA', 
        '01210', 'F', 'AmerNorthEast', 'USA', 0.05, 'Doctor');

INSERT INTO customer (customer_id, city, state, 
                      postal_code, gender, region, 
                      country, tax_rate, occupation) 
VALUES ('AB130001', 'Boston', 'MA', 
        '01210', 'F', 'AmerNorthEast', 'USA', 0.05, 'Doctor');

COMMIT;

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

variable stmt_id varchar2(30);

execute :stmt_id := 'CUST_DIM_VAL';
execute dbms_dimension.validate_dimension ('EASYDW.CUSTOMER_DIM',  
                                            FALSE, TRUE, :stmt_id);


SELECT distinct owner,table_name,dimension_name,relationship
FROM dimension_exceptions
WHERE statement_id = :stmt_id;

SELECT customer_id, city, state, postal_code FROM customer
WHERE rowid IN (SELECT bad_rowid FROM dimension_exceptions 
                WHERE statement_id = :stmt_id);

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