Testing Data


To test the regular population, you need to prepare the customer, product, and sales order test data. Each of the sources is discussed in the sections below.

The customer.csv File

The changes to the customer.csv file are as follows:

  • The street number of customer number 6 is now 7777 Ritter Rd. (It was 7000 Ritter Rd.)

  • The name of customer number 7 is now Distinguished Agencies. (It was Distinguished Partners).

  • Add a new customer as the eighth customer.

Here is the content of the customer.csv file.

 CUSTOMER NO, CUSTOMER NAME,STREET ADDRESS, ZIP CODE,CITY,STATE 1, Really Large Customers, 7500 Louise Dr., 17050, Mechanicsburg, PA 2, Small Stores, 2500 Woodland St., 17055, Pittsburgh, PA 3, Medium Retailers, 1111 Ritter Rd., 17055, Pittsburgh, PA 4, Good Companies, 9500 Scott St., 17050, Mechanicsburg, PA 5, Wonderful Shops, 3333 Rossmoyne Rd., 17050, Mechanicsburg, PA 6, Extremely Loyal Clients, 7777 Ritter Rd., 17055, Pittsburgh, PA 7, Distinguished Agencies, 9999 Scott St., 17050, Mechanicsburg, PA 8, Subsidiaries, 10000 Wetline Blvd., 17055, Pittsburgh, PA

The product.txt File

These are the changes to the product.txt file.

  • The name of Product 3 is now Flat Panel. (It was LCD Panel).

  • Add a new product as the fourth product.

Here is the modified product.txt file.

 PRODUCT CODE,PRODUCT NAME,PRODUCT GROUP 1          Hard Disk Drive                 Storage 2          Floppy Drive                    Storage 3          Flat Panel                      Monitor 4          Keyboard                        Peripheral

Sales Order Transactions

The last test data you need to prepare is the sales orders. Assuming you start the data warehouse operation on March 1, 2007 (the date the first time you run the regular population). The script in Listing 8.2 adds 16 sales orders with 2007–03–01 order dates.

Listing 8.2: Adding sales orders

image from book
 /*****************************************************************/ /*                                                               */ /* sales_order_regular.sql                                       */ /*                                                               */ /*****************************************************************/ USE source; INSERT INTO sales_order VALUES   (22, 1, 1, '2007–03–01', '2007–03–01', 1000) , (23, 2, 2, '2007–03–01', '2007–03–01', 2000) , (24, 3, 3, '2007–03–01', '2007–03–01', 3000) , (25, 4, 4, '2007–03–01', '2007–03–01', 4000) , (26, 5, 2, '2007–03–01', '2007–03–01', 1000) , (27, 6, 2, '2007–03–01', '2007–03–01', 3000) , (28, 7, 3, '2007–03–01', '2007–03–01', 5000) , (29, 8, 4, '2007–03–01', '2007–03–01', 7000) , (30, 1, 1, '2007–03–01', '2007–03–01', 1000) , (31, 2, 2, '2007–03–01', '2007–03–01', 2000) , (32, 3, 3, '2007–03–01', '2007–03–01', 4000) , (33, 4, 4, '2007–03–01', '2007–03–01', 6000) , (34, 5, 1, '2007–03–01', '2007–03–01', 2500) , (35, 6, 2, '2007–03–01', '2007–03–01', 5000) , (36, 7, 3, '2007–03–01', '2007–03–01', 7500) , (37, 8, 4, '2007–03–01', '2007–03–01', 1000) ; /* end of script                                                 */
image from book

Run the sales_order_regular.sql the script using this command.

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

The response on your console should be similar to this.

 Database changed Query OK, 16 rows affected (0.08 sec) Records: 16  Duplicates:  0  Warnings: 0

The sales_order table now has a total of 37 rows.

Running the Regular Population Script

Before you run the dw_regular.sql script in Listing 8.1, you need to set your MySQL date to March 1, 2007. Then, run the script by using this command.

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

