ISQS 6339 Exercises

 

If not specified for a hard copy,

Please direct your electronic submissions of exercises to the Blackboard system

 

Instructions for Setting up Computing Resources

o    Add access to local drives in Parallels for Mac

o    How to Open a SQL File in Parallels on Windows

o    Mapping network drives

o    Install Parallels

 

No:

Exercise Contents

Due

BigData-EX3

Tasks:

1.     Implement a demonstrative Hadoop application. 

a.     Set up a Hadoop/Spark platform

b.    Install a NoSQL

c.     Load data

d.    Use either Tableau, Pentaho, or QlikView to analyze the sample data in the system

2.     Complete the term project report. The report will contain the following parts

a.     Cover page. Including the team’s name, team members, title of the report, course number and name, and the date the report is submitted.

b.    Table of contents

c.     Introduction section.

d.    Data mart development. This part only focus on the Hadoop part.

e.     Data mart populating section, mainly ETL.

f.     Analysis report section

g.    Summary section of the project, 1-2 page. It may contain: what you have learned, the top three comments on this project, the progress of the project regarding the time schedule.

h.     Reference list. If a reference is from the Internet, you need to list the title of the page in addition to the url address.

A hardcopy of the report will be submitted for grading, plus an electronic copy uploaded to the Blackboard system with a subject title: “ISQS 6339 Term Project – Team #”. The file name must be: “ISQS6339 Team # - term project report”.

 

Team assignment.

Written Report each team by May 12

BigData-EX2

Teams will meet the instructor before the scheduled presentation for 1-2 hours. Every team member must talk to the instructor in the meeting.

 

Each team MUST install a Hadoop application for the project and demonstrate it in the presentation. For two teams presented on April 27, this demonstrative system could be completed by April 30 before midnight. 

 

Deliverables:

 

Before the presentation

1. Three questions/answers for classmates to review for the final exam.

2. Reference materials

 

After presentation

1. Demonstration videos for some application product or application case.

2. Slides

3. Software resource information

 

All document will be uploaded to the Blackboard system.

Team assignment.

At least 2 days before the presentation

BigData-EX1

Install a copy of Hadoop/Spark onto a personal computer. Follow the instructions in the slides in the network drive under \References folder.

 

A single screenshot showing the success of the installation will be uploaded to the Blackboard system.

Individual assignment.

Due May 8, before midnight

EG-EX4

SAS Enterprise Guide (4)

 

Market Segmentation


1. Following the case in Chapter 4 of GACS, page 4-7 to 4-43 (Section 4.1 to 4.3)

2. Optional: Page 4-44 to 4-80 (Section 4.4 to 4.5)

Upload the screenshots to the Blackboard system

Dataset for quiz 5

Due: 5/2 before 2:00p

EG-EX3

SAS Enterprise Guide (3) (Optional)

 

In-class exercises:

1.     P 5-9 to 5-14 Summarizing Data

2.     P 5-37 to 5-47 Creating a Tabular Summary Report

3.     P 5-53 to 5-59 Creating a Bar Chart

 

Take-home exercise:

P5-70 to 5-76, Chapter 5 Exercise 1, 3-5 (Skip the creating format part)

 

Upload the screenshots to the Blackboard system

Due: 4/27 before 2:00p

EG-EX2

SAS Enterprise Guide (2)

 

In-class exercises:

1.     P 4-17 to 4-23 Selecting Columns and Filtering Rows

2.     P 4-27 to 4-29 Creating a Column with an Expression

3.     P 4-37 to 4-43 Creating a New Column by Recoding Values

4.     P 4-51 to 4-56 Joining Tables

 

Reference: How to create variable parameters with EG 6.1

 

Take-home exercise:

P4-68 to 4-71, Chapter 4 Exercise 1-5

 

Upload the screenshots to the Blackboard system

Due: 4/23 before midnight

EG-EX1

SAS Enterprise Guide (1)

 

In-class exercises:

1.     P 2-29 to 2-36 Adding Data from a Fixed-Width Text File

2.     P 3-13 to 3-19 Creating a listing report

3.     P 3-26 to 3-30 Generating a One-Way Frequency Report

4.     P 3-35 to 3-37 Creating a Two-Way Frequency Report

 

Take-home exercise:

a.     p2-51, Chapter 2 Exercises

  1. p3-49 to 3-52, Chapter 3 Exercise 1, 2, and 4

