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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

Listing 5.3: Data for testing Push mode

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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.



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