ETL and SQL Select from EXSale 2 rows

ETL and SQL

Select * from EX_Sale (2 rows) Receipt_No A 19082019 G 300001 A 19082019 G 300002 Cust_Id Date C 001 19/08/2016 C 002 19/08/2016 Select * from EX_Sale_Detail (5 Rows) Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 1 2 1 1 2 select * from EX_Sale, EX_Sale_Detail Receipt_No A 19082019 G 300001 A 19082019 G 300001 A 19082019 G 300002 A 19082019 G 300002 Cust_Id C 001 C 001 C 002 C 002 Date 19/08/2016 19/08/2016 19/08/2016 Receipt_no A 19082019 G 300001 A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 1 2 1 1 2

Select * from EX_Sale (2 rows) Receipt_No A 19082019 G 300001 A 19082019 G 300002 Select * from EX_Sale_Detail (5 Rows) Receipt_no Cust_Id Date C 001 19/08/2016 C 002 19/08/2016 A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 select * from EX_Sale A, EX_Sale_Detail B where A. Receipt_No=B. Receipt_No A 19082019 G 300001 A 19082019 G 300002 Cust_Id C 001 C 002 Date 19/08/2016 19/08/2016 Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 1 G 002 2 G 003 1 G 001 1 G 004 2 1 1 2

select * from EX_Product Select * from EX_Sale_Detail (5 Rows) Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 Product_Id G 001 G 002 G 003 G 004 G 005 1 2 1 1 2 select * from EX_Sale_Detail A, EX_Product B where A. Product_Id=B. Product_Id ? ? ? ? Receipt_no Product_Id Sale_Qty A 19082019 G 300001 G 001 A 19082019 G 300001 G 002 A 19082019 G 300001 G 003 A 19082019 G 300002 G 001 A 19082019 G 300002 G 004 1 2 1 1 2 Product_Id G 001 G 002 G 003 G 001 G 004 Product_Name Beer Blanket Bread Candy Coke Product_Name Beer Blanket Bread Beer Candy

select * from EX_Product Select * from EX_Sale_Detail (5 Rows) Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 Product_Id G 001 G 002 G 003 G 004 G 005 1 2 1 1 2 select * from EX_Sale_Detail A, EX_Product B where A. Product_Id=B. Product_Id Receipt_no Product_Id Sale_Qty A 19082019 G 300001 G 001 A 19082019 G 300001 G 002 A 19082019 G 300001 G 003 A 19082019 G 300002 G 001 A 19082019 G 300002 G 004 1 2 1 1 2 Product_Id G 001 G 002 G 003 G 001 G 004 Product_Name Beer Blanket Bread Candy Coke Product_Name Beer Blanket Bread Beer Candy

select * from EX_Product Select * from EX_Sale_Detail (5 Rows) Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 1 2 1 1 2 Product_Id G 001 G 002 G 003 G 004 G 005 Product_Name Beer Blanket Bread Candy Coke select A. Receipt_No, A. Product_Id, A. Sale_Qty, B. Product_Name from EX_Sale_Detail A, EX_Product B ? ? ? ? where A. Product_Id=B. Product_Id Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id G 001 G 002 G 003 G 001 G 004 Sale_Qty Product_Name 1 Beer 2 Blanket 1 Bread 1 Beer 2 Candy

select * from EX_Product Select * from EX_Sale_Detail (5 Rows) Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id Sale_Qty G 001 G 002 G 003 G 001 G 004 1 2 1 1 2 Product_Id G 001 G 002 G 003 G 004 G 005 Product_Name Beer Blanket Bread Candy Coke select A. Receipt_No, A. Product_Id, A. Sale_Qty, B. Product_Name from EX_Sale_Detail A, EX_Product B where A. Product_Id=B. Product_Id Receipt_no A 19082019 G 300001 A 19082019 G 300002 Product_Id G 001 G 002 G 003 G 001 G 004 Sale_Qty Product_Name 1 Beer 2 Blanket 1 Bread 1 Beer 2 Candy

Requirement OLAP (Star Schema)

ขนตอนการนำเขาขอมลเขาส 1. สราง Staging Area Table Stage_Sale. Data 2. Load ขอมลจาก Table Sales Staging Area ไป ยง Data Warehouse 3. ขอมลบางสวน Load จาก TPS ไปยง Data Warehouse Table Sale_Date Table Product Table Shop DW

ETL Data. To Data Warehouse ETL Data Staging Area Table Stage_Sale. Data DW 1 Table ? 2 Table ? 3 Table ? 4 Table ?

Stage_Sale. Data 1. delete from Stage_Sale. Data 2. insert into Stage_Sale. Data(Receipt_No, Date, Cust_id, Branch_No, Product_Id, Total_Amount) Select A. Receipt_No, A. Date, A. Cust_Id, A. Branch_No, B. Product_Id, B. Total_Amount from TPS_Sale A, TPS_Sale_Detail B where A. Receipt_No = B. Receipt_No order by Receipt_No asc 3. Select * from Stage_Sale. Data

สราง Dataware house 1. delete from sales 2. insert into Sales select Branch_No, Product_Id, Date, SUM(Total_amount) from Stage_Sale. Data group by DATE, Product_Id, Branch_No

Update sales set Date_Id='D 001' where Date_Id='19/08/2016' Update sales set Date_Id='D 002' where Date_Id='20/08/2016'

การ Insert Table Shop 1. Delete Shop 2. insert into Shop select Branch_No, Location from TPS_Warehouse 3. Select * from Shop

การ Insert Table Product 1. Delete Product 2. insert into Product(Product_id, Product_Name) select Product_Id, Product_Name from TPS_Product 3. Select * from Product

select * from sales