Push-by-source CDC on Sales Orders Extraction


I demonstrate in this section how push-by-source CDC works on sales order source data. Push-by-source CDC means the source system extracts only the changes since the last extraction. In the case of sales order source data, the source system is the source database created in Chapter 1, “Basic Components.”

The script in Listing 5.1 is a stored procedure that extracts sales order data from the sales_order table in the source database. The script captures the changes in the table daily (Let’s assume you load data daily into your data warehouse).

Listing 5.1: Push CDC sales orders

image from book
 /*****************************************************************/ /*                                                               */ /* push_sales_order.sql                                          */ /*                                                               */ /*****************************************************************/ /* point to source database                                      */ USE source; DELIMITER // ; DROP PROCEDURE IF EXISTS push_sales_order // CREATE PROCEDURE push_sales_order() BEGIN INSERT INTO dw.sales_order_fact SELECT   a.order_amount , b.order_sk , c.customer_sk , d.product_sk , e.date_sk FROM   sales_order a , dw.order_dim b , dw.customer_dim c , dw.product_dim d , dw.date_dim e WHERE     a.entry_date = CURRENT_DATE AND a.order_number = b.order_number AND a.customer_number = c.customer_number AND a.product_code = d.product_code AND a.order_date >= d.effective_date AND a.order_date <= d.expiry_date AND a.order date = e.date ; END // DELIMITER ; // /* end of script                                                 */
image from book

You compile the stored procedure and store it in the source database. You also run the script daily at the source. In this case, the script runs push-by-source CDC extraction.

Note 

The two lines in bold almost towards the end of the script ensure that valid products on the order date will be picked up for the sales orders.

To compile and run the stored procedure, use the following command.

 mysql> \. c:\mysql\scripts\push_sales_order.sql

Here is the response on your console.

 Database changed Query OK, 0 rows affected, 1 warning (0.08 sec) Query OK, 0 rows affected (0.05 sec)

To confirm that the stored procedure has successfully been created, invoke the run procedure command.

 mysql> show procedure status like 'push_sales_order' \G;

You should see this on your console.

 *************************** 1. row ***************************            Db: source          Name: push_sales_order          Type: PROCEDURE       Definer: root@localhost      Modified: 2007-02-05 22:26:56       Created: 2007-02-05 22:26:56 Security_type: DEFINER       Comment: 1 row in set (0.00 sec) mysql>



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net