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:\mysql\scripts\month_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_prder_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.



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net