BI Data 201A: Assignment 03: Designing a Data Warehouse

Steps to complete Assignment 03

UPDATES in RED

Step 1: Run this script / execute

Step 2 Table Structure : DB name NorthwindLite

Step 3: Ran this script to look at the data in db

USE NorthwindLite

SELECT * FROM Categories

SELECT * FROM Customers

SELECT * FROM OrderDetails

SELECT * FROM Orders

SELECT * FROM Products

Return this

Step 4 Built a Database Diagram to see relationships

Step 5: Started building out Data Warehouse

We were assigned to complete this spreadsheet, which I did

This was done incorrectly. I was changing Primary Keys to Composite Keys. Instead I should have used ie ProductsID than a ProductKey to go along with it. Here is the answer discussed in class

 FactOrders
  OrderID int
, ProductKey int
, CustomerKey int
, OrderDateKey int  -- 20161024
, OrderTimeKey int  -- 190134
, OrderUnitPrice money -- M
, OrderQuantity int -- M
Primary Key (
  OrderID
, ProductID
, CustomerID
, OrderDate
)

DimProducts
  ProductKey int
, ProductID int
, ProductName nvarchar(50)
, ProductCategoryID int
, ProductCategoryName nvarchar(50)
, SCDStartDate datetime
, SCDEndDate datetime
, SCDIsCurrent bit

DimCustomers
  CustomerKey
, CustomerID nchar(5)
, CustomerName
, CustomerContactName
, CustomerAddress
, CustomerCity
, CustomerCountry
, SCDStartDate datetime
, SCDEndDate datetime
, SCDIsCurrent bit

DimDates
DateKey int
[DateName] nvarchar(50)
FullDate datetime
DateMonthID int
DateMonthName  nvarchar(50)
DateYearId int
DateYearName nvarchar(50)

Step 6: Provide a Narrative

Assigning Destination Type

·       When assigning my DW destination type I chose a simple design. As mentioned in class, it isn’t as important in DW design unlike TSQL design to use different character restraints. I simply used a nvarchar(50)

·       Next I assigned all my keys with int. My assumption is,  all keys will be whole numbers.

·       For datetime, I chose date since time should be irrelevant.

·       Lastly, currency (money) was changed to domical(18,4). This will make the design more compatible.

  

Assigning Object Name

·       My design was modeled after the Pubs example in class

·       When choosing my Object Name, I chose a name as close to the Source Name as possible. It this manner, it would be easy to track where the Object Name came from.

·       When selecting name for the a primary or foreign key, I chose the name ()Key instead of ID. This was the same outline that the pubs deign used.