superstore_DML.sql

INSERT INTO CUSTOMERS_DIM
SELECT T1.$6,T1.$7,T1.$8,T1.$10,T1.$11,T1.$9,T1.$12,T1.$13,T2.$1
FROM ALL_DATA T1 INNER JOIN PEOPLE T2
ON T1.$13 = T2.$2;


INSERT INTO PRODUCTS_DIM
SELECT T.$14, T.$17, T.$15, T.$16, COUNT(T.*)
FROM(
    SELECT $14,$17,$15,$16
    FROM ALL_DATA
    ) T
GROUP BY T.$14, T.$17, T.$15, T.$16;


INSERT INTO ORDERS_DIM
SELECT DISTINCT P1.$2,P1.$3,P1.$4,P1.$5,NVL(P2.$1,'No')
FROM ALL_DATA P1 LEFT OUTER JOIN RETURNS P2
ON P1.$2 = P2.$2;

TRUNCATE SALES_FACT;
TRUNCATE ORDERS_DIM;

SELECT * FROM CUSTOMERS_DIM;
SELECT * FROM PRODUCTS_DIM;
SELECT * FROM ORDERS_DIM;

COMMIT;

INSERT INTO SALES_FACT
SELECT 'US-' || LPAD(FACT_SEQ.NEXTVAL,5,'0') AS FACT_ID,$2,$6,$14,$18,$19,$20,$21
FROM ALL_DATA;

SELECT * FROM SALES_FACT;

COMMIT;