Testing | Dimensional Data Warehousing with MySQL: A Tutorial

This section explains the steps you need to do before running the revised scripts in Listings 22.2 and 22.3.

The first step is to load the entry dates for March sales orders by executing the following SQL statement. This SQL statement updates the sales orders’ entry_date_sk that have the same values as their order_date_sk. These March entry dates are later required to test the population of March month-end snapshot.

 UPDATE sales_order_fact SET entry_date_sk = order_date_sk WHERE order_date_sk BETWEEN 731 AND 754 ;

You can then query the month_end_sales_order_fact table before running the regular population script. Later, you use this ‘before’ data to compare with the ‘after’ date to confirm correct regular population.

Here is the statement you can use to query the month_end_sales_order_fact table.

 mysql> select year, month, product_name, month_order_amount amt,     -> month_order_quantity qty     -> from month_end_sales_order_fact a, month_dim b_,     -> product_dim c     -> where a.month_order_sk=b.month_sk     -> and a.product_sk=c.product_sk     -> order by year, month, product_name;

The query result is given below.

 +------+-------+-----------------+---------+------+ | year | month | product_name    | amt     | qty  | +------+-------+-----------------+---------+------+ | 2006 |     1 | LCD Panel       | 1000.00 | NULL | | 2006 |     2 | Hard Disk Drive | 1000.00 | NULL | | 2006 |     3 | Floppy Drive    | 2000.00 | NULL | | 2006 |     4 | LCD Panel       | 2500.00 | NULL | | 2006 |     5 | Hard Disk Drive | 3000.00 | NULL | | 2006 |     6 | Floppy Drive    | 3500.00 | NULL | | 2006 |     7 | LCD Panel       | 4000.00 | NULL | | 2006 |     8 | Hard Disk Drive | 4500.00 | NULL | | 2006 |     9 | Floppy Drive    | 1000.00 | NULL | | 2006 |    10 | LCD Panel       | 1000.00 | NULL | | 2007 |     2 | Floppy Drive    | 4000.00 | NULL | | 2007 |     2 | LCD Panel       | 4000.00 | NULL | +------+-------+-----------------+---------+------+ 12 rows in set (0.00 sec)

To compare the ‘before’ and ‘after’ dates, query the sales_order_fact table using this statement.

 mysql> SELECT product_name, SUM (order_amount)     -> FROM sales_order_fact a, product_dim b     -> WHERE order_date_sk > 730     -> AND a.product_sk=b.product_sk     -> GROUP BY a.product_sk     -> ORDER BY product_name;

The result should be similar to the following.

 +-----------------+-------------------+ | product_name    | SUM(order_amount) | +-----------------+-------------------+ | Flat Panel      |         47000.00  | | Floppy Drive    |         25000.00  | | Hard Disk Drive |         46500.00  | | Keyboard        |         27000.00  | +-----------------+-------------------+ 4 rows in set (0.00 sec)

The next step is to prepare sales orders test data by running the script in Listing 22.4. The scripts loads two sales orders into the sales order source data, one that is late and one that is not late.

Listing 22.4: Adding two sales orders

 /**********************************************************************/ /*                                                                    */ /* sales_order_22.sql                                                 */ /*                                                                    */ /**********************************************************************/ USE source; INSERT INTO sales_order VALUES /* late arrival                                                       */   (62, 6, 2, 'Y', 'Y', 'Y', 'N', '2007-02-25', 'N', '2007-03-30',        '2007-03-26', 1000, 10) /* normal                                                             */ , (63, 12, 5, 'Y', 'N', 'Y', 'N', '2007-03-26', 'N', '2007-03-30',        '2007-03-26', 2000, 20) ; /* end of script                                                       */

Run the above script by using this command.

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

You will see the following response on your MySQL console.

 Database changed Query OK, 2 rows affected (0.07 sec) Records: 2  Duplicates: 0  Warnings: 0

Before you run the new month end sales order population script, you must first load the two new sales orders into the sales_order_fact table. Set your MySQL date to March 26, 2005, which is the entry date the two sales orders in your test data, then run the dw_regular_22.sql script using this command.

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

You should see this on your console.

 Database changed Query OK, 9 rows affected (0.08 sec) Query OK, 9 rows affected (0.07 sec) Records: 9  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 19 rows affected (0.09 sec) Query OK, 19 rows affected (0.06 sec) Records: 19  Duplicates: 0  Warnings: 0 Query OK, 5 rows affected (0.06 sec) Query OK, 5 rows affected (0.05 sec) Records: 5  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 Query OK, 3 rows affected (0.02 sec) Records: 3  Duplicates: 0  Warnings: 0 Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0 Query OK, 2 rows affected (0.09 sec) Records: 2  Duplicates: 0  Warnings: 0 Query OK, 0 rows affected (0.01 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.01 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.01 sec) Rows matched: 0  Changed: 0  Warnings: 0 Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0

You're now ready to run the revised month end snapshot population. Set your MySQL date to March 31, 2007 (end of the March month) and run the month_end_sales_order_22.sql script to populate the March 2007 snapshot:

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

Here is what you should see on your console.

 Database changed Query OK, 1 row affected (0.09 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 1 row affected (0.07 sec) Rows matched: 1  Changed: 1  Warnings: 0 Query OK, 0 rows affected (3.48 sec) Records: 0  Duplicates: 0  Warnings: 0

Finally, run the same query to get the 'after' data of the month end sales orders.

 mysql> select year, month, product_name, month_order_amount amt,     -> month_order_quantity qty     -> from month_end_sales_order_fact a, month_dim b,     -> product_dim c     -> where a.month_order_sk = b.month_sk     -> and a.product_sk = c.product_sk     -> order by year, month, product_name;

Here is the query result.

 +------+-------+--------------------------+----------+------+ | year | month | product_name             | amt      | qty  | +------+-------+--------------------------+----------+------+ | 2006 |     1 | LCD Panel                |  1000.00 | NULL | | 2006 |     2 | Hard Disk Drive          |  1000.00 | NULL | | 2006 |     3 | Floppy Drive             |  2000.00 | NULL | | 2006 |     4 | LCD Panel                |  2500.00 | NULL | | 2006 |     5 | Hard Disk Drive          |  3000.00 | NULL | | 2006 |     6 | Floppy Drive             |  3500.00 | NULL | | 2006 |     7 | LCD Panel                |  4000.00 | NULL | | 2006 |     8 | Hard Disk Drive          |  4500.00 | NULL | | 2006 |     9 | Floppy Drive             |  1000.00 | NULL | | 2006 |    10 | LCD Panel                |  1000.00 | NULL | | 2007 |     2 | Floppy Drive             |  5000.00 | NULL | | 2007 |     2 | LCD Panel                |  4000.00 | NULL | | 2007 |     3 | Flat Panel               | 47000.00 |  275 | | 2007 |     3 | Floppy Drive             | 25000.00 |  120 | | 2007 |     3 | Hard Disk Drive          | 46500.00 |  420 | | 2007 |     3 | High End Hard Disk Drive |  4000.00 |   40 | | 2007 |     3 | Keyboard                 | 27000.00 |   90 | +------+-------+--------------------------+----------+------+ 17 rows in set (0.00 sec)

If you compare the results of the 'before' and 'after' queries, you can see that

The Floppy Drive's sales amount in February 2007 has been correctly increased from 4,000 to 5,000, thanks to the addition of the late arrival product sales order with the amount of 1,000 amount.
All March sales orders are summarized. These include the High End Hard Disk Drive sales orders that you just added when you ran the sales_order_22.sql script earlier in this chapter.