Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- FlyByNite Data Warehouse Script.
- */
- DELETE FROM SALES_FACT_DOS;
- DROP TABLE SALES_FACT_DOS;
- DROP TABLE CUSTOMER_DOS;
- DROP TABLE PRODUCT_DOS;
- DROP TABLE COUNTRY_DOS;
- DROP TABLE TIMEDIMENSION_DOS;
- DROP DIMENSION CUSTOMERDIMENSION_DOS;
- DROP MATERIALIZED VIEW MV_SALESSINCE2013_DOS;
- CREATE TABLE COUNTRY_DOS(COUNTRYID NUMERIC(10) NOT NULL,
- COUNTRYNAME VARCHAR2(40) NOT NULL,
- REGION VARCHAR2(11) NOT NULL,
- CONSTRAINT COUNTRY_PK_DOS PRIMARY KEY(COUNTRYID));
- CREATE TABLE CUSTOMER_DOS(MOBILEPHONENUMBER NUMERIC(10) NOT NULL,
- FIRSTNAME VARCHAR2(40) NOT NULL,
- LASTNAME VARCHAR2(40) NOT NULL,
- EMAILADDRESS VARCHAR(40) NOT NULL,
- HOUSEADDRESS VARCHAR2(40) NOT NULL,
- STREETADDRESS VARCHAR2(50) NOT NULL,
- CITY VARCHAR2(30) NOT NULL,
- COUNTY_STATE VARCHAR2(40) NOT NULL,
- ZIPCODE VARCHAR2(20),
- COUNTRYID NUMERIC(10) NOT NULL,
- PASSPORT VARCHAR2(10) NOT NULL,
- CONSTRAINT CUSTOMER_PK_DOS PRIMARY KEY(EMAILADDRESS),
- CONSTRAINT COUNTRY_FK1_DOS FOREIGN KEY(COUNTRYID)
- REFERENCES COUNTRY_DOS(COUNTRYID));
- CREATE TABLE PRODUCT_DOS(PRODUCTID NUMERIC(10) NOT NULL,
- PRODUCTNAME VARCHAR2(255) NOT NULL,
- PRODUCTUNITCOST DECIMAL(10,2) NOT NULL,
- CONSTRAINT PRODUCT_PK_DOS PRIMARY KEY(PRODUCTID));
- CREATE TABLE TIMEDIMENSION_DOS(TIMEID NUMERIC(10) NOT NULL,
- TIMEDAY VARCHAR2(10) NOT NULL,
- TIMEMONTH VARCHAR2(9) NOT NULL,
- TIMEQUARTER NUMERIC(1) NOT NULL,
- TIMEYEAR NUMERIC(4) NOT NULL,
- TIMEDAYOFWEEK VARCHAR2(9) NOT NULL,
- CONSTRAINT TIME_PK_DOS PRIMARY KEY(TIMEID));
- CREATE TABLE SALES_FACT_DOS(SALESID NUMERIC(10) NOT NULL,
- SALESDATE DATE NOT NULL,
- SALESUNITS NUMERIC(10) NOT NULL,
- EUROSALESAMOUNT DECIMAL(10,2) NOT NULL,
- TIMEID NUMERIC(10) NOT NULL,
- PRODUCTID NUMERIC(10) NOT NULL,
- COUNTRYID NUMERIC(10) NOT NULL,
- CUSTOMERID VARCHAR2(40) NOT NULL,
- CONSTRAINT SALES_PK_DOS PRIMARY KEY(SALESID),
- CONSTRAINT TIME_FK_DOS FOREIGN KEY(TIMEID)
- REFERENCES TIMEDIMENSION_DOS(TIMEID),
- CONSTRAINT PRODUCT_FK_DOS FOREIGN KEY(PRODUCTID)
- REFERENCES PRODUCT_DOS(PRODUCTID),
- CONSTRAINT COUNTRY_FK2_DOS FOREIGN KEY(COUNTRYID)
- REFERENCES COUNTRY_DOS(COUNTRYID),
- CONSTRAINT CUSTOMER_FK_DOS FOREIGN KEY(CUSTOMERID)
- REFERENCES CUSTOMER_DOS(EMAILADDRESS));
- CREATE DIMENSION CUSTOMERDIMENSION_DOS
- LEVEL CUSTOMERID IS CUSTOMER_DOS.MOBILEPHONENUMBER
- LEVEL HOUSEADDRESS IS CUSTOMER_DOS.HOUSEADDRESS
- LEVEL STREETADDRESS IS CUSTOMER_DOS.STREETADDRESS
- LEVEL CITY IS CUSTOMER_DOS.CITY
- LEVEL COUNTY_STATE IS CUSTOMER_DOS.COUNTY_STATE
- LEVEL COUNTRY IS COUNTRY_DOS.COUNTRYID
- LEVEL REGION IS COUNTRY_DOS.REGION
- HIERARCHY ADDRESSROLLUP (
- HOUSEADDRESS CHILD OF
- STREETADDRESS CHILD OF
- CITY CHILD OF
- COUNTY_STATE CHILD OF
- COUNTRY CHILD OF
- REGION
- JOIN KEY CUSTOMER_DOS.COUNTRYID REFERENCES COUNTRY)
- ATTRIBUTE CUSTOMERID DETERMINES
- (CUSTOMER_DOS.FIRSTNAME, CUSTOMER_DOS.LASTNAME)
- ATTRIBUTE COUNTRY DETERMINES (COUNTRY_DOS.COUNTRYNAME);
- INSERT INTO COUNTRY_DOS VALUES(1,'Ireland','EU');
- INSERT INTO COUNTRY_DOS VALUES(2,'England','EU');
- INSERT INTO COUNTRY_DOS VALUES(3,'France','EU');
- INSERT INTO COUNTRY_DOS VALUES(4,'Spain','EU');
- INSERT INTO COUNTRY_DOS VALUES(5,'Germany','EU');
- INSERT INTO COUNTRY_DOS VALUES(6,'UK','EU');
- INSERT INTO PRODUCT_DOS VALUES(1,'Flights within a country', 25.00);
- INSERT INTO PRODUCT_DOS VALUES(2,'Flights Throughout EU' ,70.00);
- INSERT INTO PRODUCT_DOS VALUES(3,'Late Booking EU' ,50.00);
- INSERT INTO PRODUCT_DOS VALUES(4,'Late Booking INT' ,150.00);
- INSERT INTO PRODUCT_DOS VALUES(5,'Budjet Hotel' ,45.00);
- INSERT INTO PRODUCT_DOS VALUES(6,'Mid Range Hotel', 60.00);
- INSERT INTO PRODUCT_DOS VALUES(7,'Premium Hotel' ,100.00);
- INSERT INTO PRODUCT_DOS VALUES(8,'Overweight KG' ,15.00);
- INSERT INTO PRODUCT_DOS VALUES(9,'Boarding Card Pen', 40.00);
- --CUSTOMER PURCHASING A DOMESTIC FLIGHT FROM IRELAND
- INSERT INTO CUSTOMER_DOS VALUES('0877785411', 'Diarmaid', 'O Sullivan', 'diarmaidosullivan@mycit.ie','Ambleside', 'SCR', 'Limerick', 'Limerick', '12554', '1', 'P225654');
- INSERT INTO TIMEDIMENSION_DOS VALUES(1,06,06,2,2016,'Monday');
- INSERT INTO SALES_FACT_DOS VALUES(1,TO_DATE('2016/06/06', 'yyyy/mm/dd'),1,25.00,1,1,1,'diarmaidosullivan@mycit.ie');
- INSERT INTO CUSTOMER_DOS VALUES('0877556474', 'Laura', 'Butler', 'laurabutler@gmail.com','Downside', 'Ralla Road', 'Paris', 'Paris', '12555', '3', 'P256487');
- INSERT INTO TIMEDIMENSION_DOS VALUES(2,10,07,2,2016,'Sunday');
- INSERT INTO SALES_FACT_DOS VALUES(2,TO_DATE('2016/07/10', 'yyyy/mm/dd'),2,70.00,2,2,3,'laurabutler@gmail.com');
- INSERT INTO CUSTOMER_DOS VALUES('0851256698', 'James', 'Green', 'jamesgreen@mycit.ie','Rockville', 'Chico Street', 'Valencia', 'Valencia', '34554', '4', 'P226474');
- INSERT INTO TIMEDIMENSION_DOS VALUES(3,01,12,4,2015,'Tuesday');
- INSERT INTO SALES_FACT_DOS VALUES(3,TO_DATE('2015/12/01', 'yyyy/mm/dd'),1,70.00,3,2,4,'jamesgreen@mycit.ie');
- INSERT INTO CUSTOMER_DOS VALUES('0836654214', 'Marcus', 'Finucan', 'marucsfinucan@mycit.ie','25', 'Amber Road', 'Berlin', 'Berlin', '67554', '5', 'P233644');
- INSERT INTO TIMEDIMENSION_DOS VALUES(4,25,06,2,2016,'Saturday');
- INSERT INTO SALES_FACT_DOS VALUES(4,TO_DATE('2016/06/25', 'yyyy/mm/dd'),1,70.00,4,2,5,'marucsfinucan@mycit.ie');
- INSERT INTO CUSTOMER_DOS VALUES('0836652312', 'John', 'O Shea', 'johnoshea@mycit.ie','Nashville Terrace', 'NCR', 'Limerick', 'Limerick', '99554', '1', 'P015654');
- INSERT INTO TIMEDIMENSION_DOS VALUES(5,25,09,4,2016,'Sunday');
- INSERT INTO SALES_FACT_DOS VALUES(5,TO_DATE('2016/09/25', 'yyyy/mm/dd'),1,25.00,5,1,1,'johnoshea@mycit.ie');
- INSERT INTO SALES_FACT_DOS VALUES(9,TO_DATE('2016/09/25', 'yyyy/mm/dd'),1,50.00,5,3,1,'johnoshea@mycit.ie');
- INSERT INTO SALES_FACT_DOS VALUES(10,TO_DATE('2016/09/25', 'yyyy/mm/dd'),1,15.00,5,8,1,'johnoshea@mycit.ie');
- INSERT INTO CUSTOMER_DOS VALUES('0862584213', 'David', 'O Leary', 'davidoleary@gmail.com','Broke Yard', 'N50', 'Barcelona', 'Barcelona', '13364', '4', 'P225111');
- INSERT INTO TIMEDIMENSION_DOS VALUES(6,12,03,1,2015,'Thursday');
- INSERT INTO SALES_FACT_DOS VALUES(6,TO_DATE('2016/03/12', 'yyyy/mm/dd'),1,70.00,6,2,4,'davidoleary@gmail.com');
- INSERT INTO CUSTOMER_DOS VALUES('0869089523', 'JJ', 'O Neil', 'jjoneil@mycit.ie','44', 'Mount Terrace', 'Dortmund', 'Dortmund', '12666', '5', 'P227774');
- INSERT INTO TIMEDIMENSION_DOS VALUES(7,15,03,1,2016,'Sunday');
- INSERT INTO SALES_FACT_DOS VALUES(7,TO_DATE('2016/03/15', 'yyyy/mm/dd'),1,70.00,7,2,5,'jjoneil@mycit.ie');
- INSERT INTO CUSTOMER_DOS VALUES('0877744569', 'Roisin', 'Murphy', 'roisinmurphy@gmail.com','122', 'Raheen', 'Dublin', 'Dublin', '33554', '1', 'P885654');
- INSERT INTO TIMEDIMENSION_DOS VALUES(8,29,10,4,2016,'Saturday');
- INSERT INTO SALES_FACT_DOS VALUES(8,TO_DATE('2016/10/29', 'yyyy/mm/dd'),1,70.00,8,2,1,'roisinmurphy@gmail.com');
- SET SERVEROUTPUT ON FORMAT WRAPPED;
- EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('CUSTOMERDIMENSION_DOS');
- --DATACUBE
- SELECT COUNTY_STATE, TIMEMONTH,
- SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
- FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
- WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
- AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
- AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
- AND (COUNTRYNAME = 'Ireland')
- AND TIMEYEAR = 2016
- GROUP BY CUBE (COUNTY_STATE, TIMEMONTH);
- --ROLLUP
- SELECT TIMEMONTH, TIMEYEAR,
- SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
- FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
- WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
- AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
- AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
- AND (COUNTRYNAME = 'France'
- OR COUNTRYNAME = 'Spain')
- AND TIMEYEAR BETWEEN 2015 AND 2016
- GROUP BY ROLLUP (TIMEMONTH, TIMEYEAR, COUNTRYNAME);
- --STILL TO DO >>
- --GROUPING SETS
- SELECT ZIPCODE, TIMEMONTH,
- SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
- FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
- WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
- AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
- AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
- AND (COUNTRYNAME = 'USA'
- OR COUNTRYNAME = 'Canada')
- AND TIMEYEAR = 2016
- GROUP BY GROUPING SETS((ZIPCODE, TIMEMONTH),
- ZIPCODE, TIMEMONTH, ());
- --MATERIALSED VIEW
- CREATE MATERIALIZED VIEW MV_SALESSINCE2013_DOS
- BUILD IMMEDIATE
- REFRESH COMPLETE ON DEMAND
- ENABLE QUERY REWRITE AS
- SELECT ZIPCODE, TIMEYEAR,
- SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMREGION
- FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
- WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
- AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
- AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
- AND TIMEYEAR > 2013
- GROUP BY REGION, TIMEYEAR;
- SELECT * FROM MV_SALESSINCE2013_DOS WHERE TIMEYEAR = 2016 AND COUNTRYNAME = 'Europe';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement