Advertisement
Guest User

Untitled

a guest
Mar 26th, 2017
150
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.54 KB | None | 0 0
  1. /*
  2. FlyByNite Data Warehouse
  3. */
  4.  
  5. DELETE FROM SALES_FACT_DOS;
  6.  
  7. DROP TABLE SALES_FACT_DOS;
  8.  
  9. DROP TABLE CUSTOMER_DOS;
  10.  
  11. DROP TABLE PRODUCT_DOS;
  12.  
  13. DROP TABLE COUNTRY_DOS;
  14.  
  15. DROP TABLE TIMEDIMENSION_DOS;
  16.  
  17. DROP DIMENSION CUSTOMERDIMENSION_DOS;
  18.  
  19. DROP MATERIALIZED VIEW MV_SALESSINCE2013_DOS;
  20.  
  21. CREATE TABLE COUNTRY_DOS(COUNTRYID NUMERIC(10) NOT NULL,
  22. COUNTRYNAME VARCHAR2(40) NOT NULL,
  23. REGION VARCHAR2(11) NOT NULL,
  24. CONSTRAINT COUNTRY_PK_DOS PRIMARY KEY(COUNTRYID));
  25.  
  26. CREATE TABLE CUSTOMER_DOS(MOBILEPHONENUMBER NUMERIC(10) NOT NULL,
  27. FIRSTNAME VARCHAR2(40) NOT NULL,
  28. LASTNAME VARCHAR2(40) NOT NULL,
  29. EMAILADDRESS VARCHAR(40) NOT NULL,
  30. HOUSEADDRESS VARCHAR2(40) NOT NULL,
  31. STREETADDRESS VARCHAR2(50) NOT NULL,
  32. CITY VARCHAR2(30) NOT NULL,
  33. COUNTY_STATE VARCHAR2(40) NOT NULL,
  34. ZIPCODE VARCHAR2(20),
  35. COUNTRYID NUMERIC(10) NOT NULL,
  36. PASSPORT VARCHAR2(10) NOT NULL,
  37. CONSTRAINT CUSTOMER_PK_DOS PRIMARY KEY(EMAILADDRESS),
  38. CONSTRAINT COUNTRY_FK1_DOS FOREIGN KEY(COUNTRYID)
  39. REFERENCES COUNTRY_DOS(COUNTRYID));
  40.  
  41. CREATE TABLE PRODUCT_DOS(PRODUCTID NUMERIC(10) NOT NULL,
  42. PRODUCTNAME VARCHAR2(255) NOT NULL,
  43. PRODUCTUNITCOST DECIMAL(10,2) NOT NULL,
  44. CONSTRAINT PRODUCT_PK_DOS PRIMARY KEY(PRODUCTID));
  45.  
  46. CREATE TABLE TIMEDIMENSION_DOS(TIMEID NUMERIC(10) NOT NULL,
  47. TIMEDAY VARCHAR2(10) NOT NULL,
  48. TIMEMONTH VARCHAR2(9) NOT NULL,
  49. TIMEQUARTER NUMERIC(1) NOT NULL,
  50. TIMEYEAR NUMERIC(4) NOT NULL,
  51. TIMEDAYOFWEEK VARCHAR2(9) NOT NULL,
  52. CONSTRAINT TIME_PK_DOS PRIMARY KEY(TIMEID));
  53.  
  54. CREATE TABLE SALES_FACT_DOS(SALESID NUMERIC(10) NOT NULL,
  55. SALESDATE DATE NOT NULL,
  56. SALESUNITS NUMERIC(10) NOT NULL,
  57. EUROSALESAMOUNT DECIMAL(10,2) NOT NULL,
  58. TIMEID NUMERIC(10) NOT NULL,
  59. PRODUCTID NUMERIC(10) NOT NULL,
  60. COUNTRYID NUMERIC(10) NOT NULL,
  61. CUSTOMERID VARCHAR2(40) NOT NULL,
  62. CONSTRAINT SALES_PK_DOS PRIMARY KEY(SALESID),
  63. CONSTRAINT TIME_FK_DOS FOREIGN KEY(TIMEID)
  64. REFERENCES TIMEDIMENSION_DOS(TIMEID),
  65. CONSTRAINT PRODUCT_FK_DOS FOREIGN KEY(PRODUCTID)
  66. REFERENCES PRODUCT_DOS(PRODUCTID),
  67. CONSTRAINT COUNTRY_FK2_DOS FOREIGN KEY(COUNTRYID)
  68. REFERENCES COUNTRY_DOS(COUNTRYID),
  69. CONSTRAINT CUSTOMER_FK_DOS FOREIGN KEY(CUSTOMERID)
  70. REFERENCES CUSTOMER_DOS(EMAILADDRESS));
  71.  
  72. CREATE DIMENSION CUSTOMERDIMENSION_DOS
  73. LEVEL CUSTOMERID IS CUSTOMER_DOS.MOBILEPHONENUMBER
  74. LEVEL HOUSEADDRESS IS CUSTOMER_DOS.HOUSEADDRESS
  75. LEVEL STREETADDRESS IS CUSTOMER_DOS.STREETADDRESS
  76. LEVEL CITY IS CUSTOMER_DOS.CITY
  77. LEVEL COUNTY_STATE IS CUSTOMER_DOS.COUNTY_STATE
  78. LEVEL COUNTRY IS COUNTRY_DOS.COUNTRYID
  79. LEVEL REGION IS COUNTRY_DOS.REGION
  80. HIERARCHY ADDRESSROLLUP (
  81. HOUSEADDRESS CHILD OF
  82. STREETADDRESS CHILD OF
  83. CITY CHILD OF
  84. COUNTY_STATE CHILD OF
  85. COUNTRY CHILD OF
  86. REGION
  87. JOIN KEY CUSTOMER_DOS.COUNTRYID REFERENCES COUNTRY)
  88. ATTRIBUTE CUSTOMERID DETERMINES
  89. (CUSTOMER_DOS.FIRSTNAME, CUSTOMER_DOS.LASTNAME)
  90. ATTRIBUTE COUNTRY DETERMINES (COUNTRY_DOS.COUNTRYNAME);
  91.  
  92.  
  93. INSERT INTO COUNTRY_DOS VALUES(1,'Ireland','EU');
  94. INSERT INTO COUNTRY_DOS VALUES(2,'England','EU');
  95. INSERT INTO COUNTRY_DOS VALUES(3,'France','EU');
  96. INSERT INTO COUNTRY_DOS VALUES(4,'Spain','EU');
  97. INSERT INTO COUNTRY_DOS VALUES(5,'Germany','EU');
  98. INSERT INTO COUNTRY_DOS VALUES(6,'UK','EU');
  99.  
  100.  
  101. INSERT INTO PRODUCT_DOS VALUES(1,'Flights within a country', 25.00);
  102. INSERT INTO PRODUCT_DOS VALUES(2,'Flights Throughout EU' ,70.00);
  103. INSERT INTO PRODUCT_DOS VALUES(3,'Late Booking EU' ,50.00);
  104. INSERT INTO PRODUCT_DOS VALUES(4,'Late Booking INT' ,150.00);
  105. INSERT INTO PRODUCT_DOS VALUES(5,'Budjet Hotel' ,45.00);
  106. INSERT INTO PRODUCT_DOS VALUES(6,'Mid Range Hotel', 60.00);
  107. INSERT INTO PRODUCT_DOS VALUES(7,'Premium Hotel' ,100.00);
  108. INSERT INTO PRODUCT_DOS VALUES(8,'Overweight KG' ,15.00);
  109. INSERT INTO PRODUCT_DOS VALUES(9,'Boarding Card Pen', 40.00);
  110.  
  111.  
  112. --CUSTOMER PURCHASING A DOMESTIC FLIGHT FROM IRELAND
  113. INSERT INTO CUSTOMER_DOS VALUES('0877785411', 'Diarmaid', 'O Sullivan', 'diarmaidosullivan@mycit.ie','Ambleside', 'SCR', 'Limerick', 'Limerick', 'LK1', '1', 'P225654');
  114. INSERT INTO TIMEDIMENSION_DOS VALUES(1,06,06,2,2016,'Monday');
  115. INSERT INTO SALES_FACT_DOS VALUES(1,TO_DATE('2016/06/06', 'yyyy/mm/dd'),1,25.00,1,1,1,'diarmaidosullivan@mycit.ie');
  116.  
  117. INSERT INTO CUSTOMER_DOS VALUES('0877556474', 'Laura', 'Butler', 'laurabutler@gmail.com','Downside', 'Ralla Road', 'Paris', 'Paris', 'PR2', '3', 'P256487');
  118. INSERT INTO TIMEDIMENSION_DOS VALUES(2,10,07,2,2016,'Sunday');
  119. INSERT INTO SALES_FACT_DOS VALUES(2,TO_DATE('2016/07/10', 'yyyy/mm/dd'),2,70.00,2,2,3,'laurabutler@gmail.com');
  120.  
  121. INSERT INTO CUSTOMER_DOS VALUES('0851256698', 'James', 'Green', 'jamesgreen@mycit.ie','Rockville', 'Chico Street', 'Valencia', 'Valencia', 'VL3', '4', 'P226474');
  122. INSERT INTO TIMEDIMENSION_DOS VALUES(3,01,12,4,2015,'Tuesday');
  123. INSERT INTO SALES_FACT_DOS VALUES(3,TO_DATE('2015/12/01', 'yyyy/mm/dd'),1,70.00,3,2,4,'jamesgreen@mycit.ie');
  124.  
  125.  
  126. INSERT INTO CUSTOMER_DOS VALUES('0836654214', 'Marcus', 'Finucan', 'marucsfinucan@mycit.ie','25', 'Amber Road', 'Berlin', 'Berlin', 'BR4', '5', 'P233644');
  127. INSERT INTO TIMEDIMENSION_DOS VALUES(4,25,06,2,2016,'Saturday');
  128. INSERT INTO SALES_FACT_DOS VALUES(4,TO_DATE('2016/06/25', 'yyyy/mm/dd'),1,70.00,4,2,5,'marucsfinucan@mycit.ie');
  129.  
  130. INSERT INTO CUSTOMER_DOS VALUES('0836652312', 'John', 'O Shea', 'johnoshea@mycit.ie','Nashville Terrace', 'NCR', 'Limerick', 'LK1', '99554', '1', 'P015654');
  131. INSERT INTO TIMEDIMENSION_DOS VALUES(5,25,09,4,2016,'Sunday');
  132. INSERT INTO SALES_FACT_DOS VALUES(5,TO_DATE('2016/09/25', 'yyyy/mm/dd'),1,25.00,5,1,1,'johnoshea@mycit.ie');
  133.  
  134. INSERT INTO CUSTOMER_DOS VALUES('0862584213', 'David', 'O Leary', 'davidoleary@gmail.com','Broke Yard', 'N50', 'Barcelona', 'Barcelona', 'BR5', '4', 'P225111');
  135. INSERT INTO TIMEDIMENSION_DOS VALUES(6,12,03,1,2015,'Thursday');
  136. INSERT INTO SALES_FACT_DOS VALUES(6,TO_DATE('2016/03/12', 'yyyy/mm/dd'),1,70.00,6,2,4,'davidoleary@gmail.com');
  137.  
  138. INSERT INTO CUSTOMER_DOS VALUES('0869089523', 'JJ', 'O Neil', 'jjoneil@mycit.ie','44', 'Mount Terrace', 'Dortmund', 'Dortmund', 'DM6', '5', 'P227774');
  139. INSERT INTO TIMEDIMENSION_DOS VALUES(7,15,03,1,2016,'Sunday');
  140. INSERT INTO SALES_FACT_DOS VALUES(7,TO_DATE('2016/03/15', 'yyyy/mm/dd'),1,70.00,7,2,5,'jjoneil@mycit.ie');
  141.  
  142. INSERT INTO CUSTOMER_DOS VALUES('0877744569', 'Roisin', 'Murphy', 'roisinmurphy@gmail.com','122', 'Raheen', 'Dublin', 'Dublin', 'DN7', '1', 'P885654');
  143. INSERT INTO TIMEDIMENSION_DOS VALUES(8,29,10,4,2016,'Saturday');
  144. INSERT INTO SALES_FACT_DOS VALUES(8,TO_DATE('2016/10/29', 'yyyy/mm/dd'),1,70.00,8,1,1,'roisinmurphy@gmail.com');
  145.  
  146. INSERT INTO CUSTOMER_DOS VALUES('0877777885', 'Eric', 'Moylan', 'ericmoylan@gmail.com','99', 'Earl Street', 'Chelsea', 'London', 'LN8', '6', 'P884454');
  147. INSERT INTO TIMEDIMENSION_DOS VALUES(9,2,11,4,2016,'Friday');
  148. INSERT INTO SALES_FACT_DOS VALUES(9,TO_DATE('2016/11/02', 'yyyy/mm/dd'),1,70.00,9,2,6,'ericmoylan@gmail.com');
  149. INSERT INTO SALES_FACT_DOS VALUES(10,TO_DATE('2016/11/02', 'yyyy/mm/dd'),1,50.00,9,3,6,'ericmoylan@gmail.com');
  150. INSERT INTO SALES_FACT_DOS VALUES(11,TO_DATE('2016/09/02', 'yyyy/mm/dd'),1,15.00,9,8,6,'ericmoylan@gmail.com');
  151.  
  152.  
  153.  
  154. SET SERVEROUTPUT ON FORMAT WRAPPED;
  155. EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('CUSTOMERDIMENSION_DOS');
  156.  
  157. --DATACUBE
  158.  
  159. SELECT COUNTY_STATE, TIMEMONTH,
  160. SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
  161. FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
  162. WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
  163. AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
  164. AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
  165. AND (COUNTRYNAME = 'Ireland')
  166. AND TIMEYEAR = 2016
  167. GROUP BY CUBE (COUNTY_STATE, TIMEMONTH);
  168.  
  169. --ROLLUP
  170.  
  171. SELECT TIMEMONTH, TIMEYEAR,
  172. SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
  173. FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
  174. WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
  175. AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
  176. AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
  177. AND (COUNTRYNAME = 'France'
  178. OR COUNTRYNAME = 'Spain')
  179. AND TIMEYEAR BETWEEN 2015 AND 2016
  180. GROUP BY ROLLUP (TIMEMONTH, TIMEYEAR, COUNTRYNAME);
  181.  
  182.  
  183. --GROUPING SETS
  184.  
  185. SELECT ZIPCODE, TIMEMONTH,
  186. SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
  187. FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
  188. WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
  189. AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
  190. AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
  191. AND (COUNTRYNAME = 'UK')
  192. AND TIMEYEAR = 2016
  193. GROUP BY GROUPING SETS((ZIPCODE, TIMEMONTH),
  194. ZIPCODE, TIMEMONTH, ());
  195.  
  196.  
  197. --MATERIALSED VIEW 1
  198.  
  199. CREATE MATERIALIZED VIEW MV_SALESSINCE2013_DOS
  200. BUILD IMMEDIATE
  201. REFRESH COMPLETE ON DEMAND
  202. ENABLE QUERY REWRITE AS
  203. SELECT COUNTRYNAME, TIMEYEAR,
  204. SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
  205. FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
  206. WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
  207. AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
  208. AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
  209. AND TIMEYEAR >= 2013
  210. GROUP BY COUNTRYNAME, TIMEYEAR;
  211.  
  212. SELECT * FROM MV_SALESSINCE2013_DOS WHERE TIMEYEAR >= 2013;
  213.  
  214.  
  215. --MATERIALSED VIEW 2
  216.  
  217. CREATE MATERIALIZED VIEW MV_GERMANY2016_DOS
  218. BUILD IMMEDIATE
  219. REFRESH COMPLETE ON DEMAND
  220. ENABLE QUERY REWRITE AS
  221. SELECT COUNTRYNAME, TIMEYEAR,
  222. SUM(SALESUNITS * EUROSALESAMOUNT) AS SUMSALES
  223. FROM SALES_FACT_DOS, CUSTOMER_DOS, TIMEDIMENSION_DOS, COUNTRY_DOS
  224. WHERE SALES_FACT_DOS.CUSTOMERID = CUSTOMER_DOS.EMAILADDRESS
  225. AND SALES_FACT_DOS.TIMEID = TIMEDIMENSION_DOS.TIMEID
  226. AND CUSTOMER_DOS.COUNTRYID = COUNTRY_DOS.COUNTRYID
  227. AND (COUNTRYNAME = 'Germany')
  228. AND TIMEYEAR = 2016
  229. GROUP BY COUNTRYNAME, TIMEYEAR;
  230.  
  231. SELECT * FROM MV_GERMANY2016_DOS WHERE TIMEYEAR = 2016 AND COUNTRYNAME ='Germany';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement