Chapter 25: Band Dimensions

This chapter, the last in this book, teaches you a technique for implementing band dimensions. A band dimension contains bands of continuous values. For example, an annual sales order band dimension might contain bands called “LOW”, “MED”, and “HIGH”; which are defined as 0.01 to 15000, 15000.01 to 30000.00, and 30000.01 to 99999999.99, respectively. For example, a sales order with an annual amount of 10000 falls into LOW.

A band dimension can store more than one set of bands. For example, you might have a set of bands for promotion analysis, another for market segmentation, and possibly yet another for sales territory planning. Your users define the bands; they are rarely available directly from the transaction source data.

In this chapter you also apply the multi-star development experience you learned previously to implement band dimensions.

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.

Table 25.1: Marketing segment bands
Open table as spreadsheet

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:\mysql\scripts\band_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:\mysql\scripts\annual_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)