Data Warehouse

1

BCIS 4660 Introduction to Data Warehousing

Assignment 3

Objectives

1. Apply dimensional modeling

o Design fact and dimension tables

2. Continue to gain more experience with SQL Server

3. Continue to gain more experience with T-SQL

Instructions

1. Create a new Word document. Do not use the current document.

2. Submit a Word document with your name on the cover page

o To create a cover page, click Insert, Cover Page, and select

a cover page)

3. Clearly identify your name and assignment name on the cover page

4. The Word document should contain the requested screenshots and

answers to any questions

5. Each screenshot image: o Must be a full-screen window grab

o Must include a caption (e.g., Screenshot1: ERD1 Shipment)

o Must include your computer name, username, and execution results

6. A professional quality work is expected. Points will be deducted for unprofessional work.

Notes

1. Environment: Windows 10 and SQL Server Management Studio (not supported on Mac)

2. Sample Script: LonicaOperationalDatabase.sql

Exercise: Dimensional Modeling (40pts)

FashionKey Inc is an online and traditional brick and mortar clothing company based in New York. Their

point-of-sales, internet, and mobile applications are based on their operational database system. This

database has the daily operations of the business. The retail company is looking to better understand their

sales and specifically, the purchase habit and history of their internet customers. The operational database

includes several tables: customers, product, region, store, vendor, sales transaction, product detail

(Includes).

2

Your objective is to help the retailer understand their sales and customers. You are going to design and

build their data warehouse, design the fact table, and dimension tables.

1. A sample script has been provided for your use: LonicaOperationalDatabase.sql. Edit this script

and use it to create an operational database for FashionKey. This sample script can be used to create

all the tables, and populate the tables.

a. Provide a short description of your actions and the reasons for the actions

b. Screenshot1 showing all database objects (tables, keys, constraints).

2. Using the database diagram, create and view the E-R Model

a. Provide a short description of your action

b. Screenshot2 showing E-R model. See instructions

3. In your own words, identity and describe the 4-step process for designing a dimensional model for

FashionKey Inc (Hint: the business process, grain, dimensions, and fact design should be specific

to FashionKey Inc.)

4. Create the FashionKey data warehouse database (FashionKeyDW)

a. Screenshot3 showing the data warehouse. See instructions

5. Using the database diagram, design the dimension tables (e.g., DimProduct, DimCustomer,

DimStore)

a. Provide a short description of your action

b. Screenshot5 of the dimension tables dimensional model. See instructions

6. Using the database diagram, design the fact table (FactSalesFashionKey)

a. Provide a short description of your action

b. In your own words, describe the differences between a fact table and dimension tables.

c. Screenshot5 of fact table dimensional model. See instructions

Exercise: T-SQL (10pts)

1. Create the following database objects from the information provided below. Please create the objects

in your “Lastname” database.

3

Customer Product

CustomerID -Int (PK) Identity (10, 1) ProductID – Int (PK) Identity (100, 1)

FirstName - Varchar 50 Not Null ProductName - Varchar 50 Not Null

LastName - Varchar 50 Not Null ProductDescription - Varchar 50 Not Null

Phone - Char 50 Not Null ProductCategoryID- Int (FK) Null

Email - Varchar 50 Not Null

ModifiedDate - Datetime (Default to use current date time)

ProductCategory

ProductCategoryID - Int (PK) Identity (1, 10)

ProductCategoryName - Varchar 50 Not Null

Screenshot 6 should show the 3 table create scripts, as well as the objects in the object explorer. The scripts

should be re-runnable (HINT; USE databasename, Drop if exist etc.).