Dimension Model Data Warehouse (21)--Segmented dimension

(v) Advanced technology
16. Segmented dimension
This article describes the implementation technology of the piecewise dimension. A segmented dimension contains a fragment of a contiguous value. For example, the annual Sales order segmentation dimension may contain three files called "Low", "medium", "High", and each file definition is 0.01 to 15000, 15000.01 to 30000.00, 30000.01 to 99999999.99. If a customer's annual sales order amount is 10000, it is classified as low.
A segmented dimension can store multiple segmented collections. For example, there might be a segmented collection for promotional analysis, another for market segmentation, and perhaps a regional plan for sales. Segments are generally defined by the user and are rarely available directly from the transaction source data. This article will use (V) Advanced Technology 10. The development experience of multiple star pattern realizes segmented dimension.

Annual Sales order star mode
This section describes how to implement an annual order segment dimension. You need two new star patterns, as shown in Figure (v)-16-1. The fact table of the star pattern uses (correlates to) existing Customer_dim and a new Year_dim table. The year dimension is a subset of the date dimension. Annual_customer_segment_fact is the only table that uses the Annual_order_segment_dim table. Annual_order_segement_dim is a segmented dimension.
Figure (v)-16-1
The Annual_order_segment_dim table stores multiple segmented collections. In the following example, the two segmented sets of "PROJECT ALPHA" and "Grid" are imported into the Annual_order_segment_dim table. Both of these segmented collections are categorized according to the user's annual sales order amount. PROJECT Alpha is divided into six segments, and the grid is divided into three segments. Table (v)-16-1 shows an example of this fragment.

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

Table (v)-16-1
Each fragment has a start value and an ending value. The granularity of a fragment is the gap between this and the next paragraph. The granularity must be the minimum possible value for the metric, which is 0.01 in the example of the sales order amount. The end value of the last fragment is the possible maximum value for the sales order amount.
The script in Listing (v)-16-1 is used to establish a segmented dimension Data Warehouse pattern.

Use DW; CREATE TABLE Annual_order_segment_dim (segment_sk INT not NULL auto_increment PRIMARY KEY, Segment_name VARCHAR (3 0), Band_name VARCHAR (m), Band_start_amount Dec (2), Band_end_amount Dec (2), Effective_date DA

TE, expiry_date date); INSERT into Annual_order_segment_dim VALUES (NULL, ' PROJECT ALPHA ', ' Bottom ', 0.01, 2500.00, ' 1900-01-01 ', ' 2200- 01-01 '), (NULL, ' Project alpha ', ' low ', 2500.01, 3000.00, ' 1900-01-01 ', ' 2200-01-01 '), (NULL, ' Project Alpha ', ' M Id-low ', 3000.01, 4000.00, ' 1900-01-01 ', ' 2200-01-01 '), (NULL, ' PROJECT ALPHA ', ' Mid ', 4000.01, 5500.00, ' 1900-01- ', ' 2200-01-01 '), (NULL, ' PROJECT ALPHA ', ' Mid_high ', 5500.01, 6500.00, ' 1900-01-01 ', ' 2200-01-01 '), (N  ull, ' PROJECT ALPHA ', ' top ', 6500.01, 99999999.99, ' 1900-01-01 ', ' 2200-01-01 ', (NULL, ' Grid ', ' low ', 0.01, 3000, ' 1900-01-01 ', ' 2200-01-01 '), (null, ' Grid ', ' MED ', 3000.01, 6000.00, ' 1900-01-01 ', ' 2200-01-01 '), (NULL,' Grid ', ' High ', 6000.01, 99999999.99, ' 1900-01-01 ', ' 2200-01-01 ';

Commit
    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)); ALTER TABLE Annual_sales_order_fact add foreign key (Customer_sk) references Customer_dim (CUSTOMER_SK), add foreign key (

Year_sk) references Year_dim (YEAR_SK);

