Chapter 19: Multi-Star Schemas


Starting from Chapter 10, “Adding Columns” you have been growing your data warehouse by adding columns and tables, and in Chapter 14, “Snapshots” you added a second fact table, the month_end_sales_order_fact table. After the addition, your schema has had two fact tables (the first is the sales_order_fact table you created at the start of the data warehouse). With two fact tables, the data warehouse is officially a two-star schema.

In this chapter you will add a new star to the existing stars of the dimensional data warehouse. Unlike the existing stars that are related to sales, the new star addresses the needs of the production business area. The new star, which has one fact table and one dimension table, stores the production data in the data warehouse.

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.

image from book
Figure 19.1: A three-star dimensional data warehouse schema

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

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

Now run the script in Listing 19.1.

 mysql> \. c:\mysql\scripts\third_star_tables.sql



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net