Dimensional Data Warehousing with MySQL: A Tutorial
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_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 */
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
/*****************************************************************/ /* */ /* 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:\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)