iLab 2: Create a data warehouse using a star schema and then analyze data warehouse information.
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 FactOrders 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 (FactOrders), 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.
Print
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.)
i L A B O V E R V I E W
Scenario and Summary
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
1 of 17 9/12/2014 12:10 PM
The primary key of the FactOrders 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 iLab Dropbox.
Section Deliverable Points
Part A Import comma delimited text file into an SQL server database. 10
Part B Create the dimension and fact tables for a star schema. 10
Part C Create the relations between your dimension tables and fact table. 10
Part D Populate your new dimension and fact tables with the data from
your imported LabInputData table.
15
Part E Analyze the data in your data warehouse using traditional SQL
techniques.
15
Download the Week2_Lab2_Questions.docx from DocSharing. You will answer the questions and provide screen prints as
required for each part of the lab.
1.
2. Create a folder on your C drive named Transfer.
3. Download the Week2_Lab2InputData.txt file (a comma delimited text file) from DocSharing.
4. Log in to the Citrix remote lab.
1. Follow the login instructions located in the iLab tab in Course Home.
2. Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course Home.
Part A: Import a Data File into an SQL 2008 Database
Import your Lab2InputData.txt comma delimited file into an SQL 2008 database temporary table.
Deliverables
i L A B S T E P S
Preparation Back to Top
STEP 1: Problem Description Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
2 of 17 9/12/2014 12:10 PM
a. Open your Citrix Laboratory, click the SQL 2008 Import Utility and then click Next to get to the Choose a Data Source screen.
b. On the Choose a Data Source screen, select the Flat File Source from the Data Source pull down.
Click the Browse button and navigate to your Transfer folder, select your Lab2InputData.txt text file, and then click Open to
select your Lab2InputData.txt file for the File Name.
c.
d. Check Column names in the first data row on the Choose a Data Source screen.
e. Your screen should look similar to the following:
f. Click Next twice to get to the Choose A Destination screen.
g. Leave the default SQL Server Native Client 10.0 for Destination.
h. Insert into the Server Name combo box your server: BIS445SQL2008
In the Database combo box area, select BIS445_CoffeeMerchant for your Database. You do not have the access rights in the
Citrix Lab area to create a new database.
i.
j. Your screen should look similar to the following:
STEP 2: Label Title Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
3 of 17 9/12/2014 12:10 PM
k. Click Next to get to the Select Source Tables and Views screen.
Click Edit Mappings on the Select Source Tables and Views screen. Change the Type column to get more meaningful SQL field
types.
l.
m. Your field type selections should look similar to the following:
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
4 of 17 9/12/2014 12:10 PM
n. Take a screen shot of the above Column Mappings screen for your Lab 2 questions.
o. Click OK on the Column Mappings sceen and then click Next.
p. On the Review Data Type Mapping screen set On Error (global) and On Truncation (global) to Ignore.
q. Your Review Data Type Mapping screen should look like the following:
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
5 of 17 9/12/2014 12:10 PM
r. Click Next twice and then Finish until you see the end of the SQL 2008 import wizard.
s. Take a screenshot at the end of the import wizard for your Lab2Question area.
Your results at the end of the import wizard should look similar to this:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
6 of 17 9/12/2014 12:10 PM
Using the Lab2_Questions, answer the questions for Part A and paste a copy of your import wizard execution results.
Click the Close button to end your import wizard.
Part B – Create new dimension and fact tables for your star schema data warehouse
After importing your comma delimited text file into a new temporary table in an existing SQL 2008 database, you will need to create
new dimension tables and a fact table.
Note: When working with SQL 2008 Server Management Studio it is best to uncheck the “Prevent saving changes that require table
creation” option under the Tools>Options>Designers>Table and Database Designers pull-down menu.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Create the dimension and fact tables for your star schema data warehouse. Back to Top
Please see the following screenshot:
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
7 of 17 9/12/2014 12:10 PM
a. Click the SQL 2008 Server Management Studio.
b. Select Full Access for the Citrix File Security screen as follows:
On the Connect to Server screen, Server type should be Database Engine, Server Name should be BIS445SQL2008,
Authentication is Windows Authentication.
c.
d. Click Connect to see the Object Explorer in your Microsoft SQL Server Management Studio area.
e. Expand your Databases node, the BIS445_CoffeeMerchant node, and then the Tables node.
f. You should see a new Lab2InputData table in your Tables node area.
g. Expand the node for your Lab2InputData table and then the Column nodes to verify your column data types.
h. Inspect your data by clicking the New Query button and writing a query to dump your Lab2InputData table information.
Right click your Tables node, select New Table, and add your field information for your DimCustomer new table. Before you
put in your column information, go to your properties window and change the Schema information to dbo as shown below. If
the Properties window is not open, select View -> Properties Window.
i.
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
8 of 17 9/12/2014 12:10 PM
j. Right click the gray box next to CustomerID and select Set Primary Key.
k. Close your new table, click Yes to save your results, and then name your table DimCustomer and click the OK button.
Repeat the process from Step j to Step k and create the DimProduct table with the column information found in the star schema
in the Lab Overview Scenario. Be sure to assign the Primary Key.
l.
Repeat the process from Step j to Step k and create your FactOrders table with column information found in the star schema in
the Lab Overview Scenario. For this table, make sure that your OrderSurrogateKey is an Identity by changing the Identify
Specifications in the Column Properites panel. See screenshot below:
m.
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
9 of 17 9/12/2014 12:10 PM
n. Take a screenshot showing your column data types in your new tables.
Your screenshot should look similar to this:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
10 of 17 9/12/2014 12:10 PM
Using the Lab2_Questions, answer the questions for Part B.
Part C – Create the relations for your new dimension tables and fact table
Relations for your new dimension tables and fact table need to be created.
a. Right click your FactOrders table and select Design.
b. Click the Relationships button and then click the Add button in the Foreign Key Relations window.
c. In the Tables and Columns Specification area, click the blank column on the right to bring up Add Relations Button.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Create relations Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
11 of 17 9/12/2014 12:10 PM
When the Tables and Columns window appears, select DimCustomer as the Primary key table and CustomerID as the
Primary key. For the Foreign key table, make sure that FactOrders is selected and CustomerID is the Foreign key. Your
Tables and Columns selections should look like the following:
d.
Click OK and then Add to create a second relationship in the Foreign Relationships window. Name the relationship
FK_FactOrders_DimProduct. Your Foreign Key Relationship window should look like the following:
e.
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
12 of 17 9/12/2014 12:10 PM
f. In the Tables and Columns Specification area, click the blank column on the right to bring up Add Relations Button.
When the Tables and Columns window appears, select DimProduct as the Primary key table and ProductID as the Primary
key. For the Foreign key table, make sure that FactOrders is selected and ProductID is the Foreign key. Your Tables and
Columns selections should look like the following:
g.
Click the OK button, close your Foreign Key Relationships window, and then close your Design window. Click Yes to save the
database when prompted.
h.
i. Take a screenshot of your query results.
Your results should look similar to this:
Step 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
13 of 17 9/12/2014 12:10 PM
Using the Lab2_Questions, answer the questions for Part C.
Part D – Populate your star diagram dimension and fact tables with the Lab2InputData temporary table
data
When you complete your new dimension tables and fact table and have set the appropriate relationships, you need to populate these
tables with the data from your temporary Lab2InputData table.
Write an SQL statement to populate your DimCustomer table from your Lab2InputData table. Remember to write this SQL query
so that no duplicate data exist, because this would represent a violation of first normal form requirements.
a.
Write an SQL statement to populate your DimProduct table from your Lab2InputData table. Remember that this table has no
duplicate data.
b.
[Answer:
USE BIS445_CoffeeMerchant;
INSERT INTO DimProduct(ProductID, ProductDescription)
SELECT DISTINCT ProductID, Description
FROM Lab2InputData; ]
Write an SQL statement to populate your FactOrders table from your Lab2InputData table. For this table you will violate
normalization by populating the computed field Sales_Total with data.
c.
First compose and execute your SQL statement to add the data to the FactOrders table.
Second, use the following statement to compute Sales_Total.
[Answer:
INSERT INTO FactOrders(CustomerID, ProductID, OrderID,
OrderDate, Price, Quantity)
SELECT CustomerID, ProductID, OrderID,
Step 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Enhance SQL script and execute it Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
14 of 17 9/12/2014 12:10 PM
Order_Date, Price, Quantity
FROM Lab2InputData; ]
Then run the following to compute Sales_Total:
d. Save your SQL query.
e. Write an SQL query to show your results of populating your FactOrders table:
[Answer:
SELECT * FROM FactOrders;
]
f. Take a screenshot of your query results.
Your results should look similar to this:
Using the Lab2_Questions, answer the questions for Part D.
Part E: Analyze data in your data warehouse using traditional SQL techniques
You will use your new star schema data warehouse to analyze the information in your system.
a. Write a SQL statement to display the sum of sales totals by customer id.
[Answer:
SELECT CustomerID, SUM(Sales_Total) AS Sum_Sales_Total
FROM FactOrders
STEP 3: Sample Output Back to Top
Step 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Create an SLQ script to modify data and execute it Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
15 of 17 9/12/2014 12:10 PM
GROUP BY CustomerID
ORDER BY CustomerID;]
b. Save it as SQLQuery1E1 and execute the SQL Statement.
c. Take a screenshot of your results.
d. Write the following SQL statement:
e. Save it as SQLQuery1E2 and execute the SQL Statement.
f. Take a screenshot of your results.
Your results should look similar to this:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
16 of 17 9/12/2014 12:10 PM
Using the Lab2_Questions, answer the questions for Part E.
Submit your completed YourName_Lab2_Questions.docxto the Week 2 iLab Dropbox.
Step 4: Deliverable Back to Top
Final Deliverable Back to Top
iLab https://devry.equella.ecollege.com/file/078cb706-2692-44f0-9061-80bae…
17 of 17 9/12/2014 12:10 PM