CMPT 459 (00-3): Assignment 1

(50 marks)

Due time and date: 13:30 on October 11, 2000, Wednesday.

Please hand in your assignment to the instructor.

Submission Requirements

• All answers must be computer generated (including text and diagrams).
• The hand-in version must be ordered correctly and stapled in the top left corner.
• The hand-in version must include a header page indicating: student name, student number, user id, course number and assignment number.

Part 1: Exercise Assignment (28 points, 7 points for each question)

• Question 1: Exercise 1.8 on page 35 in the text book.
• Question 2: Exercise 2.7 on page 101 in the text book.
• Question 3: Exercise 2.12 on page 102 in the text book.
• Question 4: Exercise 5.7 on page 223 in the text book.

Part 2: Program Assignment (22 points)

1. Warm-up Question (4 marks)

Get Familiar with Multi-Dimensional Model.

• Required Setup:

  (1) In Windows Explorer, go to the location "C:\win32app\OLAP Services\Bin". Double click on the file "msolap.msc". Please wait for a couple of seconds.

  (2) When the OLAP manager is ready for use, read through the OLAP Manager Tutorial. Only focus on the OLAP Manager Basic Tutorial. The tutorial is also started up from the HTML file at "C:\win32app\OLAP Services\Help.

   

• Tasks: Follow the steps illustrated in the basic tutorial to create a data cube called Sales. 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 datebase file from "W:\OLAP_Sample\FootMart.mdb". Please don't use other names. Or you could not connect to the OLAP server.
  

  (2) When you are required to supply the new database name, instead of using Tutorial, use your account id, such as jsmith.
  

  (3) When you are required to set the data source connection, select FoodMart or foodmart instead of Tutorial.
  

  (4) 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 peijian in the step 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.

   

2. Further Question (18 marks)

Create a Data Cube by Your Own (10 marks)

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

Browse the Data Cube Inventory (8 marks)

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