CONNECT scott/tiger@SOL3;

DROP TABLE country CASCADE CONSTRAINTS;
DROP TABLE area CASCADE CONSTRAINTS;
DROP TABLE msisdn CASCADE CONSTRAINTS;
DROP TABLE stat CASCADE CONSTRAINTS;
DROP TABLE call CASCADE CONSTRAINTS;
DROP DIMENSION statdim;
DROP SEQUENCE seq_call_id;

CONNECT system/manager@SOL3;
GRANT CREATE DIMENSION TO scott;

CONNECT scott/tiger@SOL3;
CREATE TABLE country (
  ctr_code    NUMBER(10)      PRIMARY KEY,
  ctr_name    VARCHAR2(20)    NOT NULL
);
INSERT INTO country VALUES (1,'USA');
INSERT INTO country VALUES (2,'Italy');
INSERT INTO country VALUES (3,'Germany');
INSERT INTO country VALUES (4,'Switzerland');
COMMIT;

CREATE TABLE area (
  area_code     NUMBER(10) PRIMARY KEY,
  area_name     VARCHAR2(20) NOT NULL,
  ctr_code      NUMBER(10) REFERENCES country(ctr_code)
);
INSERT INTO  area VALUES (1,'California',1);
INSERT INTO  area VALUES (2,'Colorado',1);
INSERT INTO  area VALUES (3,'Washington',1);
INSERT INTO  area VALUES (4,'Parma',2);
INSERT INTO  area VALUES (5,'Roma',2);
INSERT INTO  area VALUES (6,'Genova',2);
INSERT INTO  area VALUES (7,'Rheinland',3);
INSERT INTO  area VALUES (8,'Bayern',3);
INSERT INTO  area VALUES (9,'Berne',4);
INSERT INTO  area VALUES (10,'Zurich',4);
INSERT INTO  area VALUES (11,'Lucerne',4);
COMMIT;

CREATE TABLE msisdn (
  msn_id   NUMBER(10) PRIMARY KEY,
  msisdn   VARCHAR2(20) NOT NULL,
  status   VARCHAR2(20) NOT NULL
);
INSERT INTO msisdn VALUES (1,'0794102787','active');
INSERT INTO msisdn VALUES (2,'0794678431','active');
INSERT INTO msisdn VALUES (3,'0794968764','suspended');
INSERT INTO msisdn VALUES (4,'0790981734','barred');
INSERT INTO msisdn VALUES (5,'0794977232','active');
INSERT INTO msisdn VALUES (6,'0791088473','active');
COMMIT;

CREATE TABLE call (
   call_id     NUMBER(10)      NOT NULL PRIMARY KEY,
   call_date   DATE            NOT NULL,
   msn_id      NUMBER(10)      NOT NULL REFERENCES msisdn(msn_id),
   amount      NUMBER(7,2)     NOT NULL,
   area_code   NUMBER(10)      NOT NULL REFERENCES area(area_code));
CREATE sequence seq_call_id;
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),1,1499,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('2.1.2000','DD.MM.YYYY'),1,1400,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,1500,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('15.5.2000','DD.MM.YYYY'),2,1399,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('16.5.2000','DD.MM.YYYY'),2,1299,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1199,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1399,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1388,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1377,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),1,1499,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('2.1.2000','DD.MM.YYYY'),1,1390,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,2000,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('15.5.2000','DD.MM.YYYY'),2,2100,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('16.5.2000','DD.MM.YYYY'),2,1700,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1500,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1399,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1499,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1599,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1400,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,2000,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1700,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,650,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,499,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,399,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,395,1);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,613,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,299,2);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),1,1199,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('2.1.2000','DD.MM.YYYY'),1,1600,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,1500,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('15.5.2000','DD.MM.YYYY'),2,1499,4);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('16.5.2000','DD.MM.YYYY'),2,1299,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1199,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,399,5);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1388,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1377,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),1,3499,4);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('2.1.2000','DD.MM.YYYY'),1,1390,5);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,2000,6);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('15.5.2000','DD.MM.YYYY'),2,1100,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('16.5.2000','DD.MM.YYYY'),2,1700,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1500,6);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,799,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1499,4);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,2599,5);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('7.8.2000','DD.MM.YYYY'),3,1400,6);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('14.5.2000','DD.MM.YYYY'),1,1000,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('6.7.2000','DD.MM.YYYY'),2,1600,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,450,5);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,999,5);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,999,6);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,895,6);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),4,113,3);
INSERT INTO call VALUES (seq_call_id.nextval,to_date('1.1.2000','DD.MM.YYYY'),5,386,3);
COMMIT;

