Tasks

There are four tasks you perform in this chapter:

1. Creating a database user

2. Creating the data warehouse database and the source database.

3. Creating data warehouse tables.

4. Generating surrogate keys.

Each task is explained in a separate subsection.

Creating a Database User Id

The first step is to create a database user that you'll use to access the data warehouse and the data source. Before you start, make sure you store all scripts accompanying this book in the scripts directory of your MySQL installation. For example, my installation directory is C:\mysql, so I stored all my scripts in the C:\mysql\scripts directory.

Let’s start by logging on to MySQL as root by typing the following command.

 C:\>mysql -uroot -p

You’ll be prompted to enter the password.

 Enter password: ********

If you typed in the correct password, you'll see a greeting message similar to this on the console.

 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 5.0.21-community-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

The lines after the password prompt are a typical response from MySQL when a root user logs in to the MySQL monitor.

The create_user_id.sql script in Listing 1.1 creates a user id dwid with password pw.

Listing 1.1: Creating dwid user id

 /*****************************************************************/                                                             */ /* create_user_id.sql                                            */ /*                                                               */ /*****************************************************************/ GRANT ALL ON *.* TO dwid@localhost IDENTIFIED BY 'pw' ; /* end of script                                                 */

Run the script by typing the following command.

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

You will see this response after you press Enter.

 Query OK, 0 rows affected (0.03 sec)

You can confirm that the user dwid has been successfully created using the show grants command. You might need to include your MySQL host name.

 mysql> show grants for dwid@localhost;

If the user dwid was created, you will see

 Grants for dwid@localhost GRANT ALL PRIVILEGES ON *.* TO 'dwid'@'localhost' IDENTIFIED BY       PASSWORD '*D821809F681A40A6E379B50D0463EFAE20BDD122' 1 row in set (0.00 sec)

Note 

The PASSWORD portion displayed on your monitor may be different from that shown above.

You now need to log off and log back in as dwid. To log off, type the exit command:

 mysql> exit

To log on as dwid, use this command:

 c:\>mysql -udwid -p

Then, enter the password for dwid. Remember the password is pw.

Creating the Databases

There are two databases you need to create, source and dw. The source database stores your data, namely the data that will be the source of your data warehouse. The dw database is for the data warehouse.

You create the databases using the create_databases.sql script in Listing 1.2.

Listing 1.2: Creating dw and source databases

 /*****************************************************************/ /*                                                               */ /* create_databases.sql                                          */ /*                                                               */ /*****************************************************************/ CREATE DATABASE dw ; CREATE DATABASE source ;

Here is the command to run the create_databases.sql script.

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

On your console, you’ll see

 Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec)

You can confirm that the command was executed successfully by using the show databases command. To confirm that the dw database has been successfully created, use this command.

 mysql> show databases like 'dw';

The response should look something like the following.

 +---------------+ | Database (dw) | +---------------+ | dw            | +---------------+ 1 row in set (0.00 sec)

To confirm that the source database has been successfully created, use this command.

 mysql> show databases like 'source';

The response should be

 +-------------------+ | Database (source) | +-------------------+ | source            | +-------------------+ 1 row in set (0.00 sec)

Creating Data Warehouse Tables

The next step is to create data warehouse tables in the dw database. You can use the create_dw_tables.sql script in Listing 1.3 to create the sales_order_fact table and the four dimension tables shown in Figure 1.1.

Listing 1.3: Creating data warehouse tables

 /*****************************************************************/ /*                                                               */ /* create_dw_tables.sql                                          */ /*                                                               */ /*****************************************************************/ /* default to dw database                                        */ USE dw; /* creating customer_dim table                                   */ CREATE TABLE 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) , effective_date DATE , expiry_date DATE ) ; /* creating product_dim table                                    */ CREATE TABLE product_dim ( product_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY , product_code INT , product_name CHAR (30) , product_category CHAR (30) , effective_date DATE , expiry_date DATE ) ; /* creating order_dim table                                      */ CREATE TABLE order_dim ( order_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY , order_number INT , effective_date DATE , expiry_date DATE ) ; /* creating date_dim table                                       */ CREATE TABLE date_dim ( date_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY , date DATE , month_name CHAR (9) , month INT (1) , quarter INT (1) , year INT (4) , effective_date DATE , expiry_date DATE ) ; /* creating sales_order_fact_table                               */ CREATE TABLE sales_order_fact ( order_sk INT , customer_sk INT , product_sk INT , order_date_sk INT , order_amount DECIMAL (10, 2) ) ;

Now run the create_dw_tables.sql script.

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

You’ll see something similar to this on your console:

 Database changed Query OK, 0 rows affected (0.13 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.11 sec)

You can confirm a table has been created correctly using the show create table command. For example, to verify that the customer_dim table has been created successfully, use this command.

 mysql> show create table customer_dim \G

