BIS 445 DeVry Week 2 I Lab

Downloading is very simple, you can download this Course here:


Contact us at:



BIS 445 DeVry Week 2 I Lab


BIS 445 DeVry Week 2 I Lab

Create a data warehouse using a star schema and then analyze data warehouse information.

Note! Submit your assignment to the Dropbox located on the silver tab at the top of this page. (See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

Remember This! Connect to the I Lab here.


Scenario and Summary

You will start with a comma delimited file that looks like this:

When you are finished moving the data into the data warehouse, you will have split the customer, order, and product information into their own tables and, as a result, create a star schema that looks like this:

As you can see, the Fact Orders table has become a fact table containing statistics we might want to analyze as managers of a company. The fact table contains the primary keys of the other tables as foreign keys. Also in the fact table (Fact Orders), we have stored the result of Price*Quantity in a newly created column called Sales Total. Sales Total is a fact we would like to analyze using an OLAP tool, such as a pivot table, in the future.

The primary key of the Fact Orders table is a Surrogate Key which has been generated by the database management system.

In the star schema, the products and customers table have become dimension tables. Furthermore, the star schema removes the duplicated customer and product names that existed in the original comma delimited file and puts them in normalized customer and product tables.

Last of all, the star schema violates the third normal form, which says that no calculated fields should exist in the design (schema). In this case, the Sales Total column is a calculated field.

This violation isn’t a problem because in a Decision Support System, software that accesses a data warehouse, it is acceptable to violate normal forms if it speeds up SQL queries by minimizing table joins and storing calculations in the database.

Your task is to create a star schema that is capable of holding the information in the comma delimited file, and then load the star schema with the data from the text file. You will load the comma delimited data file into a temporary table in the SQL database. After you design the star schema, you will use SQL statements to populate the star schema with the necessary data from the temporary table. Finally, you will analyze the data by order year in order to find out which customers have the highest to lowest sales.


Submit the YourName_Lab2_Questions.docx to the Week 2 I Lab Dropbox.