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.

Figure 14.1. The schema for the sales order monthly snapshot

[View full size image]

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

Listing 14.1. Creating monthly_sales_order_fact

/******************************************************************/
/*                                                                */
/* 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                                                  */

Now run the script in Listing 14.1 using this command.

mysql> . c:mysqlscriptscreate_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

/******************************************************************/
/*                                                                */
/* 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                                                  */

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:mysqlscriptsmonth_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)

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