/* DW-OLAP-SQL.TXT                                    	*/
/* DATA WAREHOUSE OLAP SQL 				*/
/* Script file for ORACLE DBMS				*/

/* CREATE TABLES */
@PATH\Ch13-SaleCo_DW_ORA.txt

/* 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) AS SUM_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;
