Periodic Snapshots


This section shows how you can implement a periodic snapshot using the month end sales order summary as an example.

The first thing you need to do is add a new fact table. The schema in Figure 14.1 shows a new fact table called month_end_sales_order_fact. You need the new table because you need two new measures, month_order_amount and month_order_quantity, and you cannot add these measures to the sales_order_fact table. The reason you cannot do this is because the sales_order_fact table and the new measures have different time aspects. The sales_order_fact table includes a date in each record. The new measures require monthly data.

image from book
Figure 14.1: The schema for the sales order monthly snapshot

The script in Listing 14.1 is used to create the monthly__sales_order_fact table.

Listing 14.1: Creating monthly_sales_order_fact

image from book
 /*****************************************************************/ /*                                                               */ /* create_month_end_sales_order_fact.sql                         */ /*                                                               */ /*****************************************************************/  USE dw;  CREATE TABLE month_end_sales_order_fact ( month_order_sk INT , product_sk INT , month_order_amount DEC (10,2) , month_order_quantity INT ) ;  /* end of script                                                 */
image from book

Now run the script in Listing 14.1 using this command.

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

The response from MySQL should look like this.

 Database changed Query OK, 0 rows affected (0.17 sec)

Having created the month_end_sales_order_fact table, you now need to populate the table.

The source for the month end sales order fact is the existing sales order fact. The script in Listing 14.2 populates the month end sales order fact. You run it at every month end after the daily sales order population.

Listing 14.2: Populating month_end_sales_order_fact

image from book
 /*****************************************************************/ /*                                                               */ /* month_end_sales_order. sql                                    */ /*                                                               */ /*****************************************************************/ USE dw; INSERT INTO month_end_sales_order_fact SELECT   b.month_sk , a.product_sk , SUM (order_amount) , SUM (order_quantity) FROM   sales_order_fact a , month_dim b , order_date_dim d WHERE   a.order_date_sk = d.order_date_sk AND b.month = d.month ND b.year = d.year AND b.month = MONTH (CURRENT_DATE) AND b.year = YEAR (CURRENT_DATE) GROUP BY b.month, b.year, a.product_sk ; /* end of script                                                 */
image from book

Before you run the script in Listing 14.2, set your MySQL date to February 28, 2007 (the month end of February 2007). This step is required because the script may only be executed at month end.

Once you change your MySQL date, run the script using this command.

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

Once you press Enter, you should see this on your command prompt.

 Database changed Query OK, 2 rows affected (0.10 sec) Records: 2  Duplicates: 0  Warnings: 0

Now, you can query the month_end_sales_order_fact table using this command.

 mysql> select * from month_end_sales_order_fact \G

The output is as follows.

 *************************** 1. row ***************************       month_order_sk: 24           product_sk: 2   month_order_amount: 4000.00 month_order_quantity: NULL *************************** 2. row ***************************       month_order_sk: 24           product_sk: 3   month_order_amount: 4000.00 month_order_quantity: NULL 2 rows in set (0.00 sec) 

Note 

The month_sk 24 is February 2007.

The month_sales_order_fact table contains only two rows, both from sales orders placed in February 2007.



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