mysql多维数据仓库指南--第三篇第10章(2)

测试

       在你可以运行列表10-3所示的脚本之前，还有几个事情需要处理。

       第一，准备如下所示的客户数据，并保存为名为customer.csv的文件。相对于之前客户数据所发生的变化是：

n        现有全部8个客户的海运地址信息是有效的。

n        客户编码为9的记录是新的客户资料，也包含自己的海运地址。

CUSTOMER NO, CUSTOMER NAME, STREET ADDRESS, ZIP CODE, CITY, STATE,

SHIPPING ADDRESS, ZIP CODE, CITY, STATE

1, Really Large Customers, 7500 Louise Dr., 17050, Mechanicsburg, PA, 7500

        Louise Dr., 17050, Mechanicsburg, PA

2, Small Stores, 2500 Woodland St., 17055, Pittsburgh, PA, 2500 Woodland

        St., 17055, Pittsburgh, PA

3, Medium Retailers, 1111 Ritter Rd., 17055, Pittsburgh, PA, 1111 Ritter

        Rd., 17055, Pittsburgh, PA

4, Good Companies, 9500 Scott St., 17050, Mechanicsburg, PA, 9500 Scott

        St., 17050, Mechanicsburg, PA

5, Wonderful Shops, 3333 Rossmoyne Rd., 17050, Mechanicsburg, PA, 3333

        Rossmoyne Rd., 17050, Mechanicsburg, PA

6, Extremely Loyal Clients, 7777 Ritter Rd., 17055, Pittsburgh, PA, 7777

        Ritter Rd., 17055, Pittsburgh, PA

7, Distinguished Agencies, 9999 Scott St., 17050, Mechanicsburg, PA, 9999

        Scott St., 17050, Mechanicsburg, PA

8, Subsidiaries, 10000 Wetline Blvd., 17055, Pittsburgh, PA, 10000 Wetline

        Blvd., 17055, Pittsburgh, PA

9, Online Distributors, 2323 Louise Dr., 17055, Pittsburgh, PA, 2323

        Louise Dr., 17055, Pittsburgh, PA

       注:你仍然需要前面测试中用到的product.txt文件,尽管这里它没有什么变化。因为定期装载任务运行时，需要访问所有的源数据,这些源数据就包括product.txt文件。

       第二件你必须作的事情是应用列表10-4中所示的sales_order_quantity_data.sql脚本向source数据库中的sales_order表新增order_quantity字段。这个新增的字段中的数据就是sales_order_fac事实表中新增的order_quantity字段的源数据。

列表10-4：向sales_order表添加order_quantity字段

/*****************************************************************/

/*                                                              */

/* add_sales_order_quantity.sql                                 */

/*                                                              */

/*****************************************************************/

 

USE source;

 

ALTER TABLE sales_order

  ADD order_quantity INT AFTER order_amount

 

;

 

/* end of script                                                */

用如下命令运行该脚本：

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

你将在你的控制台上看到：

Database changed

Query OK, 37 rows affected (0.39 sec)

Records: 37  Duplicates: 0  Warnings: 0

       现在销售订单源有order_quantity字段，你可以添加销售订单测试数据了。列表10-5中的脚本添加9个销售订单记录到sales_order表中。注意到这些销售订单记录有订单数量，并且他们的订单日期是2007-03-02。

列表10-5：新增带有订单数量信息的9条销售订单记录

/*****************************************************************/

/*                                                              */

/* sales_order_quantity_data.sql                                */

/*                                                              */

/*****************************************************************/

 

USE source;

 

INSERT INTO sales_order VALUES

  (38, 1, 1, '2007-03-02', '2007-03-02', 1000, 10)

, (39, 2, 2, '2007-03-02', '2007-03-02', 2000, 20)

, (40, 3, 3, '2007-03-02', '2007-03-02', 4000, 40)

, (41, 4, 4, '2007-03-02', '2007-03-02', 6000, 60)

, (42, 5, 1, '2007-03-02', '2007-03-02', 2500, 25)

