Project2

COSC 6340 Project2
OLAP, Decision Trees, and Clustering
using MS SQL Server 2000

This project centers on using OLAP and data mining technology for a FoodMart database. It is a group project and groups should subdivide the work between their members, but it is recommended that each students does Part1 on her/his own (you can help each other) to get familiarized with OLAP.

In the second part "interesting" data analysis problems for the FoodMart database have to be identified and the MS SQL Server 2000 model generation functions will be used to solve the problems your group selected. Moreover, if you have any doubts about selecting "good" data analysis problems, feel free to visit Dr. Eick during his office hour. In general, Haili Tu is be responsible for Part1 and Part2 of the project, and Dr. Eick is responsible for Part2 and Part3 of the project. Moreover, if you find a "very interesting" problem with respect to the FoodMart database that does not completely matches the task specification (given below), feel free to discuss the the "alternative" task with Dr. Eick in order to determine its suitability for the project.

Moreover, the results of the project have to be summarized in a report and each group is giving a 12 minute presentation about their project results in the last week of the semester.

Part1: OLAP

1.1: Get Familiar with the Multi-Dimensional Model.

Required Setup:
(1) from start -> program -> microsoft SQL Server -> Analysis Services -> Analysis Manage. when Analysis Manager is on, click on Analysis Manager Concepts and Tutorial -> Quick Start.

(2) read through the Tutorial.
Tasks: Follow the steps illustrated in the basic tutorial to create a data cube called Sales in a new database. In this process, make the following changes.

(1) In the step of Setting up Your System Data Source Connection, instead of using Tutorial, choose FoodMart or foodmart if it is already existing there. If not, add a new data source called FoodMart or foodmart and select the database file from "microsoft Analysis Services\Samples\FoodMart.mdb".
(2) Create a new database first by using your account id, such as jsmith as the database name.
(3) following the tutorial, create a new Data Source using name of FoodMart or foodmart instead of Tutorial. When you are required to set the data source connection, select FoodMart or foodmart.
(4) creat a new cube named sales. In the step of Adding Measures to the Cube, select the fact table Sales_fact_1998 instead of Sales_fact_1997.
(5) Add yourself as well as haili in the step of Adding Roles to the Cube. The Role name is called Manager.
Submission:

(1) Understand how to obtain the metadata about a cube. Submit the metadata for the data cube Sales.
(2) Understand how to obtain the schema diagram for a cube. Submit the schema diagram for the data cube Sales.

1.2 Create a Data Cube by Your Own

Tasks: Under the same data base name created in Question 1, i.e., your account id, create another data cube called Inventory according to the following specifications.

(1) The fact table is inventory_fact_1998. The measures are: Units Ordered, Units Shipped, Warehouse Sales, Warehouse Cost.
(2) The dimensions are:
- w_Product using multiple tables product and product_class. The levels for the dimension are all, Product Category, Product Subcategory, Product Name,
- Warehouse using single table Warehouse. The levels for the dimension are all, Warehouse Country, Warehouse State Province, Warehouse City,
- w_Time using single table time_by_day. The levels for the dimension are all, Year, Quarter, Month, Day,
- w_Store using single table store. The levels for the dimension are all, Store Country, Store State, Store City.
(3) The roles for the cube should be yourself and peijian. The Role name is also called Manager. You can borrow it from cube Sales you created in Question 1.
(4) The type of the data cube is MOLAP.
Submission:

(1) Submit the metadata for the data cube Inventory just created.
(2) Submit the schema diagram for the data cube Inventory.

1.3 Browse the Data Cube Inventory

Tasks:

(1) Understand the steps in the basic tutorial on how to browse the data in a data cube.

(2) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product fruit in the stores in BC and the warehouses in BC in the third quarter in 1998.
(3) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product Bologna (a kind of meat) in the stores across USA and the warehouses in Seattle in the second quarter in 1998.
(4) Find Warehouse Sales, Warehouse Cost, Units Shipped and Units Ordered for product Moms Beef Bologna in the stores across USA and the warehouses in Seattle in the second quarter in 1998.
(5) Find Warehouse Sales and Units Shipped for product meat in the stores across USA and the warehouses in state WA in the second quarter in 1998.
Submission:

(1) Submit for each query the steps you took in order to get the result you wanted.
(2) Submit for each query the result you obtained.

Part2: Create a Decision Tree Data Mining Model

Analyze the schema of the FoodMart Database. Identity 2 "interesting" attributes in the database. Using MS SQL server 2000 2 models (one for each attribute) to predict the value of each attribute. Evaluate the goodness of your prediction. Looking at the decision tree you obtained what can be said what is important for predicting the particular value of this attribute? Extra credit will be given, if this prediction is done accross at different levels of granularity with respect to the available dimensions of the chosen data analysis problem. Also feel free to create additional data cubes to facilitate conduction the project.

Part3: Create a Clustering Model

Analyze the schema of the FoodMart Database and identify a class of objects in the database that are "interesting" to be clustered. Create a MS clustering model for this class of objects. Interpret the clusters; what can be said how the objects belonging to a particular cluster? Are the result of the clustering algorithm stabil?

There should be a single, comprehensive report per group. In addition to presenting your results of Part1, Part2, and Part3 your report should address the following questions (if your group members disagree state the majority opinions and minority opinions when aswering the questions below):

Did you find anything unexpected while doing Part2 and Part3.
Evaluate the graphical features to view data cubes. Do you believe that OLAP really helps data analyst to "making sense out of data"?
Give a evaluation of the MS SQL Server 2000 OLAP, decision tree, and clustering capabilities.
Give a summary what percentage of the group's time was spent conduction a particular task, for report writing, and for talk preparation.
Did you like the project?
Which database system did you like more (and why?): MS SQL Server or Oracle 8i?

Deadlines:A group reports are due on Tu., April 23 and group presentations are scheduled during the Th., April 25 class.