thinkphp网站开发教程,网站建设业务客户来源,廉政网站建设,自己做的网站用在博客上宾夕法尼亚州地区客户维在本节我将用宾夕法尼亚州地区客户的子集维度来解释第二种维度子集的类型。我也将向你说明如何测试该子集维度。相对的#xff0c;一个向上钻取的维包含了它基础维的所有更高级别的数据。而一个特定子集维度则选择了它基础维的某个特定的数据集合。列表…宾夕法尼亚州地区客户维在本节我将用宾夕法尼亚州地区客户的子集维度来解释第二种维度子集的类型。我也将向你说明如何测试该子集维度。相对的一个向上钻取的维包含了它基础维的所有更高级别的数据。而一个特定子集维度则选择了它基础维的某个特定的数据集合。列表12-3所示的脚本产生并加载了宾夕法尼亚州(PA)地区客户子集维。注意到,有两个事情是宾夕法尼亚州地区客户子集维区别于月份子集维的地方。npa_customer_dim表和customer_dim表的字段结构一样而month_dim表没有 date_dim表中的日期字段。npa_customer_dim表的代理键是客户表的代理键。Month_dim表的代理键只属于month_dim表并不是来自日期维表。列表12-3 PA的客户:/**********************************************************************//**//* pa_customer.sql*//**//**********************************************************************/USE dw;CREATE TABLE pa_customer_dim( customer_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_number INT, customer_name CHAR (50), customer_street_address CHAR (50), customer_zip_code INT (5), customer_city CHAR (30), customer_state CHAR (2), shipping_address CHAR (50), shipping_zip_code INT (5), shipping_city CHAR (30), shipping_state CHAR (2), effective_date DATE, expiry_date DATE );INSERT INTO pa_customer_dimSELECTcustomer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state, effective_date, expiry_dateFROM customer_dimWHERE customer_state PA;/* end of script*/为了测试PA子维脚本你需要先用列表12-4的脚本来增加三个居住于俄亥俄州的客户。列表12-4非PA客户/**********************************************************************//**//* non_pa_customer.sql*//**//**********************************************************************//* default to dw*/USE dw;INSERT INTO customer_dim( customer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state, effective_date, expiry_date )VALUES(NULL, 10, Bigger Customers, 7777 Ridge Rd., 44102,Cleveland, OH, 7777 Ridge Rd., 44102, Cleveland,OH, CURRENT_DATE, 9999-12-31), (NULL, 11, Smaller Stores, 8888 Jennings Fwy., 44102,Cleveland, OH, 8888 Jennings Fwy., 44102,Cleveland, OH, CURRENT_DATE, 9999-12-31), (NULL, 12, Small-Medium Retailers, 9999 Memphis Ave., 44102,Cleveland, OH, 9999 Memphis Ave., 44102, Cleveland,OH, CURRENT_DATE, 9999-12-31);/* end of script*/用以下命令运行列表12-4中的脚本。mysql \. c:\mysql\scripts\non_pa_customer.sql在你的控制台上将得到下面的响应信息Database changedQuery OK, 3 rows affected (0.86 sec)Records: 3Duplicates: 0Warnings: 0现在你已经准备好运行列表12-3中的pa_customer.sql脚本在你做这些之前确定你的Msql数据库的日期仍然是2007-03-02。你可以用如下命令方式运行pa_customer.sql脚本。mysql \. c:\mysql\scripts\pa_customer.sql你将在控制台上看到Database changedQuery OK, 0 rows affected (0.20 sec)Query OK, 18 rows affected (0.08 sec)Records: 18 Duplicates: 0 Warnings: 0为了确保三个OH客户已经成功的载入查询customer_dim表mysql select customer_name, customer_state, effective_date from customer_dim;控制台上将看到:----------------------------------------------------------------| customer_name| customer_state | effective_date|----------------------------------------------------------------| Really Large Customers| PA| 2005-03-01|| Small Stores| PA| 2005-03-01|| Medium Retailers| PA| 2005-03-01|| Good Companies| PA| 2005-03-01|| Wonderful Shops| PA| 2005-03-01|| Extremely Loyal Clients| PA| 2005-03-01|| Distinguished Agencies| PA| 2005-03-01|| Extremely Loyal Clients| PA| 2007-03-01|| Subsidiaries| PA| 2007-03-01|| Really Large Customers| PA| 2007-03-02|| Small Stores| PA| 2007-03-02|| Medium Retailers| PA| 2007-03-02|| Good Companies| PA| 2007-03-02|| Wonderful Shops| PA| 2007-03-02|| Extremely Loyal Clients| PA| 2007-03-02|| Distinguished Agencies| PA| 2007-03-02|| Subsidiaries| PA| 2007-03-02|| Online Distributors| PA| 2007-03-02|| Bigger Customers| OH| 2007-03-02|| Smaller Stores| OH| 2007-03-02|| Small-Medium Retailers| OH| 2007-03-02|----------------------------------------------------------------21 rows in set (0.00 sec)现在查询pa_customer_dim表来确定只有PA的客户在PA客户维表中。mysql select customer_name, customer_state, effective_date from pa_customer_dim;结果如下:----------------------------------------------------------------| customer_name| customer_state | effective_date|----------------------------------------------------------------| Really Large Customers| PA| 2004-01-01|| Small Stores| PA| 2004-01-01|| Medium Retailers| PA| 2004-01-01|| Good Companies| PA| 2004-01-01|| Wonderful Shops| PA| 2004-01-01|| Extremely Loyal Clients| PA| 2004-01-01|| Distinguished Agencies| PA| 2004-01-01|| Extremely Loyal Clients| PA| 2005-11-01|| Subsidiaries| PA| 2005-11-01|| Really Large Customers| PA| 2005-11-03|| Small Stores| PA| 2005-11-03|| Medium Retailers| PA| 2005-11-03|| Good Companies| PA| 2005-11-03|| Wonderful Shops| PA| 2005-11-03|| Extremely Loyal Clients| PA| 2005-11-03|| Distinguished Agencies| PA | 2005-11-03|| Subsidiaries| PA| 2005-11-03|| Online Distributors| PA| 2005-11-03|----------------------------------------------------------------18 rows in set (0.00 sec)正如你所看到的只有PA客户进入到该表中之前加入的OH客户并没有在其中。修改定期装载当一个新的PA客户资料加入到客户维中时为了能够同时加入PA客户维表你需要把PA客户子集维的装载合并到数据仓库的定期装载过程中。修改后的定期装载脚本将在列表12-5中列出。这个改变(新增)用粗体显示。注意到当每次你运行每日的定期装载脚本时该脚本重建(截断然后加入所有的PA客户)了PA客户子集维。列表12-5修正后的每日DW定期装载/******************************************************************//**//* dw_regular_12.sql*//**//******************************************************************/USE dw;/* CUSTOMER_DIM POPULATION*/TRUNCATE customer_stg;LOAD DATA INFILE customer.csvINTO TABLE customer_stgFIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \r\nIGNORE 1 LINES( customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state );/* SCD 2 ON ADDRESSES*/UPDATEcustomer_dim a, customer_stg bSETa.expiry_date SUBDATE (CURRENT_DATE, 1)WHEREa.customer_number b.customer_numberAND (a.customer_street_address b.customer_street_addressOR a.customer_city b.customer_cityOR a.customer_zip_code b.customer_zip_codeOR a.customer_state b.customer_stateOR a.shipping_address b.shipping_addressOR a.shipping_city b.shipping_cityOR a.shipping_zip_code b.shipping_zip_codeOR a.shipping_state b.shipping_stateOR a.shipping_address IS NULLOR a.shipping_city IS NULLOR a.shipping_zip_code IS NULLOR a.shipping_state IS NULL)AND expiry_date 9999-12-31;INSERT INTO customer_dimSELECTNULL, b.customer_number, b.customer_name, b.customer_street_address, b.customer_zip_code, b.customer_city, b.customer_state, b.shipping_address, b.shipping_zip_code, b.shipping_city, b.shipping_state, CURRENT_DATE, 9999-12-31FROMcustomer_dim a, customer_stg bWHEREa.customer_number b.customer_numberAND (a.customer_street_address b.customer_street_addressOR a.customer_city b.customer_cityOR a.customer_zip_code b.customer_zip_codeOR a.customer_state b.customer_stateOR a.shipping_address b.shipping_addressOR a.shipping_city b.shipping_cityOR a.shipping_zip_code b.shipping_zip_codeOR a.shipping_state b.shipping_stateOR a.shipping_address IS NULLOR a.shipping_city IS NULLOR a.shipping_zip_code IS NULLOR a.shipping_state IS NULL)AND EXISTS (SELECT *FROM customer_dim xWHERE b.customer_number x.customer_numberAND a.expiry_date - SUBDATE (CURRENT_DATE, 1))AND NOT EXISTS (SELECT *FROM customer_dim yWHEREb.customer_number y.customer_numberAND y.expiry_date - 9999-12-31);/* END OF SCD 2*//* SCD 1 ON NAME*/UPDATE customer_dim a, customer_stg bSET a.customer_name b.customer_nameWHEREa.customer_number b.customer_numberAND a.expiry_date - 9999-12-31AND a.customer_name b.customer_name;/* ADD NEW CUSTOMER*/INSERT INTO customer_dimSELECTNULL, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state, CURRENT_DATE, 9999-12-31FROM customer_stgWHERE customer_number NOT IN(SELECT a.customer_numberFROMcustomer_dim a, customer_stg bWHERE b.customer_number a.customer_number )/* RE-BUILD PA CUSTOMER DIMENSION*/TRUNCATE pa_customer_dim;INSERT INTO pa_customer_dimSELECTcustomer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state, effective_date, expiry_dateFROM customer_dimWHERE customer_state PA;/* END OF CUSTOMER_DIM POPULATION*//* product dimension loading*/TRUNCATE product_stg;LOAD DATA INFILE product.txtINTO TABLE product_stgFIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LINES TERMINATED BY \r\nIGNORE 1 LINES( product_code, product_name, product_category );/* PRODUCT_DIM POPULATION*//* SCD2 ON PRODUCT NAME AND GROUP*/UPDATEproduct_dim a, product_stg bSETexpiry_date SUBDATE (CURRENT_DATE, 1)WHEREa.product_code b.product_codeAND (a.product_name b.product_nameOR a.product_category b.product_category )AND expiry_date 9999-12-31;INSERT INTO product_dimSELECTNULL, b.product_code, b.product_name, b.product_category, CURRENT_DATE, 9999-12-31FROMproduct_dim a, product_stg bWHEREa.product_code b.product_codeAND (a.product_name b.product_nameOR a.product_category b.product_category )AND EXISTS (SELECT *FROM product_dim xWHERE b.product_code x.product_codeAND a.expiry_date SUBDATE (CURRENT_DATE, 1) )AND NOT EXISTS (SELECT *FROM product_dim yWHERE b.product_code y.product_codeAND y.expiry_date 9999-12-31);/* END OF SCD 2*//* ADD NEW PRODUCT*/INSERT INTO product_dimSELECTNULL, product_code, product_name, product_category, CURRENT_DATE, 9999-12-31FROM product_stgWHERE product_code NOT IN(SELECT y.product_codeFROM product_dim x, product_stg yWHERE x.product_code y.product_code;/* END OF PRODUCT_DIM POPULATION*//* ORDER_DIM POPULATION*/INSERT INTO order_dim (order_sk, order_number, effective_date, expiry_date)SELECTNULL, order_number, order_date, 9999-12-31FROM source.sales_orderWHERE entry_date CURRENT_DATE;/* END OF ORDER_DIM POPULATION*//* SALES_ORDER_FACT POPULATION*/INSERT INTO sales_order_factSELECTorder_sk, customer_sk, product_sk, date_sk, order_amount, order_quantityFROMsource.sales_order a, order_dim b, customer_dim c, product_dim d, date_dim eWHEREa.order_number b.order_numberAND a.customer_number c.customer_numberAND a.order_date c.effective_dateAND a.order_date c.expiry_dateAND a.product_code d.product_codeAND a.order_date d.effective_dateAND a.order_date d.expiry_dateAND a.order_date e.dateAND a.entry_date CURRENT_DATE;/* end of script*/测试修正后的定期装载现在你可以测试列表12-5的脚本。在你实施之前先增加一些客户数据通过运行列表12-6的脚本增加一个PA客户和一个OH客户到客户维中。列表12-6增加两个客户/******************************************************************//**//* two_more_customers.sql*//**//******************************************************************//* default to dw*/USE dw;INSERT INTO customer_dim( customer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, shipping_address, shipping_zip_code, shipping_city, shipping_state, effective_date, expiry_date )VALUES(NULL, 13, PA Customer, 1111 Louise Dr., 17050,Mechanicsburg, PA, 1111 Louise Dr., 17050,Mechanicsburg, PA, CURRENT_DATE, 9999-12-31), (NULL, 14, OH Customer, 6666 Ridge Rd., 44102,Cleveland, OH, 6666 Ridge Rd., 44102,Cleveland, OH, CURRENT_DATE, 9999-12-31);/* end of script*/现在运行列表12-6中的脚本mysql \. c:\mysql\scripts\two_more_customers.sqlMysql将显示有两个记录生效。Database changedQuery OK, 2 rows affected (0.06 sec)Records: 2Duplicates: 0Warnings: 0现在更改你的Msql数据库的日期为2007-03-03以保证老的数据不会重新载入然后运行dw_regular_12.sql脚本。mysql \. c:\mysql\scripts\dw_regular_12.sql你将看到你的控制台有如下显示Database changedQuery OK, 9 rows affected (0.15 sec)Query OK, 9 rows affected (0.14 sec)Records: 9Deleted: 0Skipped: 0Warnings: 0Query OK, 0 rows affected (0.05 sec)Rows matched: 0Changed: 0Warnings: 0Query OK, 0 rows affected (0.01 sec)Records: 0Duplicates: 0Warnings: 0Query OK, 0 rows affected (0.00 sec)Rows matched: 0Changed: 0Warnings: 0Query OK, 0 rows affected (0.00 sec)Records: 0Duplicates: 0Warnings: 0Query OK, 18 rows affected (0.04 sec)Query OK, 19 rows affected (0.06 sec)Records: 19Duplicates: 0Warnings: 0Query OK, 4 rows affected (0.09 sec)Query OK, 4 rows affected (0.07 sec)Records: 4Deleted: 0Skipped: 0Warnings: 0Query OK, 0 rows affected (0.06 sec)Rows matched: 0Changed: 0Warnings: 0Query OK, 0 rows affected (0.00 sec)Records: 0Duplicates: 0Warnings: 0Query OK, 0 rows affected (0.00 sec)Records: 0Duplicates: 0Warnings: 0Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0Warnings: 0Query OK, 0 rows affected (0.17 sec)Records: 0Duplicates: 0Warnings: 0现在用该语句查询pa_customer_dim表。你将看到新的PA客户被插入到表中。mysql select customer_name, customer_state, effective_date from pa_customer_dim;这是结果--------------------------------------------------------| customer_name| customer_state | effective_date|--------------------------------------------------------| Really Large Customers| PA| 2005-03-01|| Small Stores| PA| 2005-03-01|| Medium Retailers| PA| 2005-03-01|| Good Companies| PA| 2005-03-01|| Wonderful Shops| PA| 2005-03-01|| Extremely Loyal Clients | PA| 2005-03-01|| Distinguished Agencies| PA| 2005-03-01|| Extremely Loyal Clients | PA| 2007-03-01|| Subsidiaries| PA| 2007-03-01|| Really Large Customers| PA| 2007-03-02|| Small Stores| PA| 2007-03-02|| Medium Retailers| PA| 2007-03-02|| Good Companies| PA| 2007-03-02|| Wonderful Shops| PA| 2007-03-02|| Extremely Loyal Clients | PA| 2007-03-02|| Distinguished Agencies| PA| 2007-03-02|| Subsidiaries| PA| 2007-03-02|| Online Distributors| PA| 2007-03-02|| PA Customer| PA| 2007-03-03|--------------------------------------------------------19 rows in set (0.00 sec)小结在这章你学习了两种类型的子集维。月份子集维是向上钻取维的一个例子一个装载自比它更为详细的基础维的具有更高级的维。PA客户维是一个特定的子集维只从它的基础维选择PA客户数据进行装载。下一章你将学习另外一个复用现有维的技术叫角色扮演维度。
