Testing Pre-Population

In this section I show you how to verify that the pre-population date stored procedure generates the dates in the date_dim table correctly.

Before we start, clear the date_dim table using the following command.

 mysql> truncate date_dim;

MySQL will indicate that three rows were deleted.

 Query OK, 3 rows affected (0.59 sec)

Now, run the stored procedure in Listing 6.1 to pre-populate the date dimension for the period of January 1, 2007 to December 31, 2010. It will take a few minutes to insert the 1461 rows, which is the number of dates from January 1, 2007 to December 31, 2010.

 mysql> call pre_populate_date ('2007-01-01', '2010-12-31');

To confirm the dates are correctly populated into the date_dim table, query the number of rows in the date_dim.

 mysql> select count(0) from date_dim;

You should see 1461 as the result.

 +----------+ | count(0) | +----------+ |     1461 | +----------+ 1 row in set (0.06 sec)

If you query the first ten dates using this command

 mysql> select * from date_dim limit 10 \G

you’ll see these records.

 *************************** 1. row ***************************        date_sk: 1           date: 2007-01-01     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 2. row ***************************        date_sk: 2           date: 2007-01-02     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 3. row ***************************        date_sk: 3           date: 2007-01-03     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 4. row ***************************        date_sk: 4           date: 2007-01-04     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 5. row ***************************        date_sk: 5           date: 2007-01-05     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 6. row ***************************        date_sk: 6           date: 2007-01-06     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 7. row ***************************        date_sk: 7           date: 2007-01-07     month_name: January          month: 1        quarter: 1           year: 2007 effective date: 0000-00-00    expiry_date: 9999-12-31 *************************** 8. row ***************************        date_sk: 8           date: 2007-01-08     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 9. row ***************************        date_sk: 9           date: 2007-01-09     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 10. row **************************        date_sk: 10           date: 2007-01-10     month_name: January          month: 1        quarter: 1           year: 2007 effective_date: 0000-00-00    expiry_date: 9999-12-31 10 rows in set (0.00 sec)

If you query the last ten dates using this command

 mysql> select * from date_dim limit 1451, 1461 \G

you’ll see these records shown.

 *************************** 1. row ***************************        date_sk: 1452           date: 2010-12-22     month_name: December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 2. row ***************************        date_sk: 1453           date: 2010-12-23     month_name: December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 3. row ***************************        date_sk: 1454           date: 2010-12-24     month_name: December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 4. row ***************************        date_sk: 1455           date: 2010-12-25     month_name: December          month: 12        quarter: 4           year: 2010 effective date: 0000-00-00    expiry_date: 9999-12-31 *************************** 5. row **************************        date_sk: 1456           date: 2010-12-26     month_name: December          month: 12        quarter: 4           year: 2010 effective date: 0000-00-00    expiry_date: 9999-12-31 *************************** 6. row ***************************        date_sk: 1457           date: 2010-12-27     month_name: December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 7. row ***************************        date_sk: 1458           date: 2010-12-28    month_name : December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 8. row ***************************        date_sk: 1459           date: 2010-12-29     month_name: December          month: 12        quarter: 4           year: 2010 effective date: 0000-00-00    expiry_date: 9999-12-31 *************************** 9. row ***************************        date_sk: 1460           date: 2010-12-30     month_name: December          month: 12        quarter: 4           year: 2010 effective date: 0000-00-00    expiry_date: 9999-12-31 *************************** 10. row ***************************        date_sk: 1461           date: 2010-12-31     month_name: December          month: 12        quarter: 4           year: 2010 effective_date: 0000-00-00    expiry_date: 9999-12-31 10 rows in set (0.00 sec)