Testing the Initial Population Script
I know the month_end_balance_initial.sql script is a long one. However, you can run it by simply calling its file name.
mysql> . c:mysqlscriptsmonth_end_balance_initial.sql
To confirm that the initial population was successful, query the month_end_sales_order_fact and the month_end_balance_fact tables. Use this statement to query the first table.
mysql> select month_order_sk mosk, product_sk psk,
month_order_amount amt,
-> month_order_quantity qty from month_end_sales_order_fact
-> order by month_order_sk, product_sk;Here is the query result.
+------+------+----------+------+ | mosk | psk | amt | qty | +------+------+----------+------+ | 11 | 3 | 1000.00 | NULL | | 12 | 1 | 1000.00 | NULL | | 13 | 2 | 2000.00 | NULL | | 14 | 3 | 2500.00 | NULL | | 15 | 1 | 3000.00 | NULL | | 16 | 2 | 3500.00 | NULL | | 17 | 3 | 4000.00 | NULL | | 18 | 1 | 4500.00 | NULL | | 19 | 2 | 1000.00 | NULL | | 20 | 3 | 1000.00 | NULL | | 24 | 2 | 5000.00 | NULL | | 24 | 3 | 4000.00 | NULL | | 25 | 1 | 46500.00 | 420 | | 25 | 2 | 25000.00 | 120 | | 25 | 4 | 47000.00 | 275 | | 25 | 5 | 27000.00 | 90 | | 25 | 7 | 4000.00 | 40 | +------+------+----------+------+ 17 rows in set (0.00 sec)
To query the month_end_balance_fact table, enter the following on your console.
mysql> select month_sk msk, product_sk psk, month_end_amount_balance
amt,
-> month_end_quantity_balance qty from month_end_balance_fact
-> order by month_sk, product_sk;The query result is as follows.
+------+------+----------+------+ | msk | psk | amt | qty | +------+------+----------+------+ | 11 | 3 | 1000.00 | NULL | | 12 | 1 | 1000.00 | NULL | | 12 | 3 | 1000.00 | NULL | | 13 | 1 | 1000.00 | NULL | | 13 | 2 | 2000.00 | NULL | | 13 | 3 | 1000.00 | NULL | | 14 | 1 | 1000.00 | NULL | | 14 | 2 | 2000.00 | NULL | | 14 | 3 | 3500.00 | NULL | | 15 | 1 | 4000.00 | NULL | | 15 | 2 | 2000.00 | NULL | | 15 | 3 | 3500.00 | NULL | | 16 | 1 | 4000.00 | NULL | | 16 | 2 | 5500.00 | NULL | | 16 | 3 | 3500.00 | NULL | | 17 | 1 | 4000.00 | NULL | | 17 | 2 | 5500.00 | NULL | | 17 | 3 | 7500.00 | NULL | | 18 | 1 | 8500.00 | NULL | | 18 | 2 | 5500.00 | NULL | | 18 | 3 | 7500.00 | NULL | | 19 | 1 | 8500.00 | NULL | | 19 | 2 | 6500.00 | NULL | | 19 | 3 | 7500.00 | NULL | | 20 | 1 | 8500.00 | NULL | | 20 | 2 | 6500.00 | NULL | | 20 | 3 | 8500.00 | NULL | | 21 | 1 | 8500.00 | NULL | | 21 | 2 | 6500.00 | NULL | | 21 | 3 | 8500.00 | NULL | | 22 | 1 | 8500.00 | NULL | | 22 | 2 | 6500.00 | NULL | | 22 | 3 | 8500.00 | NULL | | 24 | 2 | 5000.00 | NULL | | 24 | 3 | 4000.00 | NULL | | 25 | 1 | 46500.00 | 420 | | 25 | 2 | 30000.00 | NULL | | 25 | 3 | 4000.00 | NULL | | 25 | 4 | 47000.00 | 275 | | 25 | 5 | 27000.00 | 90 | | 25 | 7 | 4000.00 | 40 | +------+------+----------+------+ 41 rows in set (0.00 sec)
Note
The month surrogate key 11 is January 2006 and the month surrogate key 25 is March 2007, meaning the month_end_balance_fact table has been correctly populated with all month end sales order facts from January 2006 to March 2007. The balances are all populated correctly as well: The amounts and quantities are accumulated, rolled onto the next months.