On you console, you’ll see

 *************************** 1. row ***************************        Table: customer_dim Create Table: CREATE TABLE 'customer_dim' (   'customer_sk' int(11) NOT NULL auto_increment,   'customer_number' int(11) default NULL,   'customer_name' char (50) default NULL,   'customer_street_address' char (50) default NULL,   'customer_zip_code' int (5) default NULL,   'customer_city' char (30) default NULL,   'customer_state' char (2) default NULL,   'effective_date' date default NULL,   'expiry_date' date default NULL,   PRIMARY KEY ('customer_sk') ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

Using the same command, you can verify the other four tables were created successfully.

Generating Surrogate Keys

The last task in this chapter is to generate surrogate keys using the customer_sk.sql script in Listing 1.4. This script inserts three records into the customer_dim table.

Listing 1.4: Generating customer surrogate key values

 /******************************************************************/ /*                                                                */ /* customer_sk.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 , effective_date , expiry_date ) VALUES   (NULL, 1, 'Big Customers', '7500 Louise Dr.', '17050',        'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31') , (NULL, 2, 'Small Stores', '2500 Woodland St.', '17055',        'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31') , (NULL, 3, 'Medium Retailers', '1111 Ritter Rd.', '17055'        'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31') ; /* end of script                                            */

Before you run the customer_sk.sql script, however, you must set your MySQL date to February 1, 2007. This is because the CURRENT_DATE function used in the customer_sk.sql script uses the operating system date to populate the effective_date column and I ran the customer_sk.sql script on my computer on that date. You can change the MySQL date by setting the date of the computer running the MySQL to the specified date.

Note 

Bear in mind that you change the MySQL date so that you have a smooth learning experience. In production environment, you do not change your MySQL or system date before or after running a script. In fact, you schedule your scripts to run regularly in your data warehouse production environment. Regular scheduling is discussed in Chapter 8, “Regular Population.”

Note 

Your MySQL monitor might lose its connection to the server after the system date is modified. To ensure you don’t get any problem when running the script, run a simple command such as use dw; right after adjusting the date. You might still get an error message, but your monitor should be back. You can confirm that it’s connected to the server by running the use dw command once more. This time you should not get any more error message.

Now that you’ve changed your MySQL date to February 1, 2007, run the script in Listing 1.4 by using this command.

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

You’ll see this on your MySQL console.

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

Query the table and you will see that the script has inserted the surrogate key correctly.

 mysql> select * from customer_dim \G *************************** 1. row ***************************             customer_sk: 1         customer_number: 1           customer_name: Big Customers customer_street_address: 7500 Louise Dr.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 2. row ***************************             customer_sk: 2         customer_number: 2           customer_name: Small Stores customer_street_address: 2500 Woodland St.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 3. row ***************************             customer_sk: 3         customer_number: 3           customer_name: Medium Retailers customer_street_address: 1111 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 3 rows in set (0.00 sec) mysql>

Now add more rows by using the more_customer_sk.sql script in Listing 1.5.

Listing 1.5: Inserting more customers

 /*****************************************************************/ /*                                                               */ /* more_customer_sk.sql                                          */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO customer_dim (   customer_sk , customer_number , customer_name , customer_street_address , customer_zip_code , customer_city , customer_state , effective_date , expiry_date ) VALUES   (NULL, 4, 'Good Companies', '9500 Scott St.', '17050',        'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31') , (NULL, 5, 'Wonderful Shops', '3333 Rossmoyne Rd.', '17050',       'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31') , (NULL, 6, 'Loyal Clients', '7070 Ritter Rd.', '17055',        'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31') ; 

Here is how you run the more_customer_sk.sql script.

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

This is what you'll see on the console.

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

Now if you query the customer_dim table, you will see six records in it.

 mysql> select * from customer_dim \G *************************** 1. row ***************************             customer_sk: 1         customer_number: 1           customer_name: Big Customers customer_street_address: 7500 Louise Dr.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 2. row ***************************             customer_sk: 2         customer_number: 2           customer_name: Small Stores customer_street_address: 2500 Woodland St.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 3 row ***************************             customer_sk: 3         customer_number: 3           customer_name: Medium Retailers customer_street_address: 1111 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 4. row ***************************             customer_sk: 4         customer_number: 4           customer_name: Good Companies customer_street_address: 9500 Scott St.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 5. row ***************************             customer_sk: 5         customer_number: 5           customer_name: Wonderful Shops customer_street_address: 3333 Rossmoyne Rd.       customer_zip_code: 17050           customer_city: Mechanicsburg          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 *************************** 6. row ***************************             customer_sk: 6         customer_number: 6           customer_name: Loyal Clients customer_street_address: 7070 Ritter Rd.       customer_zip_code: 17055           customer_city: Pittsburgh          customer_state: PA          effective_date: 2007-02-01             expiry_date: 9999-12-31 6 rows in set (0.01 sec)

Do not delete these customers, as you will use them in the next chapters.