Adding the Sales Order Attribute Junk Dimension


Let’s add a sales order junk dimension to our data warehouse. First off, you need to add a dimension named sales_order_attribute_dim. Figure 18.1 shows our data warehouse schema after the addition. Note that only tables related to the sales order attribute dim table are shown.

image from book
Figure 18.1: The schema with the sales_order_attribute_dim junk dimension

The new dimension contains four yes-no columns: verification_ind, credit_check_flag, new_customer_ind, and web_order_flag. Each of the four columns can have one of two possible values (Y or N), therefore the sales_order_attribute_dim can have a maximum of sixteen (2^4) rows. You can pre-populate the dimension and you need only do this once.

Note 

If you know that a certain combination is not possible, you do not need to load that combination.

The script in Listing 18.1 creates the sales_order_attribute_dim table and pre-populate the table with its all sixteen possible combinations.

Listing 18.1: Pre-populating the sales_order_attribute_dim table

image from book
 /*****************************************************************/ /*                                                               */ /* junk_dim.sql                                                  */ /*                                                               */ /*****************************************************************/ USE dw; CREATE TABLE sales_order_attribute_dim ( sales_prder_attribute_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY , verification_ind CHAR (1) , credit_check_flag CHAR (1) , new_customer_ind CHAR (1) , web_order_flag CHAR (1) , effective_date DATE , expiry_date DATE ) ; INSERT INTO sales_order_attribute_dim VALUES   (NULL, 'Y', 'N', 'N', 'N', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'Y', 'N', 'N', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'Y', 'Y', 'N', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'Y', 'Y', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'N', 'Y', 'N', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'N', 'Y', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'N', 'N', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'Y', 'Y', 'N', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'N', 'N', 'N', 'N', '0000-00-00', '9999-12-31') , (NULL, 'N', 'Y', 'N', 'N', '0000-00-00', '9999-12-31') , (NULL, 'N', 'Y', 'Y', 'N', '0000-00-00', '9999-12-31') , (NULL, 'N', 'Y', 'Y', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'N', 'N', 'Y', 'N', '0000-00-00', '9999-12-31') , (NULL, 'N', 'N', 'Y', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'N', 'N', 'N', 'Y', '0000-00-00', '9999-12-31') , (NULL, 'N', 'Y', 'N', 'Y', '0000-00-00', '9999-12-31') ; /* end of script                                                 */
image from book

Run the script in Listing 18.1 using this command.

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

This is how the response on your console should look like.

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

Query the sales_order_attribute_dim table to confirm correct population.

 mysql> select sales_order_attribute_sk soa_sk, verification_ind vi,     -> credit_check_flag ccf, new_customer_ind nci, web_order_flag        wof     -> from sales_order_attribute_dim;

The query result is presented below.

 +--------+----+-----+-----+-----+ | soa_sk | vi | ccf | nci | wof | +--------+----+-----+-----+-----+ |      1 | Y  | N   | N   | N   | |      2 | Y  | Y   | N   | N   | |      3 | Y  | Y   | Y   | N   | |      4 | Y  | Y   | Y   | Y   | |      5 | Y  | N   | Y   | N   | |      6 | Y    N   | Y   | Y   | |      7 | Y  | N   | N   | Y   | |      8 | Y  | Y   | N   | Y   | |      9 | N  | N   | N   | N   | |     10 | N  | Y   | N   | N   | |     11 | N  | Y   | Y   | N   | |     12 | N  | Y   | Y   | Y   | |     13 | N  | N   | Y   | N   | |     14 | N  | N   | Y   | Y   | |     15 | N  | N   | N   | Y   | |     16 | N  | Y   | N   | Y   |  +--------+----+-----+-----+-----+ 16 rows in set (0.00 sec)

The next step is to add a sales order attribute surrogate key using the script in Listing 18.2.

Listing 18.2: Adding sales_order_attribute_sk

image from book
 /*****************************************************************/ /*                                                               */ /* sales_order_attribute_sk.sql                                  */ /*                                                               */ /*****************************************************************/ USE dw; ALTER TABLE sales_order_fact ADD sales_order_attribute_sk INT AFTER product_sk ; /* end of script                                                 */ 
image from book

Run the script in Listing 18.2 using this command.

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

You should see the following on your console.

 Database changed Query OK, 51 rows affected (0.36 sec) Records: 51  Duplicates: 0  Warnings: 0

Confirm the sales_order_attribute_sk column was added to the sales_order_fact table by using this statement.

 mysql> desc sales_order_fact;

Here is the description of the table.

 +---------------------------+----------------+------+-----+---------+-----+ | Field                     | Type           | Null | Key | Default |Extra| +---------------------------+----------------+------+-----+---------+-----+ | customer_sk               |   int(11)      | YES  |     | NULL    |     | | product_sk                |  int(11)       | YES  |     | NULL    |     | | sales_order_attribute_sk  | int(11)        | YES  |     | NULL    |     | | order_date_sk             | int(11)        | YES  |     | NULL    |     | | allocate_date_sk          | int(11)        | YES  |     | NULL    |     | | packing_date_sk           | int(11)        | YES  |     | NULL    |     | | ship_date_sk              | int(11)        | YES  |     | NULL    |     | | receive_date_sk           | int(11)        | YES  |     | NULL    |     | | order_number              | int(11)        | YES  |     | NULL    |     | | request_delivery_date_sk  | int(11)        | YES  |     | NULL    |     | | order_amount              | decimal (10,2) | YES  |     | NULL    |     | | order_quantity            | int(11)        | YES  |     | NULL    |     | | allocate_quantity         | int(11)        | YES  |     | NULL    |     | | packing_quantity          | int(11)        | YES  |     | NULL    |     | | ship_quantity             | int(11)        | YES  |     | NULL    |     | | receive_quantity          | int(11)        | YES  |     | NULL    |     |  +---------------------------+----------------+------+-----+---------+-----+ 16 rows in set (0.00 sec)



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

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

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