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

 /*****************************************************************/ /*                                                               */ /* 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                                                 */

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>