Testing

I show you in this section how to confirm that the stored procedure you already compiled correctly does push-by-source CDC daily extraction from the sales_order table in the source database and loading the extracted data into the sales order fact table in the data warehouse.

The first step you need to do is create a sales_order table in the source database by running the script in Listing 5.2. Then, add some test data (four sales orders and a date) to the data warehouse (dw) database by running the script in Listing 5.3.

Listing 5.2: Creating the sales_order table

 /*****************************************************************/ /*                                                               */ /* create_sales_order.sql                                        */ /*                                                               */ /*****************************************************************/ USE source; CREATE TABLE sales_order ( order_number INT , customer_number INT , product_code INT , order_date DATE , entry_date DATE , order_amount DECIMAL (10, 2)) ; /* end of script                                                 */

Listing 5.3: Data for testing Push mode

 /*****************************************************************/ /*                                                               */ /* push_data.sql                                                 */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO order_dim VALUES   (NULL, 17, CURRENT_DATE, '9999-12-31') , (NULL, 18, CURRENT_DATE, '9999-12-31') , (NULL, 19, CURRENT_DATE, '9999-12-31') , (NULL, 20, CURRENT_DATE, '9999-12-31') ; INSERT INTO date_dim VALUES   (NULL, '2007-02-06', 'February', 2, 6, 2007, CURRENT_DATE, '9999        12-31') ; /* load sales orders in the source database                      */ USE source; INSERT INTO sales_order VALUES   (17, 1, 1, '2007-02-06', '2007-02-06', 1000) , (18, 2, 1, '2007-02-06', '2007-02-06', 1000) , (19, 3, 1, '2007-02-06', '2007-02-06', 4000) , (20, 4, 1, '2007-02-06', '2007-02-06', 4000) ; /* end of script                                                 */

Before you start, you must set your MySQL date to February 7, 2007 (the entry date of the sales order test data) and point to the source database (the database where you have the stored procedure).

To use the source database, type in the following and press Enter.

 mysql> use source;

MySQL will respond by printing

 Database changed

Next, run the push_sales_order stored procedure to populate the sales order fact using this command.

 mysql> call push_sales_order();

The response should indicate that four rows were affected.

 Query OK, 4 rows affected (0.09 sec)

Finally, confirm that only the current sales orders are loaded into the sales_order_fact table by querying the table. Recall that you loaded sixteen sales orders in the previous chapters, the last four of which was on February 6, 2007.

Now change the database to dw.

 mysql> use dw;

Then, query the sales_order_fact table by issuing this command.

 mysql> select * from sales_order_fact;

The result should be the same as the following.

 +----------+-------------+------------+-------------+--------------+ | order_sk | customer_sk | product_sk |order_date_sk| order_amount | +----------+-------------+------------+-------------+--------------+ |        1 |           1 |          2 |           1 |      1000.00 | |        2 |           2 |          3 |           1 |      1000.00 | |        3 |           3 |          4 |           1 |      4000.00 | |        4 |           4 |          2 |           1 |      4000.00 | |        5 |           5 |          3 |           1 |      6000.00 | |        6 |           1 |          4 |           1 |      6000.00 | |        7 |           2 |          2 |           1 |      8000.00 | |        8 |           3 |          3 |           1 |      8000.00 | |        9 |           4 |          4 |           1 |     10000.00 | |       10 |           5 |          2 |           1 |     10000.00 | |       11 |           1 |          2 |           2 |     20000.00 | |       12 |           2 |          3 |           2 |     25000.00 | |       13 |           3 |          4 |           2 |     30000.00 | |       14 |           4 |          2 |           2 |     35000.00 | |       15 |           5 |          3 |           2 |     40000.00 | |       16 |           1 |          4 |           2 |     45000.00 | |       17 |           1 |          3 |           3 |      1000.00 | |       18 |           2 |          3 |           3 |      1000.00 | |       19 |           3 |          3 |           3 |      4000.00 | |       20 |           4 |          3 |           3 |      4000.00 | +----------+-------------+------------+-------------+--------------+ 20 rows in set (0.00 sec)

Note that the last four records are new data.