Business Intelligence

Lab 5 Student Answer Sheet

Student Name:

Using Lab 5 provide copies of your screen shots or answer the questions for each Section listed below

Part A: Use import wizard to import comma delimited file into a SQL 2008 table

a. Paste your screen shot of your final Import wizard results here

Part B: Create scatter plots

a. Paste your screen shot of your scatter plot for food court drink sales here:

b. Paste your screenshot of your scatter plot for street vendor drink sales here:

Part C: Regression analyses

a. Paste your screen shot on your regression analysis on period number and food court drink sales here :

b. Paste your screen shot on your regression analysis on period number and street drink sales here (include all of your regression analysis results including the above:

c. What is the equation of the line of best fit for period and mall drink sales (answer below):

d. Do you think period number is a good predictor of street drink sales? Justify your reasons with answers from your linear regression output. Refer to at least two statistics in your answer

e. Make a prediction for period 11 street drink sales

iLab 5: Predict sales patterns using simple linear regression.

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 iLab here.

iLAB OVERVIEW

Scenario and Summary

Joe Sanders scratched his head. He owned two Hungry Boy Subs franchises in a city. In an unusual arrangement, one of the franchises was located in the food court of the local mall. The other franchise was about two blocks down the street from the mall. The area in which Joe had purchased the franchises was in a new end of town, well-known for its good schools, professional offices, and well-kept neighborhoods. The area had been growing rapidly since Joe purchased the franchise about 10 months ago.

Joe purchased the local mall’s sub shop first, and found it to be immensely successful. Encouraged by the success of the food court shop, he went on to purchase a new franchise in a street vendor area up the street. Even though the two franchises were close together geographically, he felt they catered to different markets; one to mall employees and mall-goers, and another to passers-by who had no intention of going to the mall.

Joe thought the sales patterns of his sales at the food court would help him forecast sales of his street location, and thus help him determine quantities of various products to order. Particularly, Joe wanted to forecast the number of drinks he would sell in each period and the percent of sales that are cold drinks.

Therefore, Joe collected data from both franchises and decided to do an analysis to see if he could design a forecasting model for each location. He was also interested in analyzing buying patterns in both franchises to see if there were any similarities or differences. The data is found in DrinkSales.txt.

Your task is to predict sales patterns using simple linear regression. You will import the comma delimited file into a SQL database table that has marketing research data on drinking patterns and perform some basic analyses on this data by creating a scatter plot. After you analyze this data you will use simple regression to try to predict drink patterns.

Deliverables

Submit the YourName_Lab5_Questions.docx to the Week 5 iLab Dropbox.

Section Deliverable Points

Part A Import comma delimited file and create SQL 2008 table. 10

Part B Create scatter plots on drink sales. 20

Part C Regression analyses on drink sales. 30

iLAB STEPS

Preparation

1. Download the Week5_Lab5_Questions.docx from DocSharing. You will answer the questions and provide screen prints as required for each part of the lab.

2. Create a folder on your local drive named Transfer.

3. Download the Week5_DrinkSales.txt file from DocSharing to your Transfer folder.

4. Login to the Citrix remote lab.

a. Follow the login instructions located in the iLab tab in Course Home.

b. Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course Home.

Part A: Import your DrinkSales.txt File into an SQL 2008 Database

STEP 1: Problem Description

Import your DrinkSales.txt file, a comma delimited file, into a SQL 2008 database.

STEP 2: Use the SQL Import Wizard

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 for the Data Source the Flat File Source in the combo box.

c. Browse to your Transfer folder. When the Client File Security pop up window appears, select Full Access and then click OK.

d. Navigate to your Transfer folder, select your DrinkSales.txt file and then click Open to select this file for the File Name area.

e. Check the Column names in the first data row on the Choose a Data Source screen.

f. Your screen should look similar to the following:

g. Click Next until you get to the Choose A Destination screen.

h. Leave the default SQL Server Native Client 10.0 for Destination.

i. Insert into the Server Name combo box your server: BIS445SQL2008

j. 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).

k. Your screen should look similar to the following:

l. Click Next to get to the Select Source Tables and Views screen.

m. Click Edit Mappings on the Select Source Tables and Views screen. Change the Type column to get more meaningful SQL field types.

n. Your field type selections should look similar to the following:

o. Take a screen shot of the above Column Mappings screen for your Lab 6 questions.

p. Click OK on the Column Mappings screen and the click Next.

q. On the Review Data Type Mapping screen, change both pull-down menus to Ignore:

r. Click Next twice and then Finish until you see the end of the SQL 2008 Import wizard. Note: You may receive a warning message indicating that data might be truncated due to the column length being reduced from 50 to 2.

Please disregard this and click OK since your Period column data length does not exceed 2 characters.

s. Take a screen shot at the end of the import wizard for your Lab5_Question area.

t. Open up SQL Server 2008 Management Studio and check your DrinkSales table to make sure everything was imported into your new table correctly.

u. Close your Microsoft SQL Server Management Studio when you have verified the import wizard created correctly your DrinkSales table.

STEP 3: Sample Output

Your results at the end of the import wizard should look similar to this.

STEP 4: Deliverable

Using the Lab5_Questions.docx, paste your screen shots in the appropriate areas.

