iLab 3: Using Enterprise Data to Create Pivot Tables.
You are working as a data analyst for a large manufacturing firm. You have been asked to develop a series of pivot table
queries, which will present aggregated views of corporate data that can be used for decision support and trend analysis by
decision-makers.
Using SQL, you can create aggregated views of your data. Pivot tables are especially useful if you are analyzing large volumes
of data. For example, if you wanted to determine the average value of sales for each sales person in your database, you could
use a pivot table to aggregate thousands or millions of sales records. This is much faster than attempting to scroll through all of
the available records.
You will log into our Citrix Server, access SQL Server, and the BIS445_AdventureWorks database, and then develop five pivot
tables.
Submit the YourName_Lab3_Questions.docx to the Week 3 iLab Dropbox.
Section Deliverable Points
Part A Create a query to determine cost data for manufacturing a product. 10
Part B Develop a query to analyze average, minimum, and maximum costs
for each product.
10
Part C Determine average sales amount for each salesperson. 10
Part D Determine average cost based upon a number of days to
manufacture a product.
15
Part E Analyze the number of purchase orders placed by each vendor. 15
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
Deliverables
i L A B S T E P S
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
1 of 12 9/18/2014 11:34 PM
Download the Week3_Lab3_Questions.docx from DocSharing. You will answer the questions and provide screen
prints as required for each part of the lab.
1.
Create a folder on your C drive named Transfer.
Follow the login instructions located in the iLab 1. tab in Course Home.
2.
Launch SQL Server
1. Connect to BIS445 SQL data source.
2. Connect to the BIS445_AdventureWorks database.
3.
Part A: Create a query to determine cost data for manufacturing a product.
Create a query to determine average cost relative to the number of days needed to manufacture a product.
Open your Citrix Laboratory, click the SQL 2008 Server Management Studio and then click Next to get to the Choose a
Data Source screen. b. Select Full Access for the Citrix File Security screen as follows:
a.
b. Select Full Access for the Citrix File Security screen as follows:
c. When the Connect to Server dialog appears, enter the following information and click the Connect button:
Preparation Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Label Title Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
2 of 12 9/18/2014 11:34 PM
Your Microsoft SQL Server Management Studio screen d. will then appear:
Expand the Databases folder and locate BIS44_AdventureWorks. Then expand this database to see the tables. Your
screen should be similar to the following:
e.
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
3 of 12 9/18/2014 11:34 PM
Now click on the New Query button on the Toolbar. You will be writing SQL code in f. this query window.
Before you start writing your SQL code, change the current database from master to BIS44_AdventureWorks. Your
screen should now look like the following:
g.
Now, you will write a query to answer the following question:
Using the Product table, determine the average cost relative to the number of days needed to manufacture a
h.
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
4 of 12 9/18/2014 11:34 PM
product. You need to group your results based upon the number of days to manufacture the product.
As you prepare to create this query, decide what information needs to be displayed; this information will be listed in the
SELECT clause. What aggregate functions will you need to produce the required result? Will you need a Group By
clause? You will find the cost in a field in the table named StandardCost.
Using the query window, type the SELECT statement needed to produce the i. desired results.
j. After you type your qukery, click on the Execute button to run your query and see your results.
k. Your SQL code should look like the following:
SELECT DaysToManufacture, AVG(StandardCost) as AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Take a screen shot of your SQL code and query results for step j, and paste this screen shot in your Lab3 Question area
for Part A.
l.
Write your interpretation of the results. The interpretation must be a minimum of one paragraph (3 to 5 well formed
sentences) with no spelling or grammatical errors. Answer such questions as what do the results tell us? How could the
results be used by decision-makers to improve business performance? Based on these results, what other data would
you recommend that should be considered for further analysis?
m.
Your results at the end of the import wizard should look similar to this.
Using the Lab3_Questions, paste your screen shot in the appropriate area.
Part B – Develop a query to analyze average, minimum and maximum costs for each product.
STEP 3: Sample Output Back to Top
STEP 4: Deliverable Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
5 of 12 9/18/2014 11:34 PM
Using the ProductCostHistory table, determine the average, minimum, and maximum cost for each product. You need to
group your results by product id.
This problem requires a new query. Delete the query in the query window and create the new statement.
As you prepare to create this query, decide what information needs to be displayed; this information will be listed in the
SELECT clause. What aggregate functions will you need to produce the required result? Will you need a Group By clause?
Clear a. the query window.
Write the SQL code to solve the above problem, and then click on the Execute button to run the query and see the
results.
b.
c. Your query should look similar to the following:
SELECT ProductID, AVG(StandardCost) as Average,
MIN(StandardCost) as Minimum,
MAX(StandardCost) as Maximum
FROM Production.ProductCostHistory
GROUP BY ProductID;
d. Your partial query results should look similar to the following:
Take a screen shot of your SQL code and query results and paste this screen shot into Part B of your Lab 3 Question
area.
e.
f. Write in your Part B area a paragraph on interpreting your query results. This interpretation should be a minimum of one
STEP 1: Problem Description Back to Top
STEP 2: Create the dimension and fact tables for your star schema data warehouse. Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
6 of 12 9/18/2014 11:34 PM
paragraph (three to five well formed sentences) with no spelling or grammatical errors. In writing your paragraph answer
such questions as what do the results tell us? How could the results be used by decision-makers to improve business
perfomance? Based on these results, what other data would you recommend for further analysis?
Your query results should look like the following:
Using the Lab3_Questions, answer the questions for Part B.
Part C – Determine average sales amount for each salesperson.
Using the SalesOrderHeader table, determine the average sales amount for each salesperson before taxes and shipping
costs. You need to group and order your output by sales person id.
This problem requires a new query. Delete the query in the query window and create the new statement.
As you prepare to create this query, decide what information needs to be displayed; this information will be listed in the
SELECT clause. What aggregate functions will you need to produce the required result? Will you need a Group By clause?
How will results be ordered?
STEP 3: Sample Output Back to Top
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
7 of 12 9/18/2014 11:34 PM
Clear a. the query window.
Write the SQL code to solve the above problem and then click on the Execute button to run the query and see the
results.
b.
c. Your query should look similar to the following:
SELECT SalesPersonID, AVG(SubTotal) as AverageSales
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
ORDER BY SalesPersonID;
d. Your partial query results should look similar to the following:
Take a screen shot of your SQL code and query results and paste this screen shot in Part C of your Lab 3 Question
area.
e.
Write in your Part C area a paragraph on interpreting your query results. This interpretation should be a minimum of one
paragraph (three to five well formed sentences) with no spelling or grammatical errors. In writing your paragraph answer
such questions as what do the results tell us? How could the results be used by decision makers to improve business
perfomance? Based on these results, what other data would you recommend for further analysis?
f.
Your final results should look similar to the following:
STEP 2: Create a new query to solve the third problem. Back to Top
Step 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
8 of 12 9/18/2014 11:34 PM
Using the Lab3_Questions, paste your screenshot and answer for Part C.
Part D – Determine average cost based upon number of days to manufacture a product.
Using the product table, determine the average cost based upon the number of days to manufacture a product. Your output
should be displayed in a single row. You will use the PIVOT relational operator in order to display the data in a single row Pivot
Table.
This problem requires a new query. Delete the query in the query window and create the new statement.
This query is asking for the same data as the first question, but the difference is that the output must be in a single row; if you
refer back to your output from question 1, you will see there is more than 1 row. In Part D, the results will be pivoted or turned.
In order to achieve this, you will need to use a subquery in the FROM clause.
Clear a. the query window.
Write the SQL code to solve the above problem and then click on the Execute button to run the query and see the
results.
b.
c. Your query should look similar to the following:
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/2b3be363-723a-4cb3-ad6c-9d85…
9 of 12 9/18/2014 11:34 PM
Take a screen shot of your SQL code and query results and paste this screen shot in Part D of your Lab 3 Question
area.
d.
Write in your Part D area a paragraph on interpreting your query results. This interpretation should be a minimum of one
paragraph (three to five well formed sentences) with no spelling or grammatical errors. In writing your paragraph answer
such questions as what do the results tell us? How could the results be used by decision makers to improve bu siness
perfomance? Based on these results, what other data would you recommend for further analysis?
e.
Your final results should look similar to the following:
Using the Lab3_Questions, answer the questions for Part D.
Part E: Analyze the number of purchase orders placed by each vendor.
Using the PurchaseOrderheader table, determine the number of purchase orders placed by specific employees for each
vendor. You will use the PIVOT relational operator and the COUNT aggregate function to display the data.
STEP 3: Sample Output Back to Top
Step 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
10 of 12 9/18/2014 11:34 PM
This problem requires a new query. Delete the query in the query window and create the new statement.
What is required in the output? What order will it be in? A subquery will be needed in the FROM clause to get the required
fields to provide a count of the number of purchases placed by each employee.
Clear a. the query window.
Write the SQL code to solve the above problem and then click on the Execute button to run the query and see the
results.
b.
c. Your query should look similar to the following:
d. Your partial query results should look similar to the following:
Take a screen shot of your SQL code and query results and paste this screen shot in Part C of your Lab 3 Question
area.
e.
f. Write in your Part E area a paragraph on interpreting your query results. This interpretation should be a minimum of one
Step 2: Create a new query to solve the third problem. Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
11 of 12 9/18/2014 11:34 PM
paragraph (three to five well formed sentences) with no spelling or grammatical errors. In writing your paragraph answer
such questions as what do the results tell us? How could the results be used by decision makers to improve business
perfomance? Based on these results, what other data would you recommend for further analysis?
Your final results should look similar to the following:
Using the Lab3_Questions, paste your screenshot and answer for Part E.
Submit your completed YourName_Lab3_Questions.docx to the Week 3 iLab Dropbox.
STEP 3: Sample Output Back to Top
Step 4: Deliverable Back to Top
Final Deliverable Back to Top
iLab https://devry.equella.ecollege.com/file/2b3be363-723a-4cb3-ad6c-9d85…
12 of 12 9/18/2014 11:34 PM