Regular Population Part 1-Data Warehouse-Lab Mannual, Lecture notes of Data Warehousing

Download Regular Population Part 1-Data Warehouse-Lab Mannual and more Lecture notes Data Warehousing in PDF only on Docsity! Objectives • Regular Population • Regular Population Scheduling • Adding Columns • On-Demand Population docsity.com Regular Population • It is used to populate the data warehouse on daily basis. • The extraction mode and loading type used are as follows: – The customer.csv and product.txt flat files are loaded into the customer_dim and product_dim tables through the customer_stg and product_stg tables. – SCD2 is applied to customer addresses, product names, and product groups. SCD1 is applied to customer names. – Only sales orders entered on the current date are loaded to the order_dim and sales_order_fact tables. • Now you will create a script file for regular Population. • Copy the commands in text file file and save it as dw_regular.sql file docsity.com Adding New Row for Customer INSERT INTO customer_dim SELECT NULL , b.customer_number , b.customer_name , b.customer_street_address , b.customer_zip_code , b.customer_city , b.customer_state , CURRENT_DATE , '9999-12-31' FROM customer_dim a , customer_stg b WHERE a.customer_number = b.customer_number AND (a.customer_street_address <> b.customer_street_address) AND EXISTS ( SELECT * FROM customer_dim x WHERE b.customer_number = x.customer_number AND a.expiry_date = SUBDATE(CURRENT_DATE, 1)) AND NOT EXISTS ( SELECT * FROM customer_dim y WHERE b.customer_number = y.customer_number AND y.expiry_date = '9999-12-31') ; docsity.com Applying SCD1 on Customer Name UPDATE customer_dim a, customer_stg b SET a.customer_name = b.customer_name WHERE a.customer_number = b.customer_number AND a.customer_name <> b.customer_name ; docsity.com Adding New Customer INSERT INTO customer_dim SELECT NULL , customer_number , customer_name , customer_street_address , customer_zip_code , customer_city , customer_state , CURRENT_DATE , '9999-12-31' FROM customer_stg WHERE customer_number NOT IN( SELECT y.customer_number FROM customer_dim x, customer_stg y WHERE x.customer_number = y.customer_number ) ; docsity.com Adding a New Row for Product INSERT INTO product_dim SELECT NULL , b.product_code , b.product_name , b.product_category , CURRENT_DATE , '9999-12-31' FROM product_dim a , product_stg b WHERE a.product_code = b.product_code AND ( a.product_name <> b.product_name OR a.product_category <> b.product_category ) AND EXISTS ( SELECT * FROM product_dim x WHERE b.product_code = x.product_code AND a.expiry_date = SUBDATE(CURRENT_DATE, 1)) AND NOT EXISTS ( SELECT * FROM product_dim y WHERE b.product_code = y.product_code AND y.expiry_date = '9999-12-31') ; docsity.com Add New Row INSERT INTO product_dim SELECT NULL , product_code , product_name , product_category , CURRENT_DATE , '9999-12-31' FROM product_stg WHERE product_code NOT IN( SELECT y.product_code FROM product_dim x, product_stg y WHERE x.product_code = y.product_code ) ; docsity.com Insertion in Sales Order Dim Table INSERT INTO order_dim ( order_sk , order_number , effective_date , expiry_date ) SELECT NULL , order_number , order_date , '9999-12-31' FROM source.sales_order WHERE entry_date = CURRENT_DATE ; docsity.com Preparing Customer • The changes to the customer.csv file are as follows: – The street number of customer number 6 is now 7777 Ritter Rd. (It was 7000 Ritter Rd.) – The name of customer number 7 is now Distinguished Agencies. (It was Distinguished Partners). – Add a new customer as the eighth customer. docsity.com Preparing Product • These are the changes to the product.txt file. • The name of Product 3 is now Flat Panel. (It was LCD Panel). • Add a new product as the fourth product. docsity.com Preparing the sales orders USE source; INSERT INTO sales_order VALUES (22, 1, 1, '2010-11-29', '2010-11-29', 1000) , (23, 2, 2, '2010-11-29', '2010-11-29', 2000) , (24, 3, 3, '2010-11-29', '2010-11-29', 3000) , (25, 4, 4, '2010-11-29', '2010-11-29', 4000) , (26, 5, 2, '2010-11-29', '2010-11-29', 1000) , (27, 6, 2, '2010-11-29', '2010-11-29', 3000) , (28, 7, 3, '2010-11-29', '2010-11-29', 5000) , (29, 8, 4, '2010-11-29', '2010-11-29', 7000) , (30, 1, 1, '2010-11-29', '2010-11-29', 1000) , (31, 2, 2, '2010-11-29', '2010-11-29', 2000) , (32, 3, 3, '2010-11-29', '2010-11-29', 4000) , (33, 4, 4, '2010-11-29', '2010-11-29', 6000) , (34, 5, 1, '2010-11-29', '2010-11-29', 2500) , (35, 6, 2, '2010-11-29', '2010-11-29', 5000) , (36, 7, 3, '2010-11-29', '2010-11-29', 7500) , (37, 8, 4, '2010-11-29', '2010-11-29', 1000) ; docsity.com Confirming Successful Regular Population(01) • uery the product_dim table, use this SQL statement. – select * from product_dim \G • The result from querying the product_dim table shows you that – SCD2 was applied to the name of Product 3 – The new product 4 was added docsity.com Confirming Successful Regular Population(02) • query the order_dim table. – select * from order_dim; • In the result, You should now have 35 orders docsity.com Confirming Successful Regular Population(03) • query the sales_order_fact table. – select * from sales_order_fact; • The query result shows that: – The sixteen sales orders entered on November 29, 2010 were added – The valid product and customer were picked up correctly based on the order dates: • Surrogate key 4 for Product 3, not the one with surrogate key 3 • Surrogate key 8 for Customer 6, not the one with surrogate key 6 docsity.com Scheduling the Batch Job(01) 6. Click Next again. The next window appears. You will be asked to enter the name and password of the user whose credentials will be used to run the task. 7. Click Next. The next window in the Scheduled Task Wizard appears. 8. Enable the Open advanced properties for this task when I click Finish check box and click Finish. 9. The Advanced Properties window will appear. 10. Click the Browse button and browse the file system and select the dw_regular_load.bat file. 11. Click the Set password button and you will see the Set Account Information box. Type in your user name and password and click OK. docsity.com Scheduling the Batch Job(02) • The daily scheduled task of your data warehouse regular load is now set up. It will start to run at the date and time you specified. You can see a new icon in the Scheduled Tasks window docsity.com Adding Columns USE dw; ALTER TABLE customer_dim ADD shipping_address CHAR(50) AFTER customer_state , ADD shipping_zip_code INT(5) AFTER shipping_address , ADD shipping_city CHAR(30) AFTER shipping_zip_code , ADD shipping_state CHAR(2) AFTER shipping_city ; ALTER TABLE customer_stg ADD shipping_address CHAR(50) AFTER customer_state , ADD shipping_zip_code INT(5) AFTER shipping_address , ADD shipping_city CHAR(30) AFTER shipping_zip_code , ADD shipping_state CHAR(2) AFTER shipping_city ; docsity.com