Part B: Create scatter plots of the period number and drink sale information.

STEP 1: Problem Description

Create a scatter plot of the period number and food court number of drinks sold, and then a scatter plot of street drink sales.

STEP 2: Use the SQL Import Wizard

a. Open your Citrix Laboratory, click the icon for Microsoft Office 2010 Applications, and then select Microsoft Excel 2010.

Note: You Must Use Microsoft Excel 2010 In The Citrix Environment.

b. Click the Data ribbon, then select the From Other Sources icon, and click the From SQL Server option.

c. Insert into the Server name of the Data Connection Wizard the following: BIS445SQL2008

d. Click Next and then select the BIS44_CoffeeMerchant database and your DrinkSales table. Your Select Database and Table screen should look like the following:

e. Click Next and then Finish.

f. Your Import Data screen will then appear and accept the default information:

g. Click OK. Your Excel Spread Sheet should look like the following:

h. Create a scatter plot of your food court drink sales. Start by highlighting the left two columns in your Excel spreadsheet:

i. Go to the Insert ribbon and put your mouse over the Scatter diagram item:

j. When you put your mouse over the Scatter diagram icon, you will get a pop up window. Select the top left scatter diagram format which is titled, Scatter with only Markers:

k. After you select this format, a scatter diagram should appear on your spreadsheet. Move this scatter diagram so that it does not overlap with your data:

l. Now you need to format the title and axes of your scatter diagram. Make sure that you have selected the Layout ribbon. You should see the Axis Titles menu item, select it:

m. Select the primary horizontal axis title option, and then select the Title Below Axis option. A textbox should appear below the X axis where you can type the name of the axis: Period.

n. With the chart still selected, go back to the Axis Titles option on the Layout ribbon and choose Primary Vertical Axis Title. Then select the Rotated Title option. A textbox should appear to the left of the y-axis. Place your mouse inside this textbox and type the following: Number of Drinks Sold.

o. Now you need to fix the title of the scatter plot. Go to the Layout ribbon again and put your mouse in the Title textbox. Change this textbox to read Food Court Number of Drinks Sold by Period [Your Name]. Your scatter plot should look similar to the following:

p. Take a screen shot of this scatter plot and paste it in your Lab5_Questions.docx Section A area.

q. Create a second scatter plot which shows the Street Number of Drinks Sold by Period. For the second scatter plot, select column A data and then hold CTRL and click and drag to select column C data.

r. Go to the Insert ribbon and select the Scatter diagram icon. Create a scatter diagram for the Street Vendor Number of Drinks Sold by Period data that you have selected. Use the Layout ribbon to create the necessary axis titles and format the title appropriately.

s. Take a screenshot of your second scatter plot and paste it in your Lab5_Questions.docx in the appropriate area.

STEP 3: Sample Output

Your results at the end of the second Scatter plot diagram should look similar to this.

STEP 4: Deliverable

Using the Lab5_Questions.docx, paste your screen shots of your scatter plots in the appropriate areas.

Part C: Create regression analyses on drink sales.

STEP 1: Problem Description

After creating scatter plots on your drink sale data you will conduct regression analyses on your drink sales data.

Step 2: Create the dimension and fact tables for your star schema data warehouse.

a. Review your scatter plots to get a feel for how drink sales have been changing over time.

b. Before you do your linear regression analyses, you need to make sure the Analysis Tookpak is installed on your machine. To verify this, go to the Office button at the top left of your screen and left click it. The following menu should appear:

c. Click on Excel Options at the bottom right of the screen. Then click on Add Ins that appears in the left hand area. You should see the following screen:

d. Verify that the Analysis TookPak is installed. If it is installed, it will appear in the Active Application Add-Ins. If it is not installed, then it will appear in the Inactive Add-Ins area. If the Analysis ToolPak is not installed, click on it in the Inactive Add-Ins section of the screen and then click Go. In the Add-Ins dialog select Analysis ToolPak and click OK. The Analysis TookPak should now appear in the Active Application Add-Ins area and in the Data ribbon.

e. Replace cell values A2:A11 with the corresponding number on your keyboard. For example, in cell A2 enter new value of 1, in cell A3 enter a value of 2, etc. Initially this will not appear to change any values; however, when the database was imported into excel additional data was imported into cells A2:A11 which would cause problems in later steps.

f. Select the Data ribbon, Data Analysis to open the following dialog:

g. Select Regression and press OK to produce this screen:p

h. In the Input Y Range textbox select the cells in B2 to B11:

i. Put your mouse in the Input X Range textbox and select the numerical period values:

j. Now select the Output Range Radio button, and then select cell $A$21. This indicates where Excel should put the linear regression output:

k. Press OK. You should get the following linear regression output describing the relationship between period number and food court num sales:

l. Take a screen shot of your above regression results and paste it to your Lab5_Questions.docx Section C area.

m. Repeat this process for the relationship between period number and street drink sales (repeat steps g through p). Your regression entry screen should resemble the following:

n. When you are finished, you should have two sets of regression output under your data.

o. Take screen shot of your final results.

STEP 3: Sample Output

Your final results should look similar to the following:

STEP 4: Deliverable

Using the Lab5_Questions.docx, paste your screenshots and answer the questions for Part C.