BIS 445 DeVry All Week I Labs

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

http://wiseamerican.us/product/bis-445-devry-week-ilabs/

Or

Contact us at:

SUPPORT@WISEAMERICAN.US

BIS 445 DeVry All Week iLabs

BIS445

BIS 445 DeVry Week 1 iLab

Part a: Using action queries in SQL

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

You have been requested to import a comma delimited text file into an existing SQL 2008 database. Using the SQL 2008 Import Utility, you will create field mappings for a new SQL 2008 table and then upload your text file into this new table. After you upload your text file, you will perform some action queries to add, delete, and modify data in your new table.

Upon completing this lab, you will be able to:

Import text file data into an existing SQL 2008 database and create a new SQL 2008 table.

Use action queries to add, delete, and modify data.

Deliverables

Submit the YourName_Lab1A_Questions.docx to the Week 1 iLab Dropbox.

Section Deliverable Points
Part A Import comma delimited text file. 6
Part B Review uploaded data. 6
Part C Add records into a table. 6
Part D Delete records in a table. 6
Part E Modify records in a table.  

 

BIS 445 DeVry Week 2 iLab

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

iLAB OVERVIEW

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

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.

Deliverables

Submit the YourName_Lab2_Questions.docx to the Week 2 iLab Dropbox.

 

BIS 445 DeVry Week 3 iLab

Using Enterprise Data to Create Pivot Tables

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

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.

Deliverables

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

 

iLAB STEPS

Preparation

Back to Top

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.

 

BIS 445 DeVry Week 4 iLab

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

iLAB OVERVIEW

Scenario and Summary

The management of the Coffee Merchant needs to find the sales pattern by data mining the sales data. The purpose of the data mining exercise is to find any of the sales patterns. In particular, the senior management team is interested in the quarterly sales reports by different sales region. From the experience, the team thinks that each region would have different product needs at a different quarter. As a sales analyst, you need to apply the data mining techniques using SQL Server Reporting Services.

Your assignment is to create a parameter, matrix report, and explain the report to find sales a pattern at a different region on a different fiscal year quarter.

Deliverables

Submit the YourName_Lab4_Questions.docx to the Week 4 iLab Dropbox.

Steps Deliverable Points
Step 1 Translate the business requirements into operational data mining specifications. 10
Step 2d to Step 2h Find the right tables and columns from data warehouse (Coffee Merchant database). 10
Step 4 Create a parametric, metric report. 10

 

Final Deliverable

Lab 4 Student Answer Sheet

30

iLAB STEPS

Preparation

Back to Top

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

Read the scenario and translate the requirements into specifications.

Connect to iLab.

Connect to BIS445SQL data source.

Connect to Coffee Merchant database.

Part A: Set up data by writing SQL script

STEP 1: Problem Description

Back to Top

 

BIS 445 DeVry Week 5 iLab

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

Back to Top

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.

Create a folder on your local drive named Transfer.

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

Login to the Citrix remote lab.

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

 

BIS 445 DeVry Week 6 iLab

Part a: Create a BIDS Analysis Services Project

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

You have been asked to analyze the Redwood database by using multidimensional techniques. One way to do this is to create a data cube that represents data in various dimensions (attributes) along with various measures over a period of time.

Upon completing this lab, you will be able to:

Create a BIDS Analysis Services project

Define a data source and view

Define dimensions and attributes

Define a cube

Deliverables

Submit the YourName_Lab6A_Questions.docx to the Week 6 iLab Dropbox.

Section Deliverable Points
Part A Define a BIDS Analysis Services project 5
Part B Define a data source 5
Part C Define a data source view 5
Part D Define dimension(s) and add attribute(s) to dimension(s) 5
Part E Define a cube using measure(s), dimension(s), and time 10

 

iLAB STEPS