Homework 6 Partial Key Star Diagrams

1
Homework 6 Partial KeyStar Diagrams Data
Warehouse Design

• BCIS 4660
• Dr. Nick Evangelopoulos
• Spring 2006

2
Premier Products Final Star Diagram (also see
Pratt p. 303)
3
Steps for HW6

• Copy/paste Time table from PPTime2005.mdb (posted
  on the Web), into your PPStar database
• Delete all relationships and rearrange tables in
  relationship view
• Redesign each dimension table
• Build a select query that lists the fact table
  contents
• Convert the query to a make-table query
• Add relationships to create a Star schema

4
Delete relationships
5
Redesigning the Customer dimension

• Open CUSTOMER in design view, add CustomerKey,
  delete Balance
• Open CUSTOMER and populate CustomerKey with
  unique values (manually, by typing the values)
• Open Customer table in design view and re-key

6
Redesigning the other dimensions

• Continue this way with the other two dimensions
  add key attribute, remove unnecessary attributes,
  enter values for the key attribute, then make it
  the primary key.

7
Fact table, 1st attribute

• Start by putting together a select query that
  lists OrderNum for all orders. This is very easy,
  just build a QBE as shown below.

8
Fact table, 2nd attribute

• Open your previous query and add PartNum. For
  this, you will need the OrderLine table.

9
Fact table, 2nd attribute (contd)

• Add Part table to your QBE, add Part.PartKey
  attribute, specify OrderLine.PartNumPart.PartNum
  in the criteria and unselect PartNum so that only
  PartKey gets displayed.

10
Adjusting the order dates

• If you are using the posted Time table which
  includes years 2004-2006, do not forget to change
  the order dates from 2007 to 2004!
• If you have orders that took place in 2007 and a
  time table that doesnt list 2007, nothing will
  get displayed!

11
Completing the Fact Table

• Continue this way INCREMENTALLY, until you get
  all fact attributes, including NumOrdered,
  QuotedPrice, and OrderDollars.

12
Getting the SQL code

• Switch your Query Design view to SQL view
• Remove redundant SQL code, but make sure every
  time that your query still produces the correct
  results

13
Convert select Query to Make-table

• Open Query1 in design view and convert it to a
  make-table query.

14
Henry Books Star Diagram (not necessarily final)