Upload the screenshots to the Blackboard system

Due: 4/23 before midnight

EX7

Customizing a cube - Optional

 

·         Project name: MMMCube2017_lastname

·         Tasks

o    Define and debug calculations (optional)

o    Define KPIs (optional)

·         Deliverable – upload the following to the Blackboard system:

o    Screenshot of the results from the cube: calculated measures, KPIs, and drill down/up results.

 

Due: 4/18 before midnight

EX6

Creating an OLAP cube

 

·         Project name: MMMCube2017_lastname

·         Tasks

o    Add in new date items (year, quarter, and month) to two fact tables

o    Create time dimension using Manufacturing Fact table

o    Define calculated measures (Total Products, Percent Rejected)

o    Define hierarchies of attributes in dimension tables

o    Create a cube from the MaxMinManufacturing data mart with hierarchical date dimension

o    Deploying and processing the cube created

 

·         Deliverable – upload the following to the Blackboard system:

o    Screenshots: dimension hierarchies, dimensions, relationships of facts and dimensions, deployment result, format of measures, and browsing results.

 

Due 4/18 before 2:00p

EX5

Populating Maximum Miniatures Manufacturing Fact Tables

 

Guidelines: http://zlin.ba.ttu.edu/BI/Exercise%205%20Instructions.htm

 

Due: 4/14 before midnight

EX4

Populating Maximum Miniatures Manufacturing Dimension Tables

 

Guidelines: http://zlin.ba.ttu.edu/BI/Exercise%204%20Instructions.htm

 

Due 4/11 before 2:00p

EX3

Creating a data mart with SSAS

 

·         Learning Objectives

o    Learn an alternative way to create a data mart

o    How to deploy a data mart

·         Tasks:

o    Create data mart MaxMinSalesDM_yourname with Visual Studio from a cube template (See the video)

o    Deploy the data mart

·         Deliverable – upload the following to the Blackboard system:

o    The screenshot of the cube structure and the success of the deployment

 

·         Reference: pp119-130 in textbook DW.

q  You need to create a new database MaxMinSalesDM_yourname. Then check the instructions in pdf files: MaxMinSalesDM (not complete version) in \References of the shared network drive.

 

Measures in the fact table:

Measure Name

Measure group

Data type

Sales in Dollars

Sales Information

Currency

Sales in Units

Sales Information

Integer

Sales Tax

Sales Information

Currency

Shipping

Sales Information

Currency

 

Dimension tables:

Customer, Product, Promotion, SalesPerson (SCD), Store

For manually added columns, see file MaxMinSalesDM_more.pdf in \references in the shared network drive for the class.

 

Due: 4/7, before midnight

EX2

Creating a data mart with SSMS

 

·         Learning Objectives

o    How to design a dimensional model

o    How to create a data mart with SSMS

o    How to create a cube for a data mart.

 

·         Tasks

o    Manually create the fact table and DimProduct table using SSMS (see the detailed information from file DW_MMM.PDF in the shared directory under \References)

o    Import remaining tables from oredb2.lin.mmm.empty

o    Define the primary keys of tables and the relationships among them

o    Create a cube

 

·         The primary key of the fact table is composed of three foreign keys plus one time dimension key: ProductCode, BatchNumber, MachineNumber, and DateOfManufacture. See DW_MMM in \References in the shared network drive for the class.

 

·         Deliverable – upload the following to the Blackboard system:

o    The printout of the screenshot of the cube structure

 

Due: 4/4 before 2:00p

EX1

Walk through data warehousing process

 

·         Learning Objectives

o    To gain a general impression how to use SQL Server to implement a data mart

·         Tasks

o    Create your database with SQL Server Management Studio (SSMS), named as ISQS6339-2017-lastname

o    Find dataset Commrex_2011.xls in the network drive ~\isqs3358 under \datasets, and  import data into the newly created database (excluding the table of transact) 

o    Define primary keys of the data in SSMS

o    Create a SSAS project using SQL Server Visual Studio

o    Define data source, data source view, and cube

o    Deploy the cube and browse it using the cube browser in Visual Studio

 

·         Deliverable – upload the following to the Blackboard system:

o    One-page printout of the screenshot of the cube diagram saved in a Word file. Upload the file to Blackboard system.

 

Due: 3/31 before midnight