CREATE TABLE annual_customer_segment_fact (segment_sk int, Customer_sk int, Year_sk int);
ALTER TABLE Annual_customer_segment_fact add foreign key (Segment_sk) references Annual_order_segment_dim (SEGMENT_SK), Add foreign key (Customer_sk) references Customer_dim (CUSTOMER_SK), add foreign key (Year_sk) references Year_dim (Year_sk
 );

List (v)-16-1
Initial load
This section describes the initial load and tests it. The initial load script in Listing (v)-16-2 imports the data from the Order_date dimension table (a view of the Date_dim table) into the Year_dim table, importing the data from the Sales_order_fact tables into Annual_sales_order_ Fact table, the Annual_sales_order_fact table to import the data into Annual_customer_segment_fact tables. This script loads all historical data.

Use DW;

INSERT into Year_dim
SELECT DISTINCT
  NULL
, year
, Effective_date
, expiry_date from
Order_ Date_dim;

INSERT into Annual_sales_order_fact
SELECT
  a.customer_sk
, Year_sk
, SUM (order_amount) from
  sales_order_fact a
, Year_dim c
, Order_date_dim D
WHERE
	A.order_date_sk = d.order_date_sk< C18/>and c.year = D.year
and D.year < year (current_date)
GROUP by A.customer_sk, C.year_sk;

INSERT into Annual_customer_segment_fact
SELECT
  d.segment_sk
, A.customer_sk
, A.year_sk
  from Annual_sales_order_fact a
, Annual_order_segment_dim d
WHERE
	annual_order_amount >= Band_start_amount and
annual_order_amount <= band_end_amount;

Commit

List (v)-16-2
To test the initial Mount script, set the system date to any date of 2014 years to load data for 2013 years. A 2014-year sales order will be imported in the later periodic test summary.

After executing the script in Listing (v)-16-2, the query Annual_customer_segment_fact table confirms that the initial load was successful. The query statement and the results are shown below.
Mysql> Select
-> A.customer_sk CSK,
-> A.year_sk Ysk,
-> Annual_order_amount Amt,
-> segment_name sn,
-> Band_name Bn
-> from
-> Annual_customer_segment_fact A,
-> Annual_order_segment_dim B,
-> Year_dim C,
-> Annual_sales_order_fact D
-> where
-> A.segment_sk = B.segment_sk
-> and A.year_sk = C.year_sk
-> and A.customer_sk = D.customer_sk
-> and A.year_sk = D.year_sk
-> ORDER by A.customer_sk, year, Segment_name, Band_name;
+------+------+---------+---------------+----------+
| CSK | Ysk | AMT | sn | bn |
+------+------+---------+---------------+----------+
|   1 | 14 | 8000.00 | Grid | High |
|   1 | 14 | 8000.00 | PROJECT ALPHA | Top |
|   3 | 14 | 4000.00 | Grid | MED |
|   3 | 14 | 4000.00 | PROJECT ALPHA | Mid-Low |
|   4 | 14 | 4000.00 | Grid | MED |
|   4 | 14 | 4000.00 | PROJECT ALPHA | Mid-Low |
|   5 | 14 | 6000.00 | Grid | MED |
|   5 | 14 | 6000.00 | PROJECT ALPHA | Mid_high |
|   6 | 14 | 6000.00 | Grid | MED |
|   6 | 14 | 6000.00 | PROJECT ALPHA | Mid_high |
|   7 | 14 | 8000.00 | Grid | High |
|   7 | 14 | 8000.00 | PROJECT ALPHA | Top |
+------+------+---------+---------------+----------+
Rows in Set (0.01 sec)

The query results indicate that each customer who had an order in 2013 was given a value of two segmented collections. You can verify that the annual sales amount is assigned the correct paragraph.

Regular load
This section describes how to load a script periodically and how to test it. In addition to the need to load Year_dim tables, regular mounts are similar to initial loads. The data in the Annual_sales_order_fact table is imported into annual_customer_segment_fact tables.

Annual Dispatch execution List (v)-16-3 periodic load, this script loads the previous year's sales data.

Use DW;

INSERT into Annual_sales_order_fact
SELECT
  a.customer_sk
, Year_sk
, SUM (order_amount) from
  sales_order_fact a