You will see this on your console.

 Database changed Query OK, 7 rows affected (0.12 sec) Query OK, 8 rows affected (0.05 sec) Records: 8  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1  Warnings: 0 Query OK, 1 row affected (0.07 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 2 rows affected (0.06 sec) Rows matched: 2  Changed: 2  Warnings: 0 Query OK, 1 row affected (0.06 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 3 rows affected (0.05 sec) Query OK, 4 rows affected (0.08 sec) Records: 4  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0 Query OK, 1 row affected (0.06 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 1 row affected (0.06 sec) Records: 1  Duplicates: 0  Warnings: 0 Query OK, 16 rows affected (0.07 sec) Records: 16  Duplicates: 0  Warnings: 0 Query OK, 16 rows affected (0.11 sec) Records: 16  Duplicates: 0  Warnings: 0

Confirming Successful Regular Population

To confirm your regular population was successful, you need to query the dimension and the fact tables.

To query the customer_dim table, use this SQL statement.

 mysql> select * from customer_dim \G

The result is as follows.

 *************************** 1. row ***************             customer_sk: 1         customer_number: 1           customer_name: Really Large Customers customer_street_address: 7500 Louise Dr.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective date: 2005–03–01             expiry_date: 9999–12–31 *************************** 2. row **************             customer_sk: 2         customer_number: 2           customer_name: Small Stores customer_street_address: 2500 Woodland St.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 3. row ***************************             customer_sk: 3         customer_number: 3           customer_name: Medium Retailers customer_street_address: 1111 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer state: PA          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 4. row ***************************             customer_sk: 4         customer_number: 4           customer_name: Good Companies customer_street_address: 9500 Scott St.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 5. row ***************************             customer_sk: 5         customer_number: 5           customer_name: Wonderful Shops customer_street_address: 3333 Rossmoyne Rd.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective date: 2005–03–01             expiry_date: 9999–12–31 *************************** 6. row ***********************************             customer_sk: 6         customer_number: 6           customer_name: Extremely Loyal Clients customer_street_address: 7070 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2005–03–01             expiry_date: 2007–02–28 *************************** 7. row ***************************             customer_sk: 7         customer_number: 7           customer_name: Distinguished Agencies customer_street_address: 9999 Scott St.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2005–03–01             expiry_date: 9999–12–31 *************************** 8. row ***************************             customer_sk: 8         customer_number: 6           customer_name: Extremely Loyal Clients customer_street_address: 7777 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007–03–01             expiry_date: 9999–12–31 *************************** 9. row ***************************             customer_sk: 9         customer_number: 8           customer_name: Subsidiaries customer_street_address: 10000 Wetline Blvd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007–03–01             expiry_date: 9999–12–31 9 rows in set (0.00 sec)

The query result shows you that:

  • SCD2 was applied on the street address of Customer 6

  • SCD1 was applied to the name of both rows of Customer 6

  • SCD1 was applied to the name of Customer 7

  • A new customer 8 was added

To query the product_dim table, use this SQL statement.

 mysql> select * from product_dim \G

The result is as follows.

 *************************** 1. row ***************************        product_sk: 1     product_code: 1     product_name: Hard Disk Drive product_category: Storage   effective_date: 2005–03–01      expiry_date: 9999–12–31 *************************** 2. row ***************************        product_sk: 2     product_code: 2     product_name: Floppy Drive product_category: Storage   effective_date: 2005–03–01      expiry_date: 9999–12–31 *************************** 3. row ***************************       product_sk: 3     product_code: 3     product_name: LCD Panel product_category: Monitor   effective_date: 2005–03–01      expiry_date: 2007–02–28 *************************** 4. row ***************************       product_sk: 4     product_code: 3     product_name: Flat Panel product_category: Monitor   effective date: 2007–03–01      expiry_date: 9999–12–31 *************************** 5. row **************************       product_sk: 5     product_code: 4     product_name: Keyboard product_category: Peripheral   effective_date: 2007–03–01      expiry_date: 9999–12–31 5 rows in set (0.01 sec)

The result from querying the product_dim table shows you that

  • SCD2 was applied to the name of Product 3

  • The new product 4 was added

Now, query the order_dim table.

 mysql> select * from order_dim;

Here is the result. You should now have 35 orders, 19 of which were loaded in Chapter 7, “Initial Population” and 16 of which were loaded in this chapter.

 +----------+--------------+----------------+-------------+ | order_sk | order_number | effective_date | expiry_date | +----------+--------------+----------------+-------------+ |        1 |            3 | 2005–03–01     | 9999–12–31  | |        2 |            4 | 2005–04–15     | 9999–12–31  | |        3 |            5 | 2005–05–20     | 9999–12–31  | |        4 |            6 | 2005–07–30     | 9999–12–31  | |        5 |            7 | 2005–09–01     | 9999–12–31  | |        6 |            8 | 2005–11–10     | 9999–12–31  | |        7 |            9 | 2006–01–05     | 9999–12–31  | |        8 |           10 | 2006–02–10     | 9999–12–31  | |        9 |           11 | 2006–03–15     | 9999–12–31  | |       10 |           12 | 2006–04–20     | 9999–12–31  | |       11 |           13 | 2006–05–30     | 9999–12–31  | |       12 |           14 | 2006–06–01     | 9999–12–31  | |       13 |           15 | 2006–07–15     | 9999–12–31  | |       14 |           16 | 2006–08–30     | 9999–12–31  | |       15 |           17 | 2006–09–05     | 9999–12–31  | |       16 |           18 | 2006–10–05     | 9999–12–31  | |       17 |           19 | 2007–01–10     | 9999–12–31  | |       18 |           20 | 2007–02–20     | 9999–12–31  | |       19 |           21 | 2007–02–28     | 9999–12–31  | |       20 |           22 | 2007–03–01     | 9999–12–31  | |       21 |           23 | 2007–03–01     | 9999–12–31  | |       22 |           24 | 2007–03–01     | 9999–12–31  | |       23 |           25 | 2007–03–01     | 9999–12–31  | |       24 |           26 | 2007–03–01     | 9999–12–31  | |       25 |           27 | 2007–03–01     | 9999–12–31  | |       26 |           28 | 2007–03–01     | 9999–12–31  | |       27 |           29 | 2007–03–01     | 9999–12–31  | |       28 |           30 | 2007–03–01     | 9999–12–31  | |       29 |           31 | 2007–03–01     | 9999–12–31  | |       30 |           32 | 2007–03–01     | 9999–12–31  | |       31 |           33 | 2007–03–01     | 9999–12–31  | |       32 |           34 | 2007–03–01     | 9999–12–31  | |       33 |           35 | 2007–03–01     | 9999–12–31  | |       34 |           36 | 2007–03–01     | 9999–12–31  | |       35 |           37 | 2007–03–01     | 9999–12–31  | +----------+--------------+----------------+-------------+ 35 rows in set (0.00 sec)

The Sales Order Fact

You can now query the sales_order_fact table.

 mysql> select * from sales_order_fact;

Here is the output.

 +----------+-------------+------------+---------------+--------------+ | order_sk | customer_sk | product_sk | order_date_sk | order_amount | +----------+-------------+------------+---------------+--------------+ |       1  |           3 |          3 |             1 |      4000.00 | |       2  |           4 |          1 |            46 |      4000.00 | |       3  |           5 |          2 |            81 |      6000.00 | |       4  |           6 |          3 |           152 |      6000.00 | |       5  |           7 |          1 |           185 |      8000.00 | |       6  |           1 |          2 |           255 |      8000.00 | |       7  |           2 |          3 |           311 |      1000.00 | |       8  |           3 |          1 |           347 |      1000.00 | |       9  |           4 |          2 |           380 |      2000.00 | |      10  |           5 |          3 |           416 |      2500.00 | |      11  |           6 |          1 |           456 |      3000.00 | |      12  |           7 |          2 |           458 |      3500.00 | |      13  |           1 |          3 |           502 |      4000.00 | |      14  |           2 |          1 |           548 |      4500.00 | |      15  |           3 |          2 |           554 |      1000.00 | |      16  |           4 |          3 |           584 |      1000.00 | |      17  |           5 |          1 |           681 |      4000.00 | |      18  |           6 |          2 |           722 |      4000.00 | |      19  |           7 |          3 |           730 |      4000.00 | |      20  |           1 |          1 |           731 |      1000.00 | |      21  |           2 |          2 |           731 |      2000.00 | |      22  |           3 |          4 |           731 |      3000.00 | |      23  |           4 |          5 |           731 |      4000.00 | |      24  |           5 |          2 |           731 |      1000.00 | |      25  |           8 |          2 |           731 |      3000.00 | |      26  |           7 |          4 |           731 |      5000.00 | |      27  |           9 |          5 |           731 |      7000.00 | |      28  |           1 |          1 |           731 |      1000.00 | |      29  |           2 |          2 |           731 |      2000.00 | |      30  |           3 |          4 |           731 |      4000.00 | |      31  |           4 |          5 |           731 |      6000.00 | |      32  |           5 |          1 |           731 |      2500.00 | |      33  |           8 |          2 |           731 |      5000.00 | |      34  |           7 |          4 |           731 |      7500.00 | |      35  |           9 |          5 |           731 |      1000.00 | +----------+-------------+------------+---------------+--------------+ 35 rows ir n set (0.00 sec :)

The query result shows that:

  • The sixteen sales orders entered on March 1, 2007 were added

  • The valid product and customer were picked up correctly based on the order dates:

    • Surrogate key 4 for Product 3, not the one with surrogate key 3

    • Surrogate key 8 for Customer 6, not the one with surrogate key 6



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

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