Project 2 Milestone 3: DW Reporting and Visualization

/* DW-OLAP-SQL.TXT */

/* DATA WAREHOUSE OLAP SQL */

/* Script file for ORACLE DBMS */

/* CREATE TABLES */

@PAHT\DW-DBINIT.SQL

/* ROLLUP EXAMPLE */

SELECT V_CODE, P_CODE, SUM(SALE_UNITS*SALE_PRICE) AS TOTSALES

FROM DWDAYSALESFACT NATURAL JOIN DWPRODUCT NATURAL JOIN DWVENDOR

GROUP BY ROLLUP (V_CODE, P_CODE)

ORDER BY V_CODE, P_CODE;

/* CUBE EXAMPLE */

SELECT TM_MONTH, P_CODE, SUM(SALE_UNITS*SALE_PRICE) AS TOTSALES

FROM DWDAYSALESFACT NATURAL JOIN DWPRODUCT NATURAL JOIN DWTIME

GROUP BY CUBE (TM_MONTH, P_CODE)

ORDER BY TM_MONTH, P_CODE;

/* MATERIALIZED VIEW EXAMPLE */

/* MUST CREATE MATERIALIZED VIEW LOGS ON BASE TABLES FIRST */

/* CREATES MATERIALIZED VIEW LOG FILE */

/* ON BASE TABLES - REQUIRED IF DOING FAST REFRESH */

/* FAST REFRESH OF MV WITH AGGREGATES REQUIRES: */

/* ROWID, SEQUENCE INCLUDING NEW VALUES */

CREATE MATERIALIZED VIEW LOG ON DWTIME

WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DWDAYSALESFACT

WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

/* CREATE AMATERIALIZED VIEW TO REFRESH ON COMMIT */

/* REFRESH ON COMMIT */

CREATE MATERIALIZED VIEW SALES_MONTH_MV

BUILD IMMEDIATE

REFRESH FORCE ON COMMIT

ENABLE QUERY REWRITE

AS SELECT TM_YEAR, TM_MONTH, P_CODE,

SUM(SALE_UNITS), SUM(SALE_PRICE*SALE_UNITS) AS SUM_SALES

FROM DWTIME T, DWDAYSALESFACT S

WHERE S.TM_ID = T.TM_ID

GROUP BY TM_YEAR, TM_MONTH, P_CODE;

SELECT * FROM SALES_MONTH_MV ORDER BY TM_YEAR, TM_MONTH, SUM_SALES;

COMMIT;

INSERT INTO DWDAYSALESFACT VALUES (207,10017,'WR3/TT3',1,106.99);

COMMIT;

SELECT * FROM SALES_MONTH_MV ORDER BY TM_YEAR, TM_MONTH, SUM_SALES;

/* DROP ALL OBJECTS CREATED */

DROP MATERIALIZED VIEW SALES_MONTH_MV;

DROP MATERIALIZED VIEW LOG ON DWTIME;

DROP MATERIALIZED VIEW LOG ON DWDAYSALESFACT;

DELETE FROM DWDAYSALESFACT

WHERE TM_ID=207

AND CUS_CODE=10017

AND P_CODE='SM-18277';

COMMIT;