, Year_dim c
, Order_date_dim D
WHERE
	A.order_date_sk = D.order_date_sk and
c.year = D.year
and c.year = year (current_date)-1
GROUP by A.customer_sk, C.year_sk;

INSERT into Annual_customer_segment_fact
SELECT
  d.segment_sk
, A.customer_sk
, C.year_sk
From
  annual_sales_order_fact a
, Year_dim c
, Annual_order_segment_dim D
WHERE
	A.year_sk = C.year_sk and
c.year = year (current_date)-1 and
Annual_order_amount >= Band_start_amount and
Annual _order_amount <= Band_end_amount;

Commit

List (v)-16-3
The steps for periodic loading using the Kettle transformation are shown in Figure (v)-16-2 to Figure (v)-16-14.
Figure (v)-16-2
Figure (v)-16-3
Figure (v)-16-4
Figure (v)-16-5
Figure (v)-16-6
Figure (v)-16-7
Figure (v)-16-8
Figure (v)-16-9
Figure (v)-16-10
Figure (v)-16-11
Figure (v)-16-12
Figure (v)-16-13

Figure (v)-16-14

Test to test periodically, set the date of the system date to 2015 years and execute the script in Listing (v)-16-3 corresponding to the kettle conversion.
The Query Customer_order_segment_fact table confirms that the periodic mount is correct. The query statement and the results are shown below.
Mysql> Select
-> A.customer_sk CSK,
-> A.year_sk Ysk,
-> Annual_order_amount Amt,
-> segment_name sn,
-> Band_name Bn
-> from
-> Annual_customer_segment_fact A,
-> Annual_order_segment_dim B,
-> Year_dim C,
-> Annual_sales_order_fact D
-> where
-> A.segment_sk = B.segment_sk
-> and A.year_sk = C.year_sk
-> and A.customer_sk = D.customer_sk
-> and A.year_sk = D.year_sk
-> ORDER by A.customer_sk, year, Segment_name, Band_name;
+------+------+---------+---------------+----------+
| CSK | Ysk | AMT | sn | bn |
+------+------+---------+---------------+----------+
|   1 | 14 | 8000.00 | Grid | High |
|   1 | 14 | 8000.00 | PROJECT ALPHA | Top |
|   1 | 15 | 4000.00 | Grid | MED |
|   1 | 15 | 4000.00 | PROJECT ALPHA | Mid-Low |
|   2 | 15 | 5500.00 | Grid | MED |
|   2 | 15 | 5500.00 | PROJECT ALPHA | Mid |
|   3 | 14 | 4000.00 | Grid | MED |
|   3 | 14 | 4000.00 | PROJECT ALPHA | Mid-Low |
|   3 | 15 | 2000.00 | Grid | Low |
|   3 | 15 | 2000.00 | PROJECT ALPHA | Bottom |
|   4 | 14 | 4000.00 | Grid | MED |
|   4 | 14 | 4000.00 | PROJECT ALPHA | Mid-Low |
|   4 | 15 | 3000.00 | Grid | Low |
|   4 | 15 | 3000.00 | PROJECT ALPHA | Low |
|   5 | 14 | 6000.00 | Grid | MED |
|   5 | 14 | 6000.00 | PROJECT ALPHA | Mid_high |
|   5 | 15 | 2500.00 | Grid | Low |
|   5 | 15 | 2500.00 | PROJECT ALPHA | Bottom |
|   6 | 14 | 6000.00 | Grid | MED |
|   6 | 14 | 6000.00 | PROJECT ALPHA | Mid_high |
|   6 | 15 | 3000.00 | Grid | Low |
|   6 | 15 | 3000.00 | PROJECT ALPHA | Low |
|   7 | 14 | 8000.00 | Grid | High |
|   7 | 14 | 8000.00 | PROJECT ALPHA | Top |
|   7 | 15 | 3500.00 | Grid | MED |
|   7 | 15 | 3500.00 | PROJECT ALPHA | Mid-Low |
+------+------+---------+---------------+----------+
Rows in Set (0.00 sec)