DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON products;
DROP MATERIALIZED VIEW LOG ON costs;

ALTER TABLE costs DROP PARTITION costs_1_2002;

CREATE DIMENSION products_dim
  LEVEL product IS (products.prod_id)
  LEVEL subcategory IS (products.prod_subcategory)
  LEVEL category IS (products.prod_category)
  HIERARCHY prod_rollup (
  product CHILD OF
  subcategory CHILD OF
  category
  )
  ATTRIBUTE product DETERMINES
  (products.prod_name, products.prod_desc,
  prod_weight_class, prod_unit_of_measure,
  prod_pack_size,prod_status, prod_list_price, prod_min_price)
  ATTRIBUTE subcategory DETERMINES
  (prod_subcategory, prod_subcategory_desc)
  ATTRIBUTE category DETERMINES
  (prod_category, prod_category_desc)
;

CREATE DIMENSION promotions_dim
  LEVEL promo IS (promotions.promo_id) 
  LEVEL subcategory IS (promotions.promo_subcategory)
  LEVEL category IS (promotions.promo_category)
  HIERARCHY promo_rollup (
  promo CHILD OF
  subcategory CHILD OF
  category
)
ATTRIBUTE promo DETERMINES
  (promo_name, promo_cost,
  promo_begin_date, promo_end_date)
  ATTRIBUTE subcategory DETERMINES (promo_subcategory)
  ATTRIBUTE category DETERMINES (promo_category)
;

CREATE DIMENSION channels_dim
  LEVEL channel IS (channels.channel_id)
  LEVEL channel_class IS (channels.channel_class)
  HIERARCHY channel_rollup (
  channel CHILD OF
  channel_class
  )
  ATTRIBUTE channel DETERMINES (channel_desc)
  ATTRIBUTE channel_class DETERMINES (channel_class)
;

COMMIT;