ISQS 6339 Data Management and Business Intelligence (2017)

 

Project Instructions

 

(Subject to further modifications)

 

Instructor: Zhangxi Lin

 

Final Project Report Due: May 12, Friday

 

There will be two data warehousing projects:

A.    Data warehousing using MS SQL Server

B.    Data warehousing using Hadoop

Two data ware houses can share the same data source.

 

In this project there are 6-7 teams each with 3-5 students to fulfill two big data warehousing projects, and one Hadoop collaborative study.

A completed data warehousing project must address the following topics:

1.     Data mart planning

  1. Data mart design (must contain techniques of SCD, surrogate keys, and hierarchies)
  2. ETL system (must show how to use SAS programming in data cleansing and transformation)
  3. Data visualization (Use SSAS for KPIs and calculations; use SAS Enterprise Guide for OLAP and reporting)
  4. Reporting
  5. A data mining example using the functions in SQL Server (optional)

The following are Hadoop collaborative study topics

No:

Topic

Components

Team#

Discussant

1

Data warehousing

Focus: Hadoop Data warehouse design, MapReduce algorithm

HDFS, HBase, HIVE, Kylin NoSQL/NewSQL, Solr

DW1

DW2

2

Big data ETL

Focus on applications: Heterogeneous data processing across platforms

Kettle, Flume, Sqoop, Impala, Chakwa. Dremel, Pig

DW2

DW1

3

Big Data Visualizations

Focus: Features for big data visualization and data utilization.

Pentaho, Tableau

Saiku, Mondrian, Gephi,

DW3

DW4

4

Bid Data Analytics

Focus: Efficiency of distributed data/text mining

Mahout, H2O, R, Python, Tableau, QuikView

DW4

DW3

5

Streaming data processing

Focus: Efficiency and effectiveness of streaming data processing

Spark, Storm, Kafka, Avro

DW5

DW6

6

Realtime data analytics

Focus: Efficiency and effectiveness of real-time data processing

Spark, Storm, Kafka, R, QlikView

DW6

DW5

 

Tasks in big data collaborative study:

1) Define the scope of the topic to include more detailed functions the products will perform

2) Identify the latest available products and describe their functions. Add them into the list to up to 10 products and order their popularity from high to low.

3) Suggest the products in your list that could be used by other teams.

 

The project assessment will be based on several factors:

Completeness of the project, timeliness of submission, difficulty of the topic and data, amount of effort made, workload in data preprocessing, good understanding of course knowledge.

 

The project includes deliverables in four stages:

 

-----------------

Stage 1 (10%): Project proposal. The selection of the project mainly relies on the availability of the dataset and your understanding of the business story about the data. The deliverable is a project proposal in 2 pages, containing (1) Project topic, (2) The availability of the dataset, (3) analytic themes, (4) A conceptual dimensional model, and (5) A list of references. Due April 11, Tuesday.

 

FAQ:

What does “The availability of the data set” mean? You need to confirm the data set used for the project is available

What is “analytic themes”? They are business needs or requirements for the project. That is, what the purposes of the data mart are from the business perspective. You can find the cases in BI-04-DimensionalModel.ppt and BI-05-MMM.ppt. If you have the optional textbook “Delivering Business Intelligence” or “The Microsoft Data Warehouse Toolkit”, you can find relevant information in the book to get the clue. 

What is “A conceptual dimensional model”? It is the draft of the dimensional model in a diagram format to be implemented later.

 

-----------------

Stage 2 (20%): SQL Server data mart implementation. Refer to your data mart exercises, the data warehousing textbook, or the online materials for the tasks in this stage: (1) dimensional model design (at least four dimensions), and (2) data mart creation. The description of the data mart will be 3-5 pages including figures or screenshots of the designed model. Due Midnight, April 21, Friday.

 

Contents of the deliverables:

1.     A dimensional model diagram. This must be the refined version from the “conceptual dimensional model” in Stage 1.

2.     Screenshots of design pages for each table from SQL Server Management Studio.

3.     A brief description of the considerations in the dimensional model design.

a.     How time dimension is defined? In what granularity?

b.     The hierarchy within a dimension, if any. (For example, State, county, city, and street are in a hierarchical structure).

c.     The aggregate dimensions, if any. (For example, in MaxMinManufacturingDM, DimMachineType is an aggregate from DimMachine).

d.     Indicate the primary key and foreign keys in each table.

e.     Others.

4.     Populated data mart

5.     SSIS applications: ETL

6.     SSAS applications: OLAP Cube and drilling results

 

-----------------

Stage 3 (30%): Collaborative study

April 27 – May 4

This is actually BigData-EX2.

 

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

 

---------------------

Stage 4 (40%): Hadoop sub-project. Due: May 12, Friday.

 

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

 

-----------------------------------------------