Homework 5 Partial Key Star Diagrams - PowerPoint PPT Presentation

Loading...

PPT – Homework 5 Partial Key Star Diagrams PowerPoint presentation | free to download - id: 6c6487-MjBkZ



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Homework 5 Partial Key Star Diagrams

Description:

Homework 5 Partial Key Star Diagrams & Data Warehouse Design BCIS 4660 Dr. Nick Evangelopoulos Spring 2012 DO NOT COPY --CONFIDENTIAL DO NOT COPY --CONFIDENTIAL ... – PowerPoint PPT presentation

Number of Views:18
Avg rating:3.0/5.0
Date added: 2 September 2019
Slides: 18
Provided by: NickE98
Learn more at: http://www.cob.unt.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Homework 5 Partial Key Star Diagrams


1
Homework 5 Partial Key Star Diagrams Data
Warehouse Design
  • BCIS 4660
  • Dr. Nick Evangelopoulos
  • Spring 2012

2
Premier Products Original Design
3
Premier Products Initial Star Diagram
4
PP -- Relation List
  • Fact Table
  • OrderDetailOrderNum, PartNum, OrderDate, CustNo,
    RepNo, NumOrdered, Price
  • Dimension Tables
  • CustomerCustNum, CustName, Street, City, State,
    Zip, Balance, CreditLimit
  • RepRepNum, LastName, FirstName, Street, City,
    State, Zip, Commission, Rate
  • PartPartNum, Desc, OnHand, Class, Warehouse,
    Price
  • TimeTimeKey, Day_of_Week, Month, Year,
    JulianDate, Quarter, etc.

5
Design considerations for the Star schema
  • Put together a set of questions your star design
    is going to address
  • Create an appropriate Time dimension
  • Chose a Grain Orders or Orderline?
  • Lets go for Orderline level, to include info on
    Parts
  • Handle Slowly Changing dimensions Create new
    primary keys for the dimensions tables, called
    CustomerKey, RepKey, PartKey
  • Handle Frequently Changing dimensions Customer
    Account, Rep Compensation, could become
    minidimensions
  • Identify Fully Additive measures and include them
    into the fact table Balance can go to the fact
    table as a semi-additive measure
  • Identify which concatenation of foreign keys from
    dimension should make up the primary key of the
    fact table

6
Some problems with our Initial Star design
  • How will we handle Slowly Changing dimensions?
    Address-type of attributes may change slowly.
    Need for new primary keys on the dimension tables
  • Where will Customer balance be recorded? Another
    star?
  • What about Part OnHand quantities? Another star?

7
Premier Products Modified Star Diagram
8
PP -- Relation List
  • You now need to modify your relation list
    accordingly

9
Questions we can answer with this Star Schema
  • Q1 What are the most ordered parts?
  • Need to provide a list of all orders, group them
    by PartNum, sum NumOrdered quantities, then sort
    them by descending quantity count.
  • This can be accomplished with a query on the fact
    table.

10
Questions we can answer with this Star Schema
  • Q2 What is the average number of orders by day
    of the week?
  • Need to provide a list of all orders, group them
    by OrderDate, count distinct OrderNum occurences,
    then group them by Day_of_week, average the order
    counts, then sort them by ascending day of the
    week.
  • This can be accomplished with a query on the fact
    table.

11
Questions we can answer with this Star Schema
  • Q3 Which parts generate the largest revenue?
  • Need to add an OrderDollars attribute to the fact
    table, so that revenue by part doesnt have to be
    derived.
  • OrderDollars NumOrdered QuotedPrice
  • Now we can list all orders with their
    OrderDollars, group them by PartNum, then sum
    OrderDollars to get total revenue by part,
    finally sort by descending order of total revenue.

12
Questions we can answer with this Star Schema
  • Q4 What is the average monthly Rep revenue by
    each commission rate level?
  • Q5 What Reps have shown an increased monthly
    revenue since January?
  • Q6 What was the total revenue generated in the
    last year by each customer?
  • All these questions can be facilitated by adding
    an OrderDollars attribute to the fact table.

13
How to fake the Star design in Access (HW5)
  • Create a new Table in Design view, containing all
    the attributes we need for our star schema.
    Specify the primary key. Name it OrderDetail.

14
How to fake the Star design in Access (HW5)
  • Open Customer table and DELETE ALL ROWS. Then
    open it in design view. Add an attribute called
    CustomerKey and designate it as the primary key.
    Delete Balance attribute.
  • Repeat similar steps for the Part table. Delete
    the OnHand attribute. Similarly for Rep.

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

16
How to fake the Star design in Access (HW5)
  • You are now ready to finish your star schema by
    adding relationships between these empty tables!

17
Premier Products Final Star Diagram
About PowerShow.com