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.
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
/******************************************************************/ /* */ /* junk_dim.sql */ /* */ /******************************************************************/ USE dw; CREATE TABLE sales_order_attribute_dim ( sales_order_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 */ |
Run the script in Listing 18.1 using this command.
mysql> . c:mysqlscriptsjunk_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
/******************************************************************/ /* */ /* sales_order_attribute_sk.sql */ /* */ /******************************************************************/ USE dw; ALTER TABLE sales_order_fact ADD sales_order_attribute_sk INT AFTER product_sk ; /* end of script */ |
Run the script in Listing 18.2 using this command.
mysql> . c:mysqlscriptssales_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)