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.

image from book
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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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

image from book
 /*****************************************************************/ /*                                                               */ /* 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                                                 */
image from book

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)



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