CREATE TABLE stat (
  day           DATE PRIMARY KEY,
  week          NUMBER(2)     NOT NULL,
  month         NUMBER(2)     NOT NULL,
  month_name    VARCHAR2(20)  NOT NULL,
  quarter       VARCHAR2(20)  NOT NULL,
  year          NUMBER(4) NOT NULL,
  season        VARCHAR2(20)  NOT NULL
);
CREATE INDEX idx_stat_year on stat(year);
INSERT INTO STAT VALUES (TO_DATE('1.1.2000','DD.MM.YYYY'),1,1,'january',1,2000,'winter');
INSERT INTO STAT VALUES (TO_DATE('2.1.2000','DD.MM.YYYY'),1,1,'january',1,2000,'winter');
INSERT INTO STAT VALUES (TO_DATE('14.5.2000','DD.MM.YYYY'),22,5,'april',2,2000,'spring');
INSERT INTO STAT VALUES (TO_DATE('15.5.2000','DD.MM.YYYY'),22,5,'april',2,2000,'spring');
INSERT INTO STAT VALUES (TO_DATE('16.5.2000','DD.MM.YYYY'),22,5,'april',2,2000,'spring');
INSERT INTO STAT VALUES (TO_DATE('6.7.2000','DD.MM.YYYY'),27,7,'july',3,2000,'spring');
INSERT INTO STAT VALUES (TO_DATE('7.8.2000','DD.MM.YYYY'),31,8,'august',3,2000,'summer');
INSERT INTO STAT VALUES (TO_DATE('8.8.2000','DD.MM.YYYY'),31,8,'september',3,2000,'summer');
COMMIT;

ALTER TABLE call ADD CONSTRAINT call_date_stat_fk FOREIGN KEY (call_date) REFERENCES stat(day);
EXEC dbms_utility.analyze_schema('SCOTT','COMPUTE');

CREATE DIMENSION statdim
  LEVEL day         IS stat.day
  LEVEL week        IS stat.week
  LEVEL month       IS stat.month
  LEVEL quarter     IS stat.quarter
  LEVEL season      IS stat.season
  LEVEL year        IS stat.year
HIERARCHY calendar_hierarchy (
  day         CHILD OF
  month       CHILD OF
  quarter     CHILD OF
  year)
HIERARCHY season_hierarchy (
  day      CHILD OF
  season   CHILD OF
  year)
HIERARCHY week_hierarchy (
  day      CHILD OF
  week     CHILD OF
  year)
ATTRIBUTE month DETERMINES month_name;

EXEC dbms_olap.validate_dimension('STATDIM','SCOTT',FALSE,TRUE);

SELECT table_name,
       dimension_name,
       relationship,
       bad_rowid
FROM mview$_exceptions;

DELETE FROM stat WHERE day = TO_DATE('8.8.2000','DD.MM.YYYY');
COMMIT;

EXEC dbms_olap.validate_dimension('STATDIM','SCOTT',FALSE,TRUE);

SELECT table_name,
       dimension_name,
       relationship,
       bad_rowid
FROM mview$_exceptions;

DROP MATERIALIZED VIEW call_sum;
CREATE MATERIALIZED VIEW call_sum
ENABLE QUERY REWRITE
 AS SELECT msisdn.msisdn, area.area_name, stat.month, SUM(call.amount) AS total_call
      FROM call, area, stat, msisdn
     WHERE call.area_code = area.area_code
       AND call.call_date = stat.day
       AND call.msn_id    = msisdn.msn_id
  GROUP BY msisdn.msisdn, area.area_name, stat.month;
ANALYZE TABLE call_sum COMPUTE STATISTICS;

ALTER SESSION SET query_rewrite_integrity=TRUSTED;
SET AUTOTRACE ON EXPLAIN

SELECT msisdn.msisdn, area.area_name, stat.year, SUM(call.amount)
      FROM call, area, stat, msisdn
     WHERE call.area_code = area.area_code
       AND call.call_date = stat.day
       AND call.msn_id    = msisdn.msn_id
  GROUP BY msisdn.msisdn, area.area_name, stat.year;

CREATE DIMENSION areadim
  LEVEL area_code   IS area.area_code
  LEVEL area_name   IS area.area_name
  LEVEL ctr_code    IS country.ctr_code
  LEVEL ctr_name    IS country.ctr_name
HIERARCHY area_hierarchy (
  area_code   CHILD OF
  ctr_code    JOIN KEY area.ctr_code REFERENCES ctr_code)
ATTRIBUTE area_code DETERMINES area.area_name
ATTRIBUTE ctr_code DETERMINES country.ctr_name;

DROP MATERIALIZED VIEW call_sum;
CREATE MATERIALIZED VIEW call_sum
ENABLE QUERY REWRITE
 AS SELECT msisdn.msisdn, country.ctr_code,
           area.area_name, stat.month, SUM(call.amount) AS total_call
      FROM call, area, stat, msisdn, country
     WHERE area.ctr_code = country.ctr_code
       AND call.area_code = area.area_code
       AND call.call_date = stat.day
       AND call.msn_id    = msisdn.msn_id
  GROUP BY msisdn.msisdn, country.ctr_code, area.area_name, stat.month;
ANALYZE TABLE call_sum COMPUTE STATISTICS;

SELECT msisdn.msisdn, country.ctr_code,
       area.area_name, stat.year, SUM(call.amount)
      FROM call, area, stat, msisdn, country
     WHERE area.ctr_code = country.ctr_code
       AND call.area_code = area.area_code
       AND call.call_date = stat.day
       AND call.msn_id    = msisdn.msn_id
  GROUP BY msisdn.msisdn, country.ctr_code, area.area_name, stat.year;