The New Star Schema

Figure 19.1 is the extended schema of our data warehouse. The schema has three stars. The sales_order_fact table is the first star’s fact table. In the same schema are the customer_dim, product_dim, and date_dim tables.

Figure 19.1. A three-star dimensional data warehouse schema

[View full size image]

The month_end_sales_order_fact table is the second star’s fact table. The product_dim and month_dim tables are its corresponding dimension tables. The first and the second stars share the product_dim table. You might recall that data in the second star’s fact and month dimension are derived from the first star’s fact and date_dim, respectively. They don’t get their data from the data source.

The third star’s fact table is a new production_fact table. Its dimensions are stored in the existing date_dim and the product_dim tables as well as in a new factory_dim table. The third star’s data comes from the data source.

You create the third star’s new tables using the script in Listing 19.1.

Listing 19.1. Creating the third star’s tables

/******************************************************************/
/*                                                                */
/* third_star_tables.sql                                          */
/*                                                                */
/******************************************************************/

/* default to dw                                                  */

USE dw;

CREATE TABLE factory_dim (
  factory_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, factory_code INT
, factory_name CHAR(30)
, factory_street_address CHAR(50)
, factory_zip_code INT(5)
, factory_city CHAR(30)
, factory_state CHAR(2)
, effective_date DATE
, expiry_date DATE
)
;

CREATE TABLE production_fact (
  product_sk INT
, production_date_sk INT
, factory_sk INT
, production_quantity INT
)
;

/* end of script                                                  */

Now run the script in Listing 19.1.

mysql> . c:mysqlscripts	hird_star_tables.sql