, (43, 6, 2, '2007-03-02', '2007-03-02', 5000, 50)

, (44, 7, 3, '2007-03-02', '2007-03-02', 7500, 75)

, (45, 8, 4, '2007-03-02', '2007-03-02', 1000, 10)

, (46, 9, 1, '2007-03-02', '2007-03-02', 1000, 10)

;

 

/* end of script                                                */

       接下来，你需要设置你的mysql日期为测试数据的订单日期，也就是2007-03-02。

       现在，你已经准备好可以运行列表10-3中的dw_regular_10.sql脚本。你可以调用一下命令：

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

你将看到：

Database changed

Query OK, 8 rows affected (0.05 sec)

 

Query OK, 9 rows affected (0.06 sec)

Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

 

Query OK, 8 rows affected (0.06 sec)

Rows matched: 8  Changed: 8  Warnings: 0

 

Query OK, 8 rows affected (0.05 sec)

Records: 8  Duplicates: 0  Warnings: 0

 

Query OK, 0 rows affected (0.01 sec)

Rows matched: 0  Changed: 0  Warnings: 0

 

Query OK, 1 row affected (0.07 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

Query OK, 4 rows affected (0.05 sec)

 

Query OK, 4 rows affected (0.06 sec)

Records: 4  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, 9 rows affected (0.07 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

Query OK, 9 rows affected (0.10 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

       你可以用sql语句查询客户维表来确定这个修正后的定期装载脚本已经成功执行。

mysql> select customer_number no, customer_name name,

    -> shipping_city, shipping_zip_code zip, shipping_state st,

    -> effective_date eff, expiry_date exp

-> from customer_dim \G

修正后的定期装载脚本成功运行后将会给你如下的结果：

******************************1.row************************

           no: 1

         name: Really Large Customers

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

******************************2. row *************************

           no: 2

         name: Small Stores

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

******************************3. row *************************

           no: 3

         name: Medium Retailers

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

******************************4. row **************************

           no: 4

         name: Good Companies

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

******************************5. row ***************************

           no: 5

         name: Wonderful Shops

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

******************************6. row ***************************

           no: 6

         name: Extremely Loyal Clients

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-02-28

*************************** 7. row ***************************

           no: 7

         name: Distinguished Agencies

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2005-03-01

          exp: 2007-03-01

*************************** 8. row ***************************

           no: 6

         name: Extremely Loyal Clients

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2007-03-01

          exp: 2007-03-01

*************************** 9. row ***************************

           no: 8

         name: Subsidiaries

shipping_city: NULL

          zip: NULL

           st: NULL

          eff: 2007-03-01

          exp: 2007-03-01

*************************** 10. row ***************************

           no: 1

         name: Really Large Customers

shipping_city: Mechanicsburg

          zip: 17050

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 11. row ***************************

           no: 2

         name: Small Stores

shipping_city: Pittsburgh

          zip: 17055

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 12. row ***************************

           no: 3

         name: Medium Retailers

shipping_city: Pittsburgh

          zip: 17055

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 13. row ***************************

           no: 4

         name: Good Companies

shipping_city: Mechanicsburg

          zip: 17050

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 14. 4 row ***************************

           no: 5

         name: Wonderful Shops

shipping_city: Mechanicsburg

          zip: 17050

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 15. 5 row ***************************

           no: 6

         name: Extremely Loyal Clients

shipping_city: Pittsburgh

          zip: 17055

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 16. row ***************************

           no: 7

         name: Distinguished Agencies

shipping_city: Mechanicsburg

          zip: 17050

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 17. row ***************************

           no: 8

         name: Subsidiaries

shipping_city: Pittsburgh

          zip: 17055

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

*************************** 18. row ***************************

           no: 9

         name: Online Distributors

shipping_city: Pittsburgh

          zip: 17055

           st: PA

          eff: 2007-03-02

          exp: 9999-12-31

18 rows in set (0.00 sec)

       注 现有全部的客户记录都有海运地址，而那些老的（过期）的记录则没有。客户编号9的记录已经被添加，并且有一个海运地址信息。

       为了确保销售数据被装载成功，查询用sql语句查询sales_order_fact表：

mysql> select order_sk o_sk, customer_sk c_sk, product_sk p_sk,

       order_date_sk od_sk,

    -> order_amount amt, order_quantity qty

    -> from sales_order_fact;

这里是事实表的内容。

+------+------+------+-------+---------+------+

| o_sk | c_sk | p_sk | od_sk | amt     | qty  |

+------+------+------+-------+---------+------+

|    1 |    3 |    3 |     1 | 4000.00 | NULL |

|    2 |    4 |    1 |    46 | 4000.00 | NULL |

|    3 |    5 |    2 |    81 | 6000.00 | NULL |

|    4 |    6 |    3 |   152 |  6000.00| NULL |

|    5 |    7 |    1 |   185 | 8000.00 | NULL |

|    6 |    1 |    2 |   255 | 8000.00 | NULL |

|    7 |    2 |    3 |   311 | 1000.00 | NULL |

|    8 |    3 |    1 |   347 | 1000.00 | NULL |

|    9 |    4 |    2 |   380 | 2000.00 | NULL |

|   10 |    5 |    3 |   416 | 2500.00 | NULL |

|   11 |    6 |    1 |   456 | 3000.00 | NULL |

|   12 |    7 |    2 |   458 | 3500.00 | NULL |

|   13 |    1 |    3 |   502 | 4000.00 | NULL |

|   14 |    2 |    1 |   548 | 4500.00 | NULL |

|   15 |    3 |    2 |   554 | 1000.00 | NULL |

|   16 |    4 |    3 |   584 | 1000.00 | NULL |

|   17 |    5 |    1 |   681 | 4000.00 | NULL |

|   18 |    6 |    2 |   722 | 4000.00 | NULL |

|   19 |    7 |    3 |   730 | 4000.00 | NULL |

|   20 |    1 |    1 |   731 | 1000.00 | NULL |

|   21 |    2 |    2 |   731 | 2000.00 | NULL |

|   22 |    3 |    4 |   731 | 3000.00 | NULL |

|   23 |    4 |    5 |   731 | 4000.00 | NULL |

|   24 |    5 |    2 |   731 | 1000.00 | NULL |

|   25 |    8 |    2 |   731 | 3000.00 | NULL |

|   26 |    7 |    4 |   731 | 5000.00 | NULL |

|   27 |    9 |    5 |   731 | 7000.00 | NULL |

|   28 |    1 |    1 |   731 | 1000.00 | NULL |

|   29 |    2 |    2 |   731 | 2000.00 | NULL |

|   30 |    3 |    4 |   731 | 4000.00 | NULL |

|   31 |    4 |    5 |   731 | 6000.00 | NULL |

|   32 |    5 |    1 |   731 | 2500.00 | NULL |

|   33 |    8 |    2 |   731 | 5000.00 | NULL |

|   34 |    7 |    4 |   731 | 7500.00 | NULL |

|   35 |    9 |    5 |   731 | 1000.00 | NULL |

|   36 |   10 |    1 |   732 | 1000.00 |   10 |

|   37 |   11 |    2 |   732 | 2000.00 |   20 |

|   38 |   12 |    4 |   732 | 4000.00 |   40 |

|   39 |   13 |    5 |   732 | 6000.00 |   60 |

|   40 |   14 |    1 |   732 | 2500.00 |   25 |

|   41 |   15 |    2 |   732 | 5000.00 |   50 |

|   42 |   16 |    4 |   732 | 7500.00 |   75 |

|   43 |   17 |    5 |   732 | 1000.00 |   10 |

|   44 |   18 |    1 |   732 | 1000.00 |   10 |

+------+------+------+-------+---------+------+

44 rows in set (0.00 sec)

注 只有9条新的记录有订单数量，老的记录则没有。

 

小结

       本章你学习了通过向维度表和事实表增加新的字段来扩展数据仓库。下一章，你将考察另外一种类型的装载，按需装载，这是为了适应数据仓库发展需要你可能需要应用的。