Annual Sales Order Star Schema
In this section I explain how to implement an annual order segment band dimension. To do this, you need two new stars as shown in Figure 25.1. The fact tables of the stars use (relate to) the existing customer_dim table and a new year_dim table. The year dimension is a subset dimension of the date dimension. The annual_customer_segment_fact table is the only table that uses the annual_order_segment_dim table. The annual_order_segement_dim is the band dimension.
Figure 25.1. The annual sales order schema with the annual_order_segment_dim band dimension
The annual_order_segment_dim table stores more than one set of bands. In the example below you populate the table with two sets of bands “PROJECT ALPHA” and “Grid.” Both sets are for segmenting customers based on their annual sales order amount.
PROJECT ALPHA has six bands and Grid has three bands. A sample of bands is shown in Table 25.1.
| Segment Name | Band Name | Start Value | End Value |
|---|---|---|---|
| PROJECT ALPHA | Bottom | 0.01 | 2500.00 |
| PROJECT ALPHA | Low | 2500.01 | 3000.00 |
| PROJECT ALPHA | Mid-low | 3000.01 | 4000.00 |
| PROJECT ALPHA | Mid | 4000.01 | 5500.00 |
| PROJECT ALPHA | Mid-high | 5500.01 | 6500.00 |
| PROJECT ALPHA | Top | 6500.01 | 99999999.99 |
| Grid | LOW | 0.01 | 3000.00 |
| Grid | MED | 3000.01 | 6000.00 |
| Grid | HIGH | 6000.01 | 99999999.99 |
Each band has a start value and an end value. The granularity of the band is the gap between the band to its next band. The granularity must be the smallest possible value of the measure, which in the case of the sales order amount is 0.01. The end value of the last band in a segment is the possible maximum value of the sales order amount.
The script in Listing 25.1 creates the annual_order_segment_dim band dimension and pre-populates the bands with the sample bands in Table 25.1.
Listing 25.1. Creating the band dimension
/******************************************************************/
/* */
/* band_dim.sql */
/* */
/******************************************************************/
USE dw;
CREATE TABLE annual_order_segment_dim
( segment_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, segment_name CHAR(30)
, band_name CHAR(50)
, band_start_amount DEC(10,2)
, band_end_amount DEC(10,2)
, effective_date DATE
, expiry_date DATE )
;
INSERT INTO annual_order_segment_dim VALUES
(NULL, 'PROJECT ALPHA', 'Bottom', 0.01, 2500.00, '0000-00-00',
'9999-12-31')
, (NULL, 'PROJECT ALPHA', 'Low', 2500.01, 3000.00, '0000-00-00',
'9999-12-31')
, (NULL, 'PROJECT ALPHA', 'Mid-Low', 3000.01, 4000.00, '0000-00-00',
'9999-12-31')
, (NULL, 'PROJECT ALPHA', 'Mid', 4000.01, 5500.00, '0000-00-00',
'9999-12-31')
, (NULL, 'PROJECT ALPHA', 'Mid_High', 5500.01, 6500.00, '0000-00-
00', '9999-12-31')
, (NULL, 'PROJECT ALPHA', 'Top', 6500.01, 99999999.99, '0000-00-00',
'9999-12-31')
, (NULL, 'Grid', 'LOW', 0.01, 3000, '0000-00-00', '9999-12-31')
, (NULL, 'Grid', 'MED', 3000.01, 6000.00, '0000-00-00', '9999-12-
31')
, (NULL, 'Grid', 'HIGH', 6000.01, 99999999.99, '0000-00-00', '9999-
12-31')
;
/* end of script */ |
Run the script in Listing 25.1:
mysql> . c:mysqlscriptsand_dim.sql
You will see this on the console.
Database changed Query OK, 0 rows affected (0.18 sec) Query OK, 9 rows affected (0.05 sec) Records: 9 Duplicates: 0 Warnings: 0
The script in Listing 25.2 creates the other new tables: year_dim, annual_sales_order_fact, and annual_customer_segment_fact.
Listing 25.2. Creating the annual tables
/******************************************************************/ /* */ /* annual_tables.sql */ /* */ /******************************************************************/ USE dw; CREATE TABLE year_dim ( year_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY , year INT(4) , effective_date DATE , expiry_date DATE ) ; CREATE TABLE annual_sales_order_fact ( customer_sk INT , year_sk INT , annual_order_amount DEC(10,2) ) ; CREATE TABLE annual_customer_segment_fact ( segment_sk INT , customer_sk INT , year_sk INT) ; /* end of script */ |
Run the script in Listing 25.2 using this command.
mysql> . c:mysqlscriptsannual_tables.sql
The following is the response on your console.
Database changed Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.11 sec)