Homework 6 Partial Key Generating a Fact table

1
Homework 6 Partial KeyGenerating a Fact table

• BCIS 4660
• Dr. Nick Evangelopoulos
• Spring 2012

2
Premier Products Final Star Diagram (from HW5)
3
Time Dimension

• Create a Time table in Excel. Cover 1 year, Jan1,
  2013, to Dec 31, 2013 (posted on the Web).
• Import it in Access. Make Time_key the primary key

4
Customer Dimension

• Open Customer table in Design view and add a
  field called Customer_Key.
• Type in that field the values 1,2,3, etc.

5
Part and Rep Dimensions

• Repeat the same for dimensions Part and Rep by
  adding and typing values for Part_Key, Rep_Key.
  Orders and OrderLine do NOT need a key field.

6
Fixing the Order dates

• Orders are currently shown as placed in some
  other year (2010). Since our Time table does not
  include that year, change all dates to equivalent
  2013 dates.

7
Starting the Fact table as a select query

• Start putting together a select query that lists
  the fields we want to include in our fact table.

You can get OrderNum from Orders and PartNum from
OrderLine. You can later replace PartNum by
PartKey by adding Part table to your query.
8
Avoiding Cartesian Products

• To avoid a Cartesian product, you need to ask for
  those records of OrderLine where
  OrderLine.OrderNum Orders.OrderNum. To do this
  using the QBE interface, add OrderLine.OrderNum
  to your query, but remove the checkmark so that
  it doesnt show.

9
Incremental progress

• Continue adding fields to your query one at a
  time. Check your progress by executing your
  query.
• The correct result has 9 records

10
OrderDate attribute

• The next Fact attribute on the list is the
  Time_Key. You will get that from OrderDate. Open
  your query in design view again and add OrderDate
  from Orders.

11
Verifying OrderDate attribute

• Execute the query once again to verify the new
  attribute was added and the number of records is
  still 9.

12
Continuing this way

• Continue this way until you add all attributes
  needed for the fact table. Make sure you replace
  all old primary keys with their key version. In
  the end, convert your select query to a
  make-table query. Once you execute the make-table
  query, your Fact table will be created.