Dimensional Data Warehousing with MySQL: A Tutorial - page 71

Summary

In this chapter you learned dimension role-playing for the date dimension and applied the two types of the technique: table alias and database view.

In the next chapter, you will learn to re-use facts.

Chapter 14: Snapshots

Overview

The first three chapters in Part III dealt with dimension extensions. This chapter, on the other hand, discusses two techniques for extending the fact.

Some users, especially managers, may require data for a particular time. In other words, they need snapshots of data. The two fact extension techniques that deals with snapshots are periodic and accumulating .

A periodic snapshot is a periodic summary of the fact at a certain time. For example, a monthly sales order periodic snapshot is the total sales order amount at the end of every month.

An accumulating snapshot tracks the changes of a fact. For example, the data warehouse may need to accumulate (store) the data of a sales order as it progresses through the order life cycle, starting from the time the order was placed to the time products are allocated to fulfill the order to packing, shipping, and receiving. The user can take a snapshot on the accumulated sales order progress status at certain times.

The following sections discuss periodic snapshots and accumulated snapshots in detail.

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

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

/*****************************************************************/ /* */ /* 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:\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.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

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