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.
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.
/******************************************************************/ /* */ /* 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
3.133.133.233