Create a data warehouse using a star schema and then analyze data warehouse information.
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
1. 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.
2. Read the scenario and translate the requirements into specifications.
3. Connect to iLab.
4. Connect to BIS445SQL data source.
5. Connect to Coffee Merchant database.
Part A: Set up data by writing SQL script
STEP 1: Problem Description
Back to Top
Understand the usage of CAST function to find the quarter by dividing month number by three.
CAST(Month(Orders.OrderDate) -1) /3 + 1 as VARCHAR)
Study this SQL statement.
STEP 2: Prepare the SQL script to extract the data
Back to Top
a. Open your Citrix Laboratory, click the SQL 2008 Server Management Studio.
You will see a similar screen – where the Server Name is : BIS445SQL2008
b. On the Server name chose the right Server.
c. Browse to your available database, and find the Coffee Merchant database. Create a new query window for this database.
d. Study the following SQL script.
e. Run the SQL script, you should see the follwong result data set:
f. Check Columns to make sure you have the right data for data mining.
g. Your screen should look similar to the above screenshot.
STEP 3: Create data source connection for the report.
Back to Top
a. Open SQL 2008 Server Business Intelligence Development Studio.
b. Select File -> New Project….
c. Select Report Server Project
d. Type Report 4 as the report name.
e. Select an appropriate location to save your work.
f. Click OK to create the New Project file.
g. Move your cursor to the upper-right corner and click on Solution Explorer.
h. You should see the following screenshot, which depicts the Solution Explorer panel with the project name listed.
i. In the Solution Explorer, right click on Shared Data Sources and select Add New Data Source to define the data source that you will use by using data source wizard.
j. Click on Edit button.
k. Type in the Server information you have utilized to connect to the SQL2008 Server Management Studio databases and select BIS445_CoffeeMerchant from the Conntect to a database pulldown menu. If you do not know the server information, contact your instructor. For example:
l. Click on Test Connection, you should be able to connect to the right SQL Server data source and receive the following screenshot stating the test succeeded.
m. Click on the OK button three times to accept the connection wizard settings. You should now be connected to the data source.
STEP 4: Create a parametric, metric report for data mining
Back to Top
a. In the Solution Explorer right click on Reports.
b. Select Add New Report to open the Report Wizard.
c. Click on Next button.
d. Click on Next button again. Then, paste the following SQL Script into the Edit Panel:
e. This query is modified from Step 2, by adding the “@state” parameter at the end of the script.
f. Your screen should look like this:
g. Click on Next Button, then, select Matrix radio button.
h. Then, click on the Next button. Then, make the following selection:
i. Click on Finish button twice. You should have the design panel shown as the following.
j. Click on Preview tab; Type in PA in the State textbox; then, click on View Report button.
k. You should see the PA state sales pattern.
Lab 4 Student Answer Sheet
Student Name:
Using Lab 4, answer the questions and provide copies of your SQL Statements and results for each Section listed below
Step 1:
a. Print Screen of the SQL Statement
b. Print Screen of SQL Results
Step 2:
a. Print Screen of the SQL Statement
b. Print Screen of SQL Results
Step 4:
a. Print Screen of the SQL Statement
b. Print Screen of Report Results
Answer the following:
• Do item C sales increase from 2005-4 to 2006-1 for state of Illinois?
• Does item T sales increase from 2005-4 to 2006-1 for state of Illinois?
• Does the total sales of state of Illinois increase from 2005-4 to 2006-1?
• Do item C sales increase from 2006-1 to 2006-2 for state of Illinois?
• Does item T sales increase from 2006-1 to 2006-2 for state of Illinois?
• Does the total sales of state of Illinois increase from 2006-1 to 2006-2?
• Do you find that overall, almost all of the states if the 2006-2 sale exists, that 2006 second quarter sales are much lower than regular?
a. Why from your observation?
• Can we make a conclusion by saying that the item type T sales are better than item Type C?
• Can we make a conclusion by saying that 2005 last quarter sales are better than 2006 first quarter?