Pennsylvania Customer Dimension

In this section I use the Pennsylvania customer subset dimension to explain the second type of subset dimension. I also show you how to test the subset dimension.

While a roll-up dimension contains all higher level data of its base dimension, a specific subset dimension selects a specific set of its base dimension. The script in listing 12.3 creates the table and populates the Pennsylvania (PA) customer subset dimension.

Note that there are two things that differentiates the PA customer subset dimension from the month subset dimension:

• The pa_customer_dim table has exactly the same columns as the customer_dim. The month_dim does not have dates columns in the date_dim table.

• The pa_customer_dim table's surrogate keys are the surrogate keys of the customer dimension. The surrogate keys of the month dimension belong to the month_dim table and do not come from the date dimension.

Listing 12.3: PA customers

 /**********************************************************************/ /*                                                                  */ /* 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_dim SELECT   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 FROM customer_dim WHERE customer_state = 'PA' ; /* end of script                                                    */

To test the PA subset dimension script, you first need to add three customers who reside in Ohio using the script in Listing 12.4.

Listing 12.4: Non-PA customers

 /**********************************************************************/ /*                                                                  */ /* 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                                                    */

Run the script in Listing 12.4 using this command.

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

The response on your MySQL console should be

 Database changed Query OK, 3 rows affected (0.86 sec) Records: 3  Duplicates: 0  Warnings: 0

Now, you’re ready to run the pa_customer.sql script in Listing 12.3. Before you do that, make sure your MySQL date is still March 2, 2007.

You run the pa_customer.sql script by using this command.

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

Here is what you should see on your console.

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

To confirm the three OH customers are loaded correctly, query the customer_dim table:

 mysql> select customer_name, customer_state, effective_date     -> from customer_dim;

You should see the following on your console.

 +---------------------------------+----------------+---------------+ | 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)

Now, query the pa_customer_dim table to confirm only PA customers are in the PA customer dimension table.

 mysql> select customer_name, customer_state, effective_date     -> from pa_customer_dim;

The result should be as follows.

 +---------------------------------+----------------+---------------+ | 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) 

As you can see, only PA customers got into the table. The recently added OH customers are not there.